Google Sheets notes

Notes on Google Sheets online spreadsheet.



Referencing sheets


Where ‘sheet_name’ is the name of the sheet as shown on its tab at the bottom.



Also see Import data from another spreadsheet.

List of items


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.


  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))


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.

‘sheet_name’ is the name of the sheet as shown on its tab at the bottom.

See ImportRange - Google Docs Help


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:


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.


Last modified: 24/02/2012 Tags: ,

This website is a personal resource. Nothing here is guaranteed correct or complete, so use at your own risk and try not to delete the Internet. -Stephan

Site Info

Privacy policy

Go to top