makerships package package notification google apps script

emailnotif.js

//When run, (a) creates a custom menu -- script is not run automatically, you have to hit the button
//(b) checks the spreadsheet to see who if package has arrived and email notification has not yet been sent. 
//This is not smart enough to combine multiple packages into one email, so fairly spamtastic.

//loads our special menu each time the spreadsheet is opened
function onOpen() {
  loadMenu();
}

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var packageSheet = ss.getSheets()[0];
  var range = packageSheet.getDataRange();
  var data = range.getValues();
  
  //iterate over all the rows in this sheet
  for(var i = 1; i < range.getNumRows(); ++i){
    var row = data[i];
    var recipient = row[2];
    var packageName = row[3];
    var dateArrived = row[9];
    var haveSentEmail = row[10];

    //check if the package has arrived and that we haven't sent a notification email yet
    if (dateArrived !== "" && haveSentEmail == ""){
      dateArrived = Utilities.formatDate(dateArrived, "EST", "MM-dd-yyyy");
      //Send a notification email. I'm cheating and not doing a lookup of name:to email address, instead asking people to use their MIT username and all I have to do is append @mit.edu
      MailApp.sendEmail(recipient+"@mit.edu", 
                        "makerships package at MITERS: " + packageName, 
                        "Hello " + recipient + "," +
                        "\nYour package, " + packageName + ", was ordered with your makership and arrived at MITERS on " + dateArrived + 
                        ". You can find it in the backroom." +
                        "\nFor more information, see the ordering form https://docs.google.com/spreadsheet/ccc?key=0Atr0HZeoMbmgdF9SNU5fTi1Zbk0xNnQyVTFTRkxFamc&hl=en_US#gid=0" +
                        "\n\nThis is an automated message; if this method is too spamm-y, let me know." +
                        "\n\nThank you, \n--Nancy (on behalf of MITERS)");
       //check off that we sent the email so we don't spam people THAT much
       packageSheet.getRange(i+1,11,1,1).setValue("yes");
    }
}
}

//When you first run this script, google will prompt you to confirm that you want to run this script. After you say yes, you're set.
function loadMenu() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.addMenu("don't touch!", [{name:"Send package arrival emails", functionName:"sendEmails"}]);
 }

//Pops up a message box in the spreadsheet. Useful for debugging:
//Browser.msgBox("sending email, " + "i: " + i);