Where developers come to connect, share, build and be inspired.


Use a Google Spreadsheet as your JSON backend


When writing a simple web app or prototyping something, you may want a quick and simple way to store, edit and retrieve data.

Let's say you write a simple blog and don't want to build a backend to write, edit and store your posts - 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 blog example, you may want to create a spreadsheet like this: http://i.imgur.com/eIPIJmL.png

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 web app you can retrieve the data and use it however you wish to - for example:

$.getJSON("http://cors.io/spreadsheets.google.com/feeds/list/0AtMEoZDi5-pedElCS1lrVnp0Yk1vbFdPaUlOc3F3a2c/od6/public/values?alt=json", function(data) {
  //first row "title" column


Add a comment