0

I'm having an issue with my Google Script. It actually works as-is but the problem is that after the form is submitted and someone submits a new form the first submitted form will no longer send an email because it's looking for the LastRow. I'm having difficulties getting this to work correctly... Can someone assist?

The column highlighted in yellow, after it has been filled out by a vendor the 5th column to the right (green) should also fill in "Sent" letting the user know an email has been sent to the person who created the form.

Here's a picture of the form to hopefully get a better idea http://i57.tinypic.com/34tej4w.png

function sendNotification() {
 var doc = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = doc.getSheets()[0];
 var startRow = sheet.getLastRow();
 var dataRange = sheet.getRange(startRow, 1, 1, 21) ;
 var data = dataRange.getValues();
 var row;
 for (i in data) {
 row = data[i];
 var EMAIL_SENT = 'Sent';
 var emailSent = row[19];     // Third column
  if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates

   //Define Notification Details
  var subject = "Row 12 has been edited!";
  var body = 
          '<html><body><b><u>Product Check</u></b>'
           + '<br><b>Time: </b>'
           + row[0]
           + '<br><b>Email requested: </b>'
           + row[1]
           + '<br><b>Request date: </b>'
           + row[2]
           + '<br><b>ISBN: </b>'
           + row[4]
           + '<br><b>Product Title: </b>'
           + row[7]
           + '<br><b>Supplier Name: </b>'
           + row[8]
           + '<br><b>PO Number: </b>'
           + row[10]
           + '<br><b>Comments: </b>'
           + row[12]
           + '<br>View the spreadsheet here: <a     href="#"><b>Click here</b></a> </html></body>';
 if (row[13] != '' && row[0] != '' && row[18] == '') {
  var recipients = row[1];
  var subject = "Column 'N' was edited!";
  sheet.getRange(startRow, 19, 1, 1).setValue(EMAIL_SENT) && sheet.getRange(startRow, 19, 1, 1).setBackgroundColor("#00ff00");
  SpreadsheetApp.flush();

    MailApp.sendEmail({
    to: recipients,
    subject: subject,
    htmlBody: body}); 
 }
Joseph Steimle
  • 3
  • 2
  • So the problem occurs when you have multiple users accessing the same spreadsheet at the same time? – Alan Wells Sep 04 '14 at 02:05
  • We have 1 group of users to submit the forms and 1 group of users who edit the response form (column N) .. only the last row sends the email after column N has been edited. – Joseph Steimle Sep 04 '14 at 13:49

1 Answers1

0

The behavior you describe is normal and you discovered the origin yourself already...

Instead of using last row as reference just use the first row that has no 'EMAIL_SENT' in column corresponding to row[19], this row will be the right one.

But in case of concurrency only one request will be handled so I would suggest you use a timer trigger to run this code every few minutes so that all the submissions are treated in every case.

Another solution would be to use the on Form Submit trigger but get the value from the handler callback object instead of the sheet (e.namedValues, see doc here) but that would imply more changes in your code. In this latter case, every submission would be handled independently, even is 2 or more submissions come all together.

Serge insas
  • 45,904
  • 7
  • 105
  • 131
  • Thank you for your response. I already have a basic email being sent as the form is submitted that emails the person who submitted the form. The thing is, we have another set of users (column N for example) that manually edit the response form after the form has been submitted. so it has happened to where multiple forms have been filled and submitted before the other group (column N) has had time to edit. if this occurs, only the last row will send the email --none of the others will – Joseph Steimle Sep 04 '14 at 13:45
  • then the timer solution would be the best choice... any row without the "email_sent" flag would be sent. – Serge insas Sep 04 '14 at 14:12
  • Serge, Do you have any examples on how to use e.namedValues or lead me in the right direction? thanks for your time – Joseph Steimle Sep 16 '14 at 19:38
  • About e.namedValues see this other post http://stackoverflow.com/questions/25734624/replace-empty-field-with-string-on-google-forms/25786241#25786241 -about Triggers :simply create a trigger that runs your function every 5 minutes. If it doesn't take too long to execute it will be sufficient. If not you can setup a more complex trigger that runs only on "working hours". – Serge insas Sep 16 '14 at 19:42