Google Sheet - Email

Question

I am looking at having automated emails trigger on submission of a google form. It is working fine, however I am facing 3 issues with the code below

Two Emails are getting triggered for every response whereas I only want One Email to go
Emails are getting sent from "Owner"'s ID instead of the one submitting the response on google form.
It is supposed to capture the status of all successfully sent email as "EMAIL SENT" in col 7, but, its not doing so.

// has been sent successfully.
var EMAIL_SENT = "EMAIL_SENT";

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = sheet.getLastRow();   // Number of rows to process
  var dataRange = sheet.getRange(numRows, 2, 1, sheet.getLastColumn());
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[5];  // First column
    var message = 'Hello,'+ "\n"
                   +  "\n" 
                   + 'We have received an inquiry from your customer in Inbound'+ ".\n"
                   + "\n"
                   + 'Lead No is' + " - " 
                   +  row[1] + "\n"
                   +  "\n"
                   + 'Kindly arrange a callback'+ "\n"
                   + "\n"
                   + 'Regards,'+ "\n"
                   + 'Team Inbound' + "\n"
                   + "\n"
                   + 'This is an auto-generated email'; // Second column
    var emailSent = row[7];     // Third column
    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      var subject = "Inbound Inquiry"+ " - " + row[1];
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 8).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

With regard to the two email issue this might be of some help: https://stackoverflow.com/questions/58781729/sending-emails-twice-with-mailapp-sendemail — Cooper, Nov 10 '19 at 02:48

Cooper · Answer 1 · 2019-11-10T02:09:28.197

0

Try it this way:

function sendEmails() {
  var sheet=SpreadsheetApp.getActiveSheet();
  var startRow=2;  
  var dataRange=sheet.getRange(startRow,1,sheet.getLastRow()-startRow+1,sheet.getLastColumn());//I think startRow is a better fit for the first parameter and my guess is that you did't really intend for data.length to be only 1 row.
  var data=dataRange.getValues();
  for (var i=0;i<data.length;++i) {
    var row=data[i];
    var emailAddress=row[5];//column 6
    var message=Utilities.formatString('Hello,\n\nWe have received an inquiry from your customer in Inbound.\n\nLead No is - %s\n\nKindly arrange a callback.\n\nRegards,\nTeam Inbound\n\nThis is an auto-generated email.',row[1]);    
    var emailSent = row[7];//column 8
    if (emailSent != "EMAIL_SENT") {  // Prevents sending duplicates
      var subject = "Inbound Inquiry"+ " - " + row[1];//column 2
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 8).setValue("EMAIL_SENT");//column 8 and I don't think it's necessary to flush in the loop
    }
  }
}

Check the comments to make sure I got the correct columns.

Read Sheet.getRange(row, column, number of rows, number of columns)

edited Nov 10 '19 at 02:09

answered Nov 09 '19 at 15:38

Cooper

59,616
6
23
54

For those of us who are less perceptive, would you please add some explanation about how your answer resolves the OP's question. I can see that your data range starts in Column A, the OP in Column B; your range covers all the data, the OP just one row; you don't flush (if you don't mind me saying so); and then what?? – Tedinoz Nov 09 '19 at 21:00
Well, my guess is that the OP didn't actually intend it to be only one row. So yes, that's a bit a guess. And in a similar fashion I don't think the OP really want to use numRows here `sheet.getRange(numRows, 2, 1, sheet.getLastColumn());` I think startRow makes more sense. And finally, I just don't see any need to flush. It just clogs up the loop. – Cooper Nov 10 '19 at 02:05
Sounds reasonable. But I'm still perplexed about how it resolves any of the OP's three issues. I guess the proof of the pudding is in the eating - the OP will hopefully respond at some point. – Tedinoz Nov 10 '19 at 02:13

Google Sheet - Email

1 Answers1