duapqq

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
  console.log(data.feed.entry[0]['gsx$title']['$t']);
});

42 Responses
Add your response

10594
Icflorescu

Wow, I never thought about it :-)

over 1 year ago ·
10597
Avatar

Thanks for sharing cors.io! Definitely checking this out for my next project.

over 1 year ago ·
10598

Really cool. Great for prototyping!

over 1 year ago ·
10599
482443770fd67cd3730768677e9bbde2

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

over 1 year ago ·
10600

Brilliant!

over 1 year ago ·
10601
D41d8cd98f00b204e9800998ecf8427e

nice!

over 1 year ago ·
10602

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

over 1 year ago ·
10604
9f2a1db7d95b36b91c2b97dab2941cf6

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?

over 1 year ago ·
10609
35f4d000a88cdbcf6392dfb206ebd5e2

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.

over 1 year ago ·
10610

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!

over 1 year ago ·
10616

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

over 1 year ago ·
10617
736212 10200976756252420 836055407 o

Backend without saving the data?

Where is your God now?!

over 1 year ago ·
10619
0 ubeyu6q74mdsglnhuputukqyzuog0lchz1y3u59obzo htheclstglcrn1ejyg9xsne8 gkad3gb

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!

over 1 year ago ·
10622
1a8211dd607733fbd0cb67e4906e0c1a

@emgiezet you can save data with apis like gspread (https://github.com/burnash/gspread). And this trick lets you retrieve your data faster.

over 1 year ago ·
10623
Ec5c77caef310e4932395d37ad027e2b

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.

over 1 year ago ·
10624
8b785ee54bb136b4414fb3f15cd17a68

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.

over 1 year ago ·
10625
0 ubeyu6q74mdsglnhuputukqyzuog0lchz1y3u59obzo htheclstglcrn1ejyg9xsne8 gkad3gb

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!

over 1 year ago ·
10637
7b6d55d1d2a55cac8a17334bd2c5546d

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.

http://chriszarate.github.io/sheetrock/

over 1 year ago ·
10639
8ce7e220111d79ebefa88d2f48364218 normal

I like these kind of tricks, I actually used Blogger to power my blog using RSS feed http://jawbfl.blogspot.com

over 1 year ago ·
10664
5f3fdb03cc1a530df5ae3148cf808eed

This is really what I am looking for! :)

over 1 year ago ·
10688
Myself

Hey guys, it is part of the unhosted movement!

https://unhosted.org/

I personally like the NimbusBase approach : http://nimbusbase.com/

over 1 year ago ·
10703
9b51c0db928eefd139d71297e5893a2e

Love this!

over 1 year ago ·
10730
8fdfe6510f04a881be959b60c1fdbb21

Great .... loved it to know

over 1 year ago ·
10746
2b3837af1d68d0b71639e693c780386f

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.

over 1 year ago ·
10786
1sh8irnr9c2yicfaepea normal

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.

https://codenvy.com/factory?v=1.0&pname=googleSpreadsheet&wname=ecavazos&vcs=git&vcsurl=http%3A%2F%2Fcodenvy.com%2Fgit%2F48%2Fe7%2Fdc%2Fworkspaceu909ru5xicwtsrnx%2FgoogleSpreadsheet&idcommit=9d44017b13af4123ddbb2ffa109428a56612b44b&action=openproject&ptype=PHP&openfile=index.php

over 1 year ago ·
10788

Any idea why it does not load?

Actually not even google's own example is working https://developers.google.com/gdata/samples/spreadsheet_sample

over 1 year ago ·
10836
5759b6735ee2cd8f7fb2697ec59ace1d

And you can use Dropbox Datastore API. And Thanks for cors.io

over 1 year ago ·
10861
Myself

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 :)

over 1 year ago ·
11012
F35417c3606cd7b06af313abbbb14353

hot !!

over 1 year ago ·
11116
95cb64d23eb1492cf020224c90145981

cool idea, Any thoughts why I am getting bad request (400) after entering my key

over 1 year ago ·
11294
A46ffb37034fd4cdeadec5970578bcf0

Oh.. I like this. This might be exactly what I was looking for.

over 1 year ago ·
11310
Unbenannt 1 normal

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

over 1 year ago ·
12693
Me

Created a webapp out of this:

http://spreadsheet2json.herokuapp.com/

over 1 year ago ·
12898
My face

never thought about that before...great!

over 1 year ago ·
14230
5j8nm  a normal

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

over 1 year ago ·
15768
5e6ceef905d14ade228ea22c445d57bc

See also: Sheetsee.js

over 1 year ago ·
16176
F898b4e1ba5b8ed93f643859b9d55cd6

If you want to filter data from spreadsheet with SQL here's how to do it: https://coderwall.com/p/pluhsg

over 1 year ago ·
17371
8fzbol3v normal

this is great. question: what if i would like to retrieve the json feed every 5 seconds, how is it possible? thanks.

over 1 year ago ·
20878
94skqcaw normal

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.

over 1 year ago ·
21184
None

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

over 1 year ago ·
21238
None

This is awesome. Free data hosting! Can you use a Google Form Spreadsheet as well?

over 1 year ago ·
24931
0bq0xmsa normal

Coll post! Btw. check out https://sheetsu.com - it turn Google Spreadsheet into REST API in one click.

10 months ago ·