CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Øystein Øren
Øystein Øren

Reputation: 3

Google apps script - Selecting sheet by a key instead of gettsheetbyname

I have about 50 sheets in my Google spreadsheet, and want to return different values from different sheets. However, I don't feel safe using the sheet names as a key by using getSheetByName as they are easy to change. I was wondering if there is any way to use the gid-ID I see changing for each spreadsheet as key? Or something else?

Below is what I am using now:

var ssValve = SpreadsheetApp.openById('Key').getSheetByName("SheetName")

Upvotes: 0

Views: 1150

Answers (3)

DigitalWizard
DigitalWizard

Reputation: 1

YES! The gid is the unchangeable unique identifier for the individual sheets within the workbook.

It is surprising that you cant script with it as the basic identifier, Like GetSheetByID(). I'll be using your code in my sheets.

A suggestion to rename the mastersheet variable to something more standard like "spreadsheetKey" or "workbookKey".

Upvotes: 0

Kriggs
Kriggs

Reputation: 3778

There's no native method, but you can build a function for this:

function getSheetById(masterSheet, sheetId) {
  var ss = SpreadsheetApp.openById(masterSheet);
  var sheet = ss.getSheets();

  for( i in sheet )
    if( sheetId == sheet[i].getSheetId())
      return sheet[i];

  return -1;
}

Upvotes: 2

Vasim
Vasim

Reputation: 3143

You can use getSheetId().

var ssValve = SpreadsheetApp.openById('Key').getSheets()[0]

Change [0] to [1] [2] [3] and so on..for going from sheet1 to 2, 3 ,4 and so on

More info here

Upvotes: -1

Related Questions