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

learn more… | top users | synonyms (2)

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
54 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
24 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
531 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
175 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
221 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
162 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
414 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
193 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
150 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
56 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
51 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
50 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
176 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
109 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
573 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
43 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
66 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
42 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
111 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
92 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
154 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
187 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
58 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 ...
3
votes
1answer
39 views

Highlight/reject cells that contain any of the values from another sheet

I have a sheet/tab that contains nothing but a single column where each row is a word, let's call it wordlist. In another sheet/tab, I would like to make it so that for one of the columns, if the ...
2
votes
1answer
75 views

Can this script be more optimized and why is it running slow sometimes?

I just started using Google App Script and wrote this script (mostly copy and paste) to check the status of my website and its pages. Sometimes, mostly after opening the spreadsheet, but even if it ...