Using Google Spreadsheets with Timelines

We’ve been making use of the fabulous Timeline.js library for a while now. The first timeline we published, compiled by Mary Samouelian about the life of Doris Duke, uses Timeline.js to display text and images in an elegant interactive format. Back then the library was called Verite Timeline and our implementation involved parsing XML files using Python to render out the content on the page. And in general, this approach worked great. However, managing and updating the XML files isn’t all that easy. Things also get complicated when more than one person wants to work on them — especially at the same time.

Enter Google Spreadsheets! Timeline.js is now designed to easily grab data from a publicly-published Google spreadsheet and create great looking output out of the box. Managing the timeline data in the spreadsheet is a huge step up from XML files in terms of ease of use for our researchers and for maintainability. And it helps that librarians love spreadsheets. If someone errantly enters some bad data, it’s simple to undo that particular edit as all changes are tracked by default. If a researcher wants to add a new timeline event, they can easily go into the spreadsheet and enter a new row. Changes are reflected on the live page almost immediately.

Spreadsheet data

Timeline.js provides a very helpful template for getting started with entering your data. They require that you include certain key columns and that the columns be named following their data schema. You are free to add additional columns, however, and we’ve played around with doing so in order to include categorical descriptions and the like.

Here is a sample of some data from our Doris Duke timeline.

For entries with more than one image, we don’t include a ‘Start Date’ which means Timeline.js will skip over them. We then render these out as smaller thumbnails on our timeline page.

Going all-in with spreadsheets

We’ve published our subsequent timelines using a combination of the Google spreadsheet data to generate the Timeline.js output while also using the XML files to load in and display relational data (using the EAC-CPF standard) while using Python to generate the pages. However, for our latest timeline on the J. Walter Thompson Company (preview the dev version), we’ve decided to house all of the data (including the CPF relations) in a Google Spreadsheet and use PHP to parse everything. This approach will mean that we no longer need to rely on the XML files, so our researchers can quickly make updates to the timeline pages. We can easily convert the spreadsheet data back into an XML file if the need arises.

Code snippets

Note: there’s an updated syntax for newly created spreadsheets.

We’re taking advantage of the Google spreadsheet data API that allows for the data to easily be parsed as JSON. Querying the spreadsheet in PHP looks something like this:

$theURL = "http://spreadsheets.google.com/feeds/list/[your-spreadsheet-key]/
od6/public/values?alt=json&callback=displayContent";

$theJSON = file_get_contents($theURL, 0, $ctx); //the $ctx variable sets a timeout limit

$theData = json_decode($theJSON, TRUE);

And then we can loop through and parse out the data using something like this:

foreach ($theData['feed']['entry'] as $item) {

	echo $item['gsx$startdate']['$t'];
	// Note that the column names in the spreadsheet are targeted by adding 'gsx$' 
	   and 'the column name in lc with no spaces'
	   You may also want to use 'strtotime' on the dates so that you can 
	   transform them using 'date'

	echo $item['gsx$enddate']['$t'];

	echo $item['gsx$headline']['$t'];

	echo $item['gsx$text']['$t'];

	... // and so on
}

One important thing to note is that by default, the above query structure only gets data from the primary worksheet in the spreadsheet (which is targeted using the od6 variable). Should you want to target other worksheets, you’ll need to know which ‘od’ variable to use in your query. You can view the full structure of your spreadsheet by using a url like this:

https://spreadsheets.google.com/feeds/worksheets/[your-spreadsheet-key]/public/basic

Then match up the ‘od’ instance to the correct content and query it.

Timelines and Drupal

We’ve also decided to integrate the publishing of timelines into our Drupal CMS, which drives the Duke University Libraries website, by developing a custom module. Implementing the backend code as a module will make it easy to apply custom templates in the future so that we can change the look and feel of a timeline for a given context. The module isn’t quite finished yet, but it should be ready in the next week or two. All in all, this new process will allow timelines to be created, published, and updated quickly and easily.

---

UPDATE

I recently learned that sometime in early 2014, google changed the syntax for published spreadsheet URLs and they are no longer using spreadsheet key as an identifier. As such, the syntax for retrieving a JSON feed has changed.

The new syntax looks like this:

https://spreadsheets.google.com/feeds/cells/[spreadsheet-ID]/[spreadsheet-index]/public/basic?alt=json&callback=displayContent

‘spreadsheet-ID’ is the string of text that shows up when you publish your spreadsheet:

https://docs.google.com/spreadsheets/d/[spreadsheet-ID]/pubhtml

‘spreadsheet-index’ you can see when editing your spreadsheet – it’s the value that is assigned to ‘gid’ or in the case below, it’s ‘0’:

https://docs.google.com/spreadsheets/d/[spreadsheet-ID]/edit#gid=0

I hope this helps save some frustration of finding documentation on the new syntax.

Post contributed by Michael Daul