Reference sheet by name, not index?

Question

I have a Google Sheets workbook that links to other individual sheets in other workbooks. I need the linked (child) sheets to be aware of the Worksheet/Cell that linked to them (parent).

To reference the parent worksheet, I do this:

var linkCell = SpreadsheetApp.openById("1-Ekv2f4CYjf6ivGxsJCnAeSY_4b86k1gCw").getSheets()[1].getRange("D20");

This works great, but my users need to move the worksheets in the parent workbook occasionally, which breaks the links because the sheet index changes (in the above example, the index might change from [1] to [3]).

I tried to use the sheet name as the index (e.g. .getSheets()["sheetName"].getRange("D20"); but that doesn't seem to work. Right now I'm trying to do a funky workaround with a 'for each' loop, but it would sure be more straightforward to just reference the sheet name directly.

Can this be done?

Parag Jadhav Parag Jadhav · Accepted Answer · 2017-06-23T17:31:11

Dead simple man, you just need to use

//Get the worksheet object to get the range
var worksheet =SpreadsheetApp.openById("1-Ekv2f4CYjf6ivGxsJCnAeSY_4b86k1gCw").getSheetByName(<WORKSHEET_NAME_IN_DOUBLE_QUOTES>);

Hope this helps :)

Reference sheet by name, not index?

1 Answers