Send email notification based on timeframe and a given email address from a cell?

Question

How specifically does the code found in this article need to be modified to allow for a specified email address column to inform who the reminder should go to?

I have an example spreadsheet here with the script provided from the blog post installed.

If it helps explain, my comment to the article author is as follows:

...it almost seems as though in your section “Sending the Reminder Email” that the code could reference, instead of a static email address, a cell value that meets the criteria – i.e. the adjacent date column cell is within the proper time range to trigger

And the author's response:

Yes, that’s certainly something that can be done. It’s honestly a pretty simple extension of the logic – the MailApp.sendEmail call needs to be in the loop, rather than after it.

It looks as though I have the "what" – I just need the "how". I'll take advice or a code example/modification of my installed script

Answer 1

Here's a pseudocode version of what he was saying:

What he wrote...

counter = 0

loop rows {
    if something {
        add to message
        increment counter
    }
}

if counter {
    send message to one person
}

What you need to write:

counter = 0

loop rows {
    if something {
        make message
        send message to person in row
    }
}

You will need to access the data from the email column as well. I'll leave the rest to you to work out, except to say that you should read this article on how to get the data out of your sheet efficiently.

Answer 2

Here's the final result/answer:

function checkReminder() {
  // get the spreadsheet object
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // set the first sheet as active
  SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
  // fetch this sheet
  var sheet = spreadsheet.getActiveSheet();

  // figure out what the last row is
  var lastRow = sheet.getLastRow();

  // the rows are indexed starting at 1, and the first row
  // is the headers, so start with row 2
  var startRow = 2;

  // grab column 5 (the 'days left' column)
  var range = sheet.getRange(startRow,4,lastRow-startRow+1,1 );
  var numRows = range.getNumRows();
  var day_values = range.getValues();

  // Now, grab the reminder name column
  range = sheet.getRange(startRow, 2, lastRow-startRow+1, 1);
  var reminder_info_values = range.getValues();

  // Grab Status
  range = sheet.getRange(startRow, 5, lastRow-startRow+1, 1);
  var status_values = range.getValues();

  // Grab email addresses
  range = sheet.getRange(startRow, 6, lastRow-startRow+1, 1);
  var email_values = range.getValues();

  var msg = "";

  // Loop over the days left values
  for (var i = 0; i < numRows; i++) {
    var d1 = new Date();
    var d2 = day_values[i][0];
    var days_left = Math.floor((d2 - d1) / (1000 * 60 * 60 * 24));
    var status = status_values[i][0];

    if(days_left == 2 && status.toLowerCase() != "completed") {
      var reminder_name = reminder_info_values[i][0];

      msg = "Heads up, the target completion date for " + reminder_name + " is in " + days_left + " days.\n";
      MailApp.sendEmail(email_values[i][0], "Reminder Spreadsheet Message", msg);
      //Logger.log(msg);
    }
  }
};

I should note that it is still necessary to set a time based trigger to run the script daily, as mentioned in the original blog post