
Use a Google Spreadsheet as your JSON backend - bitsweet
https://coderwall.com/p/duapqq
======
nirvanatikku
..FWIW, for those of you who haven't been enlightened with the power that
Google Apps Script[1] offers yet, be sure to check it out:
[http://script.google.com](http://script.google.com). Layered on top of
Spreadsheets, this pair takes prototyping to a whole new level.

[1] [https://developers.google.com/apps-
script/](https://developers.google.com/apps-script/)

~~~
AmericanOP
I've been hunting for a dev to connect Google spreadsheets with our company's
CRM.

If someone is interested in a project, email is in my profile. I've gotten
very lucky meeting smart people through HN before, so why not try again!

~~~
MediaSquirrel
You probably don't need a dev. Try Zapier.com

~~~
rattray
I've done a number of things with Apps Script exactly because I couldn't do
them with Zapier (or IFTTT, for that matter).

------
surreal
Good for prototyping, thanks. I'd be wary of using this in a live system
though: relying on Google's public APIs/services is risky enough (Checkout is
one example, Reader, etc) let alone an undocumented feature like this which
could change/disappear suddenly.

Edit: it has been pointed out that the criticism of their documented/public
APIs may be unjustified. The issue here is that this particular feature is
undocumented

~~~
dragonwriter
Its using it behind an abstraction layer that decouples the substantive work
of the rest of the app from the Google Spreadsheet API and storage backend,
for the express purpose of limiting the impact of any later need to move to a
different backend service. Given that Checkout and Reader -- the Google
examples you cite as reasons to be wary of it for a live system -- were
terminated with extensive notice, that doesn't seem like it would pose any
problem with this approach.

~~~
surreal
Maybe the comparison wasn't the wisest, valid point.

But I stand by the fact that this seems like hacking an undocumented feature:
"Copy the key=... part [...] and put it into this URL: [...]", which means it
could easily be modified/removed with no notice period, and is therefore risky
in a production system, no?

~~~
dragonwriter
> But I stand by the fact that this seems like hacking an undocumented
> feature: "Copy the key=... part [...] and put it into this URL: [...]",

No, the key value for use in the API being the same that is displayed in the
browser URL when working with the sheet is a _documented_ feature:

[https://developers.google.com/google-
apps/spreadsheets/#retr...](https://developers.google.com/google-
apps/spreadsheets/#retrieving_a_list_of_spreadsheets)

~~~
surreal
I didn't dispute that. The key itself is fine. I'm just worried about using it
for JSON retrieval! That part is undocumented as far as I can see, and
therefore subject to change without notice

~~~
dragonwriter
Huh. I thought that the Spreadsheet API was documented as an XML/JSON API, but
it looks like only the XML is directly documented so ... poking around a bit
more

The alt=json thing is a documented common feature of Google Data APIs, and the
current Google Spreadsheets API is the target of the link in the list of
Google Data APIs titled "Google Spreadsheets Data API". OTOH, the current v3
Spreadsheets API no longer has "Data" in the name, and there is a note on the
Data APIs documentation that some Google Data APIs have been replaced with
newer APIs that aren't Google Data APIs.

So, its at best an ambiguously-documented feature.

------
donohoe
We use Google Spreadsheets for our sites Version history page. Makes it easy
to maintain an up-to-date list of all the changes we push out.

Its pretty easy to setup your own:

[http://open.qz.com/post/52146389669/build-your-own-
versions-...](http://open.qz.com/post/52146389669/build-your-own-versions-
page-on-google-spreadsheets)

[https://github.com/Quartz/tumblr/tree/master/open/examples/g...](https://github.com/Quartz/tumblr/tree/master/open/examples/google-
drive-jsonp)

------
tgasson
This is great for prototypes but there's an edge case that breaks it's
usefulness in live sites.

Sometimes google will make already logged in users reauthenticate. It will
redirect to the authentication page and you'll get a bunch of HTML rather than
json returned, and the user won't know why it's not working.

~~~
jzwinck
The URL has "public" in it; are you sure Google requires authentication at all
for this?

~~~
tgasson
Yes I have run into this issue myself. It is a public resource, if you're not
logged in at all its fine or if you're recently logged in its fine.

It's the same reauth you sometimes get when visiting a public blogger site. It
is an edge case, but quite unpredictable and hard to track down from client
reports.

------
minikomi
For what it's worth, sheets also has publish as CSV which is super useful for,
say, building d3 graphs (d3 consumes csv like a champ).

I'm using it a lot lately as I have to create static sites with a bunch of
different translations. I have the translators edit a set template, which is
aggregated into a single sheet. Then, a (racket.. could be python or anything)
script reads from the published csv and outputs all the translated pages.
Super useful.

~~~
est
G Spreadsheet CSV format sucks, with or without doublequote, with or without
newline, all kinds of undefined behaviors.

------
espeed
We use a Google Spreadsheet as the DB for the TinkerPop Book preview sign up
form ([http://www.tinkerpopbook.com](http://www.tinkerpopbook.com)), however,
we used the old-style Google Docs Form
([https://spreadsheets.google.com/formResponse](https://spreadsheets.google.com/formResponse)),
which allows anyone to add an entry to the spreadsheet while protecting
against anyone from edititing existing entries.

This postContactToGoogle function gets around the cross-domain issue:
[http://www.tinkerpopbook.com/js/script.js](http://www.tinkerpopbook.com/js/script.js)
\-- props to the base22 team for the tip
([https://wiki.base22.com/pages/viewpage.action?pageId=7294200...](https://wiki.base22.com/pages/viewpage.action?pageId=72942000)).

Unfortunately the option for creating the old-style Google Form is not
directly available since Google switched everything over to Google Drive (if
anyone knows how to access it please let me know) so I cloned/copied an
existing old-style form for future use.

------
justincormack
Yes I have done this. Google make it particularly difficult to get the URL of
the spreadsheet. And that od6? Thats if you have multiple tabs, they have
random identifiers. Its almost impossible to work out what they will be. Its
like they are on the web but not of the web.

But it is an easy interface for unskilled users to add data to say a graph on
a website, have done that for clients and they have been very happy.

~~~
tjohns
The tab identifiers are actually listed in the Spreadsheets API, inside the
"worksheets" feed: [https://developers.google.com/google-
apps/spreadsheets/#work...](https://developers.google.com/google-
apps/spreadsheets/#working_with_worksheets)

As a historical note, these identifiers used to be part of the URL, long ago.
The newer version of the Spreadsheets frontend doesn't use them, but they're
still used by the Spreadsheets API.

~~~
justincormack
Yeah I used the API but ita a bit overkill just to get this. I wrote a
JavaScript function to get the spreadsheet URL inside the sheet but there
isn't one to get the worksheet URL.

------
yahelc
Tabletop is a great JS library for dealing with this:

[https://github.com/jsoma/tabletop](https://github.com/jsoma/tabletop)

~~~
atestu
Yep! We're using it for our press page[1], it allows us to let our
business/marketing people update our press mentions in Google Spreadsheet. We
wrote about it here: [http://www.cbinsights.com/team-blog/google-spreadsheet-
one-p...](http://www.cbinsights.com/team-blog/google-spreadsheet-one-page-cms-
tabletop-js/)

[1] [http://www.cbinsights.com/press](http://www.cbinsights.com/press)

------
noiv
Hmm, I would think thrice, before I go his path again. Thought it was a clever
idea to use the mixture of easy to maintain spreadsheets, the cron service and
JSON to feed huge satellite images into a tiling service (zoom.it) and let
Google autonomously update and serve the list of daily mosaics as JSON.

I got used to daily time out messages, but waiting 1 min for a (cached!) 10 kb
JSON list is far too much. However, organizing and correcting data using an
online spreadsheet saves a lot of time and is kinda fun.

~~~
justincormack
I always stick a caching proxy in front of it rather than using it directly. (
Mine is actually public but doesn't come with an SLA)...

~~~
noiv
Yes, I ended up using App Engine's memcache on top, much faster.

------
nicolsc
We've been using Google Spreadsheets as part of our "CMS" in our latest
website redesign.

We're relying on schema.org normalization: no more item.gsx$stuff everywhere +
switching or mixing data providers is effortless.

[http://joshfire-tech.tumblr.com/post/65032069418/using-
third...](http://joshfire-tech.tumblr.com/post/65032069418/using-third-party-
service-providers-as-cms)

~~~
krrishd
I would love to see such a CMS open-sourced, I'm sure there are a lot of
people who would like such a thing

------
tburch
This is a great way of putting a UI on top of JSON! I created
[http://jsonblob.com/](http://jsonblob.com/) to accomplish the same thing, but
a spreadsheet is much more familiar than a JSON editor.

~~~
hamburglar
Cool site. What's the development status? Seems well-polished in some regards
but pretty broken in others (I can't get open/save/clear to work at all).
[Edit: i'm using chrome latest release]

~~~
tburch
It was something that I built a while back, mostly over a weekend, but haven't
really touched since. It's running on a free Heroku dyno and coming close to
the 512M limit (grails is quite the memory hog), so I've been rewriting it
using DropWizard so that it can take better advantage of the dyno.

It does seem pretty broken with the latest Chrome (seems like menu items now
require a double click?), so I'll take a look and try to get it fixed soonish.

------
stu_k
I wrote a small library a while ago to use Google spreadsheets like this:
[https://github.com/Stuk/gooss](https://github.com/Stuk/gooss) (although it
appears some better, more maintained ones have appeared in the mean time).

You can see it working at
[http://stuartk.com/bundle/](http://stuartk.com/bundle/) (data from
[https://docs.google.com/spreadsheet/ccc?key=0Ar35F5WUAjXedDY...](https://docs.google.com/spreadsheet/ccc?key=0Ar35F5WUAjXedDY4R0VTS05UcWhaM081eklIclN0VFE)
)

This is combined with with Google forms to allow people to submit new data,
and the publish to RSS feature, although the content of the RSS feed isn't
very pretty.

------
exratione
I've implemented a Node.js app that used Google Spreadsheets as the backend
for a client. They found it more cost effective for non-technical admins to
deal with than building their own, so more power to them.

I wrote up some notes from the experience, as Google Spreadsheets is full of
quirks and some of the APIs and other means of access are very easy to break -
it is very easy to create a spreadsheet that will return broken JSON, for
example, in some modes, and then cannot be fixed (ever) to return unbroken
JSON.

[https://www.exratione.com/2013/04/some-notes-on-csv-
parsing-...](https://www.exratione.com/2013/04/some-notes-on-csv-parsing-and-
google-spreadsheets-in-nodejs/)

------
tsieling
We used this strategy on a small vendor database for a local nonprofit street
newspaper when we built a vendor locater web app for them. The non-technical
staff can update vendor names, availability, photos (by URL) and locations,
which we then pull from to populate a map-driven search. It worked really well
and saved hours of work building a custom CMS.

The app is at
[http://find.megaphonemagazine.com](http://find.megaphonemagazine.com) (best
viewed on a smartphone) and the code is open source at
[https://github.com/denimandsteel/megaphone](https://github.com/denimandsteel/megaphone).
Case study is at [http://denimandsteel.com/work/megaphone-
finder/](http://denimandsteel.com/work/megaphone-finder/)

------
lennel
I played with this in 2008, works nicely. I remember running into a 42k row
limit with a single spreadsheet.

------
stared
There is a wonderful example - a crowdsourced collection of d3.js
visualizations:
[http://christopheviau.com/d3list/gallery.html](http://christopheviau.com/d3list/gallery.html)
(all code GitHub, all data - a Google Spreadsheet everyone can contribute to).

GitHub:
[https://github.com/biovisualize/d3visualization](https://github.com/biovisualize/d3visualization)

Spreadsheet:
[https://docs.google.com/spreadsheet/ccc?key=0AqMEGBUNwXeHdHp...](https://docs.google.com/spreadsheet/ccc?key=0AqMEGBUNwXeHdHpQNlVuY29SUE5BSXVtS3JueGlNYVE#gid=0)

------
FromStoneage
you're stealing my idea ! [http://www.nextofwindows.com/how-to-use-google-doc-
spreadshe...](http://www.nextofwindows.com/how-to-use-google-doc-spreadsheet-
serve-json-from-excel-spreadsheet/)

~~~
avgp
Hey Jonathan,

I am the person who wrote the coderwall tip and I am sorry to hear that you
feel like this. Especially because I did read your post a while ago, forgot
about it and lost the link. I then just searched the GData docs and it's a
documented feature.
[https://developers.google.com/gdata/samples/spreadsheet_samp...](https://developers.google.com/gdata/samples/spreadsheet_sample)
so I just spread something lesser known and didn't mean to "steal" anything.

~~~
FromStoneage
I'd appreciate it if you can provide some credit :) Thanks

------
walshemj
Sorry if you cant put together a basic back end for your app in mysql, Berkly
DB or if you need to use json mongodb - you should stick to the day job at
MacDonalds

~~~
theg2
Yes, because why use existing tools that end users are familiar with when you
can be a pretentious jerk about it?

------
dota168
Logical Increments PC Parts Guide

[http://www.logicalincrements.com/](http://www.logicalincrements.com/)

has been doing this to present their data.

------
theg2
I ended up building this into our data warehouse system as managing my own UI
for a KVP data store became a nightmare with rapidly changing requirements.
It's in PHP but allows reporters and producers to enter their data in a
spreadsheet and then allows us to publish it out to JSON for use in D3 or
leaflet.

It's greatly sped up our process for visualization.

------
Q_the_Novice
I have once used Google Spreadsheets as a database for a bookmarking app:
[https://github.com/qawemlilo/Bookmarks](https://github.com/qawemlilo/Bookmarks).
What I did different was that I published my spreadsheet as a CSV doc and then
used YQL to convert is to JSONP.

------
mqzaidi
You can also use the Google query language to do more with the API - see
[http://qzaidi.github.io/2013/10/05/quranjs/](http://qzaidi.github.io/2013/10/05/quranjs/)

------
fatihacet
It's nice tip. However IMO, it would be better to use services like Firebase.

~~~
lowboy
Care to justify that opinion for those of us that can't read into your mind?

~~~
avgp
He does have a point - but only if you want to do something more complex. If
you need a JSON store that you can easily edit, I don't see a real advantage
over Firebase - but once you want to incorporate, say Twitter login or some
form of user authentication for your visitors, you're better off with
Firebase, I would say.

------
chrisweekly
Clever hack, for prototypes.

------
el_shayan
I learned it the hard way: if an API is not official it is likely to break.

------
warrior10111
cool, I made this bookmarklet to convert spreadsheets to JSON directly from
Google Drive:

javascript:(function(){var
key=/key=[a-zA-Z0-9]+/.exec(window.location.search)[1];var
url="[https://spreadsheets.google.com/feeds/list/"+key+"/od6/publi...](https://spreadsheets.google.com/feeds/list/"+key+"/od6/public/values?alt=json";window.location=url;}\)\(\);)

------
genericacct
In this thread: people who don't know they should be using ethercalc =)

~~~
theg2
How about you explain how this is better than Google docs? Not requiring a
Google account is nice but what else does it do better?

~~~
genericacct
It's open source and you can run anywhere from a raspberry pi to sun clusters,
for one. For free. And your data remains yours and is not delivered to the
NSA..

------
collyw
Wonderful. "Excel as a database" more or less.

