cronspam.org

You’ve just finished learning all about Timeline, you have a working example on your own website, and you’re dreading having to manage an XML or JSON eventsource file.

One good solution is Google Spreadsheets – you can manage your events in a standard spreadsheet, then export them in JSON format to Timeline.

Even better, Google Spreadsheets allows custom forms to be created to simplify the entry of events (you can even embed this form in your own webpage alongside Timeline).

Create a Google Spreadsheet

Go to Google Docs and select New -> Spreadsheet

Add columns for each eventsource field you want to populate. I recommend the following:

• start
• end
• duration
• title
• description
• link
• icon
• category (not an actual eventsource field, it’ll be used for automatic styling)

It should look something like this:

Publish your spreadsheet

Now you need to make your spreadsheet accessible to the public by publishing it. Go to the Share tab and select Share with the world:

Make sure Let people view without signing in is selected, copy the provided link & click Save:

The link should look something like this:

http://spreadsheets.google.com/ccc?key=pu1JqzPKsipKl2H7e9EmmRA

Get your spreadsheet data as a feed

To incorporate your spreadsheet data with Timeline, you’ll need to retrieve the feed as JSON formatted data. Thankfully the Google Docs API now supports this, use the key value from the previous step and build the feed URL:

http://spreadsheets.google.com/feeds/list/pu1JqzPKsipKl2H7e9EmmRA/od6/public/basic

More information: Google Spreadsheets Data API

It’s helpful to be able to view the data from the JSON feed, but in a format prettier than the actual raw feed data. Check out JSON Inspector (Firefox only) for a nice solution.

Integrate your spreadsheet data with Timeline

Ok, this is where it gets dirty. You’ll need to write some Javascript to process the JSON data from the spreadsheet feed & create event sources for Timeline to display. Here’s what I did: timeline.js

Breaking it down, these are the important excerpts from timeline.js:

At the end of tl_init(), after Timeline is configured, the feed is processed using a callback (tl_loadWorksheetJSON):

  // JSON feed for google spreadsheet, will load events internally
  var feedUrl = "http://spreadsheets.google.com/feeds/list/pu1JqzPKsipKl2H7e9EmmRA/od6/public/values";
  feedUrl += "?alt=json-in-script&callback=tl_loadWorksheetJSON";
 
  var scriptTag = document.createElement('script');
  scriptTag.src = feedUrl;
  document.body.appendChild(scriptTag);

Taking a look at tl_loadWorksheetJSON, you’ll see that it iterates through the rows from the spreadsheet feed, converting each row to an event based on the column name. The list of events is then added to Timeline:

  eventSource.addMany(timelinerEntries);

You’re welcome to reuse the code, it’s under a Creative Commons license.

Create a webform for your spreadsheet

Google Spreadsheets supports the creation of forms, this really simplifies your data entry and allows others to help enter data without exposing your spreadsheet. You can even embed the form into a website, allowing others to update the Timeline interactively.

Don’t edit the titles of the questions in the form – those are the column names, and if you edit the titles they’ll update the spreadsheet and break your spreadsheet/javascript parsing.

Credits

Many thanks to the blog entry here: http://kiyo.wordpress.com/2007/04/29/google-spreadsheet-json-to-timeline/ where I was able to see the original Javascript in action and everything I’d been reading about the Google Docs API and Timeline references all finally made sense.