Activate a sheet in Google Sheets using Google Apps Script

In this tutorial, you will learn how to activate a sheet in Google Sheets using Apps Script.

What does it mean to activate a sheet?

The active sheet is just the sheet that is currently open. In the spreadsheet below, the active sheet is a sheet called Step 2. You can activate a sheet by manually opening it or programmatically using Apps Script. I'm confident you know how to do the former and in this tutorial, I will show you how to do the latter!

Use cases where activating a sheet programmatically could be useful

Use case 1: Building micro applications that require the user to provide step-by-step input

Activating a sheet using Apps Script can be very useful when you are building micro applications within Google Sheets that require the user to provide step-by-step input.

Let us say you are building an application where users need to provide some input configuration and then the application takes some action or generates some output. One way to structure the input configuration is by using a separate sheet for each step in the configuration process. These steps could be something like:

• Step 1: Configure the objective (Sheet 1)

• Step 2: Enter details for that objective (Sheet 2)

• Step 3: Scenario planning (Sheet 3)

• Step 3.1: View scenarios

• Step 3.2: Select the scenario

• Step 3.3: Generate a report for that scenario

• Step 4: Email the scenario (happens automatically at the end of Step 3)

Here, you might use a separate sheet for each of the first three steps. Apps Script can be used to automatically change the active sheet to the next one in the sequence when the user completes a step.

Use case 2: Making it easy for users to find the right sheet by building a custom navigation menu

This functionality can also be useful to build a navigation menu in your spreadsheet if you have lots of sheets and you want to organize them. Here, when users select a menu item from the menu, the corresponding sheet will be automatically activated.

How to set the active sheet in Google Sheets using Google Apps Script?

To activate a sheet, you need to first access its object and then use the activate() method. There are different ways to access a specific sheet and in this tutorial, we'll cover two of these methods

• Activate a sheet using its name

• Activate a sheet using its Id

Method 1: Activate a sheet in Google Sheets using its name

The activateSheetByName() method below accesses the sheet using its name and then uses the activate() method of the Sheet object to activate it.

function activateSheetByName(sheetName) {
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  sheet.activate();
}

Method 2: Activate a sheet in Google Sheets using its Id

The activateSheetById() method below accesses the sheet using its Id and then uses the activate() method of the Sheet object to activate it.

function activateSheetById(sheetId) {
 
  //Access all the sheets in the Google Sheets spreadsheet
  var sheets = SpreadsheetApp.getActive().getSheets();

  //Filter out sheets whose Ids do not match
  var sheetsForId = sheets.filter(function(sheet) {
    return sheet.getSheetId() === sheetId;
  });

  //If a sheet with the Id was found, activate it
  if(sheetsForId.length > 0)
    sheetsForId[0].activate();
}

Conclusion

In this tutorial, you learned how to activate a sheet programmatically using Google Apps Script. There are two common ways to activate a sheet: (1) using its name and (2) using its Id. This tutorial covered both of these methods.

Thanks for reading!