Get Google Sheet by ID?

Question

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.

Answer 1

var sheetActive = SpreadsheetApp.openById("ID");
var sheet = sheetActive.getSheetByName("Name");

Answer 2

Look at your URL for query parameter #gid

https://docs.google.com/spreadsheets/d/18K3KY2veYSQGaku8DxEI_a8V1ODEQyIGQCTgwP3uqg4/edit#gid=1962246736

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 (https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet)
  * @return {Sheet} the Google Sheet object if found; otherwise undefined (https://developers.google.com/apps-script/reference/spreadsheet/sheet)
  */
function getSheetById(gid, ss) {
  var foundSheets = (ss ?? SpreadsheetApp.getActive()).getSheets().filter(sheet => sheet.getSheetId() === gid);
  return foundSheets.length ? foundSheets[0] : undefined;
}

Answer 3

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 (https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet)
  * @param {Integer} sheetId - the ID of a Google Sheet 
  * @return {Sheet} the Google Sheet object if found; otherwise undefined (https://developers.google.com/apps-script/reference/spreadsheet/sheet)
  */
function getSheetById(ss, sheetId) {
  var foundSheets = ss.getSheets().filter(sheet => sheet.getSheetId() === sheetId);
  return foundSheets.length ? foundSheets[0] : undefined;
}