Google Sheets Script

sozbox

So I have a Google sheet with 3 date columns, First column is the date something occurred which is manually populated. Column 2 is Column 1 + 14 days and Column 3 is Column 2 + 28 days. Example:

Column 1 Column 2 Column 3
01/01/2015 15/01/2015 12/02/2015


I need to create a script that sends me an email on a date that equals Column 2 - 1 day and Column 3 -1 day. So for each row I'd receive 2 reminder mails, one on 14/01/2015 and another on 11/02/2015.

Is anyone familiar with Google script?

Talisman

Google Apps Script is JavaScript.

The code below should be straight forward enough to follow and get you started. It only uses the date from the first column, if you want to use the dates from the other columns you should be able to modify the code yourself. It hasn't been tested so it may contain some errors and it also doesn't validate the spreadsheet data.

function myEmailSchedule() {
  // email variables
  var emailAddress = "blah@gmail.com";
  var emailMessage = "Task message";
  var emailSubject = "Task Item Due";
  // date variables
  var dayInMilliseconds = 86400000; // 24 * 60 * 60 * 1000
  var dateToday = parseInt((new Date().setHours(0,0,0,0)) / dayInMilliseconds);
  // get the spreadsheet
  var mySpreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  // getRange(row, column, numRows, numColumns)
  var myColumn = mySpreadsheet.getRange(1, 1, mySpreadsheet.getLastRow(), 1);
  var myValues = myColumn.getValues();
  for (var i = 0; i < myValues.length; i++) {
    var dataDay = parseInt(myValues[i][0].getTime() / dayInMilliseconds);
    // is tomorrow 14 or 28 days from the spreadsheet date?
    if (dataDay === dateToday - 13) || (dataDay === dateToday - 27) {
      MailApp.sendEmail(emailAddress, emailSubject, emailMessage);
    }
  }
}

function createTimeDrivenTriggers() {
  // Trigger every day at 09:00 UTC
  ScriptApp.newTrigger('myEmailSchedule')
    .timeBased()
    .atHour(9)
    .everyDays(1)
    .inTimezone('UTC')
    .create();
}