Use a Google Spreadsheet as your JSON backend | Hacker News Reader

Use a Google Spreadsheet as your JSON backend

coderwall.com

219 pointsbitsweet67 comments

..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. Layered on top of Spreadsheets, this pair takes prototyping to a whole new level.

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

From personal experience, Google Apps Script is not ready for Enterprise / Production. It's simply too unreliable.

However I do enjoy the API. It's wonderful how easy it is to prototype an idea and get it off the ground with minimal effort.

Apps Scripts are pretty amazing. You can add custom UI elements to Google Spreadsheets and have it talk to external services (even your own).

My team is currently using it as a makeshift JSON level editor for a mobile game where each tab represents a collection of objects and the schema is defined dynamically by whatever is in the frozen header row. (think Backbone collections and models).

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!

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

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

VBA in the cloud?

I've actually used an excel doc as a backend, which wrote json to a server for an app to consume. And honestly, I would do it again over using a google doc.

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

This feature is documented: https://developers.google.com/gdata/samples/spreadsheet_samp...

In fact, it's a core feature of GData: https://developers.google.com/gdata/docs/json#Request

That's not to say the API will never change, but if it does, it will be documented here: https://developers.google.com/google-apps/spreadsheets/

(Disclaimer: I used to be the Google Developer Relations engineer responsible for Spreadsheets, but that was ~4 years ago.)

I maintain a library[1] that's pretty popular with the journalism crowd for converting Google Spreadsheets to JSON, and you're exactly right about the risk[2]. Google's been sitting on a bug for over 6 months now because API usage of Google Spreadsheets just isn't important enough to merit a fix[3].

We've sent people off into the caching world[4] to fit it on our end, but your criticism is unfortunately spot-on.

[1] https://github.com/jsoma/tabletop

[2] https://github.com/jsoma/tabletop#okay-wait-weve-got-a-big-p...

[3] http://productforums.google.com/forum/#!category-topic/docs/...

[4] http://github.com/jsoma/flatware

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.

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?

> 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...

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

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.

Yes and no. I built an app for a client on top of Google Fusion tables, which is nearly the same thing. It was acknowledged that the API could change (indeed it did mid-way through). Nothing is constant, but some things are more constant than others. You have to work out what's suitable on a case-by-case basis I think.

Agree. But I'd personally place 'using an undocumented feature that requires constructing a URL based on a parameter from a different URL' towards the 'unsuitable' end of the spectrum! Somewhere around the 'be careful' mark. Unless I'm missing some documentation

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-...

https://github.com/Quartz/tumblr/tree/master/open/examples/g...

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.

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

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.

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.

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

We use a Google Spreadsheet as the DB for the TinkerPop Book preview sign up form (http://www.tinkerpopbook.com), however, we used the old-style Google Docs Form (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 -- props to the base22 team for the tip (https://wiki.base22.com/pages/viewpage.action?pageId=7294200...).

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.

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.

The tab identifiers are actually listed in the Spreadsheets API, inside the "worksheets" feed: https://developers.google.com/google-apps/spreadsheets/#work...

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.

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.

Tabletop is a great JS library for dealing with this:

https://github.com/jsoma/tabletop

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...

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

Tabletop creator here, thanks for mentioning it! It's funny - since my target audience was journalists I never ever ever thought of describing it as "getting JSON from Google Spreadsheets." Looks like it's time to update the ol' README.

Another alternative is the Miso Project http://misoproject.com/dataset/tutorials/googlespreadsheets

I did the same in php and posted about it here, it's the poor man's DB http://sastrestuff.wordpress.com/2013/10/12/poor-mans-sql/

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.

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

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

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...

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

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

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]

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.

I wrote a small library a while ago to use Google spreadsheets like this: 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/ (data from https://docs.google.com/spreadsheet/ccc?key=0Ar35F5WUAjXedDY... )

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.

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-...

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 (best viewed on a smartphone) and the code is open source at https://github.com/denimandsteel/megaphone. Case study is at http://denimandsteel.com/work/megaphone-finder/

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

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

GitHub: https://github.com/biovisualize/d3visualization

Spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0AqMEGBUNwXeHdHp...

you're stealing my idea ! http://www.nextofwindows.com/how-to-use-google-doc-spreadshe...

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... so I just spread something lesser known and didn't mean to "steal" anything.

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

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

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

Logical Increments PC Parts Guide

http://www.logicalincrements.com/

has been doing this to present their data.

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.

I have once used Google Spreadsheets as a database for a bookmarking app: 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.

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

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

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

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.

Because Firebase has a well written and documented API The best advantage of Firebase is it's realtime and it's blazing fast. You can create collaborative apps without worrying the backend. I don't want to change the topic, however it's a great tip. Firebase should be used for more complex things.

Clever hack, for prototypes.

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

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...;})();

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

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?

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..

Oh and it loads 20 megs of CSVs without skipping a beat. try that with google docs..

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

Reply on news.ycombinator.com