Send emails from google spreadsheet - coding suggestions

Question

I am still learning the ropes here. Based on code suggested by other contributors, I put together a script to send reminder emails to consultants who record their time entries using a Google Form. The spreadsheet first imports calendar entries with all the Job information for each consultant. After the calendar entries are imported, if the consultant has not yet recorded their time entry, the following script will send them an email with a reminder to do so:

function sendReminder() {

  var rmndrFrom = "XYZ, Inc.";
  var myemail   = "support@xyz.com";

  var sheet     = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Pending");
  var numRows   = sheet.getLastRow();
  var lastCol   = sheet.getLastColumn();
  var dataRange = sheet.getRange(2, 1, numRows, lastCol); // row 1 is the header row
  var sheetData = dataRange.getValues();

  for (var i = 0; i < sheetData.length; ++i) {
    var row = sheetData[i];
    if (row[0]){
      var jobNumb = row[0]; // Job Number
      var conName = row[2]; // Consultant Name
      var conMail = row[3]; // Consultant Email
      var jobDate = row[4]; // Date

      // format email string
      var subject = "Time Entry Reminder: " + conName + " / Job " + jobNumb;

        try {
          var conMsgH = 'This is a reminder for you to record your time entry for Job #<strong>' + jobNum + '/' + jobDate + '</strong>';

          // strip HTML for plain text message
          var conMsgP = conMsgH.replace(/\<br\/\>/gi, '\n').replace(/(<([^>]+)>)/ig, "");

          // send reminder to consultants
          MailApp.sendEmail(conMail, subject, conMsgP, { htmlBody:conMsgH, name:rmndrFrom });

        } catch (e) { // error handler
          MailApp.sendEmail(myemail, "Error in sending reminder email.", e.message);
        }
      }
  }
}

So basically, this script parses the Pending sheet, if column A has a Job Number, it will send a reminder email to the consultant with that Job Number. However, a single consultant may have several job numbers to their name. I would like the script to send a single email to each consultant with a list of the Job Numbers for which they have to record their time entries.

Thanks in advance for your kind help. Any suggestions on how to optimize the code will also be very much appreciated.

Answer 1

I must say that fooby's answer is far beyond my JS skills, I'm sure it will work nicely but I still feel like proposing something different (and simpler from my pov), just for the fun of it ;-)

The main difference with your original script is the sorting of the array that allowed me to detect duplicate names and threat it accordingly. The html composition could be far better for sure but that was not your main request.

Here is the code

function sendReminder() {

  var rmndrFrom = "XYZ, Inc.";
  var myemail   = "support@xyz.com";
  var sheet     = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Pending");
  var initial = "Hi conName,<BR>This is a reminder for you to record your time entry for the following jobs : <BR><BR><table border = 1 cellpadding = 3 bgcolor='#FFFFBB'><TR><TD>";

  var sheetData = sheet.getDataRange().getValues();// simplified code to get the data array
  sheetData.shift();//skips the headers
  sheetData.sort(function(x,y){
  var xp = x[2];// sort on column 3 but you can change  here...
  var yp = y[2];// sort on column 3 but you can change  here...
  return xp == yp ? 0 : xp < yp ? -1 : 1;// sort ascending, eventually change here...
  });
//  Logger.log(sheetData);// sorted
  var last = sheetData.length
  var i = 1;//index 0 is handled outside the loop
  var row = sheetData[0];
  var subject = "Time Entry Reminder: " + row[2] + " / Job "
  var conMsgH = initial
  var msgComponent = makeline_(row)
  subject += msgComponent[0]
  conMsgH += msgComponent[1]

  while (i<last){
    if(sheetData[i][2]!=sheetData[i-1][2]||i==last-1){
      sendData_(sheetData[i-1][3],sheetData[i-1][2],subject,conMsgH)
      var subject = "Time Entry Reminder: " + sheetData[i][2] + " / Job "
  var conMsgH = initial;
      }
  msgComponent = makeline_(sheetData[i])
  subject += msgComponent[0]
  conMsgH += msgComponent[1]
  ++i
  }
}

