This week’s tip is a follow-up to last week’s post on working with ranges in Google Apps Script (GAS). Just like ranges, you need to be careful that you are working with the correct object if you’re dealing with sheets in GAS.
If you’re used to Excel and VBA, GAS vocabulary is slightly different. In Excel, the spreadsheet file is called a Workbook, and the sheets in a specific spreadsheet are called Worksheets. In GAS, the file is called a Spreadsheet, and the sheets are simply called Sheets.
Spreadsheets have unique alphanumeric keys (which you can find in their URLs).
Once you have the key of a spreadsheet, it’s easy to work with it in GAS. You can get data from any spreadsheet that you have editing permission for using SpreadsheetApp.openById().
Referring to specific sheets can be done in a few different ways. The SpreadsheetApp class has a method getSheets(), which returns an array of Sheet objects in the spreadsheet. Initially, I thought that you could use getSheets() and that the sheets would stay in order. Totally wrong. If sheets are moved around or added, getSheets()[1] may refer to a different sheet.
A better way of referencing sheets is to get their unique IDs or names. Names can change, of course, but they can be a good way of referencing sheets if you are looping through multiple spreadsheets with identical naming conventions.
Whichever method you use, make sure that you’re aware what level you’re working on. Spreadsheets don’t really store data; the sheets in them do. If you try to get data from a spreadsheet without referring to a range or a sheet, you’ll probably get headaches.