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

learn more… | top users | synonyms (2)

2
votes
2answers
29 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
39 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
44 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
83 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
21 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
154 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
55 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
46 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
50 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
39 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
35 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
62 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
125 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
29 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
21 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
61 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
167 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
22 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
53 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
56 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
28 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
21 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
88 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 ...
4
votes
1answer
29 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
64 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 ...
4
votes
2answers
39 views

Google Sheets used to show current users but now it doesn't

Google Sheets used to show current users on a spreadsheet. Now it doesn't. I can't find a setting to fix this. How can I see who is active on a spreadsheet?
1
vote
0answers
32 views

How can I get locations of thousands of places saved in Google Sheets?

I have a list of ten thousand places saved in Google Sheets. I want to get their location and sort them based on that. How can I efficiently get all of their locations? Can Google Maps API be of use ...
5
votes
1answer
84 views

Google Spreadsheet, partial formatting using formula in cell

I'm using Google Spreadsheet to make some calculations. At the bottom of my table I need SUM, AVG and some others and everything is fine. But I made a long cell with all the text, like this: ="Hi ...
5
votes
1answer
65 views

Test Google Spreadsheets Add-On with custom functions

I have set up a Google Apps Scripts project with the intent of creating a Google Spreadsheets add-on with custom functions. However, when I use the option "Test as add-on", I can't use the functions ...
2
votes
1answer
84 views

Access Google Spreadsheet revision history through API

Is there any way I could access Google Spreadsheet revision history through the API? The reason for that is I have a lot of sheets and simply relying on build-in revision history does not suffice to ...
1
vote
1answer
150 views

How do I make a Google Sheets pivot table with a custom formula per row?

I have a Google Survey/Spreadsheet. It simply collects some data on therapists we have and the patient satisfaction. I want to make a pivot table that summarizes this data so we can compare the ...
0
votes
0answers
43 views

How to Resolve the Mismatched Row Size in Google Sheets?

I am trying to figure out what the mismatched row size error means. I cannot find a description of what that error means? I have an array formula that pulls data from a responses spreadsheet (from a ...
1
vote
2answers
54 views

How can I highlight duplicate cells?

I'd like a formula that highlights all duplicate cells, even if they are in different columns. In my example spreadsheet A1 is equal to D15, the formula should highlight both, and so on with all ...
1
vote
1answer
32 views

COUNT value in column A + filter column B & C + > & < value column D (date)

I need to calculate how many rows in a range(>= <= of VALUE in column D( date value where every row has own date): VALUE in column A(if duplicate count as 1), same VALUE in column B, same VALUE ...
3
votes
1answer
93 views

Count Occurence of a Regex Search Term

Using CTRL+F indicates the number of occurrences of a search term. However, when using regex (via Find and Replace) there's no indication of count. Does anyone know how to find the number of ...
3
votes
1answer
121 views

Running Total In Google Sheets with Array

QUESTION: How do I create a running balance that has the following criteria: It uses an array formula the rows don't need to be sorted by date, but running total only returns balance of: Rows ...
2
votes
2answers
52 views

Can a button be added to Google Drive (Sheets) toolbar?

I use Google Drive Sheets for spreadsheet budget management. I am frequently using the Insert > Row Above/Below menus. I don't find any shortcut for either menu option in the Keyboard Shortcuts ...
1
vote
2answers
48 views

Google sheets: How do I do a group count on 2 fields, to summarise colour and size choices

I have a spreadsheet where each row is a persons choice of colour and size of a T-Shirt. It looks something like this: ┌─────────┬────────┐ │ Large │ Red │ │ Small │ Blue │ │ Medium │ Red ...
4
votes
1answer
47 views

How do get data from a previous row when a different column matches?

I'm using Google sheets and trying to find a prior service date for each location. I'd like to have a result look like the below. I already have data filled in for column A and B. What formula ...
2
votes
1answer
26 views

How to get sums by group, and output only those, where sum is not equal to 0?

In Google Sheets, I have the following data: | A | B 1 | foo | 20 2 | bar | 50 3 | baz | -40 5 | bar | -50 4 | foo | 100 6 | baz | 60 Currenly, I'm using this query ...
2
votes
2answers
225 views

Automatically copy and paste cells that have just been updated to a list from forms

I have a spreadsheet that collects information from two different forms. Each form feeds a different sheet in the spreadsheet. There are 3 other sheets that the info from either of the first two forms ...
2
votes
0answers
27 views

Import site URLs to Google Sheet (and add new row each time)

I am trying to make a list of site pages that will automatically update in Google Sheets, while keeping the full history rather than overwriting older rows with new ones. I see two possible ways of ...
2
votes
1answer
20 views

How to concatenate 1 in front of many cells at once on a google sheets doc?

I am trying to concatenate many cells of a google sheets with "1 " in front of every cell. There are 360 cells so changing each individually would be a difficult task. Any help would be greatly ...
2
votes
2answers
46 views

How to word count multiple rows in Google Sheets?

I have a spreadsheet similar to: A B C 1 "text a1" "text b1" "text c1" 2 "text a2" "text b2" "text c2" 3 "text a3" "text b3" "text c3" 4 "text a4" ...
4
votes
1answer
394 views

Add a script trigger to Google Sheet that will work in Android mobile app

I have a Google sheet that includes a script that should be run when clicking a button. The recommended way to do this (as far as I can tell) is to insert an image and then attach a script to the ...
2
votes
1answer
77 views

Creating duplicate linked Google spreadsheets that share copy and formatting

I'm looking to create a content calendar on one Google Drive and then have that exact content calendar (copy, formatting, and any new rows that get added) shared as a tab in a spreadsheet on another ...
2
votes
1answer
85 views

Formula to use in Google sheets to count all cells containing specific texts

Here is a link to a sample spreadsheet: https://docs.google.com/spreadsheets/d/1LcfqPxsnQGEVZKd2imM1WPYRvgbZBiEQ18mHVd3znjE/edit?usp=sharing What I want to do is count all unique values in column C ...
3
votes
1answer
54 views

How do I create a Google Form where the user can upload a CSV?

I'd like to create a Google Form where the user can upload a CSV and each line in the CSV populates a spreadsheet. Is there any way to do this in Google Sheets?