Google sheet script linked sending duplicate emails when form is submitted

Question

I'm having an issue where a script I wrote is sending duplicate emails when a form is submitted. The script is also executing on occasion when I simply open the spreadsheet. I only have one trigger set up to run the script when the form is submitted, and I'm the only one with edit access to the sheet. I tried deleting the script project altogether and creating a new one, which didn't resolve the issue. I'm not sure if there's anything wonky with my script, but here it is:

function sendEmails() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Raw Data'); // Gets Raw Data Sheet
  var lastRow = sheet.getLastRow(); // Gets last row of sheet everytime the form is submitted
  var lastColumn = sheet.getLastColumn(); // Gets last column of sheet everytime the form is submitted
  var value = sheet.getRange(lastRow,1,lastRow,lastColumn).getValues().toString(); 
  var comments = sheet.getRange(lastRow, 41).getValue().toString(); // Gets additional comments from inspection form submitted
  if (value.indexOf("NOT OK") > -1) {
    MailApp.sendEmail({
    to: "[email protected]",
    subject: 'Machine Issue',
    htmlBody: "An inspection of the xyz machine has returned issues: " + "<br/><br/>"
      + "<b>" + comments + "</b>" + "<br/><br/>" +
      " Click " + '<a href="https:goo.gl/ahGbGu&^"> <b>HERE</b></a>' 
      + " to see the last inspection report.",
    });
  } // Produces email based on defined parameters.
}

I've also tried deleting the trigger and setting up a new one, which hasn't worked either.

Answer 1

Check your executions log. If you have multiple executions but only one Form Responses line, this is a known bug with the form submit trigger. The only way to get around it is to use a script lock.

Like this:

     SpreadsheetApp.flush();
     var lock = LockService.getScriptLock();
  try {
    lock.waitLock(15000); // wait 15 seconds for others' use of the code section and lock to stop and then proceed
     } catch (e) {
        Logger.log('Could not obtain lock after 30 seconds.');
        return HtmlService.createHtmlOutput("<b> Server Busy please try after some time <p>")
        // In case this a server side code called asynchronously you return a error code and display the appropriate message on the client side
        return "Error: Server busy try again later... Sorry :("
     }
START NORMAL CODE HERE

I've only got one script where this is really a problem but it was a really awful problem, up to six executions per form submission and the scriptlock is the tidiest way to lock it down. If your code itself takes less than 15 seconds reduct your waitlock time so the extra copies give up faster. If you use this method you'll still see the extra copies in the executions log, but they'll only be 15 seconds long. It's very satisfying to watch them caught and killed in this way.

Answer 2

I had an issue with multiple emails on form submission too. I noticed that my code was sending an email for every send email function i had. Like, it was based on score email, so what i did is, i closed function for every alternative, so, it would check points, if it didnt met the requirements, then it just wouldnt send email, and go for the next function, until it found a the function that met requirementes.

I think that in your code, its reading send emails twice, so he reads on the send emails, look for requirements, and sends. Then, it reads the next send emails, looks again for requirements and send email again.

Its like your giving an order to send emails twice.

Answer 3

This line has a problem:

var value = sheet.getRange(lastRow,1,lastRow,lastColumn).getValues().toString(); 

Let's say lastRow is 20. Then this code with get the last row plus the next 19 rows of values which presumably are all blank. The third parameter is the number of rows and the fourth is the number of columns.

It's better to pass the event object into the function and use e.values rather than having to go get the last line. If you get multiple form submissions one after another you may in fact be getting the wrong data.

This line also has a problem:

htmlBody: "An inspection of the xyz machine has returned issues: " + "<br/><br/>"
      + "<b>" + comments + "</b>" + "<br/><br/>" +
      " Click " + '<a href="https:goo.gl/ahGbGu&^"> <b>HERE</b></a>' 
      + " to see the last inspection report.",
    });

The comma at the end of the htmlBody parameter should be removed.

Try this code:

function sendEmails(e) {
  var value=e.values.toString();
  var comments=e.values[40]; 
  if (value.indexOf("NOT OK") > -1) {
    var html="An inspection of the xyz machine has returned issues: "; 
    html+="<br/><br/>" + "<b>" + comments + "</b>" + "<br/><br/>" + " Click " 
    html+='<a href="https:goo.gl/ahGbGu&^"> <b>HERE</b></a>' + " to see the last inspection report.";
    MailApp.sendEmail({to: "[email protected]",subject: 'Machine Issue',htmlBody: html});
    //Logger.log(html);
  } 
}

• Form Submit Event Object

I played around with this a little more and according to @J. G. there is a problem with onFormSubmit triggers returning multiple triggers. I solved the situation for my testing by using the following code which I was using to log onFormSubmit triggers.

function testFormSubmission(ev) {
  var lock=LockService.getUserLock();
  try{
    if(ev.values && !ev.values[1]){throw('Spurious Returns Error');}
      if(lock.tryLock(10000)) {
      var ss=SpreadsheetApp.getActive();
      var sh=ss.getSheetByName('LogSheet');
      var tA=[Utilities.formatDate(new Date(), Session.getScriptTimeZone(),"d/M/yyyy HH:mm:ss")];
      tA=tA.concat(ev.values);
      tA.splice(tA.length-1,1,ev.triggerUid,ev.range.rowStart,ev.range.columnEnd,JSON.stringify(ev.values));
      sh.appendRow(tA);
      lock.releaseLock();  
    }
  }
  catch(error){
    console.error(error);
    return;
  }
}