Getting data from a Google Spreadsheet

When you’re in need of a way to store data collectively online, you may want a quick and simple way to store, edit and retrieve data, Google Spreadsheets are an easy-to-use choice.

Let’s say you create a simple table and don’t want to build a complicated database backend to write, edit and store for your data – but you want to conveniently consume this data as JSON – then Google Drive helps you with that.

Step 1: Create a spreadsheet and publish it to the web

Sticking with the Table example, you may want to create a spreadsheet like this:

You can then publish the document to the web by choosing “File” > “Publish to the web”.
In the lower half of the publishing dialog, you can see a link to your data – but it only gives us HTML (try to change output from “html” to “json” – it doesn’t work).

Copy the key=… part (in my example it is 0AtMEoZDi5-pedElCS1lrVnp0Yk1vbFdPaUlOc3F3a2c
) and put it into this URL: https://spreadsheets.google.com/feeds/list/PUT-KEY-HERE/od6/public/values?alt=json-in-script&callback= instead of “PUT-KEY-HERE”.

For my example the URL is https://spreadsheets.google.com/feeds/list/0AtMEoZDi5-pedElCS1lrVnp0Yk1vbFdPaUlOc3F3a2c/od6/public/values?alt=json-in-script&callback=. It does not directly work in the browser, but if you append something, say “x”, to the URL it displays your data as JSONP.

Alternatively, you can get it as pure JSON (but you may need to run it through a CORS proxy, for example cors.io) with https://spreadsheets.google.com/feeds/list/PUT-KEY-HERE/od6/public/values?alt=json

Now, in your Processing Visualization can retrieve the data and use it however you wish to.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.