12 ms·

Use a Google Spreadsheet as your JSON backend

219 points by bitsweet 13y ago

yahelc 13y ago
Tabletop is a great JS library for dealing with this: https://github.com/jsoma/tabletop https://github.com/jsoma/tabletop
- codenberg 13y ago
  Another alternative is the Miso Project http://misoproject.com/dataset/tutorials/googlespreadsheets http://misoproject.com/dataset/tutorials/googlespreadsheets
- lsv1 13y ago
  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/ http://sastrestuff.wordpress.com/2013/10/12/poor-mans-sql/
- atestu 13y ago
  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-page-cms-tabletop-js/ http://www.cbinsights.com/team-blog/google-spreadsheet-one-p... [1] http://www.cbinsights.com/press http://www.cbinsights.com/press
- dangerscarf 13y ago
  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.
nicolsc 13y ago
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-party-service-providers-as-cms http://joshfire-tech.tumblr.com/post/65032069418/using-third...
- krrishd 13y ago
  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
chrisweekly 13y ago
Clever hack, for prototypes.
tburch 13y ago
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 13y ago
  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 13y ago
    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.
nirvanatikku 13y ago
..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/
- jianshen 13y ago
  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).
- andrewb 13y ago
  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.
- AmericanOP 13y ago
  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 13y ago
    You probably don't need a dev. Try Zapier.com
    - rattray 13y ago
      I've done a number of things with Apps Script exactly because I couldn't do them with Zapier (or IFTTT, for that matter).
deleted 13y ago
[deleted]
dota168 13y ago
Logical Increments PC Parts Guide http://www.logicalincrements.com/ http://www.logicalincrements.com/ has been doing this to present their data.
donohoe 13y ago
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-page-on-google-spreadsheets http://open.qz.com/post/52146389669/build-your-own-versions-... https://github.com/Quartz/tumblr/tree/master/open/examples/google-drive-jsonp https://github.com/Quartz/tumblr/tree/master/open/examples/g...
tgasson 13y ago
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 13y ago
  The URL has "public" in it; are you sure Google requires authentication at all for this?
  - tgasson 13y ago
    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.
stu_k 13y ago
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=0Ar35F5WUAjXedDY4R0VTS05UcWhaM081eklIclN0VFE 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.
surreal 13y ago
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 13y ago
  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 13y ago
    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 13y ago
      > 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/#retrieving_a_list_of_spreadsheets https://developers.google.com/google-apps/spreadsheets/#retr...
      - surreal 13y ago
noiv 13y ago
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 13y ago
  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 13y ago
    Yes, I ended up using App Engine's memcache on top, much faster.
espeed 13y ago
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=72942000 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 13y ago
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 13y ago
  The tab identifiers are actually listed in the Spreadsheets API, inside the "worksheets" feed: https://developers.google.com/google-apps/spreadsheets/#working_with_worksheets 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.
  - justincormack 13y ago
    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.
minikomi 13y ago
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 13y ago
  G Spreadsheet CSV format sucks, with or without doublequote, with or without newline, all kinds of undefined behaviors.
FromStoneage 13y ago
you're stealing my idea ! http://www.nextofwindows.com/how-to-use-google-doc-spreadsheet-serve-json-from-excel-spreadsheet/ http://www.nextofwindows.com/how-to-use-google-doc-spreadshe...
- avgp 13y ago
  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_sample https://developers.google.com/gdata/samples/spreadsheet_samp... so I just spread something lesser known and didn't mean to "steal" anything.
  - FromStoneage 13y ago
    I'd appreciate it if you can provide some credit :) Thanks
lennel 13y ago
I played with this in 2008, works nicely. I remember running into a 42k row limit with a single spreadsheet.
fatihacet 13y ago
It's nice tip. However IMO, it would be better to use services like Firebase.
- lowboy 13y ago
  Care to justify that opinion for those of us that can't read into your mind?
  - avgp 13y ago
    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.
  - fatihacet 13y ago
    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.
Q_the_Novice 13y ago
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 13y ago
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/
el_shayan 13y ago
I learned it the hard way: if an API is not official it is likely to break.
collyw 13y ago
Wonderful. "Excel as a database" more or less.
stared 13y ago
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=0AqMEGBUNwXeHdHpQNlVuY29SUE5BSXVtS3JueGlNYVE#gid=0 https://docs.google.com/spreadsheet/ccc?key=0AqMEGBUNwXeHdHp...
warrior10111 13y ago
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/public/values?alt=json";window.location=url;})(); https://spreadsheets.google.com/feeds/list/"+key+"/od6/publi...
genericacct 13y ago
In this thread: people who don't know they should be using ethercalc =)
- theg2 13y ago
  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 13y ago
    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..
  - genericacct 13y ago
    Oh and it loads 20 megs of CSVs without skipping a beat. try that with google docs..
