Google App Scripts OnEdit Trigger Help to avoid "too many simultaneous invocations"

Question

I'm using google sheets to keep track of program details and send emails based on the information in the sheet. Each google sheet tab has 3-5 different emails that can be sent. The emails are controlled by a simple script and an "onEdit" trigger, which looks for "Send!" to be selected in a column, and then sends the appropriate email. The issue is when making lots of changes to the sheet rapidly, all the different triggers fire and the app script fails either with the error message "Service using too much computer time for one day" or "Too many simultaneous invocations". Once these google app script limits are reached they take 24 hours to reset.

In the past I'd added "Utilities.sleep(2000)" with different time intervals to avoid the "too many simultaneous..." but that only wastes more runtime and gets me to the "too much computer time..." error. So, how can I modify the script code or triggers to still operate when "Send!" is selected in the spreadsheet, but not experience these other google sheets timeouts?

Thank you in advance for any help.

Here's a sample of one of the email scripts. The triggers are the "on edit" type.

function SendDD01Confirmation() {

  Utilities.sleep(1000)

  var sheetNameToWatch = "DD";
  var columnNumberToWatch = 24; // column A = 1, B = 2, etc.
  var valueToWatch = "Send!";

  var emailAddressColumn = 5;
  var namecolumn = 3;
  var datecolumn = 1;
  var datewrittencolumn = 6;
  var schoolcolumn = 2;
  var timescolumn = 7;

  var statuscolumn = 25;  
  var statusValue = "✓";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveCell();

  var emailAddress = sheet.getRange(range.getRow(), emailAddressColumn).getValue();
  var name = sheet.getRange(range.getRow(), namecolumn).getValue();
  var date = sheet.getRange(range.getRow(), datecolumn).getValue();
  var datewritten = sheet.getRange(range.getRow(), datewrittencolumn).getValue();
  var school = sheet.getRange(range.getRow(), schoolcolumn).getValue(); 
  var times = sheet.getRange(range.getRow(), timescolumn).getValue();

  var status = sheet.getRange(range.getRow(),statuscolumn)  

  var emailSubject = "Program has been scheduled for " +date+ "!";

  var body = "Program is coming to your class! You are scheduled to receive a one-hour presentation on " +datewritten+ " at " +school+ ", with the following time(s):\n \n" +times+ "\n (Rest of email removed for brevities sake)";

  if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
       GmailApp.sendEmail(emailAddress, emailSubject, body)
       status.setValue(statusValue);

       }                                                                                                                                                                                                                                                  

}

Answer 1

Try it this way:

Your doing a lot of stuff in your code that you don't need to do. For one thing put the code that checks for the correct sheet right at the top so you don't have to do a bunch of useless declarations. Secondly, get all of the data at one time as I did with the getValues() and it would be preferable not to use intermediatevariables for the data values and instead just used the actual data elements like data[0][1] etc...

function SendDD01Confirmation(e) {
  var sh=e.range.getSheet();
  var n=sh.getName();
  if(n!="DD")return;//This is preferable close to the beginning of the function
  var statusValue = "✓";
  var data=sheet.getRange(e.range.rowStart,1,1,25).getValues();
  var emailAddress = data[0][4];//it would be better to remove these intermediate variables as well but it's a bit more difficult to trouble shoot.
  var name = data[0][2];
  var date = data[0][0];
  var datewritten = data[0][5];
  var school = data[0][1];
  var times = data[0][6];
  var status = data[0][24];
  var emailSubject = "Program has been scheduled for " +date+ "!";
  var body = "Program is coming to your class! You are scheduled to receive a one-hour presentation on " +datewritten+ " at " +school+ ", with the following time(s):\n \n" +times+ "\n (Rest of email removed for brevities sake)";
  if (e.range.columnStart == 24 && e.value == "Send!") {
    GmailApp.sendEmail(emailAddress, emailSubject, body);
    sh.getRange(e.range.rowStart,25).setValue(statusValue);
  }                                                                                                                                                                                                                                                  
}

You could probably scrunch it down to this:

function SendDD01Confirmation(e) {
  var sh=e.range.getSheet();
  var n=sh.getName();
  if(n!="DD")return;
  var statusValue = "✓";
  var data=sheet.getRange(e.range.rowStart,1,1,25).getValues();
  var emailAddress = data[0][4];//it would be better to remove these intermediate variables as well but it's a bit more difficult to trouble shoot.
  var emailSubject = "Program has been scheduled for " + data[0][0] + "!";
  var body = "Program is coming to your class! You are scheduled to receive a one-hour presentation on " + data[0][5] + " at " + data[0][1] + ", with the following time(s):\n \n" + data[0][6] + "\n (Rest of email removed for brevities sake)";
  if (e.range.columnStart==24 && e.value=="Send!") {
    GmailApp.sendEmail(emailAddress, emailSubject, body);
    sh.getRange(e.range.rowStart,25).setValue(statusValue);
  }                                                                                                                                                                                                                                                  
}