..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.
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 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
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.
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?
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
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.
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.
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.
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.
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 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.
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.
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).
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.
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.
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).
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.
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
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.
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.
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..
[1] https://developers.google.com/apps-script/