Skip to content

Instantly share code, notes, and snippets.

@jsvine
Created August 8, 2012 14:56
  • Star 28 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save jsvine/3295633 to your computer and use it in GitHub Desktop.
Why I love Tabletop.js but don't use it in production

Tabletop.js is a fantastic, open-source JavaScript library that lets developers easily integrate data from Google Spreadsheets into their online projects. I've used it, even contributed a minor feature, and love it for prototyping. Non-programmers love being able to update a project via Google Spreadsheets' hyper-intuitive interface.

That said, I'm extraordinarily wary of using Tabletop in production. Instead, at the Wall Street Journal, we use a bit of middleware to "prune" our Google Spreadsheets-based data and then cache it on our own servers. A few brief reasons:

  • Short-Term Reliability. With Tabletop, your project depends on Google not to rate-limit access to your spreadsheet. Google rate-limits access to their Spreadsheet API, though the thresholds aren't clear. If you're building an app you care about, you don't want to be in the position of hoping not too many people try to access it.

  • Long-Term Reliability. Your project also depends on Google not changing its Spreadsheets API. If it does so substantially, then — poof! — your project can't access the data it needs. Running your own middleware means that, with just a little work, you can provide your projects with a consistent data format, no matter how drastically Google's APIs change.

  • Load. Google adds a lot of cruft, like scheme notations, to its API responses. Lopping off that cruft will make your project load faster. In my experience, pruning the API response reduces the size of the data to somewhere between 15% and 40% of the original.

  • Editing flexibility. With Tabletop, changes you make to the spreadsheet are immediately reflected in your project. Sometimes you don't want that to happen. Middleware lets you update your public data only when you want.

  • Obfuscation. Anyone with a web inspector can trace Tabletop's live API calls back to the Google-hosted spreadsheet. You shouldn't be hiding anything confidential there anyhow, but I could envision circumstances where you might not want readers looking over your shoulders. Middleware lets you obfuscate the original source if you want.

Anything I missed? Any disagreement? I'm all ears.

@aendra-rininsland
Copy link

Seems like #2 just happened... https://github.com/jsoma/tabletop#okay-wait-weve-got-a-big-problem-but-a-solution-too

@zzolo & @cperryk -- Look in the Tabletop.js repo's "caching" folder, there are two scripts that create a locally-cached version. jsoma/flatware is another option if you want something a bit more dynamic. Personally, I just used the snippet from here to save a local version of the Json object and then provided that to my existing code with jQuery's getJSON() method (Ultimately replacing the Tabletop.js calls).

@jschleuss
Copy link

Another option would be to export your production spreadsheet as a JSON file and then call it in with a getJSON().

@nicktruch
Copy link

@jschleuss : excellent !! But from this, how do you modify your Tabletop.JS code to use the JSON file ?

@jsvine
Copy link
Author

jsvine commented Dec 17, 2013

@zzolo @cperryk @jschleuss @nicktruch

Whoa, just rediscovered this Gist; didn't realize folks had commented or asked questions. On the principle of "better late than never," finally responding.

  • The middleware was an ExpressJS app running on one of our internal servers. Given a spreadsheet ID, it'd fetch the spreadsheet's JSON representation, strip out all the Google Spreadsheet–specific cruft, and write a plain-old JSON file to any combination of development and production servers.
  • Each spreadsheet got its own endpoint in the app, which listed some metadata (last date published, sheet maintainer's email, etc.) and a "update" button, which would pull/write fresh data.
  • Configuration for each sheet was, in turn, handled by a single "master" spreadsheet that designated the spreadsheet ID, project name, destination server(s), et cetera.

@jsvine
Copy link
Author

jsvine commented Apr 16, 2014

"New Google Spreadsheets doesn't allow you to publish CSV to the web. That's going to break a lot of stuff. They keep doing stuff like this." — @rich_harris, 15 April 2014

@techieshark
Copy link

@jsvine

Assuming the permissions on the doc are set to 'Anyone with the link can view', downloading the following url gave me the CSV file:

https://docs.google.com/spreadsheets/d//export?format=csv

is google drive's long hash key looking thing that you see in the URL when editing the doc.

Found this here (which calls for listing the key in the URL twice but that didn't seem necessary when tested): http://stackoverflow.com/a/23702001/1024811

@techieshark
Copy link

@RyanOC
Copy link

RyanOC commented Dec 18, 2019

It can be a really nice solution for simple problems as long as you know its limitations.
Here's a newer implementation I found to be helpful... https://dev.to/sarthakganguly/google-sheets-as-your-web-backend-4a65

@DNALOB
Copy link

DNALOB commented Jan 12, 2021

Had no idea there was so many other people deep diving into this unique situation. Anyone have experience/review of sheetsee? its supposedly the one stop solution to this issue

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment