The best workaround I have found so far is:
but ideally I would like to be able to simply write, e.g.:
So, does anyone know: is there a syntax for specifying a whole sheet as a range?
migrated from superuser.com May 12 '13 at 17:05
This question came from our site for computer enthusiasts and power users.
I've created a small Google Apps Script (GAS) snippet, to do the work for you.
In the spreadsheet menu, select Tools>Script editor, and add the code. Make sure to press the bug button:
I've created an example file you you: Sheet as Range
In this case, there are two ways to determine a range in a spreadsheet via GAS:
I don't know a specific one, but I think you can use some formulae if you don't know the number of rows/columns:
Google Sheets doesn't have a range syntax for whole sheet range.
The following formula will return the range address of the whole sheet named Sheet1
In order to use it as reference, put it inside of INDIRECT. The following formula will return an array of all the values in Sheet1.
=ArrayFormula( INDIRECT( "Sheet1!" &ADDRESS(1,1,,TRUE) &":" &ADDRESS(ROWS(Sheet1!A:A),COLUMNS(Sheet1!1:1),,TRUE) , TRUE ) )
Assuming you know the number of rows, you can name the entire sheet as "worksheet_name":
Now every time you use "worksheet_name" in a function, it will reference the entire worksheet.