..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).
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.
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.
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.
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.
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]
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.
[+] [-] nirvanatikku|12 years ago|reply
[1] https://developers.google.com/apps-script/
[+] [-] andrewb|12 years ago|reply
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.
[+] [-] jianshen|12 years ago|reply
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).
[+] [-] AmericanOP|12 years ago|reply
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!
[+] [-] BonoboBoner|12 years ago|reply
[+] [-] surreal|12 years ago|reply
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
[+] [-] tjohns|12 years ago|reply
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.)
[+] [-] dangerscarf|12 years ago|reply
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
[+] [-] dragonwriter|12 years ago|reply
[+] [-] afandian|12 years ago|reply
[+] [-] donohoe|12 years ago|reply
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...
[+] [-] tgasson|12 years ago|reply
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|12 years ago|reply
[+] [-] minikomi|12 years ago|reply
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|12 years ago|reply
[+] [-] espeed|12 years ago|reply
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.
[+] [-] justincormack|12 years ago|reply
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|12 years ago|reply
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.
[+] [-] yahelc|12 years ago|reply
https://github.com/jsoma/tabletop
[+] [-] atestu|12 years ago|reply
[1] http://www.cbinsights.com/press
[+] [-] dangerscarf|12 years ago|reply
[+] [-] codenberg|12 years ago|reply
[+] [-] lsv1|12 years ago|reply
[+] [-] noiv|12 years ago|reply
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|12 years ago|reply
[+] [-] nicolsc|12 years ago|reply
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...
[+] [-] krrishd|12 years ago|reply
[+] [-] tburch|12 years ago|reply
[+] [-] hamburglar|12 years ago|reply
[+] [-] stu_k|12 years ago|reply
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.
[+] [-] exratione|12 years ago|reply
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-...
[+] [-] tsieling|12 years ago|reply
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/
[+] [-] lennel|12 years ago|reply
[+] [-] stared|12 years ago|reply
GitHub: https://github.com/biovisualize/d3visualization
Spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0AqMEGBUNwXeHdHp...
[+] [-] FromStoneage|12 years ago|reply
[+] [-] avgp|12 years ago|reply
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.
[+] [-] walshemj|12 years ago|reply
[+] [-] theg2|12 years ago|reply
[+] [-] dota168|12 years ago|reply
http://www.logicalincrements.com/
has been doing this to present their data.
[+] [-] theg2|12 years ago|reply
It's greatly sped up our process for visualization.