Send an email on submission of a Google form

Question

Whenever users submit a Google Form to my Google Sheet, I want to trigger a OnSubmit function to automatically send an email reply using the email address in the form field, together with a calculated 'right/wrong' data verification of the submission. Since the latest form submission data goes into the last row of the sheet, I tried the following code to pull out both data from the sheet (even though I could use the function parameter for the Email Address).

I also set up the on form submit event trigger and email authorization already. But the emailing doesn't seem to work. Do I still need to run the function in script editor if it is set to trigger on form submission? The code exceeds execution time when I try clicking on Run though.

function OnSubmit(e) {  
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = sheet.getLastRow()-1; 
  var numRows = 1; // Number of rows altogether


  // Fetch the range of cells A(last row):G(last row)
  var dataRange = sheet.getRange(startRow, 1, numRows, 7);

  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
   var row = data[i];
   var emailAddress = row[2];   // Column C of last row
   var message = ""; 
   while(row[6] === "") {     // Delay until data verification is done
     Utilities.sleep(10000);
   }
   var subject = row[6]  // Column G (data verification cell) of last row
   MailApp.sendEmail(emailAddress, subject, message);
  }
}

Could you elaborate please? This is my first time trying out Google Apps script. I put the code together based on what I read online. — Kah Kien
You can't use this part of code : while(row[6] === "") { // Delay until data verification is done Utilities.sleep(10000); } because your data won't be updated during script execution so the loop will always be infinite if row[6] is empty when the function is called. — Serge insas

Unknown Unknown · Accepted Answer · 2016-07-20T19:17:37

I get that you are trying to make the script wait until the calculation is complete.

However,

while(row[6] === "") {  
 Utilities.sleep(10000);
}

is a wrong way to go about this: the variable row isn't changing in the loop. It could if the data-fetching line var data = dataRange.getValues(); was inside of the while loop.

But there is a better way to ensure than pending changes actually happen: use

SpreadsheetApp.flush();

before var data = dataRange.getValues();

This will apply pending computations, and eliminate the need for that while loop. Result:

function OnSubmit(e) {  
  SpreadsheetApp.flush(); 
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange(sheet.getLastRow(), 1, 1, 7);
  var data = dataRange.getValues();
  var row = data[0];
  var emailAddress = row[2];   // Column C of last row
  var message = ""; 
  var subject = row[6]  // Column G (data verification cell) of last row
  MailApp.sendEmail(emailAddress, subject, message);
}

Send an email on submission of a Google form

1 Answers