A productivity application from Google that allows you to create and edit worksheets

learn more… | top users | synonyms (2)

1
vote
1answer
84 views

Freeze first row of Google spreadsheet when displayed in Google site

When a Google spreadsheet is displayed in a Google site window, the complete table being displayed moves when the displayed window is scrolled. The top row containing headers moves out of view. This ...
5
votes
1answer
11k views

Create a Google Sheets chart with multiple data ranges with separate key columns

I have this data: X1 Y1 X2 Y2 1 20 1 25 2 20 3 42 3 20 6 50 5 25 8 50 10 30 10 50 I want to create ...
3
votes
2answers
3k views

How to interpolate data in a range in Google Sheets

I have an array with data: X Y 3 50 5 60 9 120 11 130 18 90 20 150 The data is entirely non-linear. X is guaranteed to be sorted. Now for any given ...
0
votes
1answer
89 views

How do you calculate the min date between a cell and TODAY() in google spreadsheet, in a single step?

I need to calculate the minimum date between a cell and TODAY, but somehow the MIN function is failing me. Here's the demonstration. If I calculate it on a range, such as =min(A1:A2) it works, but I ...
0
votes
1answer
101 views

How do I sort several columns by a list of values?

In Google Sheets, I have several columns: Industry | Company | Country | Date Last Visited And I want to order them by Country, then by Date Last Visited, then by Industry, then by Company so that ...
1
vote
1answer
429 views

Sum of dynamic number of values derived from fomula

In short my question is this. If I use the SUM function (or some other function) that can take one or more arguments, is there some way I can pass in these arguments as an array which varies over some ...
1
vote
1answer
347 views

Conversion from Excel to Google Spreadsheet - sum across sheets

I'm importing an Excel spreadsheet into Google Drive. Its a huge spreadsheet, and overall Google spreadsheets has converted everything, except the following: There are a few cells with formulas like ...
4
votes
1answer
720 views

Case insensitive character string filter for multiple individual columns (dynamic input for each)

I'm looking for an efficient way to filter a data set by multiple individual, case insensitive character strings for multiple columns in a Google docs spreadsheet. I can already achieve this by ...
1
vote
2answers
399 views

New Google Sheet has different download URL? and errors?

I've been downloading the first sheet old Google Spreadsheets as a TSV file using urls like: https://docs.google.com/feeds/download/spreadsheets/Export?key=ID&exportFormat=tsv&gid=0 After ...
-2
votes
2answers
103 views

Interleave cells from two column ranges

I have a spreadsheet with words in columns A and B: TxtA1-TxtA2 TxtB1-TxtB2 TxtC1-TxtC2 How can I merge it into one column looking like this? TxtA1 TxtA2 TxtB1 TxtB2 TxtC1 TxtC2
2
votes
1answer
524 views

Conditional formatting across sheets

I have two sheets, SheetA for values, SheetB for calculations. I want a cell in SheetA to be colored, if a value in SheetB satisfies a condition. Normally, referencing a field from another sheet is ...
1
vote
1answer
413 views

What's wrong with this Google Apps Script code?

Forewarning: I'm a noob at JavaScript, with varying experience and less knowledge. I don't get why this code fails with TypeError: can't call method "getSheetName"/undefined. (line 7, file "Koodi"). ...
1
vote
2answers
44 views

Adding a title to cell number 0?

It is good convention to name your columns, to put some kind of description in cell 1. However, this really bugs me, because then all my values are indexed starting at 2, so I think I'm dealing with ...
2
votes
1answer
119 views

How do I reference a cell from a Google Spreadsheet in a Google Document

I would like to create a Google Spreadsheet and reference a cell(-range) from that spreadsheet in a Google Document. Basically, I want the spreadsheet to be a variable holder where I can input data ...
3
votes
1answer
340 views

Is GOOGLETOURNAMENT defunct?

I do not follow the NCAA Division I Basketball Championship but am interested in the accuracy of current advice re Google Spreadsheets. One of the functions is GOOGLETOURNAMENT which, rumour has it, ...
2
votes
1answer
79 views

Is it possible to define user permissions on a sheet basis in Google spreadsheet?

I have a Google spreadsheet with 10 sheets. Is it possible to define user permissions on a sheet basis in Google spreadsheet? For example, on sheet 1, user X can edit but user Y can only view.
1
vote
1answer
30 views

