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

learn more… | top users | synonyms (2)

1
vote
1answer
55 views

Automatically record daily values in a new column

So I have this spreadsheet I use to control Content funnel. I've come up with a few formulas to use something else than a pivot table. But in order to get the trends and alarms I'm looking for, I need ...
1
vote
2answers
26 views

How do I calculate the sum of 2 columns using the max from each row based on a condition?

I have data in two columns. I would like to sum the two columns (A and B) based on the max value in each row and a condition (c) indicating if the row should be included in the sum. Example: A B ...
2
votes
1answer
31 views

How would I apply formatting to another cell based on whether another cell's date has passed or not?

I'm working on making a spreadsheet in Google Sheets, and I'm attempting to have a column of names and then a column of dates adjacent to it. I'd like to set up some sort of conditional formatting ...
1
vote
1answer
41 views

Change text in one cell by removing the parts entered in other cells

I want to know if there is a way to add multiple numbers with letters to one cell and each number/ letter get eliminated when that number gets placed in another cell. For example: cell b4 has: 4, ...
1
vote
1answer
22 views

Update latest spend on Google Sheets based on Date dimension

I have a spreadsheet report -- used to monitor our marketing campaign reports. So my goal here is to be able to update a cell showing the our current spending as of yesterday. How can I go about ...
1
vote
2answers
31 views

Using Arrayformula to compute running decremented total, subtracting the previous cell

This doesn't seem like it should be difficult. I'm keeping a running decremented total owed, where each line may apply some payment against the outstanding debt. Basically: A: Outstanding Owed | ...
6
votes
2answers
145 views

In Google Sheets, how does one get a 12-hour time of day *without* an AM/PM indicator?

I am trying to display times in Google Sheets without an AM/PM indicator, but with 12-hour time (not 24-hour "military-style" time). I do not need the AM/PM indicator as it is redundant in my ...
0
votes
1answer
71 views

How to dynamically retrieve and display data based on input from google forms

To be very specific, let's say I have a google form where it asks you your name (text box) and how much do you like carrots, with a multiple choice prompt of "very much" and "I hate them!" After the ...
1
vote
1answer
20 views

How to convert or parse olson timestamp in google spreadsheet?

How do you convert 2016-03-05T08:30:00 to time in google spreadsheet? I would like to convert it to hh:mm:ss. Thanks.
1
vote
1answer
46 views

How to receive a notification when changes are made to a specific tab in Google Sheets?

If would like to receive an email notification if a new row is added to a specific tab in a Google Sheets. The default notification rule settings are too limited and the Google Sheets Add-on Store ...
2
votes
1answer
46 views

Complex views in embedded Google Sheets

I have a Google Sheets contact list for a multicampus boarding school. The sheet at present summarizes the contact info for each person into the first 4 cells of the row. Entry is made using the ...
2
votes
1answer
19 views

Time query in Google Spreadsheets

I've started using Google Spreadsheets for our logbook and it's been a great help. I have data with a time component in it (start and end time) and I'd like to query or filter the data where the ...
3
votes
1answer
116 views

Filter multiple values using filter by condition tool in Google Spreadsheet

I'm trying to filter a long list of URLs to narrow down which ones I'm interested in. I've got my URL list in Google Spreadsheet and I'm using the filter tool to remove data that I don't want. At ...
2
votes
1answer
121 views

Forms Data Manipulation In Google Sheets

I'm trying to develop a solution using Google Forms with Google Sheets that will collect Google Forms data and then automatically divide that one form submission's data into multiple rows of data in ...
1
vote
1answer
98 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
76 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
50 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
106 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
21 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
327 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
141 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
170 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
141 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
97 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
320 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
154 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
134 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
42 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
60 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
24 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
49 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
26 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
45 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
44 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
45 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
156 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
64 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
88 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
425 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
41 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
30 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
55 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
66 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
203 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
34 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
98 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 ...