Given a list of people on a rotation to cover some responsibility, send notifications to the next people who's shift is approaching. To test this, 1. Make a duplicate of https://docs.google.com/spreadsheet/ccc?key=0AsonuW5GGQ5sdGtaTEFBZ1pzYTZ2ZzFZTWZvbW1nbmc for your sandbox. 2. In the menu of your spreadsheet, navigate to Tools > Script Editor.…

volunteerNotifications.js

/*
 * Runs in the context of 
 * https://docs.google.com/spreadsheet/ccc?key=0AsonuW5GGQ5sdGtaTEFBZ1pzYTZ2ZzFZTWZvbW1nbmc
 */

var sheetDefaults = {
  firstDataRow:  2,
  firstDataCol:  1,
  spreadsheetId: '0AsonuW5GGQ5sdGtaTEFBZ1pzYTZ2ZzFZTWZvbW1nbmc',
};

function rotateColumns() {
  var 
    that = this,
    sheet = this.sheet(),
    dateCol = this.colNum('date')
  ;
  
  var isTimeToRotateColumns = function () {
    return that.date > sheet.getRange(sheet.getLastRow(), dateCol, 1, 1).getValue();
  };
  
  while (isTimeToRotateColumns()) {
    sheet.getRange(this.firstDataRow, dateCol, 1, 1).setValue(
      sheet.getRange(this.firstDataRow, dateCol + 1, 1, 1).getValue()
    );
  }
}

var sheets = [
  {
    sheetName: 'Setup Schedule',
    columns:   ['name','email','date','date2',null,'email_template'],
    beforeNotifications: rotateColumns,
  },
  {
    sheetName: 'Dessert Schedule',
    columns:   ['name','email','date','email_template'],
  },
  {
    sheetName: 'Presenters',
    columns:   ['name','email','date','date2','email_template'],
    beforeNotifications: rotateColumns,
  },
];

function sendNotifications() {
  sheets.forEach(function(sheetSettings) {
    var notifier = Notifier.beget(_.extend({}, sheetDefaults, sheetSettings))
    notifier.sendNotifications();
  })
}

/*
 * Note: this is me indulging in a bit of OO experimentation in js.
 * BaseObject's only responsibility is to implement the .beget function--a derivative
 * of Crockford's concept at http://javascript.crockford.com/prototypal.html
 *
 * More details of my thought process at http://jcamenisch.github.io/prototypes-in-javascript/
 */
BaseObject = {
  copyMembers: function(fromObj) {
    for (name in fromObj) {
      if (fromObj.hasOwnProperty(name)) {
        this[name] = fromObj[name];
      }
    }
  },
  requiredProperties: {},
  beget: function(properties) {
    for (name in this.requiredProperties) {
      if (!(properties && properties.hasOwnProperty(name))) {
        var errorMsg = "Must provide property " + name;
        if (this.requiredProperties[name]) {
          errorMsg += ': ' + this.requiredProperties[name];
        }
        throw errorMsg;
      }
    }

    var ret = Object.create(this);

    ret.copyMembers(properties);

    if (typeof this.initialize == 'function') this.initialize.call(ret);

    return ret;
  }
}

