Using Google Spreadsheet as JSON backend

When writing a simple web application or prototyping something, sometimes you want to have a quick and simple means of storing, editing and retrieving data.
For example, you write a simple blog and you don’t want to build a backend to write, edit and store posts there, but you want to conveniently collect this data as JSON - Google Drive will help you with this.

Step 1: Creating a table and publishing 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, select "File"> "Publish on the Internet ...". At the bottom of the dialog you can see the link to the data, but it gives us access only to HTML (try changing “html” to “json” - it will not 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= 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= 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 skip it first through a CORS proxy, for example cors.io) via the link: spreadsheets.google.com/feeds/list/PUT-KEY-HERE/od6/public/values?alt=json

Now in your web application you can get data and use it as you like, 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']); });