1

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

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

2 Answers 2

Reset to default
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.

Improve this answer
0

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

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.