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
This looks exactly like what I’m looking for. Unfortunately I am not able to follow the concept of your solution.
Could you elaborate a little more about the steps that someone needs to take? What kind of structure is required for the spreadsheet for example.
Also editing the config.php is not as easy as it looks. Which fields are for what?
Thanks for your updates!
Hi, thanks for trying it, yep I haven’t fully documented everything, so it might be a bit hard to figure out. Have a look at include.php to see the order of the parameters that they go in, but I’ll hopefully make this easier sometime.
What program do I need to view the demos for the Google Spreadsheets to iCal application?
You can view the demo calendars in iCal / Outlook / Google Calendar / Sunbird/Lightening.
To view the code that produces them, download the zip file and look at the config file.
Since Google Calendar is iCal based, does this program also put dates from Google docs to Google calendar?
Yes if setup correctly on a PHP server this script will allow you to configure google spreadsheets to produce an iCal feed which can be read in google calendar. If you’ve setup your feed you can import it to google calendar using Add->Add by URL, and entering your URL.
Hi James,
Just stumbled across this bit of code, its exactly what I was looking for!
I am having a bit of trouble however, when trying to subscribe to the calendar in Google Calendar. The URL I am trying to input as ‘Add by URL’ is http://thornlie.org.au/admin/roster/, which downloads fine as an ICS file to my computer, but Google doesnt seem to like it – it adds it, but no events show, and when I go back to the calendar settings, it says ‘Could not fetch the url’.. any ideas?
Hello,
How can I make this work with a PUBLIC spreadsheet? where no username or password is required?
Thanks
I haven’t tried this, but either
1) It should just work entering no username or password
2) Or even if a spreadsheet is public, you have to enter a username and password to access it via the api.
Hello James
Would love to modify this for events which have different startTimes and startDates. PLEASE give us some tips on how to modify the way your script parses the spreadsheet. THANKS!
AWESOME! Thank you, this saved a ton of time!