Auto-Import Master Spreadsheet

I'm fairly new to Google Spreadsheets and completely useless when it comes to functions and formulas. Is there a way to take info from multiple sheets within a workbook and compile them into a ...
0
votes
1answer
429 views

Using VLOOKUP with dynamic range array

I have a sheet with a list of email address that is being auto-updated (more email addresses being added on a daily basis). I have a second sheet with a list of email addresses that have been ...
0
votes
1answer
11k views

How to format the date in this Google Docs script

I've come across this rank checking script. I'm not sure exactly where I found it but it's built on Google Docs and a script which automates the rank checking process. I understand a bit of coding ...
1
vote
3answers
2k views

How do you automatically align incoming form responses in Google Sheets?

I like to have each of my form responses aligned in the center of their cells. However when I submit any new information through Google Forms, they are always automatically aligned to the left, and so ...
2
votes
3answers
3k views

How do I eliminate extra line breaks in a Google Spreadsheet cell?

I have a spreadsheet in which some cells have multiple carriage returns. I would like to eliminate all the extras, so that 4 carriage returns shrinks down to just one. This question is similar to How ...
4
votes
1answer
438 views

Preventing paste from overwriting cell formatting in Google Sheets

I have a spreadsheet used for booking employees onto training sessions. It's very simple to use; the person booking simply enters their employee number into column A and column B, C & D ...
2
votes
0answers
113 views

Google Sheets Cells don't calculate when printed or viewing as PDF

Workbook: Very large workbook with multiple tabs of data and multiple tabs of reports using sumproduct, query and filter formulas. Payroll tab is a reflection of our master database so i'm not dead ...
1
vote
0answers
86 views

Send email from Google Spreadsheet to adress in column

From an existing Google Spreadsheet; every time a value is added in column M(x), I would like to send an email with a specified text to the address listed in column F(x) from the same row x. Which ...
0
votes
0answers
288 views

How would I use Google App Script to create a ListBox based on a spreadsheet entry?

Every month I receive a spreadsheet with tickets available with its code, expiration date and type. Code | Expiration Date | Type (3d or normal) ... ... ... ... ...
2
votes
1answer
1k views

How to make a protected range appear normal in Google spreadsheets

I have a project in Google spreadsheets shared with several other users. There are sensitive formulas throughout that I have protected to prevent accidental deletion or overwrite by the other users. ...
2
votes
0answers
169 views

How to Modify Matched Cells Across Multiple Sheets with Google Script

My question is a fairly complex one, but I'll do my best to clarify exactly what I'd like to do and this requires some background: I have a Google Spreadsheet used for user administration in my ...
4
votes
1answer
308 views

Google Spreadsheets showing “shared with xxx” count greater than actual?

For every one of my Google Spreadsheets, the sharing settings is on "Specific people can access", and each sheet is shared with 3 people I know. Their names are listed when I click on the blue "Share" ...
0
votes
1answer
39 views

List of range from another spreadsheet

I have two spreadsheets, The first sheet has a list of data companies company A company B comapny C In the second sheet i need to create a drop down menu to fetch the companies from the first ...
2
votes
2answers
9k views

Rename Column in Google Spreadsheets

Does anyone know how to rename a column in google spreadsheets? Seems easy enough in excel. https://productforums.google.com/forum/#!topic/docs/ewnE52j57YU I was hoping that ideally I could see what ...
1
vote
2answers
259 views

How can I select a sheet with a variable-supplied suffix in a script?

Here is the code I'm attempting: var day=new Date().getDay()-1; var month=new Date().getMonth(); var sheet=ss.getSheetByName((/^.\s/+month+'/'+day)); I'm new to regex, but essentially I'm writing a ...
1
vote
1answer
64 views

How do I move a Google Spreadsheet document from one Google account to another?

I created a moderately complex Google Spreadsheet document in my personal account, which become popular amongst my colleagues and is now officially adopted by our team. Therefore, it would make sense ...
1
vote
1answer
2k views

ImportHTML spreadsheet function to update every 5 mins

