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 https://developers.google.com/apps-script/your_first_script in a matter of minutes, the syntax is quite readable and easy to understand...

Helpful links:
https://developers.google.com/apps-script/defaultservices

Functions for interacting with spreadsheets:
https://developers.google.com/apps-script/class_spreadsheetapp

Functions for interacting with calendars:
https://developers.google.com/apps-script/class_calendarapp

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++) 
  {
     events[i].deleteEvent();
  }
  
  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 http://www.stylifyyourblog.com/2012/07/syntax-highlighting-in-blogger-using.html and http://www.tools.stylifyyourblog.com/p/postify.html to format code snippets in this blog... highly recommended!