google apps script - Is it possible to fire an event on active sheet change?

Question

Is there an event that fires when the active sheet changes in Google Sheets? Every time the result of getActiveSheet() changes, I would like to call a function to update some values within a sidebar I have built to assist navigation in a particularly large Google Sheets Spreadsheet.

Answer 1

You can use the onSelectionChange() simple trigger. It will fire whenever a new cell is selected, including when the active sheet changes. (This was introduced April 22, 2020, well after the original question was asked.)

The onSelectionChange(e) trigger runs automatically when a user changes the selection in a spreadsheet. Most onSelectionChange(e) triggers use the information in the event object to respond appropriately.

A stringified event object looks like this:

{
  "authMode": {},
  "range": {
    "rowEnd": 1,
    "columnStart": 1,
    "rowStart": 1,
    "columnEnd": 1
  },
  "source": {}, // Represents the spreadsheet itself
  "user": {
    "nickname": "NICKNAME",
    "email": "EMAIL"
  }
}

This will display a message box whenever the active sheet changes, except on the first run. It also attempts to minimize calls to the PropertiesService as a quota exists on it. Note that it uses the sheet ID as they are immutable, unlike sheet names.

function onSelectionChange(e) {
  if (activeSheetChanged(e.range.getSheet().getSheetId())) {
      Browser.msgBox(e.range.getSheet().getName());
  }
}

/**
 * Check if the active sheet changed.
 * Will give a false positive if no value exists in the properties.
 * @returns {boolean}
 */
function activeSheetChanged(newSheetId) {
  const key = 'activeSheetId';
  const cache = CacheService.getUserCache();
  let properties;
  const savedSheetId = getSavedSheetId();
  if (savedSheetId != newSheetId) {
    saveSheetId(newSheetId);
    return true;
  }
  return false;
  
  /**
   * Get the saved sheet ID from the Cache/Properties.
   * @returns {Number}
   */
  function getSavedSheetId() {
    let savedSheetId = cache.get(key);
    if (savedSheetId == null) {
      properties = getProperties();
      savedSheetId = properties.getProperty(key);
      cache.put(key, savedSheetId);
    }
    return cache.get(key);
  }
  
  /**
   * Save the sheet ID to the Cache & Properties
   */
  function saveSheetId(sheetId) {
    properties = properties ? properties : getProperties();
    properties.setProperty(key, sheetId);
    cache.put(key, sheetId);
  }
  
  /**
   * @returns {PropertiesService.Properties}
   */
  function getProperties() {
    return PropertiesService.getUserProperties();
  }
}