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:
- Publish the spreadsheet by going to File->Publish to the Web
- Grab the document key. You’re going to need it in a minute.
- 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.
- 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:
- If you query that, you’ll get the sheet as XML. We want is as JSON, so simply add
?alt=jsonto the end of the query
- Result! Parse the JSON and you now have your data as an array of rows, e.g.
returnedResult.entryand the columns as individual properties in the form
gsx$YourColumnNamewithin the array