Eldeebs-AutoMailer.gs

Eldeebs-AutoMailer.gs

/**
*
*   Eldeebs AutoMailer
*    tarekeldeeb@gmail.com
*
*   Installation: This script should be triggered daily.
* 
*   Example: https://docs.google.com/spreadsheets/d/1BBAyeWDLu4pXIQ7XbbcJ-PA0gVAY_ZzB2ym7R5nYluc
*
*   History: v0.1 19.9.2016: Initial Release
*            v0.2 20.9.2016: Bug fix + Added buttons + help
*            v0.3 25.9.2016: Bug fix
*            v0.4 27.9.2016: Added CC and Index columns + Report
*  
*/

var TWELVE_HOURS = 12*60*60*1000; // millis

function eldeebsAutoMailer() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var emailsToSend = sheet.getRange("B3:B").getValues();
  var lastRow = emailsToSend.filter(String).length + 3;
  var report = "";
  
  // Fetch Columns B:G for non-empty rows (According to B)
  var dataRange = sheet.getRange(3, 3, lastRow-3, 6);
  
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  //for (var i = 0; i < data.length; ++i) {
  data.forEach(function(d,i){
    var row = d; //data[i];
    var now = new Date();
    var frequency = row[0];     // column B
    var subject  = row[1];      // column C
    var message = row[2];       // column D
    var emailAddress = row[3];  // column E
    var ccAddress = row[4];     // column F
    var emailSent = row[5];     // column G
    
    //Logger.log("[%s:%s] %s - %s - %s - %s - %s",now,now.getDay(),frequency,subject,message,emailAddress,emailSent);
    Logger.log("[%s] %s - %s - %s - %s",now,frequency,now.getDay(),ScriptApp.WeekDay.SUNDAY,ScriptApp.WeekDay.MONDAY);
    
    if (emailSent == "" || (now.valueOf() - emailSent.valueOf() > TWELVE_HOURS) ) {  // Prevents sending duplicates
      
      if (   (frequency == "Saturday") && (now.getDay() == 6)
          || (frequency == "Sunday"  ) && (now.getDay() == 0)
          || (frequency == "Monday"  ) && (now.getDay() == 1)
          || (frequency == "Thursday") && (now.getDay() == 4)
          || (frequency == "Friday"  ) && (now.getDay() == 5)
          || (frequency == "Monthly" ) && (now.getDate() == 1)
          || (frequency == "Daily"  ) ){
            MailApp.sendEmail(emailAddress, subject, message, {cc:ccAddress});
            sheet.getRange(3 + i, 8).setValue(now);
            // Make sure the cell is updated right away in case the script is interrupted
            SpreadsheetApp.flush();
            report += "Sent email \#"+(i+1)+" with title \""+subject+"\"\\n";
      }
    }
  });
  //Show report
  if(report.length == 0)report="No emails sent this time!";
  Browser.msgBox('AutoMailer Report',report, Browser.Buttons.OK);
}


function help() {
   Browser.msgBox('AutoMailer Help',
                  'This sheet automates sending multiple emails to different people periodically. \\n\\nJust select the frequency, fill in the title, '+
                  'multi-lined body and a comma-separated email list of receipients. A script is triggered on a daily basis. On success, a timestamp '+
                  'is added for each row/email. The script compares the last timestamp and rejects to resend the same email within 12 hours.'+
                  '\\n\\nFeel free to add new rows/emails but be aware that google limits your daily emails quota.'+
                  '\\n\\nIf you added extra features to this sheet/script please send a copy to: tarek.eldeeb@valeo.com', Browser.Buttons.OK);
}

function onEdit(e){
  var timestamp = "Last edited on: "+new Date().toDateString()+" by ";
  var n = Session.getActiveUser().getEmail().match(/^([^@]*)@/)[1].split(".");
  for(i = 0 ; i < n.length ; i++){
    Logger.log(JSON.stringify(n));
    n[i] = n[i].charAt(0).toUpperCase() + n[i].substr(1);
  }
  timestamp = timestamp + n.join(' ');
  SpreadsheetApp.getActiveSheet().getRange('B1').setValue(timestamp);
}