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

learn more… | top users | synonyms (2)

2
votes
1answer
24 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: ...
1
vote
2answers
15 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
31 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
0answers
15 views

Google Sheets - Named Ranges

I have a problem in that I am using named ranges in my G-Sheet and, although the named range is picking up the entire range, it only allows a selection down to "P" on the drop down list on the sheet. ...
0
votes
0answers
35 views

Selecting and unselecting checkbox not updating response sheet

I have a Google Form and a response spreadsheet that is updated as soon as a recipient of the form answers and submits the form. I have several different types of field ( multiple choice, grid, ...
-1
votes
0answers
16 views

Google Sheets password protection

Is there any way to password protect an entire sheet as to hide certain elements of it until i have entered a password? Or is there some script or formula anyone knows of in order to accomplish this? ...
1
vote
2answers
32 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
1answer
45 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
35 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
33 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
78 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
34 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
29 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
29 views

Automatically (when created) import PDF from Google Drive into Google Sheet

I use the Google API to "save emails and attachments" so when I get an email message it saves a PDF file into Google Drive. I also use Cometdocs PDF Converter to convert the PDF file to a Google ...
0
votes
0answers
31 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
55 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
76 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
21 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
44 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
60 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
2answers
34 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
19 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
24 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
35 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
25 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
48 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
16 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. ...
5
votes
1answer
78 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
37 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
38 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
14 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
35 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
24 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
22 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
36 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 ...
4
votes
1answer
60 views

Using Query with column headers instead of column letters

I have a formula that I use to dynamically get the column letter for a 1-Dimensional array that are the column headers of a sheet. In order to use QUERY() with column names instead of letters. ...
0
votes
0answers
24 views

Google Sheets permissions issue regarding editors

I have several users who when they are given access to Google Sheets they can't edit the sheets. They can comment and view fine but are not able to edit and input data into the sheet. I've cleared ...
3
votes
1answer
19 views

Find the minimum of data filtered by another column

I've got a sequence of couples as follows: A B 1 Date Value 2 01/10/2014 3 3 05/03/2015 7 4 10/12/2015 9 5 15/01/2016 ...
3
votes
3answers
51 views

How do I get data from one Google Spreadsheet “A” into Spreadsheet “B”?

How do I get data from one Google spreadsheet A into spreadsheet B? And when Spreadsheet A is modified, then Spreadsheet B also changes automatically. A formula using QUERY() and IMPORTRANGE() ...
2
votes
2answers
77 views

“Incorrect range height” error in Google script when using setValues

I am trying to convert calendar dates to fiscal dates. I've been successfully able to do all that. My problem is trying to write the fiscal date values back into the spreadsheet. Here is the code: ...
1
vote
2answers
21 views

Sum of Adjacent column-row entries in Google Sheets

This seems like it would be a common question but I can't seem to find the proper way to express it as I can't find an answer. Say after row 4, I want E#n = C#n + D#n. That is, for the nth row in ...
3
votes
1answer
33 views

Auto-update a column based on another column

Given that I have the following data in sheet1: | id | red | |--------------------------------------|-----| | e6282843-efc0-44f6-8989-028153adc317 | yes | | ...
4
votes
1answer
46 views

Conditional formatting based of current cell AND another cell

Setting up a conditional formatting based on current cell or another cell is easy. But how do you mix them together? Let's say I have the following table: | id | red ...
5
votes
1answer
26 views

Need to change a value within a function for each row in a Google Sheet

In Google Sheets =ArrayFormula(row(1:100)) fills 100 columns with numbers 1-100. =ImportJSON("http://www.domain.com/sample1.json", "") inserts my JSON data keys and values into corresponding ...
2
votes
1answer
17 views

Searching a value between two columns on a reference sheet

I have my main sheet that looks like this: The compromise column (Column F) bases it's value on another sheet. To compute it's value, we check if the value of the Basic Tax (Column B) falls equal ...
1
vote
2answers
54 views

Copying importrange formulas so that they stay relative

How do I copy a formula in Google Sheets that has the address of another sheet in the formula? Now I have to edit the formula for every cell because when I copy it, it does not stay relative. And when ...
-1
votes
0answers
14 views

How do I copy a formula in google sheets that has the address of another sheet in the formula [closed]

How do I copy a formula in google sheets that has the address of another sheet in the formula. Now I have to edit the formula for every cell because when I copy it, it does not stay relative.
4
votes
1answer
26 views

Find the number of values that satisfy a condition and also appear in another table

What I have: Table 1: Name | 1 | 2 Peter | Agree | Disagree So Row 1 will be Name followed by the number 1 - 80. Row 2 will be the name followed by either "Agree" or "Disagree" ...
2
votes
0answers
39 views

Google Sheets - Dynamic Filter View by URI

I am using Google Forms to acquire "per-X" feedback, and want to dynamically produce customised reports that are filtered based on the URI. What I am looking to achieve is a unique URI per X that ...
2
votes
0answers
14 views

Splitting Multiple Multi Line Cells into new rows [duplicate]

I am working on employee data and the data I have received comes with two cells (Proj Name and Count of Hours) which have multiple names/ hours in the same cell separated by line breaks. I have ...