Get Google Sheet Tab Name and ID with Google Sheets API Advanced Service with Apps Script

The Google Sheets API Advanced Service has a strange habit of switching between using Google Sheet names like 'Sheet1' and the sheet ID that you would see in the URL, like 482103711.

This can be problematic when working with complex processes in the Sheets API.

One of the first things I do when running a complex process is to make an initial call to the Sheets.Spreadsheets.get() method to generate an array of all of the sheet tabs by name and sheet ID. Yes, it is an additional call to the API but if we do it initially in conjunction with any other core requests from the spreadsheet then we only need to do it one time.

• Starter Sheet

Let’s take a look at some sample code:

The Code Example

Our goal below is to extract all of the sheet tab names by title and sheetId.

Add the Google Sheets API Advance Service

Before you start, you will need to include the Google Sheets API advanced service in your project.

1. Click the plus icon next to ‘Service’.
2. Select Google Sheets API.
3. Ensure v4 is selected.
4. Use Sheets as the identifier for the API in the project.
5. Click ‘Add’.

Runsies

In the example code above we are calling our simple getSheetNamesAndIds() method from the runsies() function.

We are providing our own sheet example which comes from this sample sheet:

• Starter Sheet

You will need to add your own sheet ID here. Of course, if your project is bound to an existing sheet,  you could always use the SpreadsheetApp.getActiveSpreadsheet().getId() method to do this.

About Type Definitions

We’ll use the Google Apps Script’s preferred JSDoc documentation notation to assist with autocompletion using @typedef for both the array and the sheet info.

The Sheets.Spreadsheets.get() method

Line 23

the Google API Sheets.SpreadSheets.get() method takes two parameters:

1. The spreadsheet ID
2. (optional) A payload of arguments

Without the optional payload, you will get the full bloated object containing all the properties of the Google Sheet. This can be quite a large dataset.

Of course, the larger the dataset, the longer it takes to transfer this information to you slowing down your runtime.

 

The Payload

Lines 18-20

There are a few things you can add to the payload, but for us, we want to use the ‘fields’ property. The fields property is a field mask that allows us to only retrieve what we choose from the API and, significantly minimise the returned object and speed up our process.

When preparing this function, I would first run the get() method without the fields payload to find where the sheet tab data is stored and then build the fields string from this.
"sheets(properties.sheetId,properties.title)"

This means that inside the object it would be this:

As you can see, curved braces indicate arrays and dots connect nested properties.

Try…Catch Statement

Lines 22, 29-31

We include a JavaScript try…catch statement to attempt to make a request to the Sheets API.

If the request fails the catch statement allows us to generate a custom error. I have left two options here. In the execution, you may just wish to log an error with console.error() method. However, if you wish to terminate the process use the JavaScript throw new Error() approach.

The Returned Response

Line 23

The response from the get() method call is stored in the resp variable. Using our example the raw response should look a little like this:

As you can see, this is not the cleanest of responses, yet. Let’s tidy it up.

Mapping an array of just titles and sheet IDs

Line 26

Finally, we use the JavaScript map method to iterate over the resp.sheets property. On  each iteration of each sheet, we select the properties erm… property and return the tile and sheetId before it is returned from the function.

The sheets variable should then look like this:

Conclusion

I have a more practical use case for extracting the ID and title of all the Google Sheets with Google Sheets API advanced service in my tutorial:

Creating a Google Sheets Row Archiver with Google Sheets API Advanced Service and Apps Script

It is a much more practical and useful example.

In the comments, I would love to know how you would apply and modify this code for your own products. Your comments provide insight and inspiration for many developers.

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

~ Yagi