Tuesday tip: Spreadsheets and sheets in Google Apps Script

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

Example of Google Spreadsheet URL with key indicated

The spreadsheet key is in the middle. The sheet you’re working on is indicated by the fragment at the end.

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.

GAS example of referencing spreadsheets and sheets

Don’t mix up spreadsheet and sheet keys.

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.

Advertisements

About Finn Smith

Oregon... some places.... SF. Working in education, technology, and social impact (or some combination of the three).
This entry was posted in Google Apps Script. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s