多次执行Google脚本，即使在表单提交触发器上锁定也是如此 [英] Multiple executions of Google Script, even with Lock on form submit trigger
本文介绍了多次执行Google脚本，即使在表单提交触发器上锁定也是如此的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我很难理解为什么这个脚本每次提交表单时都会同时执行2、3、甚至4次。我已经添加了LockService，但是虽然它确实可以有时阻止发送多封电子邮件，但它似乎并没有阻止同时执行。
其他一切正常--我只是不希望每次都执行失败，因为这些操作还会由于脚本超时而中断后续的合法触发器执行。
我必须从工作表执行，因为我调用的是基于表单响应检索到工作表的数据。工作表不是共享的，也没有"孤立"触发器。我已经删除/重新创建了触发器，甚至重新创建了工作表和脚本，但仍获得额外的执行。提前感谢您的建议！
数据-lang="js"数据-隐藏="假"数据-控制台="真"数据-巴贝尔="假">function zoneRepEmail(e) {
var lock = LockService.getScriptLock();
lock.waitLock(30000); // wait 30 seconds before conceding defeat.
// got the lock, you may now proceed
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('SupervisorApprovals');
var lookupRow = e.range.getRow();
var rowRange = sheet.getRange(1,1,sheet.getLastRow(),37);
var appEmailSentLog = rowRange.getCell(lookupRow,36);
var repEmailSentLog = rowRange.getCell(lookupRow,37);
Logger.log(appEmailSentLog);
var form = FormApp.openById('1efkzyAgHf3PjEnPXSXP4P5mTCnII_BQaf5jf2Apr8gM');
var startRow = 3;
var headers = sheet.getRange(2, 1, 1, sheet.getLastColumn()).getValues()[0];
var timestampColumn = sheet.getRange('K'+lookupRow);
var dateColumns = sheet.getRangeList(['X'+lookupRow, 'Y'+lookupRow]);
var pctColumns = sheet.getRange('Z'+lookupRow);
var phoneColumns = sheet.getRangeList(['P'+lookupRow,'Q'+lookupRow,'U'+lookupRow]);
var dataRange = sheet.getRange(lookupRow,1,sheet.getLastRow(),37);
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i){
var row = data[i];
var timestamp = e.namedValues["Timestamp"];
var columnOfEditUrl = 46;
var email_address = "email@email.com";
var columnOfHomeUnit = 44;
var columnOfAppId = 45;
var columnOfZoneRep = 47;
var columnOfZoneRepEmail = 48;
var colEmplLName = 2;
var emplLName = sheet.getRange(lookupRow, 12).getValue();
var colEmplFName = 3;
var emplFName = sheet.getRange(lookupRow, 13).getValue();
var emplMName = sheet.getRange(lookupRow, 14).getValue();
var locDisp = sheet.getRange(lookupRow, 15).getValue();
var empPhone = row[15];
var emplAltPhone = row[16];
var emplEmail = row[17];
var emplClass = row[18];
var emplSup = row[19];
var supPhone = row[20];
var supEmail = row[21];
var trainPosition = row[22];
var firstAssign = row[23];
var lastAssign = row[24];
var tbPercent = row[25];
var trainIMT = row[26];
var imtName = row[27];
var reqTraining = row[28];
var trainNeeded = row[29];
var appComments = row[30];
var homeUnit = row[31];
var appId = row[1];
var zoneRep = row[33];
var zoneRepEmail = sheet.getRange(lookupRow, 35).getValue();
var appStatus = row[6];
var statusReason = row[7];
var statusComment = row[8];
timestampColumn.setNumberFormat("mm/dd/yyyy hh:mm:ss");
pctColumns.setNumberFormat("###%");
dateColumns.setNumberFormat("mm/dd/yyyy");
phoneColumns.setNumberFormat("###-###-####");
var emailTo = emplEmail;
var message = "The following application to the Rocky Mountain Area Priority Trainee Program has been "+appStatus+" for "+emplFName+" "+emplMName+" "+emplLName+". Please carefully review all details in the application and update your zone sheet accordingly."+"
"+"If corrections are required, please contact the applicant directly."+"
"+"A status email has been forwarded to the applicant and supervisor listed."+"
"+"
"+"--------------------------------------------------------------"+"
"+"
"+"Applicant Name: "+emplFName+" "+emplMName+" "+emplLName+"
"+"Application Reference ID: "+appId+"
"+"Dispatch: "+locDisp+"
"+"Employee Phone: "+empPhone+"
"+"Employee Alternate Phone: "+emplAltPhone+"
"+"Employee Email: "+emplEmail+"
"+"Home Unit ID: "+homeUnit+"
"+"Employment Class: "+emplClass+"
"+"
"+"Supervisor Name: "+emplSup+"
"+"Supervisor Phone: "+supPhone+"
"+"Supervisor Email: "+supEmail+"
"+"
"+"Trainee Position: "+trainPosition+"
"+"Taskbook Percentage Complete: "+tbPercent+"
"+"Taskbook First Assignment: "+firstAssign+"
"+"
"+"IMT Trainee: "+trainIMT+"
"+"Assigned to IMT: "+imtName+"
"+"
"+"All Required Training Completed?: "+reqTraining+"
"+"Training Still Needed for Position: "+trainNeeded+"
"+"
"+"Comments: "+appComments+"
"+"
"+"Application Status: "+appStatus+"
"+"Reason for Denial (if applicable): "+statusReason+"
"+"Supervisor Comments: "+statusComment;
var appMessage = "The following application to the Rocky Mountain Area Priority Trainee Program has been "+appStatus+" for "+emplFName+" "+emplMName+" "+emplLName+". The application has been forwarded to the Zone Training Representative for processing."+"
"+"
"+"--------------------------------------------------------------"+"
"+"
"+"Applicant Name: "+emplFName+" "+emplMName+" "+emplLName+"
"+"Application Reference ID: "+appId+"
"+"Dispatch: "+locDisp+"
"+"Employee Phone: "+empPhone+"
"+"Employee Alternate Phone: "+emplAltPhone+"
"+"Employee Email: "+emplEmail+"
"+"Home Unit ID: "+homeUnit+"
"+"Employment Class: "+emplClass+"
"+"
"+"Supervisor Name: "+emplSup+"
"+"Supervisor Phone: "+supPhone+"
"+"Supervisor Email: "+supEmail+"
"+"
"+"Trainee Position: "+trainPosition+"
"+"Taskbook Percentage Complete: "+tbPercent+"
"+"Taskbook First Assignment: "+firstAssign+"
"+"
"+"IMT Trainee: "+trainIMT+"
"+"Assigned to IMT: "+imtName+"
"+"
"+"All Required Training Completed?: "+reqTraining+"
"+"Training Still Needed for Position: "+trainNeeded+"
"+"
"+"Comments: "+appComments+"
"+"
"+"Application Status: "+appStatus+"
"+"Reason for Denial (if applicable): "+statusReason+"
"+"Supervisor Comments: "+statusComment;
var subject = appStatus+": PT App for "+emplFName+" "+emplMName+" "+emplLName+" // "+trainPosition+" // "+timestamp;
Utilities.sleep(5000);//allow all column formats to be applied, then proceed.
if (repEmailSentLog.isBlank()){
GmailApp.sendEmail(zoneRepEmail, subject, message,{noReply:true});
repEmailSentLog.setValue('EmailSent');
}
if (appEmailSentLog.isBlank()){
GmailApp.sendEmail(emplEmail, subject, appMessage,{cc:supEmail,noReply:true});
appEmailSentLog.setValue('EmailSent');
}
}
lock.releaseLock(); Utilities.sleep(10000);
}推荐答案
我肯定会删除该行：
Utilities.sleep(10000);
没有理由等待。
如果您想避免Apps脚本限制导致的限速错误，那么将对服务的调用放在for循环中，只有出现错误才等待。这样，代码就不会在不需要时等待，而只在需要时等待。
并且我会增加超时时间。通常，代码会释放锁，因此长时间的超时不会影响这种情况，但如果超时在代码完成之前结束，则可能会导致问题。
如果出现错误，则释放锁。因此，下面的代码添加了一个Try/Catch块来处理该问题。
另外，您应该在继续之前测试获取的锁，如果未获得锁，则退出并通知某人有错误。
function zoneRepEmail(e) {
try{
var lock = LockService.getScriptLock();
lock.waitLock(60000); // wait 60 seconds before timing out
if (!lock.hasLock()) {//Failed to get lock
MailApp.sendEmail(Session.getEffectiveUser().getEmail(),
'Code Failed', 'The code for ABC failed');
//Logger.log('Could not obtain lock');
return;
}
for (i=1;i<4;i++) {//Try up to 3 times
try{
//Rate limited service call code here
break;
}catch(e){
if (i!==3){Utilities.sleep(i*2000);}
if (i>=3) {
MailApp.sendEmail(Session.getEffectiveUser().getEmail(),
'Code Failed', 'The code for ABC failed - ' + e.message + "
" +
e.stack);
}
};
}
//Code here
lock.releaseLock();
} catch(e) {
lock.releaseLock();//Release the lock if there is an error
MailApp.sendEmail(Session.getEffectiveUser().getEmail(),
'Code Failed', 'The code for ABC failed - ' + e.message + "
" +
e.stack);
//Logger.log('error: ' + e.message + ' stack: ' + e.stack);
}
}
这篇关于多次执行Google脚本，即使在表单提交触发器上锁定也是如此的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
