CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Finn Smith
Finn Smith

Reputation: 883

Get Google Sheet by ID?

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.

Upvotes: 59

Views: 144690

Answers (8)

Justin Poehnelt
Justin Poehnelt

Reputation: 3469

I implemented the feature request in https://issuetracker.google.com/36759083. You can now do the following with the getSheetById() method:

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetById(0)

  console.log(sheet.getName());
}

The first sheet created has id 0. The id is not based upon position. For example, the second sheet created might have an id of 1982384720 and the third sheet, 1741408075.

Note that there currently is a bug in sheet.getSheetId(), https://issuetracker.google.com/380057139.

Upvotes: 5

vstepaniuk
vstepaniuk

Reputation: 868

If you are going to use it with more than one sheet you can do the following:

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheets = ss.getSheets();
const sheetsIds = sheets.map(function(s) { return s.getSheetId() + ''; });

// and then use it like this

const sheet1 = sheets[sheetsIds.indexOf('sheet1_id')];
const sheet2 = sheets[sheetsIds.indexOf('sheet2_id')];
const sheet3 = sheets[sheetsIds.indexOf('sheet3_id')];

Upvotes: -1

Emanuel Schiavoni
Emanuel Schiavoni

Reputation: 31

Simple and shortest method for oneliners:

const getSheetById = (spreadsheet, sheet_id) => spreadsheet.getSheets().find(sheet => sheet.getSheetId() === sheet_id)

Array.prototype.find returns undefined if there is no match

Upvotes: 2

Rodolpho Brock
Rodolpho Brock

Reputation: 8165

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

Upvotes: 11

Daniel Schofield
Daniel Schofield

Reputation: 21

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

Upvotes: 2

Serge insas
Serge insas

Reputation: 46812

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

Upvotes: 63

Javier
Javier

Reputation: 297

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

Upvotes: 12

Sam Richards
Sam Richards

Reputation: 115

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.

Upvotes: -5

Related Questions