Notes on Google Sheets online spreadsheet.
Index
Usage
Referencing sheets
'sheet_name'!range
Where ‘sheet_name’ is the name of the sheet as shown on its tab at the bottom.
E.g:
'Time'!A1:A100
Also see Import data from another spreadsheet.
List of items
unique(range)
E.g. B1 has the formula =unique(A1:A4):
| A | B | ||
|---|---|---|---|
| 1 | cat | cat | <- =unique(A1:A4) |
| 2 | dog | dog | |
| 3 | cat | tortoise | |
| 4 | tortoise |
Conditional sum
sumif(range, condition, sum_range)
You must use double quotes not single quotes.
E.g:
| A | B | |
|---|---|---|
| 1 | yes | 3 |
| 2 | no | 2 |
| 3 | yes | 4 |
=sumif(A1:A3,"yes",B1:B3) == 7
Complex conditional sum
=arrayformula(sumif(range_1&range_2, condition_1&condition_2, sum_range))
E.g:
| Animal | Item | Amount |
|---|---|---|
| cat | food | 3 |
| dog | toy | 6 |
| cat | food | 4 |
| dog | food | 5 |
| Animal | Total food | |
|---|---|---|
| cat | 7 | <- =arrayformula(sumif(A12:A15&B12:B15,A18&"food",C12:C15)) |
| dog | 5 | <- =arrayformula(sumif(A12:A15&B12:B15,A19&"food",C12:C15)) |
Import data from another spreadsheet
=importrange("spreadsheet_key", "sheet_name!range")
You can get the ‘spreadsheet_key’ from the spreadsheet’s URL e.g. https://docs.google.com/spreadsheet/ccc?key=1Ku2KLLIQTKIDdTSUTYN3dFlQRSNQcTBabVVkNHNPaKK#gid=0
‘sheet_name’ is the name of the sheet as shown on its tab at the bottom.
See ImportRange - Google Docs Help
Bugs
This is a list of bugs that I’ve experienced while using Google Sheets. It probably isn’t useful to anyone, but I wanted a record to check against if it’s behaving oddly in the future.
Not interpreting formulas
The following formuls worked in one cell but not in another:
=unique('Time'!E2:E)
In fact, after further exploration, it seems no new formulas are interpreted. In order to have it work again I had to close the spreadsheet and open it again.
Infinite warning popups
Trying to create a pie chart and adding an invalid range, the warning popup “The range you specified is not in a valid range format” would not stop appearing, rendering the page useless, preventing me navigating away from the page and very nearly forcing me to kill the browser.
Inconsistent results
Using an identical formula in two different cells generates different results. The formula is in a ‘Summary’ sheet and pulls data from a ‘Time’ sheet. The following two lines show the formulas, actually copy and pasted from the sheet:
=SUMIF(YEAR('Time'!A2:A), 2009, 'Time'!J2:J) -> 5
=SUMIF(YEAR('Time'!A2:A), 2009, 'Time'!J2:J) -> 0
Cell randomly losing formatting
I formatted a column to show numbers with 2 decimal places. One cell in the column keeps resetting to showing 13 decimal places.
Random auto formatting
10 minutes of randomly resizing the columns to be tiny.
Failing downloads
Yesterday it was failing to download the spreadsheet as CSV but working as ODF, today it was working as ODF but is now failing. It still allows you to download the file though - an empty file! - and tells you in a javascript generated dialog that it’s failed, which is great if you see it before saving the empty file but not so great if you download popup appears over the top of the dialog. Bad functionality and usability design.
