The best workaround I have found so far is:
worksheet_name!$A$1:$YY
but ideally I would like to be able to simply write, e.g.:
worksheet_name!
So, does anyone know: is there a syntax for specifying a whole sheet as a range?
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this communityThe best workaround I have found so far is:
worksheet_name!$A$1:$YY
but ideally I would like to be able to simply write, e.g.:
worksheet_name!
So, does anyone know: is there a syntax for specifying a whole sheet as a range?
I've created a small Google Apps Script (GAS) snippet, to do the work for you.
function sheetRange(targetName,int) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var asName = ss.getActiveSheet().getSheetName();
var tgSheet = ss.getSheetByName(targetName);
var output;
if(targetName == asName) {
output = "Error: target sheet is active sheet !!";
} else {
switch(int) {
case 1:
output = tgSheet.getDataRange().getValues();
break;
case 2:
output = tgSheet.getSheetValues(1, 1, tgSheet.getMaxRows(),
tgSheet.getMaxColumns());
break;
default:
output = "Choose int to be 1 or 2 !!";
}
}
return output;
}
In the spreadsheet menu, select Tools>Script editor, and add the code. Make sure to press the bug button:
=sheetRange("sheetName",int)
Use the int
option as explained under remarks.
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:
int=1
; Via the getDataRange
method. This will retrieve a range, in which the last column is the one that has data in it. The same accounts for the number of rows. This is usually the most straightforward route. See second sheet in example file.int=2
; Via the getSheetValues
method. This will retrieve a "WYSIWYG" range. See third sheet in example file.importrange
. In other words, as long as you request more columns than you have you'll get everything. Not sure if this is a problem for other functions .... Something like Maksym's answer is what I would go with, something like worksheet_name!A1:ZZZ
, which should generally cover all the columns (although maybe in some cases more Z's are needed?)
– Jimbo
Jun 18 '20 at 1:54
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
="Sheet1!"&ADDRESS(1,1,,TRUE)&":"&ADDRESS(ROWS(Sheet1!A:A),COLUMNS(Sheet1!1:1),,TRUE)
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 ) )
This works for me:
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheets()[0];
var rg = sh.getName() +"!"+ sh.getDataRange().getA1Notation();
Used in a bound script, combine the sheetname with a "!" and then get the A1 notation of the data range on the chosen sheet
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:
indirect("Sheet!1:"&countif(Sheet!A:A,"<>@")+countif(Sheet!A:A,"=@"))
Here, Sheet
is your sheet name, and @
is an arbitrary string. If that sheet you're selecting has @
in one cell, it won't work. You can replace it by another character if ever the sheet contains such a cell.
The COUNITF
here counts the number of rows in column A:A not containing @
, which should be all if there are no cells containing it, and INDIRECT
transforms Sheet!1:###
(where ###
is the number of rows) into an actual selected range.
Assuming you know the number of rows, you can name the entire sheet as "worksheet_name":
STEPS:
Now every time you use "worksheet_name" in a function, it will reference the entire worksheet.
To specify the entire sheet as a range, please use:
worksheet_name!A1:ZZ
you can try it here: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/clear
or if you use Java (to clear everything in Sheet1):
Sheets service = getSheetsService(credential);
service.spreadsheets().values().clear(REPORT_WARNINGS_LATAM_FILEID, "Sheet1!A1:ZZ", new ClearValuesRequest()).execute();
or if you want just to use it in a formula (for example: SUM) on another sheet (for example: Sheet2) you can use following reference:
=SUM(Sheet1!A1:ZZ)
^ this will sum all existing cells on Sheet1 and put the value in a cell on Sheet2, you can find the example here: https://docs.google.com/spreadsheets/d/1rP3YCl3ErlYjlYGT_Q-xFvkVr7yKw6WQfHklbohL1NM/edit?usp=sharing. In this example we have 3 columns and 5 rows on Sheet1, so this formula =SUM(Sheet1!A1:ZZ) selects all of them. You can add rows or columns on Sheet1 and this solution will still select all cells.
P.S.: if you down vote my answer - please explain why.
The best answers have already been given for those who are operating within appscript, but if anyone out there is working within Google Sheets proper, then here's an approach that may fit your needs:
"worksheet_name!1:" & ROWS(worksheet_name!A:A)
Explanation: * You're essentially specifying the range via a string that's concatenated with the number of rows in worksheet_name!
Some advantages of this approach: * It's 'dynamic'... if you add rows to the bottom, the range will adjust accordingly * Lightweight - you can slap this into an importrange/query function
Disadvantages: * Haven't tried it in all cases so might only work for specific use cases * I personally have a preference for making things dynamic / clean so a custom function of sorts would be a good middle ground between appscript and this lightweight approach
I was able to select the entire sheet by naming the range as the sheet without the exclamation mark.
I had a tab named data. Instead of using data! or data!A1:ZZ.