Email notification based on date and name

Question

hoping someone can steer me in the right direction. I have a schedule sheet and a contact sheet.

The schedule sheet looks something like this

4/11/16 John Front Desk
4/11/16 Chris Back Room
4/11/16 John Stage
4/11/16 Sarah Front Desk
4/12/16 Joe Back Room
4/12/16 Alex

The contact sheet looks something like this

John   [email protected]
Chris  [email protected]
Alex
Sarah  [email protected]
Joe

So basically the script, will go through the sheet, and if date in the date column is 3 days away from today, it will email a reminder to the person who is assigned a task that day. It will compare the name on the schedule sheet to the name in the contact sheet to find the email address and add it to the emailadd variable.

It works, but due to my limited knowledge in scripting, I feel I am doing this extremely ineffectively. Additionally, when for example John has three duties on the same day, he'll get three separate emails instead of one. Also, some folks don't have email address, so the script will still attempt to email the message, fail and move on to the next one. Though that's totally fine for me since it's no more than 10-12 emails per night, I feel that there is a more effective way to do this. Any assistance would be really appreciated!

Here is the code

function checkReminder() {

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(spreadsheet.getSheetByName('Notification'));
  var schedulesheet = spreadsheet.getActiveSheet();
  var lastRow = schedulesheet.getLastRow();

  var contactspreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  SpreadsheetApp.setActiveSheet(contactspreadsheet.getSheetByName('Contacts'));
  var contactsheet = contactspreadsheet.getActiveSheet();
  var conlastRow = contactsheet.getLastRow();

  var startRow = 2;
 //Grab column of scheduled dates and number of rows in the schedule sheet
  var schedulerange = schedulesheet.getRange(2,2,lastRow-startRow+1,1 );
  var numRows = schedulerange.getNumRows();
  var scheduledassigneddate = schedulerange.getValues();
  var scheduledformatteddate = Utilities.formatDate(new     Date(scheduledassigneddate), "GMT-4:00", "MM/dd/YYYY");
 //Grab column of  scheduled names in the schedule sheet
  var schedulenamerange =  schedulesheet.getRange(2, 3, lastRow-startRow+1, 1);
  var schedulednames =  schedulenamerange.getValues();
 //Grab column of assignments
  var scheduleassignmentrange = schedulesheet.getRange(2, 4, lastRow-startRow+1, 1);
  var assignmentrange = scheduleassignmentrange.getValues();

 //Grab column of names and number of rows in the contact sheet 
  var contactrange = contactsheet.getRange(2,2,conlastRow-startRow+1,1 );
  var contactname = contactrange.getValues();
  var numRows2 = contactrange.getNumRows();
 //Grab column of email addresses in the contact sheet  
  var contactemailrange = contactsheet.getRange(2, 3, conlastRow-startRow+1, 1);
  var assignedemail = contactemailrange.getValues();


 // Format today's date to match lists of dates in schedule sheet
  var today = new Date();
  today = today.setDate(today.getDate()+3);
  var threedaysaway =  Utilities.formatDate(new Date(today), "GMT-4:00", "MM/dd/YYYY");

   var emailadd =""; 

  // Loop over the column of date values in the schedule sheet
  for (var i = 0; i <= numRows - 1; i++) {

    var scheduleddate = scheduledassigneddate[i][0];
    var scheduledformatteddate = Utilities.formatDate(new Date(scheduleddate), "GMT-4:00", "MM/dd/YYYY");
    //if the scheduled date is three days away, start associating names to email.
    if(scheduledformatteddate == threedaysaway)
    {
       var notify_name = schedulednames[i][0];

        for (var j = 0; j <= numRows2 - 1; j++) {
        // Set email variables to null              
         var email_value = 0; 
          var msg = "";


          //compare names falling under date that is 3 days away from today
          var comparenames = contactname[j][0];
          var assignment = assignmentrange[i];
               if(notify_name == comparenames)


                 //if the name in the schedule sheet matches the name in the contact sheet, 
                 //grab the email address in the next colum "assignedemail" and add it to var emailadd.
                     emailadd = emailadd + assignedemail[j] +",";
                     assignment =  assignmentrange[i];
        }
    msg = msg + "Hello "+notify_name+", \n\nYou are scheduled to cover " + assignment + " on " +threedaysaway;
    email_value++;
             Logger.log(emailadd);
  Logger.log(msg);
    //if email_value is not null, send email. Loop back to stop and start over until loop is finished. 
         if(email_value) {
MailApp.sendEmail(emailadd, 
    "Reminder Email", msg);
     }
  emailadd = "";
  assignment = "";

    }
  }
  };

Mr.Rebot Mr.Rebot · Accepted Answer · 2016-04-09T10:51:25

You can modify your approach to this:

Filter data with dates falling under the condition for notification.
Filter Names to remove duplicate.
Prepare sending notification function.
Search for the tasks under one person and concatenate it to the email body. To search for a specific value of a row here is an SO ticket that will guide you.

Here is a sample code:

function findCell() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var dataRange = sheet.getDataRange();
var values = dataRange.getValues();

for (var i = 0; i < values.length; i++) {
var row = "";
for (var j = 0; j < values[i].length; j++) { 
if (values[i][j] == "User") {
row = values[i][j+1];
Logger.log(row);
Logger.log(i); // This is your row number
}
} 
} 
}

Send Notification with the prepared email body.

NOTE:

DATE NAME TASK

4/11/16 John Front Desk

That is the sample format for the task sheet.

Email notification based on date and name

1 Answers