Google Apps Script to publish spreadsheet to calendar

export_spreadsheet_to_calendar.js

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Export Events",
    functionName : "exportEvents"
  }];
  sheet.addMenu("Calendar Actions", entries);
};

function return1() {
  return 1;
}

// time is reserved so use thyme
function createDateTime(date, thyme) {
  dateTime = new Date(thyme);
  dateTime.setDate(date.getDate());
  dateTime.setMonth(date.getMonth());
  dateTime.setYear(date.getYear());
  return dateTime;
}

function exportRows(cal, rows, rowObjects) {
  var headerRows = 1;  // Number of rows of header info (to skip)
  for (var i = headerRows; i < rows.length; ++i) {
    exportRow(cal, rowObjects[i-1]);
  }
}

function extractEventFromRow(rowObject) {
  return {
    titleWithStatus: function() {
      return '[' + this.status + '] ' + this.title;
    },
    date: new Date(rowObject['date']),
    title: rowObject['title'],
    status: rowObject['status'],
    tstart: createDateTime(rowObject['date'], rowObject['start time']),
    tstop: createDateTime(rowObject['date'], rowObject['stop time']),
    loc: rowObject['location'],
    desc: rowObject['description'],
    id: rowObject['id']
  };
}

function exportRow(cal, rowObject) {
  if (rowObject["sync'd to calendar?"] === 'Yes') { return; }
  if (!rowObject.date) { return; }
  Logger.log('exporting row ' + rowObject.date + ': ' + rowObject.title);
  
  var event = extractEventFromRow(rowObject);
  var calEvent;

  if (event.id) {
    // consider deleting and recreating event since because of this
    // http://stackoverflow.com/questions/20671591/calendarevent-in-calendarapp-manual-delete-still-accessible-through-apps-sc/20680989?noredirect=1#20680989
    // though calEvent.deleteEventSeries errors with 'you do not have permissions'. Boo
    calEvent = cal.getEventSeriesById(event.id);
    updateCalEvent(calEvent, event);
  } else {
    rowObject.id = createCalEvent(cal, event).getId();
  }
}

function createCalEvent(cal, event) {
  return cal.createEvent(event.titleWithStatus(), event.tstart, event.tstop, {description:event.desc,location:event.loc});
}

function updateCalEvent(calEvent, event) {
  var vis = calEvent.getVisibility();
  calEvent.setTitle(event.titleWithStatus());
  calEvent.setDescription(event.desc);
  calEvent.setLocation(event.loc);
  //  calEvent.setTime(event.tstart, event.tstop); setTime does't exist unfortunately
  var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
  calEvent.setRecurrence(recurrence, event.tstart, event.tstop);
}

function createRowObjects(headerRow, allRows) {
  var rows = [];
  for (var i = 1; i < allRows.length; ++i) {
    rows.push(createRowObject(headerRow, allRows[i]));
  }
  return rows;
}

function createRowObject(headerRow, row) {
  rowObject = {};
  for (var j = 0; j < row.length; ++j) {
    rowObject[headerRow[j]] = row[j];
  }
  return rowObject;
}


function createSettingsObject(settingsRows) {
  var keyVal = settingsRows[0];
  var settings = {};
  for(var i = 0; i < keyVal.length; i++) {
    settings[keyVal[0]] = keyVal[1];
  }
  return settings;
}

function getSettings() {
  var settings = SpreadsheetApp.getActive().getSheetByName('Settings');
  var range = settings.getDataRange();
  var data = range.getValues();
  return createSettingsObject(data);
}

function exportEvents() {
  exportEventsFromSheet(SpreadsheetApp.getActiveSheet(), getSettings());
}

function exportEventsFromSheet(sheet, settings) {
//  Logger.log(settings);
  var range = sheet.getDataRange();
  var data = range.getValues();
  var headerRow = data[0];
  var rows = createRowObjects(headerRow, data);
  var idRowIndex = 6;
  var calId = settings.calendarId;
  var cal = CalendarApp.getCalendarById(calId);

  exportRows(cal, data, rows);
  
  // Record all event IDs to spreadsheet
  writeIdsToSpreadsheet(headerRow, data, rows);
  range.setValues(data);
  range.setBackground('white');
}

