Managing Google Calendar from Spreadsheet

Question

Overview:

I have a need to manage a Google Calendar from a spreadsheet, so I developed a Calendar Manager program with Google Apps Script. I found some inspiration over on Stack Overflow, but my requirements are a little different. Rather than just have a simple list of events, I have a matrix of not quite recurring all day events.

My app allows me to do the following things form a custom toolbar menu:

1. Add the selected event to the calendar.
2. Add every event in the selected row (or column).
3. Add all events in the sheet.
4. Delete all events in a given date range.

Here is the calendar and the spreadsheet. (The code can be accessed from the spreadsheet by selecting Tools>>Script Editor.)

My real spreadsheet can contain over 300 events, so I have to deal with Google's timeout mechanism when adding all events at once. I do this by leveraging Project Properties to track it's current progress along with a trigger. I'm the only one using this, so I'm not concerned about it being executed by multiple users at the same time. It could be a problem for anyone testing out this example copy though.

Perceived Issues:

• SpreadsheetBasedEvent is very tightly bound to the structure of the worksheet.
• I think showDeleteEventsDialog could be more object oriented for easier expansion later, but I'm not real swift with javascript, so I took a "You ain't gonna need it" approach to developing this. I'm wide open to suggestions about how I can take more of an OOP approach to the design in general.
• I also used Project Properties as a sort of psuedo config file so that some global "constants" could be modified without mucking around in the code. I'm not sure this was a good idea.
• I feel like I'm duplicating some code in the various "create" functions. Can they be DRYed up?

SpreadsheetBasedEvent.gs

     function SpreadsheetBasedEvent(cell, calendar) {
       var cycleRow = 1;
       var taskCol = 1;
       var descCol = 2;
       var ownerCol = 3;
       var sheet = cell.getSheet();
       var currentRow = cell.getRow();
       var currentCol = cell.getColumn();

       this.Calendar = calendar;
       this.Date = cell.getValue();
       this.Cycle = sheet.getRange(cycleRow,currentCol).getValue();
       this.Title = this.Cycle + " - " + sheet.getRange(currentRow,taskCol).getValue();
       this.Owner = sheet.getRange(currentRow,ownerCol).getValue();
       this.Description = this.Owner + "\n" + sheet.getRange(currentRow,descCol).getValue();

       /*
         Returns newly created all day CalendarEvent
       */
       this.create = function() {
           return this.Calendar.createAllDayEvent(this.Title, this.Date, {description: this.Description});
         }
     };

CalendarService.gs

function createAllEvents() {

    var calendar = CalendarApp.getCalendarById(getCalendarId());

    var firstRow = Number(getPropertyValue('StartRow'));
    var firstCol = Number(getPropertyValue('StartCol'));

    var defaultStartRow = Number(getPropertyValue('DefaultStartRow'));
    var defaultStartCol = Number(getPropertyValue('DefaultStartCol'));

    var spreadSheetID;

    if (firstCol == defaultStartCol) {
        //create new trigger that will resume execution
        ScriptApp.newTrigger('createAllEvents').timeBased().everyMinutes(5).create();
    } 

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
    var lastRow = sheet.getLastRow();
    var lastCol = sheet.getLastColumn();

    for (var column = firstCol; column <= lastCol; column++) {

        for(var row = firstRow; row <= lastRow; row++) {

            var event = new SpreadsheetBasedEvent(sheet.getRange(row, column), calendar);
            event.create();

            //store the execution state in case it times out in the middle of a column
            setPropertyValue('StartRow',row + 1);
        }

        //store the execution state
        setPropertyValue('StartRow',defaultStartRow);
        setPropertyValue('StartCol',column + 1);

        //abort early so we don't time out
        if (column < lastCol) {
            return;
        }
    }

    //reset process
    setPropertyValue('StartRow',defaultStartRow);
    setPropertyValue('StartCol',defaultStartRow);

    removeTriggers();

 }

 function createSelectedEvent() {
     var calendar = CalendarApp.getCalendarById(getCalendarId());
     /*
       nasty google bug means I can't specify sheet by name
       https://code.google.com/p/google-apps-script-issues/issues/detail?id=3522
     */
     //var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
     var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
     var event = new SpreadsheetBasedEvent(sheet.getActiveCell(),calendar);
     event.create();
 }

 function createEventsForSelectedColumn() {

     var calendar = CalendarApp.getCalendarById(getCalendarId());    
     var defaultStartRow = Number(getPropertyValue('DefaultStartRow'));
     //nasty google bug means I can't specify sheet by name
     //var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
     var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
     var lastRow = sheet.getLastRow();
     var column = sheet.getActiveCell().getColumn();

     for (var row = defaultStartRow; row<=lastRow; row++) {
         var event = new SpreadsheetBasedEvent(sheet.getRange(row, column),calendar);
         event.create();
     }
 }

 function createEventsForSelectedRow() {

     var calendar = CalendarApp.getCalendarById(getCalendarId());    
     var defaultStartCol = Number(getPropertyValue('DefaultStartCol'));
     //nasty google bug means I can't specify sheet by name
     //var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
     var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
     var lastCol = sheet.getLastColumn();
     var row = sheet.getActiveCell().getRow();

     for (var column = defaultStartCol; column<=lastCol; column++) {
         var event = new SpreadsheetBasedEvent(sheet.getRange(row, column),calendar);
         event.create();
     }
 }

 function deleteEvents(startDate,endDate) {
     var calendar = CalendarApp.getCalendarById(getCalendarId());
     var events = calendar.getEvents(startDate, endDate);
     for (var i in events) {
         events[i].deleteEvent();
     }
 }

