如何使 onEdit() 触发功能适用于多个工作表 [英] How to make onEdit() trigger function apply to multiple sheets

问题描述

我有一个正在处理的 Google 表格.

I have a Google Sheet I'm working on.

我有一个脚本，当我在第一个选项卡上更新 column A 时，它会用时间戳填充 column B.但是我需要它在第二个选项卡上做同样的事情，但我无法让它在那里工作.我需要改变什么?

I have a script that fills in the column B with a timestamp when I update column A on the first tab. However I need it to do the same on the second tab, but I can't get it to work there. What do I need to change?

我当前使用的脚本是:

function onEdit(e) {

    var sheetToWatch= 'Wrong Grading',
        columnToWatch = 1,
        columnToStamp = 2;            //change all of these to your needs

    if (e.range.columnStart !== columnToWatch
      || e.source.getActiveSheet().getName() !== sheetToWatch
      || !e.value)
        return;
    e.source.getActiveSheet()
        .getRange(e.range.rowStart, columnToStamp)
        .setValue(new Date());
}

推荐答案

对于未来的读者，Paul 问题中的代码片段来自 Google 文档帮助论坛上的代码，其中包括详细的逐行说明.

For future readers, the code snippet in Paul's question is derived from code on the Google Docs help forum, which includes a detailed line-by-line explanation.

该函数使用变量 sheetToWatch 来标识 onEdit() 函数关心的一个表(又名标签").这个比较验证了这一点:

The function uses the variable sheetToWatch to identify one sheet (aka "tab") that the onEdit() function cares about. That is validated by this comparison:

|| e.source.getActiveSheet().getName() !== sheetToWatch

...如果当前触发事件的来源不匹配，则函数不做任何事情就退出.

...and if the source of the current trigger event is not matched, the function exits without doing anything.

我需要更改什么?如果您希望此功能对电子表格中的所有工作表起作用，那么您可以完全取消此检查:

What do I need to change? If you want this function to work on all sheets in the Spreadsheet, then you can just eliminate this check altogether:

function onEdit(e) {

    var columnToWatch = 1,
        columnToStamp = 2;               //change all of these to your needs

    if (e.range.columnStart !== columnToWatch
      || !e.value)
        return;
    e.source.getActiveSheet()
        .getRange(e.range.rowStart, columnToStamp)
        .setValue(new Date());
}

如果您想让 onEdit() 对一组工作表进行操作，那么您可以更改上述比较以检查是否在工作表名称数组中找到了当前工作表的名称.这将改变与此的比较:

If you want to have the onEdit() operate on a set of sheets, then you can change that above comparison to check if the current sheet's name is found in an array of sheet names. That will change the comparison to this:

  || sheetsToWatch.indexOf( e.source.getActiveSheet().getName() ) === -1 

您可以在此处了解有关 indexOf() 方法的更多信息.但是它正在做的是获取事件触发器源表的名称，在 sheetsToWatch 数组中找到它，并返回找到的索引.如果工作表没有出现在数组中，indexOf() 返回 -1.

You can learn more about the indexOf() method here. What it's doing though, is getting the name of the event trigger source sheet, finding it in the sheetsToWatch array, and returning the found index. If the sheet does not appear in the array, indexOf() returns -1.

得到的函数是:

function onEdit(e) {

    var sheetsToWatch= ['Wrong Grading',
                        'Something Else'],
        columnToWatch = 1,
        columnToStamp = 2;            //change all of these to your needs

    if (e.range.columnStart !== columnToWatch
      || sheetsToWatch.indexOf( e.source.getActiveSheet().getName() ) === -1 
      || !e.value)
        return;
    e.source.getActiveSheet()
        .getRange(e.range.rowStart, columnToStamp)
        .setValue(new Date());
}

这篇关于如何使 onEdit() 触发功能适用于多个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！