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

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;
}

Answer 2

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

Answer 3

Look at your URL for query parameter #gid

https://docs.google.com/spreadsheets/d/1Vk7kzMD3DhfUeLasDdSK6Z9ScNrryPrawAxHGFimKp8/edit#gid=910184575

In example above gid=910184575, so you can do something like this:

function getSheetById_test(){
  var sheet = getSheetById(910184575);
  Logger.log(sheet.getName());
}

function getSheetById(gid){
  for each (var sheet in SpreadsheetApp.getActive().getSheets()) {
    if(sheet.getSheetId()==gid){
      return sheet;
    }
  }
}

Answer 4

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;
}

Answer 5

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.