Sending duplicate emails on new form submission entry

0
votes

I have the following script which sends an email to the appropriate person based on one of the fields in the form submission. It is working appropriately however it is sending multiple emails each time a new entry is submitted and I'm not sure why. Can someone tell me what is wrong in my code to fix this?

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 2;   // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 3)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  var email1 = "[email protected]";
  var email2 = "[email protected]";
  for (i in data) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var message = row[1];       // Second column
    var subject = "Sending emails from a Spreadsheet";
  if (message = "cat") {
    MailApp.sendEmail(email1, subject, message);
  if (message = "dog") {
    MailApp.sendEmail(email2, subject, message);
  }
   }
    }
      }
2
google-apps-scriptgoogle-sheets
Use == or === to test for equivalency in your if statements​. As is both items are evaluating to true. – ScampMichael
@ScampMichael tried your suggestion but no luck, it's still sending multiple. It's as if it's not checking only the new row to determine where to send the email but it's checking all rows. I'm not sure how to fix this. – T D
How many of email1 and how many of email2? – ScampMichael
As you have written your code will iterate through each row in the data range and send an email accordingly – ScampMichael
You might want to see form submission triggers in the docs to see how you pull the last form data – ScampMichael

2 Answers

2
votes

These lines:

if (message = "cat") {
  MailApp.sendEmail(email1, subject, message);
if (message = "dog") {
  MailApp.sendEmail(email2, subject, message);
}

Should be:

var email;//Create a new variable

if (message == "cat") {
  email = email1;
} else if (message == "dog") {
  email = email2;
}

MailApp.sendEmail(email, subject, message);
1
votes

I'm pretty sure a form submission ends up on the last row and iterating through the rows is unnecessary. This assumes you have not adulterated your form submission sheet with array formulas and such.

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getLastRow();
  var dataRange = sheet.getRange(row, 1, 1, sheet.getLastColumn());
  var data = dataRange.getValues()[0];

  var email1 = "[email protected]";
  var email2 = "[email protected]";

  var emailAddress = data[0];  // First column
  var message = data[1];       // Second column
  var subject = "Sending emails from a Spreadsheet";

  if (message == "cat") {
    MailApp.sendEmail(email1, subject, message);
    };
  if (message == "dog") {
    MailApp.sendEmail(email2, subject, message);
    };
   }

The last portion could probably be improved with Sandy's suggestion or a switch>case