At church and youth we have several rosters in excel for keeping track of who’s doing what and when. I’ve wanted for a while to make a rostering app that would automatically create a roster, assigning a set of users to different tasks at different frequencies for a set period of time. Additionally having it online could be people could automatically be notified of changes and provide an iCal feed.
In the end though I realised that after uploading an excel spreadsheet to Google Spreadsheet, a program could be written to produce an iCal feed for iCal / Outlook / Google Calendar / Thunderbird (via Lightning) / Sunbird, and it would save a lot of time. So that’s what I’ve done an you can download it here.
Installation:
1) Setup you spreadsheet in Google Docs
2) unzip ical.zip and upload it to your webserver
3) Edit config.php and add your username, password named range / event start times and spreadsheet key
4) Subscribe to your calendar!
Demo:
There is a demo calendar set up based on this spreadsheet. Click the link, or copy and paste the URL into your calendar application, especially if you’re using Google Calendar. The single entry configuration is available here:
http://jameslow.com/content/software/ical/?sheet=TestSheet
And an entry that produces multiple iCal events per spreadsheet row here:
http://jameslow.com/content/software/ical/?sheet=MultiEntry
Help
Help and version information is avaliable here: http://jameslow.com/content/software/ical/?help
Item query
You can query for calendar events from just a specific user or item name listed in the spreadsheet by specifying this on the URL: http://jameslow.com/content/software/ical/?sheet=MultiEntry&item=James
Multiple sheets
You can query multiple sheets by using the [ALL] tag: http://jameslow.com/content/software/ical/?sheet=[ALL] or doing a comma separated list of sheets: http://jameslow.com/content/software/ical/?sheet=TestSheet,MultiEntry. These values can also be used in the $DEFAULT_SHEET param in the config, to make these the default available at the root URL.
Test
You can run a test on any sheet by suffixing test to the URL. This will not redirect to an iCal file, so you can see any errors in your setup. It will echo the number of iCal events created if successful. http://jameslow.com/content/software/ical/?sheet=MultiEntry&test=1
Username
The username / password used to access the spreadsheet can be left out of the config file (global and sheet) and specified in the URL so that the data is not public: http://jameslow.com/content/software/ical/?sheet=MultiEntry&[email protected]&password=password
Notes:
Uses the Google GData PHP client: http://code.google.com/apis/gdata/articles/php_client_lib.html
And Flaimo’s PHP iCal library: http://www.phpclasses.org/browse/package/873.htm
FAQ:
1) Named Ranges
– Sometimes google doesn’t return data from named ranges correctly, its recommended to use a specific ranage eg. A1:C20
2) Outlook / Windows support
– Outlook 2007 natively supports subscribing to iCal feeds. For windows you can download Windows Live! Mail. For outlook 2003 you can use the free Remote Calendars add-in and for Outlook 2000/XP this commerical add-in.
Change Log:
1.4
– Query all spreadsheets and multiple spreadsheets using [ALL] or comma separated list of sheets
1.3.1
– Add some explanation to config file
1.3
– Add calendar title
1.2
– Group similar items
– Link back to spreadsheet
– Fix to show all dates
1.1
– Fix for PHP on some systems
1.0
– Initial version