Is there a google spreadsheet trigger that fires after a tab is changed?

Question

I am trying to find a way to fire a trigger when the tab changes from Sheet1 to Sheet2. I thought the onChange trigger would do it but it turns out it functions when new sheets are added or columns/rows are deleted.

Regards Saad

Answer 1

onSelectionChange has been released at April 22, 2020.

But this couldn't be used at the released day. But now, I could confirm that this got to be able to be used. By this, in the current stage, your goal can be achieved using the onSelectionChange Event Trigger.

The sample script is as follows.

Usage

1. Please copy and paste the following sample script to the container-bound script of Google Spreadsheet, and save the script.
2. Please reopen the Google Spreadsheet.
   • By this, onOpen is run and the current sheet is put to PropertiesService.
   • Unfortunately, in the current stage, it seems that the event object of onSelectionChange has no information about the change of tab. So in order to detect the change of tab, I used the PropertiesService.
3. Then, please select a cell and cells on sheet.
   • By this, onSelectionChange is run by the onSelectionChange event trigger, and put the A1Notation to the cell.
   • When the active tab is moved, the sample script detects this, and the information of the changed tab is put to the cell.

Sample script:

function onOpen(e) {
  const prop = PropertiesService.getScriptProperties();
  const sheetName = e.range.getSheet().getSheetName();
  prop.setProperty("previousSheet", sheetName);
}

function onSelectionChange(e) {
  const prop = PropertiesService.getScriptProperties();
  const previousSheet = prop.getProperty("previousSheet");
  const range = e.range;
  const a1Notation = range.getA1Notation();
  const sheetName = range.getSheet().getSheetName();
  if (sheetName != previousSheet) {
    range.setValue(`Changed tab from ${previousSheet} to ${sheetName}. ${a1Notation}`);

    // When the tab is changed, this script is run.

  } else {
    range.setValue(a1Notation);
  }
  prop.setProperty("previousSheet", sheetName);
}

Result:

When above script is used, when the cell click and change of tabs can be detected as follows.

Note:

• Please use this script with V8.

References

• onSelectionChange(e)
• Properties Service

Answer 2

There is no trigger to do this as nothing in the spreadsheet it changed.
Changing the tab that is being viewed by the user is an (almost) purely client side action that doesn't modify any properties in the backend.

Google Spreadsheets as you view them are just an html representation of all the values, formulae and scripts that are stored. Part of this html representation is the fact that the sheets are displayed as tabs.