exratione 13y ago
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-and-google-spreadsheets-in-nodejs/ https://www.exratione.com/2013/04/some-notes-on-csv-parsing-...
theg2 13y ago
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.
walshemj 13y ago
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 13y ago
  Yes, because why use existing tools that end users are familiar with when you can be a pretentious jerk about it?
tsieling 13y ago
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/

219 points by bitsweet 13y ago

Tabletop is a great JS library for dealing with this: https://github.com/jsoma/tabletop https://github.com/jsoma/tabletop

codenberg 13y ago
Another alternative is the Miso Project http://misoproject.com/dataset/tutorials/googlespreadsheets http://misoproject.com/dataset/tutorials/googlespreadsheets
lsv1 13y ago
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/ http://sastrestuff.wordpress.com/2013/10/12/poor-mans-sql/
atestu 13y ago
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-page-cms-tabletop-js/ http://www.cbinsights.com/team-blog/google-spreadsheet-one-p... [1] http://www.cbinsights.com/press http://www.cbinsights.com/press
dangerscarf 13y ago
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.

nicolsc 13y ago

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-party-service-providers-as-cms http://joshfire-tech.tumblr.com/post/65032069418/using-third...

krrishd 13y ago
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

chrisweekly 13y ago

Clever hack, for prototypes.

tburch 13y ago

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 13y ago
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 13y ago
  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.

nirvanatikku 13y ago

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

jianshen 13y ago
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).
andrewb 13y ago
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.
AmericanOP 13y ago
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 13y ago
  You probably don't need a dev. Try Zapier.com
  - rattray 13y ago
    I've done a number of things with Apps Script exactly because I couldn't do them with Zapier (or IFTTT, for that matter).

deleted 13y ago

[deleted]

dota168 13y ago

Logical Increments PC Parts Guide http://www.logicalincrements.com/ http://www.logicalincrements.com/ has been doing this to present their data.

donohoe 13y ago

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-page-on-google-spreadsheets http://open.qz.com/post/52146389669/build-your-own-versions-... https://github.com/Quartz/tumblr/tree/master/open/examples/google-drive-jsonp https://github.com/Quartz/tumblr/tree/master/open/examples/g...

tgasson 13y ago

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 13y ago
The URL has "public" in it; are you sure Google requires authentication at all for this?
- tgasson 13y ago
  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.

stu_k 13y ago

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=0Ar35F5WUAjXedDY4R0VTS05UcWhaM081eklIclN0VFE 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.

surreal 13y ago

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 13y ago
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 13y ago
  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 13y ago
    > 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/#retrieving_a_list_of_spreadsheets https://developers.google.com/google-apps/spreadsheets/#retr...
    - surreal 13y ago

noiv 13y ago

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 13y ago
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 13y ago
  Yes, I ended up using App Engine's memcache on top, much faster.

espeed 13y ago

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=72942000 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 13y ago

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 13y ago
The tab identifiers are actually listed in the Spreadsheets API, inside the "worksheets" feed: https://developers.google.com/google-apps/spreadsheets/#working_with_worksheets 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.
- justincormack 13y ago
  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.

minikomi 13y ago

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 13y ago
G Spreadsheet CSV format sucks, with or without doublequote, with or without newline, all kinds of undefined behaviors.

FromStoneage 13y ago

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

avgp 13y ago
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_sample https://developers.google.com/gdata/samples/spreadsheet_samp... so I just spread something lesser known and didn't mean to "steal" anything.
- FromStoneage 13y ago
  I'd appreciate it if you can provide some credit :) Thanks

lennel 13y ago

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

fatihacet 13y ago

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

lowboy 13y ago
Care to justify that opinion for those of us that can't read into your mind?
- avgp 13y ago
  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.
- fatihacet 13y ago
  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.

Q_the_Novice 13y ago

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 13y ago

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/

el_shayan 13y ago

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

collyw 13y ago

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

stared 13y ago

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=0AqMEGBUNwXeHdHpQNlVuY29SUE5BSXVtS3JueGlNYVE#gid=0 https://docs.google.com/spreadsheet/ccc?key=0AqMEGBUNwXeHdHp...

warrior10111 13y ago

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/public/values?alt=json";window.location=url;})(); https://spreadsheets.google.com/feeds/list/"+key+"/od6/publi...

genericacct 13y ago

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

theg2 13y ago
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 13y ago
  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..
- genericacct 13y ago
  Oh and it loads 20 megs of CSVs without skipping a beat. try that with google docs..

exratione 13y ago

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-and-google-spreadsheets-in-nodejs/ https://www.exratione.com/2013/04/some-notes-on-csv-parsing-...

theg2 13y ago

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.

walshemj 13y ago

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 13y ago
Yes, because why use existing tools that end users are familiar with when you can be a pretentious jerk about it?

tsieling 13y ago

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/