Thursday, August 16, 2012

Google Apps Script

Time to prep for the Fall semester classes.  I typically create a spreadsheet of topics on Google Docs Drive, and for student-ease-of-use I would like to use that data in a Google Calendar.  To get these two apps talking, I'm learning Google Apps Script.  It is simple to complete in a matter of minutes, the syntax is quite readable and easy to understand...

Helpful links:

Functions for interacting with spreadsheets:

Functions for interacting with calendars:

Here's the function I wrote to export data (lists of dates and topic names for a Calculus 2 course for Fall 2012) to a calendar.

To use it: from Google Spreadsheets, go to Tools -- Script Editor... and enter in the function below (changing variable values as necessary). Save it, then execute the script from Tools -- Script Manager..., accept all the permission requests (only necessary on the first run, as in the tutorial example linked to above), and then execute this script again.

function SpreadsheetToCalendar() 
  // This function should be executed from the 
  //  spreadsheet you want to export to the calendar
  var mySpreadsheet = SpreadsheetApp.getActiveSheet();
  var myCalendar = CalendarApp.openByName("Calculus 2");
  // optional - delete existing events
  var events = myCalendar.getEvents(new Date("January 1, 2011 EST"), 
      new Date("January 1, 2013 EST"));
  for (var i = 0; i < events.length; i++) 
  var dataRange = mySpreadsheet.getRange("B2:C46");
  var data = dataRange.getValues();
  // process the data
  for (i in data) 
      var row = data[i];
      // assume that each row contains a date entry and a text entry
      var theDate  = row[0];  // First column of row
      var theTitle = row[1];  // Second column of row
      myCalendar.createAllDayEvent(theTitle, theDate);
P.S. I've switched to using and to format code snippets in this blog... highly recommended!