特定Google表格标签的onSelectionChange [英] onSelectionChange for a specific Google Sheets tab

问题描述

这是我的 测试表 .

Here is my test sheet.

目标:每当我单击表1"中的单元格A5:A10时，我都希望A1的值更改为B5:B10.例如:如果我单击A7，则A1 = B7.

Goal: whenever I click on cells A5:A10 in 'Sheet 1', I want the value of A1 to change to B5:B10. For example: if I click A7, A1 = B7.

注意:我不希望此脚本针对其他任何工作表或文档运行.

Note: I don't want this script to run for any other sheet or document.

您能帮我创建一个为此目的自动运行的脚本吗?

Can you please help me create a script to run automatically for this purpose?

推荐答案

作为 Marios 建议的替代方案，我宁愿尽早退出(因为 onSelectionChange 可以非常迅速地触发，所以我发现它的性能更高).因此，您可以将支票移到函数顶部(其余部分仍然适用):

As an alternative to what Marios suggests, I prefer exiting as early as possible (since the onSelectionChange can fire very rapidly, I find it somewhat more performant). So, you can move your check to the top of the function (the rest still apply):

function onSelectionChange({ range }) {
  const sh = range.getSheet();
  const shname = sh.getSheetName();

  if( shname !== "<sheet name here>" ) { return; }

  //continue if ok
}

---

1. 请注意，通常，为了便于维护，最好将工作表名称放在配置对象中(或者甚至最好在返回配置对象的函数中).
2. 此外，由于每个工作表都有一个唯一的ID(您可以在打开的电子表格URL的 gid 锚点中直观地找到它，也可以使用下面提到的方法以编程方式找到它)，这样可以为您省去一些麻烦工作表将被重命名并使用 getSheetId :

1. Note that usually, it is better to put the sheet name in a configuration object (or, even better, in a function that returns a configuration object) for easy maintenance.
2. Also, since each sheet has a unique Id (you can visually find it in the gid anchor of the open spreadsheet URL or programmatically with the method mentioned below), you could save you some trouble if the sheet gets renamed and check for id match instead with getSheetId:

function onSelectionChange({ range }) {
  const sh = range.getSheet();
  const id = sh.getSheetId();

  if( id !== 123456789 ) { return; }

  //continue if ok
}

这篇关于特定Google表格标签的onSelectionChange的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！