CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
T D
T D

Reputation: 143

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 = "[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);
  }
   }
    }
      }

Upvotes: 0

Views: 379

Answers (2)

ScampMichael
ScampMichael

Reputation: 3728

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

Upvotes: 1

Alan Wells
Alan Wells

Reputation: 31300

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

Upvotes: 2

Related Questions