james d. low |
live / work / play / worship |
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&user=someone@gmail.com&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
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
| If you found any of the software useful, please consider supporting its further developement by donating. |
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.