Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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: http://goo.gl/IywikM

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

share|improve this question
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 Tuinstra Oct 10 '13 at 19:45
1  
Hi, thank You for your suggestions; I have edited my question. What is "what about Bob"? –  Drewdavid Oct 11 '13 at 17:54
    
Haha Drew, that's a movie, starring Bill Murray and Richard Dreyfuss. Check IMDb for more info. –  Jacob Jan Tuinstra Oct 11 '13 at 18:25
    
Ah, baby steps ... got it. :) –  Drewdavid Oct 11 '13 at 18:41
    
Drew, somehow the article link seems broken....Yesterday it was working. –  Jacob Jan Tuinstra Oct 12 '13 at 7:04
add comment

2 Answers

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.

share|improve this answer
add comment
up vote 0 down vote accepted

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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