使用程序编辑触发 onEdit() [英] Trigger onEdit() with a programmatic edit

问题描述

我有一个脚本，它在编辑时从一张工作表中获取数据，并将最近添加的数据放入 ScriptDb.

I have a script that takes data from one sheet when it is edited and puts that recently added data into ScriptDb.

当我实际打开工作表并进行编辑时，onEdit() 触发器成功触发.

The onEdit() trigger is fired successfully when I actually open the sheet and make an edit.

但是，此工作表是通过脚本以编程方式编辑的.onEdit() 是否能够根据脚本所做的编辑触发?我没能做到.

However, this sheet is edited programmatically through a script. Is onEdit() able to fire based on edits made by a script? I have not been able to make it do so.

使用 onEdit() 触发器触发的脚本是:

The script that is fired with the onEdit() trigger is:

function sheetWasEdited(event) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastRowValues = sheet.getRange(lastRow, 2, 1, 2).getValues()[0];
  CgcEmailDatabase.addEmail(now=lastRowValues[0].toString(), email=lastRowValues[1].toString());
}

推荐答案

onEdit 触发器旨在在实际用户编辑电子表格时起作用，您描述的用例应该很容易实现，只需调用您的 sheetWasEdited() 如果最新的是同一项目的一部分，则来自另一个函数的函数.

The onEdit trigger is intended to work when an actual user edits a spreadsheet, the use case you describe should be easy to implement simply by calling your sheetWasEdited() function from the other function if the latest is part of the same project.

如果更改是从另一个项目(另一个电子表格或网络应用程序)进行的，那么实施起来会变得非常困难.(如果这是您的用例，请告诉我们)

If the changes are made from another project (another spreadsheet or a web app) then it will become quite harder to put in place. (let us know if it is your use case)

根据您的评论进行

这个想法是监控工作表的长度，将值保留在某处(例如在脚本属性中)，如果添加了一行，则调用您的函数.

The idea is to monitor the length of the sheet, keep the value somewhere (in script properties for example) and call your function if a row has been added.

这个小代码应该可以解决问题，你应该设置一个时间触发器来偶尔调用 lookatsheet() ，这取决于你需要多快的反应......我会说每个小时或 30' 不应太多，由您决定.

This small code should do the trick, you should set a time trigger to call lookatsheet() once in a while, depending on how fast you need to react... I'd say every hour or 30' shouldn't be too much, you decide.

function lookatsheet(){
  var ss = SpreadsheetApp.openById('xxxxxxxxxxxxxxxxxxxxx');// the ID of the SS you want to look at
  var sh = ss.getSheets()[0];// first sheet
  var lastrow = sh.getLastRow();
  var formertest = ScriptProperties.getProperty('lastTest');// recover the value from the last test (will need to get called once to initiate a valid value)
  if (formertest < lastrow){
    sheetWasEdited(lastrow);// call your function with lastRow as parameter
    ScriptProperties.setProperties({'lastTest': lastrow}, true);   // store for next time
}
}

function sheetWasEdited(row) {  // modified to work with the other function
  var sheet = SpreadsheetApp.openById('xxxxxxxxxxxxxxxxxxxxx').getSheets()[0]
  var lastRowValues = sheet.getRange(row, 2, 1, 2).getValues()[0];
  CgcEmailDatabase.addEmail(now=lastRowValues[0].toString(), email=lastRowValues[1].toString());
}

注意: 可能有助于在第一次运行时注释邮件调用以避免发送邮件(只是为了启动脚本属性)

这篇关于使用程序编辑触发 onEdit()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！