Change Tab Detection on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script

GasTips
Google Apps Script / Spreadsheet / event / trigger

Gists

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. So in order to test this event trigger, I prepared a simple sample script. This is a sample script for detecting the change tab on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script.

Demo

Usage

  1. Please copy and paste the following 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);
}

References

 Share!