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:
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
console.log(data.feed.entry[0]['gsx$title']['$t']);
});Written by Martin Naumann
Related protips
45 Responses
Wow, I never thought about it :-)
Thanks for sharing cors.io! Definitely checking this out for my next project.
Cool. Thanks for sharing mate.
You can still get the json response using only this https://spreadsheets.google.com/feeds/list/0AtMEoZDi5-pedElCS1lrVnp0Yk1vbFdPaUlOc3F3a2c/od6/public/values?alt=json
Brilliant!
nice!
OMG! Awesomeness David! Now I can tie this into the pesky spreadsheets at work back to the p2 so I only have one system to login to :).
FTW
Great discovery. But is it really necessary to use Google spreadsheets for serving JSON? If the JSON is static (as in this case), I might as well just use a static TXT file to serve JSON. The Content-Type sure would be incorrect. I fail to understand if there is any other advantage this method offers. Also, would it be possible to have nested JSON? Shed some light?
You should try CartoDB. Similar range of functionality, pure SQL filtering of json data in real-time. Plus maps an other formats if you want them... you'll enjoy it.
Nothing is necessary. I think the benefits are pretty obvious for prototyping versus hand-writing text. Easily add columns, duplicate rows, If you want nested, just use a DB!
Well I think that is the whole ideea behind the publish to web feature anyway ... easy access ... And although nobody wants to admin it, xml is still used more than json :P
Backend without saving the data?
Where is your God now?!
Hi All! I tried this method a few weeks ago with a website I was building for educational purposes. However, when I tried to call the Google Spreadsheet using the script tag, for some reason, the rest of the javascript broke and the website was crashed. Has anyone else had this problem before? Any advice or hacks would be much appreciated!
@emgiezet you can save data with apis like gspread (https://github.com/burnash/gspread). And this trick lets you retrieve your data faster.
You need to look at the Google JavaScript Client it's in Beta at the moment but allows you access to a whole range of functionality. I've been using since it first appeared and really like the approach.
Thanks for the interesting post. How about CORS?
Last time I experimented with the Spreadsheet web API, POST and PUT weren't working cross-origin.
Ahh - thank you so much for advice. I'm currently looking at the Google Javascript Client - hopefully I can build a working solution soon. Thanks again!
This is great for small datasets on Google Spreadsheets. If you happen to work with larger spreadsheets or want to do real-time querying of the data, I made a jQuery plugin that makes this easy.
I like these kind of tricks, I actually used Blogger to power my blog using RSS feed http://jawbfl.blogspot.com
This is really what I am looking for! :)
Hey guys, it is part of the unhosted movement!
I personally like the NimbusBase approach : http://nimbusbase.com/
Love this!
Great .... loved it to know
Be careful with this technique if you're going to use it with a public facing site. Stick a caching layer between your site and Google Docs otherwise you'll make too many connections and could get your Google account temporarily restricted.
A lot of people have problems with CORS - I normaly just rout my calls through a default PHP cURL that does the proxy for the Javascript and I don't have to rely on an outside source. This Google Spreadsheet as a JSON source is awesome -
Here is an example with built in proxy. No need for CORS.
Any idea why it does not load?
Actually not even google's own example is working https://developers.google.com/gdata/samples/spreadsheet_sample
And you can use Dropbox Datastore API. And Thanks for cors.io
Why nobody saw my comment? Yes, you can use Dropbox Datastore API, and/or Google Drive API, or more simply, use http://nimbusbase.com/ :) Seriously check it out, I would like to have your feedback on that :)
hot !!
cool idea, Any thoughts why I am getting bad request (400) after entering my key
Oh.. I like this. This might be exactly what I was looking for.
jonathanhu, serious? This idea is quite old. Saw it in combination with an ajax tutorial a year ago.
https://gist.github.com/pamelafox/3926362#file-ajax-exercises-txt
You are stealing her idea! ;P
never thought about that before...great!
Great idea. I've wanted to do something like this for a while so took some time to build something thats easy to use. There is an API as well. Let me know what yout think. http://myjson.com
See also: Sheetsee.js
If you want to filter data from spreadsheet with SQL here's how to do it: https://coderwall.com/p/pluhsg
this is great. question: what if i would like to retrieve the json feed every 5 seconds, how is it possible? thanks.
I prefer datagrid libraries that are made to work on my website, I used slickgrid but it not what I was looking for, wanna check all of the http://jspreadsheets.com/ list.
Hi
heavily inspired by your Idea (and text) I create the same for GitHub as JSON storage provider. You can read/write(!) JSON files from Github with a WebApp/HTML page.
https://github.com/freegroup/draw2d_js.app.starterkit_githubstorage
Regards
Andreas
This is awesome. Free data hosting! Can you use a Google Form Spreadsheet as well?
Coll post! Btw. check out https://sheetsu.com - it turn Google Spreadsheet into REST API in one click.
I have published my spreadsheet to web but I am unable to get the JSON format. This is my link to my spreadsheet
https://docs.google.com/spreadsheets/d/e/2PACX-1vTauzjjmWOWQJ-5XrOEkWkWE-U5Jmo2c78HytkoV_bRtiJeLhB013oDsRYLSn0_4AO3hIAkFgYLGiq3/pubhtml
Great post. If you want to do it faster or don't have great software dev skill you can use https://sheetsu.com
In reaction to pwlow: You have to use a key you can see in your address bar while editing your spreadsheet, not the one from the link pointing to a html version, at least in the current version of spreadsheets. It doesn't seem to be clear from the article.
looks like cors.io is not required for this read-only usage of google spreadsheets anymore
Great article - having some trouble still getting my site to pull the JSON link. I'm using SquareSpace for this - the previous website developer was able to get it to work, and although my code is the exact same, it doesn't appear on the site when I apply the code.
Below is what I'm using, however, I can't figure out where I'm going wrong - any ideas?
Cheers
