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

  • 1
    Your question clearly involves Google Apps Script, then also tag it as such. If you're not really familiar with scripting, I would suggest baby steps (or see what about Bob). Ask simple questions, and get simple answers and learn on the go. Best is to include a Google Spreadsheet and show the expected result.
    – Jacob Jan
    Commented Oct 10, 2013 at 19:45
  • 1
    Hi, thank You for your suggestions; I have edited my question. What is "what about Bob"?
    – Drewdavid
    Commented Oct 11, 2013 at 17:54
  • Haha Drew, that's a movie, starring Bill Murray and Richard Dreyfuss. Check IMDb for more info.
    – Jacob Jan
    Commented Oct 11, 2013 at 18:25
  • Ah, baby steps ... got it. :)
    – Drewdavid
    Commented Oct 11, 2013 at 18:41
  • Drew, somehow the article link seems broken....Yesterday it was working.
    – Jacob Jan
    Commented Oct 12, 2013 at 7:04

2 Answers 2


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.


Here's the final result/answer:

function checkReminder() {
  // get the spreadsheet object
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // set the first sheet as active
  // 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);

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.