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

learn more… | top users | synonyms (2)

1
vote
1answer
69 views

Google Sheets list key with highest value in key value pair

I have a some data in key-value pairs setup like so: A B 1 Key: Value: 2 Cars 5 3 Bikes 4 4 Vans 9 5 Trucks 6 What I can't seem to ...
1
vote
2answers
318 views

IMPORTXML: xpath to get non-distinct parent node data for each child node

Google Spreadsheets has an IMPORTXML function, which I am trying to use to maintain a list of TV episodes, based on the API at http://services.tvrage.com/feeds/episode_list.php?sid=3183. I am trying ...
2
votes
1answer
65 views

Had a google spreadsheet query that worked but now doesn't

I made a copy of the spreadsheet that I'm working on here. The query that I have on on the sheets "DSM", "DiveTank", "WarmUp", "Teach" all have pretty much the same query. That worked before but now ...
5
votes
2answers
6k views

How to force a page break in Google Spreadsheets

I want to force a page break after a certain row of a sheet. Is there some analogue of Google Docs’ (and Excel’s) “insert page break” for Spreadsheets? If there is no “native” way to do this, maybe ...
0
votes
1answer
401 views

Google spreadsheet query reference to several sheets

I have this formula: =ArrayFormula({query(query({A3:D,value(B3:B)}, "select Col1, sum(Col5), count(Col4), sum(Col3) where Col1<>'' group by Col1 format sum(Col5) 'h:mm:ss'"),"select* offset ...
1
vote
1answer
69 views

Associate cells to imported ones with importrange in Google Sheets

I have the following issue: I have table A with several columns and I use the following function to import some of data to table B: ...
1
vote
0answers
33 views

Why is there sometimes no “Move to trash”/“Move to bin”

I'm trying to clean up some old Google Sheets, but some of them don't have a "move to bin" menu option: Other spreadsheets do have it: What's the difference? How can I delete spreadsheets that ...
1
vote
1answer
402 views

Google Spreadsheet: How to fill in missing values (inbetween filled values) automatically

I made a spreadsheet for tracking weight, where each row is one day, and one column is weight. Although I've had Google Spreadsheet create a row for every date in the year, I only measure my weight a ...
1
vote
1answer
152 views

Google Sheets - Auto update table and auto add new data entries

I have a table in wich raw data is entered manually, with info about clients and the requests they made. And then I have this table in which I'm trying to analyze a bit the raw data (# of requests ...
1
vote
1answer
247 views

Can I use ArrayFormula with CountBlank or IsBlank if so how?

=IF( OR(COUNTBLANK($F3)>0, COUNTBLANK($G3)>0, COUNTBLANK($H3)>0, COUNTBLANK($I3)>0), "Missing Values", $E3+(index(LSI_Index!$B$4:$B$40,MATCH(VLOOKUP($G3, ...
0
votes
1answer
40 views

Tolerant Sum of column

I would like to make the sum of a column which might also contain cells which are not parseable as a number (strings, empty, etc). These cells should just be ignored. I've tried: ...
1
vote
1answer
118 views

How can I add hours in Google Sheets to show days?

I have 2 cells in Google Sheets with values: 22:00:00 22:00:00 and I have a cell which has the function sum to sum these values and it shows the value: 20:00:00 I am guessing it is trying to ...
3
votes
1answer
4k views

Google Sheets Query Get Row When Any of These Cells are Not Empty

So like my title states I would like to select via a Query only the rows where certain cells are not blank/empty. I have 4 pools that I have a form for collecting their chemistry. In separate sheets ...
2
votes
1answer
193 views

Convert table data into a list in Google Spreadsheet

I'm using Google Spreadsheet and would like to convert a table into a single column of values so I can paste them into another program. Original Data: x y z a b c Desired Outcome: x y z a b c ...
1
vote
1answer
2k views

How to skip blank cells in Google Spreadsheet/Excel when copying a range from source to destination by Formula

I have three levels of hierarchical data in Google Spreadsheet that looks something like this: As data is being entered in here, I want it collect in three separate sheets like this: And The ...
3
votes
1answer
1k views

Created a Google Form; can't find the Spreadsheet

Long ago, I created a survey that I sent out by email. The responses were recorded in a Google Spreadsheet by filling out a Google Form. I would like to use that data again, but I cannot find the ...
1
vote
1answer
55 views

Using a cell text as sheet reference

Is it possible to use a cell text as a sheet reference in Google sheets. I have a spreadsheet with many sheets and want to reference a sheet name from text in a cell rather than having to put the ...
4
votes
1answer
893 views

How do I copy cell values programatically?

How can I do this programmatically in Google Sheets? How can I copy cell values rather than references from one sheet to another in Google Docs? I have a RSS feed, that I am working along side, so ...
1
vote
1answer
330 views

Find the nth occurrence of variable, look at adjacent cell and organise in an array

I have a Google spreadsheet with two arrays: The first is a list of ~100 names (in a single column) with the adjacent column being the variable (A,B,C,D,E,F,G,H,I or J). The purpose is to group people ...
2
votes
1answer
146 views

How to grant a specific list of user to access Google Form?

Currently when I click on the "Send Form" button in Google Form and user will be able to input the form when they know the URL. Is it possible to grant a specific user to have access to the form? (So ...
2
votes
1answer
601 views

How can I COUNT rows with ARRAYFORMULA?

I have a sheet that lists incidents in an Incidents sheet: | Date | Name | Incident | |------------|-------|---------------------| | 01.01.2015 | Vidar | Car broke down | | ...
3
votes
1answer
166 views

Mailmerge via Google Sheets: multiline cell content

I'm trying to create a batch of email messages using data from a Google Sheets. The Sheets has these columns: name, email address, message I tried 2 google Add-ons: Yet Another Mail Merge (uses ...
0
votes
1answer
366 views

Eliminate empty cells from a sort

I have a spreadsheet where club members scores are added from competitions during the year. I accumulate the data to a sheet with a name column and a value column I then sort these to another sheet by ...
2
votes
0answers
158 views

How do I create a map from Google Sheet data that's in another sheet?

I'm trying to create a Google My Map based on addresses in a Google Sheet "file". This seems to work OK following the standard instructions, except that I can't seem to extract data on anything except ...
2
votes
1answer
92 views

Cell concatenation by hotkeys rather than a formula

In my fumble-fingered attempt to copy (supposedly with Ctrl+c) part of a column containing a few text strings in individual cells within new Google Sheets, on pasting (supposedly withCtrl+v) I ended ...
1
vote
0answers
52 views

Dynamically creating sums from lookups

I was trying to do something similar to this post: Google Sheets advanced lookup + sum But I just can't figure out how to amend it for my purpose. I'm basically building a diet plan, where I have ...
3
votes
1answer
418 views

Select top n filtered rows in Google Sheets

In Google Sheets I have a table like this: JOB TYPE STATUS Generate files Scheduled In Progress Update certs Manual Completed Copy files Scheduled Pending ... ...
2
votes
2answers
496 views

Conveniently export part of Google spreadsheet to HTML page

Google spreadsheet allows you to save table as HTML: The problem is that what the HTML you get is not publishable in website or coherent HTML document: I'd just like a part of the table as HTML. ...
4
votes
1answer
150 views

Explaining proper meaning of columns to Google charts editor

I'm trying to make a simple chart displaying number of events that occured each month in X years. This means I want a chart that shows: X colored lines, one for every year Month names marked on the ...
4
votes
1answer
78 views

Save daily to my Gmail in Google Sheets

Right now what I have is this in a Google sheet for my company daily lunch program: function sendLunchPerkSummery() { var d = Date(); var file = ...
1
vote
1answer
754 views

How to pass multidimensional array to a custom function in Spreadsheets

I want to pass a multidimensional array to a custom function. I do it like this: =customFunction({{"a1v1"; "a1v2"};{"a2v1"; "a2v2"; "a2v3"};{"a3v1"}}) However I don't get the expected behaviour ...
2
votes
2answers
2k views

How to stop Now() function from auto-updating?

I'm using Google Spreadsheet's now() function as a shortcut to insert the current timestamp now. However, the value in the cell changes whenever the spreadsheet is modified in any way, even when the ...
4
votes
2answers
3k views

How to save temporary variables in Google Sheets formula?

I'm trying to create a formula in a Google spreadsheet which looks like this: if (x < 0, x + 1, x) It means if x is smaller than 0, return x + 1, else return x. However, x itself is an ...
2
votes
1answer
37 views

Select range by typing it in, e.g.,“f1:f600”

How do I select ranges by typing in the range value in Google Spreadsheets? I need to be able to select, say, d3:d1000, by typing in “d3:d1000” somewhere. In other words, what F5 does in Excel. I ...
1
vote
0answers
176 views

onEdit Script to auto run Filter on a tab

I'm trying to find a script that will run in a particular way, see below: it runs on a specific tab in a spreadsheet needs to either re-run a filter that i have in place on the tab OR can filter out ...
1
vote
1answer
465 views

How to put a dollar symbol before every column and row number in google spreadsheet

I have a big spreadsheet in google docs with many formulas like this: =MIN(C3;C4)/500+4. Now we need to add $ before every cell row/column to make it absolute. So, the formula should look like this: ...
1
vote
1answer
324 views

Google Docs invitation to edit on my Outlook email address

I have got an invitation to edit a Google doc on my outlook.com email address, but every time I click on the link, I get the message “You need permission”. I am signed in to my Gmail account as well ...
2
votes
1answer
2k views

How to set Custom data validation in Google Sheets (A really simple step-by-step guide needed)

I'm trying to figure out how to create custom data validation in Google Sheets what are the steps I need to follow to achieve this? For example if I wanted the following validation: // Set the data ...
1
vote
1answer
204 views

Pre-fill field with formula — incremental counter — in Google form [duplicate]

I have a Google Docs form that I use to track my customers complaints. Is it possible to have a "counter" field pre-fill with an incremental number? I want my customers to have a complaints number ...
1
vote
1answer
118 views

Function to retrieve a subset of rows coming from another function in Google Sheets

I have a function in a Google Sheet that returns a handful of columns and two rows. One of those rows contains column headings, which I would like to remove. Is there any way to wrap this function ...
0
votes
1answer
1k views

Click cell in Google Spreadsheet to open data in popup

Is it possible to have a cell that just says "click me" or whatever, which opens a popup so you can see the full data? I want to keep all my data contained in one row per item, but one cell per item ...
5
votes
1answer
205 views

Color Shading with Google Spreadsheets

It was easy to do in Excel, but I am having trouble following some of the scripts written on here. Basically we have 3 colors we use, Green, Yellow and Red. 1 = Green, 2 = Yellow and 3 = Red. What I ...
-1
votes
2answers
108 views

How to get the count of the current sheet?

I have a spreadsheet that I have spread out over many sheets. However, I could automate the filling out of some simple data by doing some simple calculations. I could program something simple like ...
1
vote
1answer
451 views

Get YouTube video's view count in Google Drive/Sheets?

Is there anyway to write up a Spreadsheet in Google Drive such that a cell will have the current (or as close to current as possible) number of views that video has received?
4
votes
3answers
243 views

Setting multiple protected ranges with one range value inputted

Id like to set protected ranges over several sheets and ranges of the same spreadsheet in a Google spreadsheet. The normal way to do a range is 'Sheet One'!A:L What id like to do is be able to ...
2
votes
1answer
408 views

Multiply duration (in hours) by decimal value

I have the following data in a Google Spreadsheet: +----------+------+--------+ | Duration | Rate | Value | +----------+------+--------+ | 16:15:00 | 55 | 893.75 | +----------+------+--------+ I ...
1
vote
3answers
7k views

How can I replace blank cells with zeros in Google Spreadsheet?

I have a spreadsheet where someone else entered the data. They left blank cells instead of recording 0 values. How can I replace the blank cells with 0s? I tried find and replace with regular ...
1
vote
2answers
1k views

Relative copy conditional formatting that references other worksheet

I have a group of worksheets, one of which has 'default' values while the others should only have values if they differs from the default worksheet. To facilitate this, I want to use conditional ...
3
votes
1answer
29 views

I can't seem to be able to use Google LaTex integration

Google claims that they have allowed LaTex equations in Google Docs. According to the help page, the option should appear under Insert → Equation. Doesn't seem to be the case: So what am I doing ...
1
vote
1answer
48 views

How to insert copied text or images by right click in Google Docs editors

In Google Docs editors, is there a way to allow pasting by right click and selecting paste? Why do they disabled this feature by default? Sometimes it’s faster than pressing ctrl+V if your hand is ...