更改标签页后，会触发Google电子表格触发器吗? [英] Is there a google spreadsheet trigger that fires after a tab is changed?

问题描述

我正在尝试找到一种方法来在选项卡从表格到工作表中更改时触发触发器.我以为onChange触发器会做到这一点，但事实证明，它在添加新工作表或删除列/行时起作用.

问候萨德

解决方案

onSelectionChange 已在

注意:

• 请将此脚本与V8配合使用.

参考文献

• onSelectionChange(e)
• 物业服务

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

解决方案

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

这篇关于更改标签页后，会触发Google电子表格触发器吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！