Google Form on Submit, Send Email Lock Doesn't Work

Question

I have created a google form with an email field. Responses are stored in a spreadsheet. On submit I take the last row from the sheet (latest response) and send an email to that user.

I have tried to implement a lock system, however it does not work the way I would like it too. If a there are multiple submissions in quick succession (i.e 3 within a minute) only the last user will get an email sent to them, they will in fact get 3 duplicate emails the number submitted in quick succession.

function EmailFormConfirmation() {
// Get a public lock on this script, because we're about to modify a shared resource.
var lock = LockService.getScriptLock()
// Wait for up to 30 seconds for other processes to finish.
lock.waitLock(30000);
 try {
var sheetname = "reg-responses"
var columnnumber = 4

var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var sheet = ss.getSheetByName(sheetname);

// Determines row number of most recent form submission and sets it as "lastrow"
if (sheet.getRange(sheet.getMaxRows(),1).getValue() != "") {
var lastrow = sheet.getMaxRows()
    } 
else {
  var count = 0
  for (var i = 0; i < sheet.getMaxRows(); i++) {
    if (sheet.getRange(sheet.getMaxRows()-i,1).getValue() != "") {
      var lastrow = sheet.getMaxRows()-i
      break;
    }  
  }
 }
  var email = sheet.getRange(lastrow,columnnumber).getValue();                            
  var name = sheet.getRange(lastrow,2).getValue();

  var message = "<HTML><BODY>"
    +"<h3>Your registration has been submitted</h3>"
    +"<p><b>Name</b>: "+name+"</p>"        
    +"</HTML></BODY>";      

  //validation is handled on the form as regex
 MailApp.sendEmail(email, "Form Submitted", "", {htmlBody: message}); 

 //flags it as email sent
 sheet.getRange(lastrow,8,1,1).setValue("Email Sent");

         //Here is the catch if the lock doesn't work
  if(!lock.hasLock()){

     Logger.log('Could not obtain lock after 30 seconds.');
  }
//Secondary catch for the entire function. 
} catch (e) {
Logger.log(e.toString());
}

 // Release the lock so that other processes can continue.
 lock.releaseLock();
}

What issue must I fix on my lock to resolve this?

You are getting the active spreadsheet, which means that the "On Form Submit" trigger is installed in the spreadsheet, as opposed to being installed in the Form. The function designated to be the "On Form Submit" trigger can receive an object of values. Typically, that object is assigned the variable named "e". See the following link: Apps Script Documentation - Event Objects - Sheets You can get the current Form submission values from the event object, which is better than retrieving them from the sheet. — Alan Wells
As your experience proves, the Lock Service does not do what most people assume it would accomplish. This is a problem. In certain situations, Lock Service is basically useless. I don't have the solution. It would be nice if Google would come up with a fix for this problem. — Alan Wells
hmm, lockService works fine. Whats the specific issue with locks? — Zig Mandel

Tudor Constantin Tudor Constantin · Accepted Answer · 2016-05-24T06:17:29

You can check all the rows in the sheet instead of just the last row and if the row doesn't have the flag set to "Email Sent", send the email.

Not the lock is the problem here, but the fact that you check just the last row.

Google Form on Submit, Send Email Lock Doesn't Work

1 Answers