function writeIdsToSpreadsheet(headerRow, rawRows, rowObjects) {
  var rawRow, rowObject;
  var idColumnIndex = getColIndexWithHeader(headerRow, 'id');
  var syncColumnIndex = getColIndexWithHeader(headerRow, "sync'd to calendar?");
  for(var i = 1; i < rawRows.length; ++i) {
    rawRow = rawRows[i];
    rowObject = rowObjects[i-1];
    rawRow[idColumnIndex-1] = rowObject.id;
    rawRow[syncColumnIndex-1] = 'Yes';
  }
}

function getColIndexWithHeader(headerRow, colTitle) {
  for (var i = 0; i < headerRow.length; i++) {
    if (headerRow[i] === colTitle) {
      return i + 1;
    }
  }
  throw 'no column title found ' + colTitle;
}

function getColIndex(colTitle) {
  var sheet = SpreadsheetApp.getActiveSheet()
  var rows = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  return getColIndexWithHeader(rows[0], colTitle);
}

function onEdit(event) {
  var ss = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  var rowIndex = r.getRow();
  if (rowIndex === 1) { return; } // don't trigger for header
  
  var syncColIndex = getColIndex("sync'd to calendar?");
  var range = ss.getRange(rowIndex, 1, 1, syncColIndex);
  var syncCell = range.getCell(1, syncColIndex);
  range.setBackground('#FFFF99');
  syncCell.setValue('No');
}

test.js

function doGet( e ) {
  QUnit.urlParams( e.parameter );
  QUnit.config({ title: "Unit tests for my project" });
  QUnit.load( myTests );
  return QUnit.getHtml();
};

// Imports the following functions:
// ok, equal, notEqual, deepEqual, notDeepEqual, strictEqual,
// notStrictEqual, throws, module, test, asyncTest, expect
QUnit.helpers(this);

function myTests() {
  var sheet = SpreadsheetApp.create('test-code-file');
  var toCopy = SpreadsheetApp.openByUrl('https://docs.google.com/a/codeforamerica.org/spreadsheets/d/1-7C-Nq3Z5aPeyu85j63XQEskC3K1W69DA3hw8jK4SLc/edit#gid=1531410074').getActiveSheet();
  
  sheet = toCopy.copyTo(sheet);
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  
  var range = sheet.getRange(2, 1, lastRow-1, lastCol);
  range.clearContent();
  range = sheet.getRange(2, 1, 1, lastCol);
  range.setValues([['', '6/27/2014', 'An Event','load in', '2:00 PM', '2:30 PM', 'load out', 'a location','','','','','','No']]);
  
  
  exportEventsFromSheet(sheet,
                        {calendarId: 'codeforamerica.org_oaaoc239qsupefjca27tb3apeg@group.calendar.google.com'});

  range = sheet.getRange(2, 1, lastRow-1, lastCol);
  var eventId = range.getValues()[0][lastCol-2];

  
  
  //  var headers = ['date', 'title', 'status', 'start time', 'stop time', 'location', 'description', 'id'];
//  
//  var event = [new Date(), 'erik event','Applied', '2:30 PM', '3:30 PM', 'my location', '', ''];
//  var data = [headers, event];
//  var range = sheet.getRange(1, 1, 2, headers.length);
//  
//  var rows = createRowObjects(headers, data);
//
//  var cal = CalendarApp.getCalendarById('codeforamerica.org_oaaoc239qsupefjca27tb3apeg@group.calendar.google.com');
  
  test('createsRowObject', function(assert) {
//    assert.deepEqual(rows, [{
//      "date": "1/19/2014",
//      "description": "",
//      "id": "",
//      "location": "RFS Courthouse Plaza",
//      "start time": "2:00 PM",
//      "status": "Applied",
//      "stop time": "2:30 PM",
//      "title": "Vigil for Life"
//    }]);

//    exportRows(cal, data, rows);
//    assert(rows[0].id, 'expected id');
    ok(eventId.match(/.*@google.com$/));
  });
  
  
//  range.setValues([headers, event]);

//  test("Creates event on a calendar", function(assert) {
//
//
//    
////    var sheet = SpreadsheetApp.openByUrl('https://docs.google.com/a/codeforamerica.org/spreadsheets/d/1-7C-Nq3Z5aPeyu85j63XQEskC3K1W69DA3hw8jK4SLc/edit#gid=1531410074').getActiveSheet();
//  //  var range = sheet.getDataRange();
//    //var data = range.getValues();
////    var data = [];
////    assert.deepEqual([], []);
//  });
}