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

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:

 http://spreadsheets.google.com/pub?key=pLvsUS-CftHo21r-0xjKvVA

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. If it's not published yet, click Publish Now. Then you will see something like this:

 Publish at: http://spreadsheets.google.com/pub?key=pLvsUS-CftHo21r-0xjKvVA

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

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:

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

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" 
       type="application/jsonp"
       href="http://spreadsheets.google.com/feeds/list/o08841867754116283182.6102151849127695926/od6/public/basic?alt=json-in-script"
       ex:converter="googleSpreadsheets" />

This is assuming that you are using Exhibit version 2.0.

Re-publishing

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

="2008-04-22"

instead of just typing

2008-04-22

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:

=year(F2)&"-"&if(len(month(F2))<2,"0","")&month(F2)&"-"&if(len(day(F2))<2,"0","")&day(F2)