Skip to content

Instantly share code, notes, and snippets.

@stevenbell
Created June 5, 2018 18:46
Show Gist options
  • Save stevenbell/ea94ffa5ec13454813a21a327440f7e1 to your computer and use it in GitHub Desktop.
Save stevenbell/ea94ffa5ec13454813a21a327440f7e1 to your computer and use it in GitHub Desktop.
Weekly lunch reminder from Google spreadsheet
function myFunction() {
// Open the spreadsheet (assumes that it's shared to everyone with the link, or otherwise accessible to me)
var ss = SpreadsheetApp.openByUrl(
'<SPREADSHEET LINK HERE>');
Logger.log("Opened schedule spreadsheet: " + ss.getName());
// Pull all the data into a big array
var data = ss.getDataRange().getValues();
// todo: Check that column B is "Lunch" and column D is "Talk"
// Skip the header and find the first date that is later than the current date
var today = new Date();
for(var i = 1; i < data.length; i++){
lunchdate = data[i][0];
if(lunchdate > today){
lunchperson = data[i][1];
lunchemail = data[i][2];
talkperson = data[i][3];
talkemail = data[i][4];
break; // We've found the next lunch; just quit
}
}
// If we get here, we must have run off the end
//Logger.log("No future meeting found!");
// Use the Google Apps Script way of formatting dates, since the JS way doesn't seem to work
// Hopefully DST doesn't mess this up. :-)
datestring = Utilities.formatDate(lunchdate, "PST", "MM/dd");
// Send the group email
subject = "Group lunch on "+datestring+": "+lunchperson+" food, "+talkperson+" talk";
Logger.log("Subject: " + subject);
body =
"This is a reminder that our next weekly group lunch will be on "+ Utilities.formatDate(lunchdate, "PST", "EEEE, MMM dd") + ". " +
lunchperson + " will be organizing lunch; " + talkperson + " will be giving the talk. " +
"Remember to come prepared with at least one interesting thing that you learned this week. " +
"\n\n" +
" --- Your friendly reminder robot";
Logger.log("Body: " + body);
MailApp.sendEmail("<GROUP MAILING LIST>", subject, body);
// Send the talk email
subject = "Giving group lunch talk on "+datestring;
body =
"This is a reminder that you are scheduled to give the talk at group lunch on "+ Utilities.formatDate(lunchdate, "PST", "EEEE, MMM dd") + ". " +
"\n\n" +
" --- Your friendly reminder robot";
MailApp.sendEmail(talkemail + "@<DOMAIN>", subject, body);
// Send the food email
subject = "Organizing food for group lunch on "+datestring;
body =
"This is a reminder that you are scheduled to organize food for group lunch on "+ Utilities.formatDate(lunchdate, "PST", "EEEE, MMM dd") + ". " +
"\n\n" +
" --- Your friendly reminder robot";
MailApp.sendEmail(lunchemail + "@<DOMAIN>", subject, body);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment