Using Google Spreadsheet as a JSON Backend

When writing a simple web application or prototyping something, sometimes you want to have a quick and easy way to store, edit and retrieve data.
For example, you write a simple blog and you don’t feel like building a backend to record, edit and store posts there, but you want to conveniently collect this data as JSON - Google Drive will help you with this.

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

With a blog as an example, you probably want to create a table like this:


Then you can publish this document on the web. To do this, choose File> Publish On The Internet .... At the bottom of the dialog you can see a link to the data, but it gives us access only to HTML (try changing “html” to “json” - it won’t work).

Copy everything after key = (in my example - 0AtMEoZDi5-pedElCS1lrVnp0Yk1vbFdPaUlOc3F3a2c) and substitute this in the URL: spreadsheets.google.com/feeds/list/PUT-KEY-HERE/od6/public/values?alt=json-in-script&callback=instead of " PUT-KEY-HERE".

URL for my example: spreadsheets.google.com/feeds/list/0AtMEoZDi5-pedElCS1lrVnp0Yk1vbFdPaUlOc3F3a2c/od6/public/values?alt=json-in-script&callback=. This link does not work directly from the browser, but if you add something, for example “x”, to this URL, it will show your data as JSONP.

You can also get the data as pure JSON (but you will need to pass this through the CORS proxy first, for example cors.io) using the link:spreadsheets.google.com/feeds/list/PUT-KEY-HERE/od6/public/values?alt=json

Now in your web application you can get the data and use it as you want, for example like this:

$.getJSON("http://cors.io/spreadsheets.google.com/feeds/list/0AtMEoZDi5-pedElCS1lrVnp0Yk1vbFdPaUlOc3F3a2c/od6/public/values?alt=json", function(data) {
    //first row "title" column
    console.log(data.feed.entry[0]['gsx$title']['$t']);
});