I have a Google Spreadsheet fetching stock exchange data using an ImportHTML function: =ImportHtml("http://www.xxxx.xx.xx/xxxxxxxx/livefeedfull.aspx/?"& year(now()) & month(now()) & ...
2
votes
4answers
2k views

Can I make a sheet private in a Google Drive spreadsheet?

I have a Google Drive spreadsheet with multiple sheets. The first sheet pulls data from all the other sheets. I need to share the first sheet with all users. But I only want to share the rest of the ...
1
vote
3answers
10k views

How do I get linked Google spreadsheets to auto-update when 'I am not looking' [duplicate]

My question is similar to this one, for which no answer has yet been posted. How can I get Google Sheets to auto-update a reference to another sheet? I am filling one Google Spreadsheet with Zapier ...
1
vote
2answers
114 views

Is it possible to prevent people from entering data into my Google Spreadsheet?

Is it possible that I fix the order of user's entries with a script? For example, the user must fill cell A1 first, only after that he can fill cell A2. Maybe my explanation is better with an ...
1
vote
1answer
81 views

Set Selection throughout all sheets

I have a spreadsheet with multiple sheets. I want them all to scroll in sync. For example if I scroll to J18 in Sheet1, all others sheets will have the cursor at J18 and same row/column at the top ...
0
votes
2answers
111 views

Conditional Formatting for Google Spreadsheets

I have created a Google spreadsheet. It has actual values and calculated values. I want the background to be green if the calculated value is less than the actual value and red if it is more. ...
1
vote
2answers
102 views

How can I prevent users from reverting to an old version of a Google Spreadsheet?

I would like to allow users to edit a Google Spreadsheet, but not be able to revert them to prior versions. Is this at all possible? USE CASE The spreadsheet we use keeps track of snacks consumed, so ...
4
votes
2answers
3k views

How do I sum all even or odd columns in Google Spreadsheets?

The following is given as a solution for Excel: =SUM(IF(MOD(COLUMN(B16:AO16),2)=0,B16:AO16,0)) =0 changes to =1 to toggle between odd and even columns. However, this doesn't work in Google ...
0
votes
1answer
125 views

Retrieve last entry via ImportXML

Looking for help pulling specific cells from a web site into a Google Spreadsheet. I want to pull "last" price from this page but only a few specific ones. Such as Dow, Nasdaq 100, S&P 500, ...
0
votes
3answers
77 views

How to calculate average screen reso. based on number of visitors using a spreadsheet?

I want to find out what the average screen resolution is for visitors to my website – or rather, what is the average width and the average height in pixels. To qualify what I mean by "average" – and ...
0
votes
1answer
77 views

Conditional counts based on dates

I am creating a master tracker for all the technology in my school and it is pulling data from many sheets and placing it in to blocks and I am having trouble figuring out the following ...
0
votes
1answer
53 views

How to match info between tabs in a Google spreadsheet

How can I take info from one tab and add it to another? For example, I have Tab 1 with column A—call it “cat breeds”—and I have Tab 2 with column A—call it “cat breeds”—as well. They need to match so ...
2
votes
1answer
2k views

How can I reference a cell in a custom function while setting up data validation in Google spreadsheets?

I made the following Google script to check whether a string is less than some length: function isLengthLessThan(length, text) { return (text.length < length) }; How can I set up data ...
3
votes
2answers
273 views

Single drop down with data from separate columns

I have a spreadsheet that I need to add new data to but the previous user made it so that I can't add data in a new row without moving down a lot of data which would mess with all the calculations. ...
5
votes
3answers
3k views

Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries)

I'm compiling a database of business address info, including key staff for each business. The Google spreadsheet I've inherited for this project includes a column for 'key staff' (in column B), with ...
2
votes
1answer
317 views

Is there a way to neatly format length in Google Spreadsheets across multiple orders of magnitude?

I have a spreadsheet with a column for length with values ranging from millions of kilometers to fractions of micrometers. Viewing it all in meters as unformatted numbers directly is awkward since you ...
0
votes
2answers
64 views

Which formula sums all values from a column in a line that contains a specific word?

Let's use this random spreadsheet to illustrate my problem. What I want to do is a sum of all Page Views (F) that contains the Category (B) named /Online Computers. Which formula can I use to do ...
1
vote
0answers
434 views

Yet Another Mail Merge - sending to multiple recipients

I'm required to send a parametrized mail message to multiple recipients, based on values in a Google spreadsheet. I've followed the instructions in this guide on how to use Yet Another Mail Merge. ...