Google Apps脚本以两次静默方式运行部分onEdit脚本 [英] Google Apps Script silently running portions of my onEdit script twice
问题描述
我有以下Google Apps脚本代码，该代码是通过对具有下拉数据验证(从列表中选择)的单元格进行编辑而触发的.有时，它会将两个重复的行添加到目标表，而不是所需的单行.我在多个位置添加了Logging，以查看是否可以检测到何时/何地/为什么某段代码运行两次.日志不会显示脚本的任何部分正在意外运行.我还添加了锁定.尽管它确实使脚本以更可靠的方式运行，但这并没有帮助解决此问题.我确实想深入了解这一点，尽管目前这实际上只是一个令人讨厌的事情.请让我知道是否需要其他信息:
I've got the following Google Apps Script code which is triggered by an edit to a cell that has a dropdown data validation (Pick from List) on it. Occasionally it adds two duplicate rows to the target table instead of the desired single row. I've added Logging in a variety of locations to see if I can detect when/where/why some piece of code is running twice. The logs don't show any sections of the script running unexpectedly. I've added Locking as well. It didn't help with this issue, although it did make the script run in a more reliable fashion. I really want to get to the bottom of this although it is really just a nuisance at this point. Please let me know if you need any additional info:
function onEdit(e) {
Logger.log('Running onEdit');
var lock = LockService.getUserLock();
lock.waitLock(10000);
var cache = CacheService.getUserCache();
var spreadsheet = SpreadsheetApp.getActive().getActiveSheet();
var range = e.range;
var col = range.columnStart;
var row = range.rowStart;
var status = range.getDisplayValue();
if (col == 7 && row >= 13 && row <= 53) { //Ongoing Block
cache.put("oldStatus", e.oldValue);
Logger.log(row,col,status, e.oldValue);
if (status == 'ToVendor') { //Send to Vendor Block
copyEquipRow(spreadsheet,row,col,1,true);
Logger.log(row,col,cache.get("equipID"),cache.get("dateAdded"),cache.get("priority"),cache.get("problem"),cache.get("location"),cache.get("targetDate"));
pasteToVendor(spreadsheet);
}
if (status == 'AddReq') { //Add Related Req to the Req Block
copyEquipRow(spreadsheet,row,col,1,false);
Logger.log(row,col,cache.get("equipID"),cache.get("dateAdded"),cache.get("priority"),cache.get("problem"),cache.get("location"),cache.get("targetDate"));
addReq(spreadsheet);
spreadsheet.getRange(row, col).setValue(e.oldValue)
}
}
if (col == 16 && row >= 31 && row <= 37) { //Vendor Block
cache.put("oldStatus", e.oldValue);
Logger.log(row,col,status, e.oldValue);
if (status == 'ToOngoing') {
copyEquipRow(spreadsheet,row,col,0,true);
Logger.log(row,col,cache.get("equipID"),cache.get("dateAdded"),cache.get("priority"),cache.get("problem"),cache.get("location"),cache.get("targetDate"));
pasteToOngoing(spreadsheet);
}
}
lock.releaseLock();
}
function copyEquipRow(spreadsheet,row,col,problemCol,deleteRow) { //Status Equip # Date Added Priority Problem Location Target Date
Logger.log('Running CopyEquipRow');
var cache = CacheService.getUserCache();
var locationCol = 5 + problemCol;
var targetCol = 6 + problemCol;
var clearCol = 7 + problemCol;
var dateAdded = Utilities.formatDate(spreadsheet.getCurrentCell().offset(0, 2).getValue(), "EST", "M/d/yyyy");
var targetDate = Utilities.formatDate(spreadsheet.getCurrentCell().offset(0, targetCol).getValue(), "EST", "M/d/yyyy");
cache.put("equipID", spreadsheet.getCurrentCell().offset(0, 1).getValue());
cache.put("dateAdded",dateAdded);
cache.put("priority",spreadsheet.getCurrentCell().offset(0, 3).getValue());
cache.put("problem",spreadsheet.getCurrentCell().offset(0, 4).getValue());
cache.put("location",spreadsheet.getCurrentCell().offset(0, locationCol).getValue());
cache.put("targetDate",targetDate);
if (deleteRow == true ) {
spreadsheet.getRange(row, col, 1, clearCol).clearContent();
}
}
function pasteRow(spreadsheet,startCell,problemCol,includeStatus,dateOverride) {
Logger.log('Running pasteRow');
var cache = CacheService.getUserCache();
var currentCell = spreadsheet.getRange(startCell).activate();
var row = 0;
while (currentCell.offset(row, 1).isBlank() == false) {
row = ++row;
}
var oldStatus = cache.get("oldStatus");
var equipID = cache.get("equipID");
var dateAdded = cache.get("dateAdded");
var priority = cache.get("priority");
var problem = cache.get("problem");
var location = cache.get("location");
var targetDate = cache.get("targetDate");
var locationcol = 5 + problemCol;
var targetcol = 6 + problemCol;
Logger.log(equipID,dateAdded,priority,problem,location,targetDate);
if (includeStatus == true) {
currentCell.offset(row, 0).setValue(oldStatus);
}
currentCell.offset(row, 1).setValue(equipID);
if (dateOverride == true ){
currentCell.offset(row, 2).setValue(Utilities.formatDate(new Date(), "EST", "M/d/yyyy"));
}
else {
currentCell.offset(row, 2).setValue(dateAdded);
}
currentCell.offset(row, 3).setValue(priority);
currentCell.offset(row, 4).setValue(problem);
currentCell.offset(row, locationcol).setValue(location);
currentCell.offset(row, targetcol).setValue(targetDate);
}
function pasteToVendor(spreadsheet) {
Logger.log('Running pasteToVendor');
pasteRow(spreadsheet,'P30',0,true);
}
function pasteToOngoing(spreadsheet) {
Logger.log('Running pasteToOngoing');
pasteRow(spreadsheet,'G12',1,true);
}
function addReq(spreadsheet) {
Logger.log('Running addReq');
pasteRow(spreadsheet,'O12',1,false,true);
}
推荐答案
onEdit(e)函数响应简单的触发器，该触发器无法执行需要权限的操作.
The onEdit(e) function responds to the simple trigger which cannot perform operations that require permission.
不稳定的onEdit触发器与您选择的函数绑定在一起，它获得与简单触发器相同的事件对象.如果您不将可安装的触发器重命名为onEdit()之外的其他名称，那么您肯定一直都在获得两个触发器.
The instable onEdit trigger is tied to a function of you choice and it gets the same event object as the simple trigger. If you don't rename the installable trigger to something other than onEdit() then you're definitely getting two triggers all of the time.
还请记住，onEdit触发的函数必须在30秒内完成.
Also keep in mind onEdit triggered functions must complete within 30 seconds.
这篇关于Google Apps脚本以两次静默方式运行部分onEdit脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
