I know that Google Apps Script has a getSheetId() method for the Sheet Class, but is there any way to select a sheet within a spreadsheet by referencing the ID?
I don't see anything like getSheetById() in the Spreadsheet Class documentation.
5 Answers
You can use something like this :
function getSheetById(id) {
return SpreadsheetApp.getActive().getSheets().filter(
function(s) {return s.getSheetId() === id;}
)[0];
}
var sheet = getSheetById(123456789);
And then to find the sheet ID to use for the active sheet, run this and check the Logs or use the debugger.
function getActiveSheetId(){
var id = SpreadsheetApp.getActiveSheet().getSheetId();
Logger.log(id.toString());
return id;
}
var sheetActive = SpreadsheetApp.openById("ID");
var sheet = sheetActive.getSheetByName("Name");
Look at your URL for query parameter #gid
In example above gid=1962246736, so you can do something like this:
function getSheetNameById_test() {
Logger.log(getSheetNameById(19622467362));
}
function getSheetNameById(gid) {
var sheet = getSheetById(gid ?? 0);
if (null != sheet) {
return sheet.getName();
} else {
return "#N/D";
}
}
/**
* Searches within Active (or a given) Google Spreadsheet for a provided Sheet ID and returns
* the Sheet if the sheet exists; otherwise it will return undefined if not found.
*
* @param {Integer} gid - the ID of a Google Sheet
* @param {Spreadsheet} ss - [OPTIONAL] a Google Spreadsheet object ()
* @return {Sheet} the Google Sheet object if found; otherwise undefined ()
*/
function getSheetById(gid, ss) {
var foundSheets = (ss ?? SpreadsheetApp.getActive()).getSheets().filter(sheet => sheet.getSheetId() === gid);
return foundSheets.length ? foundSheets[0] : undefined;
}
I'm surprised this API doesn't exist... It seems essential. In any case, this is what I use in my GAS Utility library:
/**
* Searches within a given Google Spreadsheet for a provided Sheet ID and returns
* the Sheet if the sheet exists; otherwise it will return undefined if not found.
*
* @param {Spreadsheet} ss - a Google Spreadsheet object ()
* @param {Integer} sheetId - the ID of a Google Sheet
* @return {Sheet} the Google Sheet object if found; otherwise undefined ()
*/
function getSheetById(ss, sheetId) {
var foundSheets = ss.getSheets().filter(sheet => sheet.getSheetId() === sheetId);
return foundSheets.length ? foundSheets[0] : undefined;
}
Not sure about ID but you can set by sheet name:
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.setActiveSheet(ss.getSheetByName("your_sheet_name"));
The SpreadsheetApp Class has a setActiveSheet method and getSheetByName method.
