Sending duplicate emails on new form submission entry

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 = "A@A.com";
  var email2 = "B@B.com";
  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 answers

  • answered 2017-06-17 18:10 Sandy Good

    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);
    

  • answered 2017-06-17 18:10 ScampMichael

    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 = "A@A.com";
      var email2 = "B@B.com";
    
      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