用编程式编辑触发onEdit（） [英] Trigger onEdit() with a programmatic edit

问题描述

当onEdit（）触发器在成功触发时触发我实际上打开了工作表并进行了编辑。

但是，此工作表是通过脚本编程编辑的。 onEdit（）是否可以根据脚本编辑进行触发？我一直无法做到这一点。

使用onEdit（）触发器触发的脚本为：

  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（）函数，如果最新的是同样的项目。

如果更改是从另一个项目（另一个电子表格或webapp）进行的，那么它将变得相当困难。 （让我们知道它是否是你的用例）

---

编辑下面的评论：

这个想法是监控工作表的长度，将值保存在某处（例如在脚本属性中），如果已添加一行，则调用您的函数。

这个小代码应该诀窍，你应该设置一个时间触发器来偶尔调用 lookatsheet（），这取决于你需要多快反应。 ...我会说，每小时或30'不应该太多，你决定。
$ b $ pre $ function lookatsheet（） {
var ss = SpreadsheetApp.openById（'xxxxxxxxxxxxxxxxxxxxx'）; //要查看的SS的ID
var sh = ss.getSheets（）[0]; //第一张
var lastrow = sh.getLastRow（）;
var formertest = ScriptProperties.getProperty（'lastTest'）; //从最后一个测试中恢复值（需要调用一次才能启动一个有效值）
if（formertest sheetWasEdited（lastrow）; //用lastRow作为参数调用你的函数
ScriptProperties.setProperties（{'lastTest'：lastrow}，true）; //下次存储
}
}

函数sheetWasEdited（row）{//修改为与其他函数一起工作
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（））;
}


注意： 对于在第一次运行时注释邮件呼叫以避免发送邮件（只是为了启动脚本属性）

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

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

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.

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 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 webapp) then it will become quite harder to put in place. (let us know if it is your use case)

---

EDIT following your comments :

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.

This small code should do the trick, you should set a time trigger to call lookatsheet() once in a while, depending 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());
}

NOTE : may be useful to comment the mail call on the first run to avoid sending a mail ( just to initiate the script properties)

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