Using a Google spreadsheet as a JSON data source

We’ve worked on a couple of projects where the client needs to be able to edit and maintain some tabular data to drives features on the front-end of a site. Google sheets provide a really familiar and user-friendly way to do this where out-and-out performance isn’t your highest priority.

There are a number of tutorials out there on how to use a Google spreadsheet as a JSON data source, but here’s our take:

  1. Publish the spreadsheet by going to File->Publish to the Web
  2. Grab the document key. You’re going to need it in a minute.Screen Shot 2015-02-03 at 16.33.31
  3. Make a HTTP GET call to https://spreadsheets.google.com/feeds/worksheets/YOUR_KEY_HERE/public/values?alt=json. This gets a list of all the sheets within your workbook. This often doesn’t work too well from within the browser – we like to use Postman for ad-hoc API calls.
  4. In the returned result, you’ll get an array that includes a variety of feeds for each sheet in your workbook. We want the ‘listfeed’ for the MP sheet. In our case, it looks like this: Screen Shot 2015-02-03 at 16.50.35
  5. If you query that, you’ll get the sheet as XML. We want is as JSON, so simply add ?alt=json to the end of the query
  6. Result! Parse the JSON and you now have your data as an array of rows, e.g. returnedResult.entry[] and the columns as individual properties in the form gsx$YourColumnName within the array