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

learn more… | top users | synonyms (2)

1
vote
1answer
130 views

Referencing a range in a sheet with variable name in another cell

When I would like to import (query) many columns in another sheet I use this code: =QUERY(sheet2!A1:D1;;) Now I would like to make this formula a little dynamic making sheet2 variable. So I put the ...
3
votes
1answer
31 views

QUERY data based on Alpha Range in Google Sheets

I'm trying to figure out if I can pull data from a sheet in a Google Sheets Workbook based on an alpha range. For example. I only want data associated with 'last name' that start 'A' and goes ...
0
votes
0answers
87 views

google.script.run.withSuccessHandler not working

In my Google Sheet script's page.html, I have <input type="button" value="Apply" onclick="google.script.run.setAttributes('abc');"> where setAttributes() is a function defined in Code.gs. ...
1
vote
1answer
55 views

How should I automate graph generation and image output in Google Sheets?

I am trying to send graphs of data to email recipients. I would like to output images of row-specific graphs based on cells in a Google Sheets document. In combination with Google Sheets/Drive, I am ...
4
votes
1answer
118 views

Can I check that all cells in a column contain unique values?

I'm have a list of IDs in a column on Google Spreadsheets. I need to check: That there are no duplicates in the column Which cells contain the duplicates Is it possible to do this?
0
votes
1answer
25 views

Using time values in conditional statements with IF formula

Algorithm: if time is 9:00am, display 6 I used the if formulas but it keeps on displaying "x" Time is in 24-hour format. =if(G2=20,"6",if(G2=21,"12",if(G2=22,"18","x"))) ...
1
vote
2answers
556 views

Basic date manipulation in Google script

I would like to define a custom function in Google Sheet. This function should do something very simple, yet I have not managed to find how to do it. I am very confused with all the answers I read ...
1
vote
1answer
179 views

How to retrieve email address of current user?

In Google Spreadsheet I am having the following issue: I am using Session.getActiveUser().getUserLoginId() to fetch the current user's email id but other than creator of the spreadsheet it is ...
3
votes
1answer
227 views

Adding a Month to a Date in a Google Spreadsheet with a Script

I'm trying to create a Google App Script that adds one month to a date already on a Google Spreadsheet. In the example below I was trying to take the date from cell B2 (1/1/16) and add one month and ...
2
votes
1answer
164 views

How to automatically wrap text to new responses from a form in a Google Spreadsheet?

I had a working Google Spreadsheet where when new responses were filled in on the form, they would automatically get textwrapped on the Form Responses sheet. Now recently I had to remake this ...
3
votes
1answer
110 views

Use a Google Form to update an existing Google Sheet

Is there a way to update an existing sheet in a Google spreadsheet using a Google form? I only see how to update to a new tab (sheet) on an existing spreadsheet.
0
votes
1answer
416 views

INDEX-formula throws random #REF!-errors due to [illogical] column value out of range

I have built a spreadsheet to help me keep track of my spending during my holidays. Whichever currency I buy in, it converts the raw amount into my local currency (Malaysian Ringgit MYR). Here is how ...
0
votes
0answers
197 views

Importrange() Error: “Results Too Large”, how to work around?

I'm trying to Importrange() from a range containing 240,000 cells (40 columns, 6000 rows). The Importrange() function is erroring out with "results too large". What are the limitations of ...
4
votes
4answers
151 views

Split a column of strings with a single formula returning an array

I have a column of strings like this: +---------+ | a bb | | ccc d | | ee ffff | +---------+ (exactly one space in each string), which I'd like to split by the space into two columns: ...
1
vote
2answers
44 views

Function name to add rows in Google Sheets

Is it possible to add rows with a built-in function (such as MIN() or INDIRECT()) in Google Sheets? I would like to fill rows in the main sheet such as +---+----+--------+ | A | 18 | Atext1 | | A | ...
3
votes
1answer
33 views

How to turn a list of months string into a list of month numbers?

I have a table with month headers at the top: January | February | March | ... Is there a way with one formula that I can get a list of month numbers? January | February | March | ... ...
2
votes
1answer
65 views

Insert current date to cell based on content in other cell

Background: I am trying to create a sheet which tracks my costs on daily basis. Instead of entering the current date everyday, I want the sheet to store the current day based on the content I enter ...
2
votes
1answer
27 views

