10
\$\begingroup\$

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;
}
\$\endgroup\$
5
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.