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.

score 58 · Accepted Answer · edited Sep 26 '18 at 04:13

58

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

edited Sep 26 '18 at 04:13

Louis St-Amour

4,065
1
30
28

answered Oct 31 '14 at 20:12

Serge insas

45,904
7
105
131

11

Thanks, I've been doing that. It just seems very clumsy that you can do SpreadsheetApp.openById() and you can getSheetId(), but you can't select a sheet by ID. – Finn Smith Oct 31 '14 at 21:48
3

Careful if you have saved a Sheet id with Properties Service. It may not be exactly the same as the actual Sheet id when you call getProperty(). Make sure you pass Sheet.getSheetId().toString() to the setProperty method instead of just Sheet.getSheetId(). Note: you may need to change the method to deal with Strings. – Trigaten Oct 29 '20 at 22:41
You can create a one liner by using `return sheets.find(sheet => sheet.getSheetId() === id)`. Provided that you created a variable named sheets. – melledijkstra Apr 18 '21 at 16:19
When I use the getSheetById() function proposed by Serge insas or the one-liner proposed by melledijkstra I'm not able to perform any functions like e.g. .getRange() on the returned sheet. Can you tell me what I have to do to make it possible? – maxgotstuck Jul 20 '21 at 07:03
It didn't work for me until I added **toString()** after **s.getSheetId()**. It's because Gogole Script was using ID in scientific notation instead of regular number or string. – Wojtek Mazurek Apr 03 '22 at 18:10
@maxgotstuck did you include the [0]? I was having the same issue until I realized that was needed as filter returns an array. – YangTegap Aug 01 '22 at 20:15

score 14 · Answer 2 · edited Apr 07 '16 at 15:31

14

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

edited Apr 07 '16 at 15:31

zx485

28,498
28
50
59

answered Apr 07 '16 at 15:09

Javier

277
2
2

10

How does this help select a sheet if you know the sheet ID, but not the sheet name? SpreadsheetApp.openByID opens a spreadsheet, not a sheet. – browly Jan 09 '17 at 23:53
@browly This helps because the ID never changes, but if you explicitly write the sheet name and the sheet is renamed the script will no longer work. – Xzila Oct 27 '17 at 15:21
8

@Xzila SpreadsheetApp.openById() opens a Spreadsheet, not a Sheet, so it doesn't help answer the actual question asked. – talljosh Jun 27 '18 at 02:38

Rodolpho Brock · Answer 3 · 2022-03-08T22:57:21.243

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

For an unknown reason, the accepted answer doesn't work for me. This classic for each does the magic! — Rivo, Jan 16 '20 at 12:31

Daniel Schofield · Answer 4 · 2021-06-23T01:11:52.453

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

score 0 · Answer 5 · answered Sep 23 '22 at 18:57

0

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

answered Sep 23 '22 at 18:57

Emanuel Schiavoni

11

score -5 · Answer 6 · edited Aug 30 '15 at 21:30

-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.

edited Aug 30 '15 at 21:30

baao

71,625
17
143
203

answered Aug 30 '15 at 21:11

Sam Richards

115
1
3

Get Google Sheet by ID?

6 Answers6

Linked