What is the difference between the DELTA and EQ functions?

The new Google Sheets includes the DELTA function, which Compare[s] two numeric values, returning 1 if they're equal. How does this differ from the EQ? From what I can tell, the EQ function ...
3
votes
2answers
50 views

Sum a column if another column contains any of the values

I have the following data in a google spread sheet. ColumnA ColumnB ======================= Bus 205 Train 10 Train 10 Fruit 10 I want to sum all ...
0
votes
2answers
27 views

How to sort spreadsheet by date+time column but with a modified hour column?

I have a spreadsheet generated by a google webform and it produces a standard spreadsheet with a full timestamp column that is two hours behind and a hour column (hh:mm am/pm) that the user fills in ...
1
vote
1answer
58 views

Drag and drop open csv in Google Sheets

The current process for opening a csv file in Google Sheets is cumbersome. Open Google Sheets > New Document > File > Import > Upload Tab > Drag in File > Select 'Replace current sheet' Would love ...
2
votes
1answer
52 views

Google Spreadsheets: Sum values for each month

I would like to sum the values that match each month in a spreadsheet. The spreadsheet looks like this: 2015-12-10 15 2015-12-19 10 2016-01-02 15 2016-01-05 25 And I want to obtain this: ...
2
votes
2answers
19 views

Set a formula for all cells in a column

