Last Updated: August 01, 2023
·
403.3K
· avgp

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']);
});

45 Responses
Add your response

Wow, I never thought about it :-)

over 1 year ago ·

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

over 1 year ago ·

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 ·

Brilliant!

over 1 year ago ·

nice!

over 1 year ago ·

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 ·

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 ·

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 ·

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 ·

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 ·

Backend without saving the data?

Where is your God now?!

over 1 year ago ·

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 ·

@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 ·

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 ·

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 ·

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 ·

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 ·

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 ·

This is really what I am looking for! :)

over 1 year ago ·

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 ·

Love this!

over 1 year ago ·

Great .... loved it to know

over 1 year ago ·

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 ·

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 ·

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 ·

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

over 1 year ago ·

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 ·

hot !!

over 1 year ago ·

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

over 1 year ago ·

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

over 1 year ago ·

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 ·

never thought about that before...great!

over 1 year ago ·

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 ·

See also: Sheetsee.js

over 1 year ago ·

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 ·

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 ·

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 ·

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 ·

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

over 1 year ago ·

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

over 1 year ago ·

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

over 1 year ago ·

Great post. If you want to do it faster or don't have great software dev skill you can use https://sheetsu.com

over 1 year ago ·

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.

over 1 year ago ·

looks like cors.io is not required for this read-only usage of google spreadsheets anymore

over 1 year ago ·

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?

data-url="https://spreadsheets.google.com/feeds/list/1qpOLdZDccDNQ6KGNF9l3W4HJsUeKiz0fMcmmnqqmaA4/od6/public/values?alt=json"

Cheers

over 1 year ago ·