How to make an exhibit from data fed directly from a Google Spreadsheet

From SIMILE Widgets
Revision as of 13:38, 1 June 2010 by (Talk)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

This tutorial shows you how to feed data directly from a Google Spreadsheet rather than from a file. The advantage of this is that you can edit your data in Google Spreadsheets' user interface. Here is an example.


Formatting the Data

You need to make sure that your Google spreadsheet follows a particular format, which can be seen here:

The first row contains the property names wrapped in {}. You can specify the value type for a property, e.g., {rating:number}. You can have multiple values for each field and they should be separated by semicolons, e.g., "Drama; Epic". If you want to tell Exhibit not to split a field by semicolons, add ":single" to the column header, e.g., {plot:single}.

Important note! Be sure to make the first column {label}. Otherwise Exhibit will use what it finds in cell A1 as {label}, since the Google Spreadsheet export format does not convey the data listed in that cell.

Getting the Feed

First, open your Google spreadsheet and then click on the Publish tab on the right (or the "Share" button, then select "Publish as a web page"). If it's not published yet, click Publish Now (or "Start Publishing"). Then you will see something like this:

 Publish at:

Click on the URL to get a new window with your spreadsheet in view only mode.


Click on the RSS feed icon in your browser to get the feed URL, or use the following methods.

For Firefox

If you're using Firefox, right click anywhere on the page and choose View Page Info. Then click on the Links tab, and look for a row in which Name is "alternate", Type is "Related Item", "Address" looks something like this:

Right click on that row and choose Copy. Then close the dialog box. What you've copied is the URL of the feed of the spreadsheet.

For Internet Explorer

Right click somewhere on the page and choose View Source. Then use the source editor's Find command to search for "feeds". That should land you in the middle of the feed URL. Copy that URL to the clipboard and close the source editor.

Setting up the Exhibit

To link in your data, paste in that feed URL and append ?alt=json-in-script to it. Then, make sure the data link has type="application/jsonp" (note the "p") and ex:converter="googleSpreadsheets":

 <link rel="exhibit/data" 
       ex:converter="googleSpreadsheets" />

This is assuming that you are using Exhibit version 2.0.

For basic information on setting up an Exhibit page, view the Exhibit documentation.


You can set your Google Spreadsheet to re-publish every so often, or you have to manually click the Re-publish button in Google Spreadsheets.

But that's it!

Useful Tips

You can provide your own schema definition for the feed in a separate JSON link to provide clarity to your data such as this example which defines the two fields as dates and provides new default labels:

  properties: {
      "start" : { valueType: "date", label: "Event Start" },
      "end" : { valueType: "date", label: "Event End" }

Dates in Google Spreadsheets

When entering dates in a google spreadsheet in ISO 8601 format ,the resulting data in you application is usually reformatted into M/D/YYYY.

E.g It is entered in the spreadsheet as YYYY-MM-DD but ends up as M/D/YYYY (e.g.2008-04-22 becomes 4/22/2008).

To overcome this problem, type


instead of just typing


This trick works in both Excel and Google Spreadsheets.

Another way to translate googSpreadsheets data format

Eg. in cell F2, date is 5/1/2008

to make G2 as 2008-05-01,set cell G2 as:


Personal tools