Send single email with values from all new rows in a spreadsheet (Google Script / GAS)

Question

Thanks to lots of reading on this site, I successfully built a script that does what I wanted it to do: Upon running, the script sends an individual email for each new row in a spreadsheet, then mark that row "sent." Yay!

But now I need the script to compile all the rows into multiple sections in a single email. So, I need the script to check for new rows, use a template to add the values from each new row into an individual section, compile all the new sections into an email, send the email, then mark all new rows "sent".

I'm stuck because it seems that I would need to have each newly added row define a relative variable, i.e. first unsent row = sectionOne, second = sectionTwo, etc. But the length of the email and the number of variables to define would depend on the number of new rows. So I don't really know how to get the script to go through a loop and add only (but all of) the new content to the email body.

Here's what I have, anyone know how to get to the goal here?

function sendEmail() {

 //setup function
 var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var StartRow = 3;
 var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
 var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,11);
 var AllValues = WholeRange.getValues();

 //iterate loop
 for (i in AllValues) {

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

 //set subject line
 var Subject = "Found by " + CurrentRow[1];

 //set HTML template for information
 var message = 
      "<p><b>Title: </b>" + CurrentRow[2] + "</p>" +
      "<p><b>Agency: </b>" + CurrentRow[3] + "</p>" +
      "<p><b>Summary: </b>" + CurrentRow[4] + "</p>" +
      "<p><b>Due: </b>" + CurrentRow[5] + "</p>" +
      "<p><b>Posted: </b>" + CurrentRow[6] + "</p>" +
      "<p><b>Total Funding: </b>" + CurrentRow[7] + "</p>" +
      "<p><b>Announcement Number: </b>" + CurrentRow[8] + "</p>" +
      "<p><b>Useful Links: </b>" + CurrentRow[9] + "</p>";

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

 //define who to send grants to 
 var SendTo = "[email protected]" + "," + "[email protected]";

 //if row has not been sent, then...  
 if (emailsent != "sent") {

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

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

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

Answer 1

Without modifying your code too much, here's what I got for ya:

function sendEmail() {

 //setup function
 var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var StartRow = 3;
 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];

 //define column to check if sent (starts from "0" not "1")
 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>Found by: </b>" + CurrentRow[1] + "</p>" +
      "<p><b>Title: </b>" + CurrentRow[2] + "</p>" +
      "<p><b>Agency: </b>" + CurrentRow[3] + "</p>" +
      "<p><b>Summary: </b>" + CurrentRow[4] + "</p>" +
      "<p><b>Due: </b>" + CurrentRow[5] + "</p>" +
      "<p><b>Posted: </b>" + CurrentRow[6] + "</p>" +
      "<p><b>Total Funding: </b>" + CurrentRow[7] + "</p>" +
      "<p><b>Announcement Number: </b>" + CurrentRow[8] + "</p>" +
      "<p><b>Useful Links: </b>" + CurrentRow[9] + "</p><br><br>";

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

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

 //define who to send grants to 
 var SendTo = "[email protected]" + "," + "[email protected]";

 //set subject line
 var Subject = "Grants";


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

So essentially we change the scope of the message variable to outside the for loop so that it can be accessed with the same content during each iteration. We then append the additional HTML content for each new record onto the message variable. I changed the checking of if the email had been sent already further to the top, which'll actually increase speed ever so slightly but also not disturb our message variable. Finally, I moved the email sending part out of the for loop, cause we collected our data inside the loop.

If I spent a little more time I could alter this code and make it much more simple to understand and also probably change the HTML content into a table format, but this should do the job for now.

I have not tested this code, cause I'm doing this on the go, but I believe it should work no problem. Let me know if you need further assistance.

Answer 2

Similar solution, but little bit cleaned up codes.

/* please edit this function for your message format */
function buildMessageRow(row) {
  return (
    'Name:' + row[0] +
    'Email:' + row[1] + '\n'
  );
}

function sendEmail() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const allValues = sheet.getDataRange().getValues();
  const columnForSentMarker = 5; /*  sent marker column */
  const markerForSentEmail = 'email_fwd';
  var messages = '';
  allValues.forEach(function (row, index) {
    if (index === 0) return; /* skip 1st row for labels */
    if (row[columnForSentMarker - 1] === markerForSentEmail) 
      return;
    messages += buildMessageRow(row);
    sheet.getRange(index + 1, columnForSentMarker).setValue(markerForSentEmail);
  });
  Logger.log(messages); /* TODO: change logging to email sending */
}

I shared my test document for your reference.