function getCalendarId() {
  //allows easy switching of from test calendar to production calendar
  return getPropertyValue('TestCalendarID');

  //return getPropertyValue('ProductionCalendarID');
 }

 function setPropertyValue(propertyName, value) {
     PropertiesService.getScriptProperties().setProperty(propertyName, value);
     return PropertiesService.getScriptProperties().getProperty(propertyName);
 }

 function getPropertyValue(propertyName) {
     return PropertiesService.getScriptProperties().getProperty(propertyName);
 }

 function removeTriggers() {
     var triggers = ScriptApp.getProjectTriggers();
     for (var i in triggers) {
         ScriptApp.deleteTrigger(triggers[i]);
     }
 }

UI.gs

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
      {name : "Create Selected Event", functionName : "createSelectedEvent"},
      {name : "Create Events in Selected Column", functionName : "createEventsForSelectedColumn"},
      {name : "Create Events in Selected Row", functionName : "createEventsForSelectedRow"},
      {name : "Create All Events", functionName : "createAllEvents"},
      {name : "Delete Events", functionName : "showDeleteEventsDialog"}
  ];
  spreadsheet.addMenu("Calendar", entries);
};

function showDeleteEventsDialog() {
    var app = UiApp.createApplication().setTitle("Delete Events");

    var datePanel = app.createHorizontalPanel();
    var startDatePanel = app.createVerticalPanel();
    var endDatePanel = app.createVerticalPanel();
    var buttonPanel = app.createHorizontalPanel();
    var startDatePicker = app.createDateBox().setId('StartDate');
    var endDatePicker = app.createDateBox().setId('EndDate');
    var startDateLabel = app.createLabel('Beginning');
    var endDateLabel = app.createLabel('Ending');  

    var debugLabel = app.createLabel().setId('DebugLabel').setVisible(false);

    datePanel.setSpacing(20);
    createDatePickerPanel(startDatePanel,startDatePicker,startDateLabel,datePanel);
    createDatePickerPanel(endDatePanel,endDatePicker,endDateLabel,datePanel);

    //UiApp.createApplication().createVerticalPanel().setCellHorizontalAlignment(widget, horizontalAlignment);

    var submitButton = app.createButton('Submit');
    var submitHandler = app.createServerHandler('onDeleteEventsSubmit')
        .addCallbackElement(startDatePicker)
        .addCallbackElement(endDatePicker)
    submitButton.addClickHandler(submitHandler);

    buttonPanel.add(submitButton);
    buttonPanel.add(debugLabel);

    app.add(datePanel);
    app.add(buttonPanel);

    SpreadsheetApp.getActive().show(app);

    return app;
}

function createDatePickerPanel(panel, datePicker, label, parentPanel) {

    label.setHorizontalAlignment(UiApp.HorizontalAlignment.CENTER);

    panel.add(label);
    panel.add(datePicker);
    parentPanel.add(panel);

    return parentPanel;
}

function onDeleteEventsSubmit(e) {

    var app = UiApp.getActiveApplication();

    var start = new Date(e.parameter.StartDate.toString());
    var end = new Date(e.parameter.EndDate.toString());

    deleteEvents(start,end);

    app.close();
    return app;
}

Answer 1

UI.gs

I left some debugging/development code that should be removed. In particular, this

var debugLabel = app.createLabel().setId('DebugLabel').setVisible(false);

and this

//UiApp.createApplication().createVerticalPanel().setCellHorizontalAlignment(widget, horizontalAlignment);

CalendarService.gs

I used == when I should have used ===. It's a minor detail that won't cause any problems, as both of the variables I'm comparing are explicitly cast to Number, but it's still best practice from what I've read.

if (firstCol === defaultStartCol) {
    //create new trigger that will resume execution
    ScriptApp.newTrigger('createAllEvents').timeBased().everyMinutes(5).create();
} 

There's a bug right here. I meant to set the StartCol equal to the default column, not the default row.

setPropertyValue('StartRow',defaultStartRow);
setPropertyValue('StartCol',defaultStartRow);

This was a poor concept:

function getCalendarId() {
  //allows easy switching of from test calendar to production calendar
  return getPropertyValue('TestCalendarID');

  //return getPropertyValue('ProductionCalendarID');
 }

Instead of storing all of the CalendarIDs in project properties, it is much better to store them in an array variable and create a UI to allow the user (me) to select which calendar to work on. This means I only need one Project Property CurrentCalendar.

var Calendars = [
     (new csCalendar_('Test','Test','someCalendarId@group.calendar.google.com')),
     (new csCalendar_('Prod1','Production Calendar 1','someCalendarId@group.calendar.google.com')),
     (new csCalendar_('Prod2','Production Calendar2','someCalendarId@group.calendar.google.com'))
   ]

 function csCalendar_(name,description,calendarId) {
   this.Name = name;
   this.Description = description;
   this.CalendarId = calendarId;

   this.getName = function() {
     return this.Name;
   }

   this.getDescription = function() {
     return this.Description;
   }

   this.getId = function() {
     return this.CalendarId;
   }
 }

Which changes getCalendarId() to

 function getCalendarId_() {
  var index = getPropertyValue_('CurrentCalendar');
  return Calendars[index].getId();
 }

Which reminds me... There are a lot of functions that should be private. It doesn't appear that Google Apps Script will let me actually make them private, but I can hide them from the spreadsheet and the function list drop down in the IDE by postfixing them with an underscore.