CalendarSheet.gs

/**
 * @OnlyCurrentDoc
 */

/*
 * Triggers
 */

function onOpen() {
  var ss = SpreadsheetApp.getActive();
  var items = [
    {name: 'Configure Calendar', functionName: 'configCalendar'},
    {name: 'Sync All Entries', functionName: 'syncAll'},
    {name: 'Track Changes', functionName: 'trackChanges'}
  ];
  ss.addMenu('Calendar Sync', items);
}

/*
function handleEdit(e) {
  var sheet = e.source.getActiveSheet(),
      firstRow = e.range.getRow(),
      lastRow = firstRow + e.range.getNumRows() - 1;
  var i, er;
  
  for (i = Math.max(firstRow, 2); i <= lastRow; i++) {
    er = new EventRow(sheet, i);
    if (needs update or create) {
      // do it
    }
}
*/


/*
 * Menu callbacks
 */

function configCalendar() {
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt('Calendar ID:');
  if (response.getSelectedButton() == ui.Button.OK) {
    PropertiesService.getDocumentProperties().setProperty('CAL_ID', response.getResponseText());
  }
}

function syncAll() {  
  var ss = SpreadsheetApp.getActive(),
      sheet = ss.getActiveSheet(),
      lastRow = sheet.getLastRow(),
      lastCol = sheet.getLastColumn();
  
  var calId = PropertiesService.getDocumentProperties().getProperty('CAL_ID'),
      cal = new CalendarUtil(calId);
  
  var row, er, evtData;
  
  for (row=2; row<=lastRow; row++) {
    er = new EventRow(sheet, row);
    evtData = er.makeEventData();
    
    Logger.log(evtData);
    if (er.get("eventid")) {
      var result = cal.updateEvent(er.get('eventid'), evtData);
    } else {
      var result = cal.createEvent(evtData);
      er.set("eventid", result.id);
    }
  }
}

function trackChanges() {
}

/*
 * EventRow
 */

function getColumnIndices(headers) {
  var cols = {
    date: -1,
    title: -1,
    description: -1,
    eventid: -1
  };
  
  headers.forEach(function(e,i) {
    if (['date', 'title', 'description', 'eventid'].indexOf(e.toLowerCase()) != -1) {
      cols[e.toLowerCase()] = i+1;
    }
  });
  
  for (prop in cols) {
    if (cols.hasOwnProperty(prop)) {
      if (cols[prop] == -1) {
        throw "Missing column: " + prop;
      }
    }
  }

  return cols;
}

function EventRow(sheet, rowNum) {
  this.sheet = sheet;
  this.rowNum = rowNum;
  
  var headers = this.sheet.getRange(1, 1, 1, this.sheet.getLastColumn()).getValues()[0];
  this.cols = getColumnIndices(headers);
}

EventRow.prototype.get = function get(field) {
  return this.sheet.getRange(this.rowNum, this.cols[field]).getValue();
}

EventRow.prototype.set = function set(field, value) {
  this.sheet.getRange(this.rowNum, this.cols[field]).setValue(value);
}

EventRow.prototype.makeEventData = function makeEventData() {
  var endDate = new Date(this.get('date'));
  endDate.setDate(endDate.getDate() + 1);
  var startISODate = this.get('date').toISOString().slice(0, 10);
  var endISODate = endDate.toISOString().slice(0, 10);
  
  var evt = {
    summary: this.get('title'),
    description: this.get('description'),
    start: {
      date: startISODate,
    },
    end: {
      date: endISODate,
    },
    source: {
      title: "Google Sheets: " + SpreadsheetApp.getActive().getName(),
      url: SpreadsheetApp.getActive().getUrl(),
    },
    transparency: "transparent",
  };
  
  return evt;
}

/*
 * CalendarUtil
 */

function CalendarUtil(calId) {
  this.id = calId;
}

CalendarUtil.prototype.createEvent = function createEvent(data) {
  var r = Calendar.Events.insert(data, this.id)
  
  return r;
}

CalendarUtil.prototype.updateEvent = function updateEvent(eventId, data) {
  var r = Calendar.Events.update(data, this.id, eventId);
  
  return r;
}