I'm struggling to understand why this script is executing 2, 3, even 4 times simultaneously every time a form is submitted. I have added the LockService, but while it does sometimes keep multiple emails from being sent, it doesn't seem to prevent the simultaneous executions from occurring.
Everything else is working fine--I just don't want the failed executions every time, because these are also disrupting subsequent legitimate trigger executions due to script timeouts.
I have to execute from the sheet, because I am calling data that is retrieved to the sheet based on form responses. The sheet is not shared, and there are no "orphaned" triggers. I've removed/re-created the trigger, and even re-created the sheet and script but still getting extra executions.
Thanks in advance for any suggestions!
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 protected]";
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."+"\n"+"If corrections are required, please contact the applicant directly."+"\n"+"A status email has been forwarded to the applicant and supervisor listed."+"\n"+"\n"+"--------------------------------------------------------------"+"\n"+"\n"+"Applicant Name: "+emplFName+" "+emplMName+" "+emplLName+"\n"+"Application Reference ID: "+appId+"\n"+"Dispatch: "+locDisp+"\n"+"Employee Phone: "+empPhone+"\n"+"Employee Alternate Phone: "+emplAltPhone+"\n"+"Employee Email: "+emplEmail+"\n"+"Home Unit ID: "+homeUnit+"\n"+"Employment Class: "+emplClass+"\n"+"\n"+"Supervisor Name: "+emplSup+"\n"+"Supervisor Phone: "+supPhone+"\n"+"Supervisor Email: "+supEmail+"\n"+"\n"+"Trainee Position: "+trainPosition+"\n"+"Taskbook Percentage Complete: "+tbPercent+"\n"+"Taskbook First Assignment: "+firstAssign+"\n"+"\n"+"IMT Trainee: "+trainIMT+"\n"+"Assigned to IMT: "+imtName+"\n"+"\n"+"All Required Training Completed?: "+reqTraining+"\n"+"Training Still Needed for Position: "+trainNeeded+"\n"+"\n"+"Comments: "+appComments+"\n"+"\n"+"Application Status: "+appStatus+"\n"+"Reason for Denial (if applicable): "+statusReason+"\n"+"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."+"\n"+"\n"+"--------------------------------------------------------------"+"\n"+"\n"+"Applicant Name: "+emplFName+" "+emplMName+" "+emplLName+"\n"+"Application Reference ID: "+appId+"\n"+"Dispatch: "+locDisp+"\n"+"Employee Phone: "+empPhone+"\n"+"Employee Alternate Phone: "+emplAltPhone+"\n"+"Employee Email: "+emplEmail+"\n"+"Home Unit ID: "+homeUnit+"\n"+"Employment Class: "+emplClass+"\n"+"\n"+"Supervisor Name: "+emplSup+"\n"+"Supervisor Phone: "+supPhone+"\n"+"Supervisor Email: "+supEmail+"\n"+"\n"+"Trainee Position: "+trainPosition+"\n"+"Taskbook Percentage Complete: "+tbPercent+"\n"+"Taskbook First Assignment: "+firstAssign+"\n"+"\n"+"IMT Trainee: "+trainIMT+"\n"+"Assigned to IMT: "+imtName+"\n"+"\n"+"All Required Training Completed?: "+reqTraining+"\n"+"Training Still Needed for Position: "+trainNeeded+"\n"+"\n"+"Comments: "+appComments+"\n"+"\n"+"Application Status: "+appStatus+"\n"+"Reason for Denial (if applicable): "+statusReason+"\n"+"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);
}I'd definitely remove the line:
Utilities.sleep(10000);
There's no reason to wait.
If you want to avoid rate limiting errors caused by Apps Script restrictions, then put calls to services inside a for loop, and only wait if there is an error. That way the code isn't waiting if it doesn't need to, and only waiting if it needs to.
And I'd increase the timeout time. Normally, the code will release the lock, so a long timeout doesn't affect that situation, but if the timeout ends before the code is done, that could cause a problem.
And if there is an error, then release the lock. So, the following code adds a try/catch block to handle that.
Also, you should test for an obtained lock before proceeding, and if the lock isn't obtained, then quit and notify someone that there was an error.
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 + "\n\n" +
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 + "\n\n" +
e.stack);
//Logger.log('error: ' + e.message + ' stack: ' + e.stack);
}
}