function sendData_(conMail,conName,subject,conMsgH){
  conMsgH = conMsgH.substr(0,conMsgH.length-8)+'</TABLE>'
  conMsgH = conMsgH.replace('conName',conName)
  var conMsgP = conMsgH.replace(/<\/tr>/ig, '\n').replace(/<br>/ig, '\n').replace(/(<([^>]+)>)/ig, "")
  subject = subject.substr(0,subject.length-2);// remove the last '+ '
//  Logger.log(subject)
//  Logger.log(conMsgH)
  Logger.log(conMsgP)
//  Logger.log(conMail)
  try{
          // send reminder to consultants          
          MailApp.sendEmail(conMail, subject, conMsgP, { htmlBody:conMsgH, name:rmndrFrom });
        } catch (e) { // error handler
//          MailApp.sendEmail(myemail, "Error in sending reminder email.", e.message);
    }
}

function makeline_(row){
      var jobNumb = row[0]; // Job Number
      var conName = row[2]; // Consultant Name
      var conMail = row[3]; // Consultant Email
      var descr = row[1]; // description
      var FUS1=new Date(row[4]).toString().substr(25,6)+':00';// get timezone of this event, taking care of daylight savings
      var jobDate = Utilities.formatDate(row[4], FUS1, "MMM dd yyyy @ hh:mm aaa"); // Date
      var subject =  jobNumb+' + ';
      var conMsgH =  'Job #'+jobNumb + '</TD><TD>' + jobDate + '</TD><TD>' + descr + '</TD></TR><TR><TD>';
return [subject,conMsgH];
}

EDIT : made some improvement in the mail format, used a table to show jobs & dates + removed some bugs ;-) (to be honest, I made this also for my personal use as I am having almost the same use case )

Answer 2

There are a number of ways that you can approach this. One way is to keep a sheet with the consultants emails, names and a list of their job numbers. Load this data into your script, a list of all job ids and the job info. Then filter the job ids based on the consultants list and build your email, or you could just send that list of numbers for a very short script.

Another way is to do all of that sorting per consultant in the code and send out the emails that way. This is the approach I've taken, and I've also made use of the iterative JS functions map, filter and reduce more details at MDN.

The code is posted below, but if you would like to take a look at it attached to a spreadsheet and commented (as well as the functions to build that extra sheet with just the consultants info on it) take a look here.

Below is my iteration of your function. I hope it is helpful for your situation:

var rmndrFrom = "XYZ, Inc.";
var myemail   = "me@emailisawesome.com";
var subject   = "Time Entry Reminder";

function sendReminder() {
  var sheet       = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Pending");
  var numRows     = sheet.getLastRow();
  var lastCol     = sheet.getLastColumn();
  var sheetData   = sheet.getRange(2, 1, numRows-1, lastCol).getValues();
  var cons = sheet.getRange(2,3,numRows-1,1).getValues().reduce(flatten_).filter(getUniqueConsultants_);
  cons.forEach(sendEmail_, sheetData);
}

function sendEmail_(consultant) {
  var consultantsJobs = this.filter(getJobsForConsultant_, consultant);
  var jobList = consultantsJobs.map(buildJobLine_).join("<br>"); 
  try {
    var conMsgH = "Hi " + consultant + ",<br>";
    conMsgH    += "This is a reminder for you to record your time entry for the following jobs:<br><br>";
    conMsgH    += jobList;
    conMsgH    += "<br><br>Thank you for your cooperation.";
    var conMsgP = conMsgH.replace(/\<br\/\>/gi, '\n').replace(/(<([^>]+)>)/ig, "");
    MailApp.sendEmail(consultantsJobs[0][3], subject, conMsgP, {htmlBody:conMsgH, name:rmndrFrom});
  } catch (e) {
    MailApp.sendEmail(myemail, "Error in sending reminder email.", e.message);
  } 
}

function buildJobLine_(job) {
  return "Job #" + job[0] + " on " + Utilities.formatDate(job[4], Session.getTimeZone(), "MMM dd yyyy");
}

function getJobsForConsultant_(row) {
  return row[2] == this;
}

function getUniqueConsultants_(v,i,a) {
  return a.indexOf(v) == i;
}

function flatten_(a,b) {
  return a.concat(b);
}