How to loop an onEdit function to send emails from multiple rows in Google Sheets?

1
votes

I'm very new to this and my job has a Google sheet that has all of our lead information from FB, and I'm trying to have a function (onEdit) that will send out an email to the lead based on one cell's data. For example, when A2 contains "Follow Up", then it sends out an email to the address in D2, and the body text comes from J2 (the canned response for the "Follow Up" data validation). Right now this works for row 2, however when inputting "Follow Up" in any other row it sends every row in the list an email. For example, when triggering the event in A29 it sends the body text in column J to every email in the list. I'm trying to make the event only send one email at a time (based on the active cell meeting validation requirements).

I've done my best looking at other posts on here to piece this code together using a for loop to also check for new rows (new rows are consistently being added to the sheet). The three if statements are probably sloppy, but I don't know how to clean that up yet into one singular if statement. I was thinking of adding a column that states whether to the email has been sent already, and updating the for loop or if statement to not send an email to those rows. 

  var sheetname = "Prometheus";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname);
  var ac = sheet.getActiveCell();
  var endRow = sheet.getLastRow();
  var startRow = 1;

for (var i = endRow; i >= startRow; i--) {

  var rangeToCheck = sheet.getRange(i, 1, 1, 21);

  if (ac.getValue() == "Appt. Set (appointment is setup)") {
    data = rangeToCheck.getValues();
    Logger.log(data)
    MailApp.sendEmail({
      to: data[0][4],
      subject: "Appt. Confirmation",
      body: data[0][10]
    }); break;
  } else if (ac.getValue() == "Lead") {
    data = rangeToCheck.getValues();
    Logger.log(data)
    MailApp.sendEmail({
      to: data[0][4],
      subject: "Welcome",
      body: data[0][11]
    }); break;
  } else if (ac.getValue() == "Follow Up") {
    data = rangeToCheck.getValues();
    Logger.log(data)
    MailApp.sendEmail({
      to: data[0][4],
      subject: "Follow Up",
      body: data[0][12]
    });
  }
 }
}

I expect when my active cell meets the condition in the if statement, for only one email to be sent to the contact info within the same row. Right now it's sending emails to everyone in the sheet when the condition is met in one row.

1
javascriptfor-loopgoogle-apps-scriptgoogle-sheetswhile-loop

1 Answers

1
votes
  • When the values of column "B" of at the row with the active cell are "Appt. Set (appointment is setup)", "Lead" and "Follow Up", you want to send an email using the values of the row.
    • You want to change the mail body by the value of column "B".
  • For example, when you put "Follow Up" to the column "B", you want to run the script using the OnEdit event trigger.

If my understanding is correct, how about this modification? In this modification, the event object which can be retrieved by the OnEdit event trigger was used. Please think of this as just one of several answers.

Before you use this script, please install the OnEdit event trigger for RunByOnEdit() of the modified script.

How to install OnEdit trigger:

  • Open the script editor.
    • Edit -> Current project's triggers.
    • Click "Add Trigger".
    • Set RunByOnEdit for "Choose which function to run".
    • Set "From spreadsheet" for "Select event source".
    • Set "On edit" for "Select event type".

Modified script:

function RunByOnEdit(e) {
  var sheetname = "Prometheus";
  var sheet = e.range.getSheet();
  var range = e.range;
  if (sheet.getSheetName() == sheetname && range.columnStart == 2) {
    var data = sheet.getRange(range.getRow(), 1, 1, 21).getValues()[0];
    var object = {
      to: data[4] // Column "E"
    };
    if (e.value == "Appt. Set (appointment is setup)") {
      object.subject = "Appt. Confirmation";
      object.body = data[10]; // Column "K"
    } else if (e.value == "Lead") {
      object.subject = "Welcome";
      object.body = data[11]; // Column "L"
    } else if (e.value == "Follow Up") {
      object.subject = "Follow Up";
      object.body = data[12];  // Column "M"
    }
    if (object.subject) MailApp.sendEmail(object);
  }
}

References:

If I misunderstood your question and this was not the result you want, I apologize. At that time, in order to correctly understand your situation, can you provide a sample Spreadsheet? Of course, please remove your personal information.