stevenbell · June 5, 2018 18:46 · Jun 5, 2018
diff --git a/reminders.gs b/reminders.gs
@@ -0,0 +1,65 @@
+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);
+
+}