Notifier = BaseObject.beget({
  requiredProperties: {
    sheetName: 'name of the sheet containing the relevant data and settings'
  },
  
  initialize: function() {
    this.date || (this.date = new Date);
  },

  // Don't send notifications regarding events past this horizon.
  maxDate: function() {
    return this._maxDate || (this._maxDate = new Date(this.date.getYear(), this.date.getMonth(), this.date.getDate() + 7));
  },

  spreadsheet: function() {
    return this._spreadsheet || (this._spreadsheet = SpreadsheetApp.openById(this.spreadsheetId));
  },

  sheet: function() {
    return this._sheet || (this._sheet = this.spreadsheet().getSheetByName(this.sheetName));
  },

  nthDayOfMonth: function(n, day, month, year) {
    var ret;
    var days = ['sunday', 'monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday'];

    var dayNum = days.indexOf(day.toLowerCase());
    if (!days[dayNum]) dayNum = parseInt(day);
    if (!days[dayNum]) throw day + ' is not a valid day';

    var firstPossibleDate = n * 7 - 6;
    ret = new Date(year, month, firstPossibleDate);

    var nextRequestedDayDelta = (dayNum - ret.getDay() + 7) % 7;
    ret.setDate(ret.getDate() + nextRequestedDayDelta);

    if (ret.getMonth() == month) return ret;
  },
  
  nextNthDayOfMonth: function(n, day) {
    var ret;
    var month = this.date.getMonth();
    var year = this.date.getYear();
  
    while ((ret = this.nthDayOfMonth(n, day, month, year)) < this.date || typeof ret == 'undefined') {
      if (month < 11) {
        month += 1;
      } else {
        year += 1;
        month = 0;
      }
    }
    
    return ret;
  },
  
  parseDate: function(dateText) {
    var m
    if (dateText instanceof Date) {
      return dateText;
    } else if (m = dateText.match(/([1-5])(?:st|nd|rd|th) (\w+day)s?/)) {
      return this.nextNthDayOfMonth(m[1], m[2]);
    } else {
      return new Date(dateText);
    }
  },
  
  emailTemplate: function() {
    if (!this._emailTemplate) {
      var
        templateText = this.sheet().getRange(
          this.firstDataRow, this.colNum('email_template'), 1, 1
        ).getValue(),
        lines = templateText.split("\n"),
        headers = {},
        ret = {}
      ;
      
      while (lines[0].length) {
        var next_header = lines.shift().split(/\s*:\s*/);
        
        if (next_header[1]) {
          headers[next_header[0]] = next_header[1];
        } else {
          (headers.unnamed || (headers.unnamed = [])).push(next_header[0]);
        }
      }
      lines.shift();

      this._emailTemplate = {
        body: lines.join("\n"),
        subject: headers.subject,
        headers: headers
      }
      delete headers.subject;
    }

    return this._emailTemplate;
  },
  
  nextRows: function() {
    var that = this;
    
    return this.data().filter(function(row) {
      return row.email &&
             row.date > that.date &&
             row.date <= that.maxDate();
    });
  },

  sendNotification: function(volunteer) {
    var recipient = volunteer.email.split(/\s*,\s*/).map(function(addr){
      return '"' + volunteer.name + '" <' + addr + '>';
    }).join(',');
    
    MailApp.sendEmail(
      // '[email protected]', recipient +
      recipient,
      this.subjectTemplate(volunteer),
      this.bodyTemplate(volunteer),
      this.emailOptions()
    );
  },
  
  sendNotifications: function() {
    var that = this;

    if (typeof this.beforeNotifications == 'function') this.beforeNotifications();
    
    this.nextRows().forEach(function(row) { that.sendNotification(row); });
  },

  subjectTemplate: function(context) {
    return _.template(this.emailTemplate().subject)(context);
  },
  
  bodyTemplate: function(context) {
    return _.template(this.emailTemplate().body)(context);
  },
  
  emailOptions: function() {
    return this.emailTemplate().headers;
  },
  
  data: function() {
    var that = this;

    if (!this._data) {
      this._data = this.sheet().getRange(
        this.firstDataRow,
        this.firstDataCol,
        this.sheet().getLastRow(),
        this.columns.length
      ).getValues();

      this._data.forEach(function(row, i) {
        that._data[i] = _({}).tap(function(obj) {
          that.columns.forEach(function(col, j) {
            if (col) {
              if (col.match(/^is/))    obj[col] = !!row[j];
              else if (col == 'date') obj[col] = that.parseDate(row[j]);
              else {
                obj[col] = row[j];
                if (col == 'name') {
                  var people = row[j].split(/\s+&\s+/).map(function (name) {
                    var names = name.split(' ');
                    var lastName = names.length > 1 ? names.pop(): null;
                    var firstName = names.join(' ');
                    
                    return { firstName: firstName, lastName: lastName }
                  });
                  
                  obj.firstName = _(people).pluck('firstName').join(' & ');
                  obj.lastName = _(people).pluck('lastName').join(' & ');
                }
              }
            }
          });
        });
      });
    }
    
    return this._data;
  },
  
  colNum: function(name) { return this.columns.indexOf(name) + 1; },
    
  dataInColumn: function (column) {
    return sheet.getRange(this.firstDataRow, column, this.sheet().getLastRow(), 1).getValues();
  }
});

function test() {
  sheets.forEach(function(sheetSettings) {
    var notifier = Notifier.beget(_({}).extend(sheetDefaults, sheetSettings))
    Logger.log(_.pluck(notifier.nextRows(), 'email'));
  })
}