I would like to use a same formula for cells in a column. Instead of writing it for all cells, is there a way to set it in a cell and tell to apply this formula to all cells in a column (or any set of ...
3
votes
1answer
51 views

How to change cell value, looping through a provided list and record result in Google Sheets

I have built a stock tracking worksheet on Google Sheets where if I enter XYZ symbol in a cell B1, the spreadsheet will pull in Google Finance data (sometimes very slow), do a series of calculations ...
1
vote
2answers
41 views

How do I sum a column of cells by date?

I have a spreadsheet with these data (all of these dates and amounts are arbitrary): A B 12/1/2015 700 1/7/2016 100 1/13/2016 200 2/5/2016 300 3/19/2016 500 and another ...
4
votes
2answers
177 views

An arrayformula to find the previous non-empty cell in another column

In Google Spreadsheets, without using Apps Script etc., is it possible to achieve the following with a single formula, such as arrayformula (or maybe mmult or something strange, but no fill-down) at ...
3
votes
1answer
72 views

Hide columns with certain dates on open

Got a sheet containing dates in the first row. I would like to hide on open all columns (some are already hidden manually) left of the column which date is closest to the current date
2
votes
1answer
111 views

Remove header and blank row from the results of a SUM, GROUP BY query

I have a two column table where one column is some sort of key where duplicates are allowed and the other column includes amounts. I then have the following formula somewhere else on the sheet: ...
2
votes
1answer
584 views

Import JSON to Google Sheet using Google Apps Script

I am using Apps Script in Google Sheets to import JSON from Google Analytics. I have previously used the ImportJSON function to easily get the JSON from an open API just using =ImportJSON(url) within ...
3
votes
1answer
44 views

How to conditional format in Google sheets based on cell directly above it?

Tried to setup a conditional format. Overall idea, I want a number to go down, and be marked red if it is going up. I tried this custom formula =if(F2>F3, true) trying to say, if the cell value ...
2
votes
2answers
31 views

How do I sequence the date in Google sheets without sequencing the year?

I am trying to sequence dates for the beginning of the week. It should read: 21-12-2015 28-12-2015 04-01-2016 and so on. My problem is that when I highlight the first two dates and drag; I get ...
0
votes
0answers
67 views

How Can I Protect Specific (More Than One) Ranges

I have 13 Spreadsheets sharing different people and every day i copy(or editors copy)from before day's sheet and the spreadsheet has lots of formula so i try to lock them for no one mess them but it ...
2
votes
1answer
73 views

Have formula copied to entire column in Google Sheets

I'm trying to make a spreadsheet that displays the average of a row, i.e. =average(c3:al3) and the function is in column AN3. I want to copy that function down a row (but need to do this for 560 ...
1
vote
1answer
247 views

IMPORTXML Google Spreadsheet Amazon review numbers

I am making a spreadsheet which requires me to have the number of amazon reviews of a product. I looked up how to dynamically update the reviews as more came in and came across the IMPORTXML formula. ...
1
vote
1answer
43 views

How do I add blank rows after each item from a FILTER() or QUERY() result?

Suppose you have a spreadsheet similar to: A 1 "text a1" 2 "text a2" 3 "text a3" 4 "text a4" ...and you'd like to create a new column containing each item followed by two ...
1
vote
2answers
112 views

Apply conditional formatting based on the formula that a cell contains

I am having trouble figuring out how to get conditional formatting to work in my Google spreadsheet. I use the second sheet to import prices of projectors, but a few don't update. I want the row of ...
2
votes
2answers
93 views

Google Sheets/Forms/Script: How to send an email then replace “@” with “#”

I am trying to create a script where the user gets an email (column 1) with the body being column 2. However, after that, I want to replace the "@" in the email with a "#" so that they do not get the ...
2
votes
3answers
157 views

Does Google Sheets have sheet-level named ranges?

Microsoft Excel allows sheet-level named ranges. See here for an explanation. Google Sheets does have "named ranges" functionality, but I have only figured out how to use it on a workbook-level ...
3
votes
1answer
24 views

Prevent editors from being able to make a copy of the spreadsheet

I do not want people with edit access to be able to make a copy of the spreadsheet. Is it possible to do that? I know how to prevent people with only viewing access to do that but how do I do the same ...
2
votes
2answers
30 views

Google Sheets LEFT function trim text generated by IMPORTHTML function

When I use this formula to import data =iferror(INDEX(IMPORTHTML("http://www.thestreet.com/quote/GE/details/company-profile.html","table",3),8,1)) The result is Sector: Industrial Goods. I'd like ...
5
votes
2answers
41 views

Add values to elements in Google Sheets drop-down list

I think I might be over-thinking, but since I have no experience with any kind of spreadsheet, I just don't know where else to turn. What I'm trying to do: I want a drop-down list and each object ...
3
votes
1answer
46 views

Google Spreadsheet Formula to show heading

I have a Google Spreadsheet in which I am comparing projectors. Basically it has formulas to divide the bulb life/cost and contrast ratio/cost. Then it adds the total of the two and gives me a ...
3
votes
1answer
92 views

How to sort groups of rows in Google Sheets?

I have a spreadsheet with lots of related rows. How can I sort the entire sheet in such a way that rows that belong together stay together in groups, and the groups instead are sorted by some common ...
0
votes
1answer
22 views

Setting cell to formula based on reference value

I have Column A which I want to contain one of two formulas based on whether the value in column B is below a certain number. For example, A2 should contain Formula 1 if B2 is less than 9.99. ...
6
votes
1answer
188 views

How to combine two Google Spreadsheet worksheets data into one while preserving updates?

I am trying to help a co-worker out with semi-automating a task. I need to combine two worksheets data into one worksheet. Let's call these A, B and C for the combined one. Data is copied pasted from ...
1
vote
1answer
42 views

Google Sheets overtime calculation if/and/or statements

I'm trying to set up a simple bi-weekly paycheck estimator in Google Sheets. Basically you put in the hours worked for Week 1 and Week 2, and I want it to automatically determine the amount of ...
0
votes
1answer
57 views

Why doesn't my onEdit Function behave the same as my onFormSubmit

With the excellent help of this site I was able to get my onFormSubmit working. However I need the same thing to happen on another sheet only it has to happen after a cell has been assigned one of two ...
1
vote
1answer
60 views

How do I filter the array returned by a filter or other formula?

I want to filter an array returned by a filter to remove blank columns. Here is a picture of an example sheet: Here is the example sheet. I am filtering the large table by the rows that are of ...
3
votes
1answer
54 views

How to select random data points in Google spreadsheet?

I have a Google spreadsheet document with over 1000 data cells in one column. I want to select 5 random samples from this data set, and copy these to a new column. And repeat this process, n number of ...
0
votes
1answer
42 views

How do I pass a range on another sheet within the spreadsheet file to a custom function?

I know how to pass simple ranges to a custom function (ranges in the same sheet as the function usage). How can I pass the address of a cell in a different sheet to that function. For example A1 has ...