是否有可能在活动工作表更改上触发事件? [英] Is it possible to fire an event on active sheet change?

问题描述

当Google表格中的活动表格发生更改时，是否会触发一个事件?每次getActiveSheet()的结果更改时，我都想调用一个函数来更新侧边栏中的一些值，该边栏是为协助在特别大的Google Sheets电子表格中导航而构建的.

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.

推荐答案

您可以使用于2020年4月22日推出，早在提出原始问题之后)

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.)

当用户在电子表格中更改选择时，onSelectionChange(e)触发器将自动运行.大多数onSelectionChange(e)触发器使用事件对象中的信息进行适当的响应.

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"
  }
}

只要活动工作表发生更改，这将显示一个消息框，但第一次运行时除外.它还尝试在 quota存在的情况下就可以了.请注意，它使用工作表ID是因为它们是不可变的，与工作表名称不同.

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();
  }
}

这篇关于是否有可能在活动工作表更改上触发事件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！