Managing Google Calendar from Spreadsheet

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:

• Add the selected event to the calendar.

• Add every event in the selected row (or column).

• Add all events in the sheet.

• 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

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.

StackExchange Code Review Q#56677, answer score: 5