Send email with values for only new rows in a spreadsheet via Apps Script

Question

I'm trying to use a script to send an email when new rows are added to a review spreadsheet. There's a great thread on "Send single email with values from all new rows in a spreadsheet" but I've found that when using the trigger onEdit for the following script I'll still receive a blank email when any edit is made, even when all rows in column 9 (the notification sent column) are marked "sent".

Is there a way to add an if statement to only send an email if there is a new row with a blank column 9 (so I'm not receiving blank emails with the onEdit trigger)?

function sendEmail() {

//setup function
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
if (ActiveSheet.getName() == 'Review Tracker') {
var StartRow = 6;
var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,11);
var AllValues = WholeRange.getValues();
for (i in AllValues) {
  var row = AllValues[i];
  if (row[7] === "Ready for Review") {  

  var message = "";
  //iterate loop
  for (i in AllValues) {

  //set current row
  var CurrentRow = AllValues[i];

  //define column to check if sent
  var EmailSent = CurrentRow[10];

  //if row has been sent, then continue to next iteration
  if (EmailSent == "sent") 
  continue;

  //set HTML template for information
  message +=
      "<p><b>Name: </b>" + CurrentRow[0] + "</p>" +
      "<p><b>Client: </b>" + CurrentRow[1] + "</p>" +
      "<p><b>Deliverable Title: </b>" + CurrentRow[2] + "</p>" +
      "<p><b>Link to Review Thread: </b>" + CurrentRow[3] + "</p>" +
      "<p><b>Deadline for Review: </b>" + CurrentRow[4] + "</p>" +
      "<p><b>Delivery to Client: </b>" + CurrentRow[5] + "</p>" +
      "<p><b>Notes: </b>" + CurrentRow[6] + "</p>" +
      "<p><b>Status: </b>" + CurrentRow[7] + "</p>" + "</p><br><br>";

  //set the row to look at
  var setRow = parseInt(i) + StartRow;

  //mark row as "sent"
  ActiveSheet.getRange(setRow, 10).setValue("sent");
  }

  //define who to send emails to 
  var SendTo = "[email protected]";

  //set subject line
  var Subject = "New Deliverable to Review for " + CurrentRow[1];

  //send the actual email  
  MailApp.sendEmail({
    to: SendTo,
    subject: Subject,
    htmlBody: message,
    });
  }
  }
  }
}

Didn't end up working - still receiving emails for any edit. Wondering if setting the trigger to requiring certain value in cell "X" like "ready to review" might work better - thanks! — cblake
Updated - essentially I have the message working but cannot figure out how to specify to only send the row that is being marked as "Ready to Review". Trying to use the define column to check as "sent" as a way to prevent emails from being resent from rows that were already emailed. — cblake

TheMaster TheMaster · Accepted Answer · 2018-05-07T23:10:41

Change From

var Subject = "New Deliverable to Review for " + CurrentRow[1];

  //send the actual email  
  MailApp.sendEmail({
    to: SendTo,
    subject: Subject,
    htmlBody: message,
    });

To

var Subject = "New Deliverable to Review for " + CurrentRow[1];

  //send the actual email If message is not empty
  if (message) {// Added
  MailApp.sendEmail({
    to: SendTo,
    subject: Subject,
    htmlBody: message,
    });
   }//Added

Modified Script:

function sendEmail() {

  //setup function
  var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  if (ActiveSheet.getName() == 'Review Tracker') {
    var StartRow = 6;
    var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
    var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,11);
    var AllValues = WholeRange.getValues();

    var message = "";
    //iterate loop
    for (i in AllValues) {

      //set current row
      var CurrentRow = AllValues[i];

      if (CurrentRow[7] == "Ready for Review" && CurrentRow[9] != "sent") {


        //define column to check if sent
        //var EmailSent = CurrentRow[10];

        //if row has been sent, then continue to next iteration
        //if (EmailSent == "sent") {
          //continue;}

        //set HTML template for information
        message +=
          "<p><b>Name: </b>" + CurrentRow[0] + "</p>" +
            "<p><b>Client: </b>" + CurrentRow[1] + "</p>" +
              "<p><b>Deliverable Title: </b>" + CurrentRow[2] + "</p>" +
                "<p><b>Link to Review Thread: </b>" + CurrentRow[3] + "</p>" +
                  "<p><b>Deadline for Review: </b>" + CurrentRow[4] + "</p>" +
                    "<p><b>Delivery to Client: </b>" + CurrentRow[5] + "</p>" +
                      "<p><b>Notes: </b>" + CurrentRow[6] + "</p>" +
                        "<p><b>Status: </b>" + CurrentRow[7] + "</p>" + "</p><br><br>";

        //set the row to look at
        var setRow = parseInt(i) + StartRow;

        //mark row as "sent"
        ActiveSheet.getRange(setRow, 10).setValue("sent");
      }//if review ready
    }//For loop close

    //define who to send emails to 
    var SendTo = "[email protected]";

    //set subject line
    var Subject = "New Deliverable to Review for " + CurrentRow[1];

    //send the actual email   if message is not empty
    if (message) {
      MailApp.sendEmail({
        to: SendTo,
        subject: Subject,
        htmlBody: message,
      });
    }//if message
  }//if sheetName Review
}//End Func

Send email with values for only new rows in a spreadsheet via Apps Script

1 Answers