README.md

Tabletop.js (gives spreadsheets legs)

Tabletop.js takes a Google Spreadsheet and makes it easily accessible through JavaScript. With zero dependencies! If you've ever wanted to get JSON from a Google Spreadsheet without jumping through a thousand hoops, welcome home.

Tabletop.js easily integrates Google Spreadsheets with Backbone.js, Handlebars, and anything else that is hip and cool. It will also help you make new friends and play jazz piano.

Okay, wait, we've got a big problem. But a solution, too.

Google broke Tabletop. Kind of.

If you're signed in to Google and your session expires, Google now requires you to sign in to view published spreadsheets. Practically speaking, this means Tabletop will fail for anyone who hasn't signed into Gmail for a while. More details here.

If you don't ever sign into Google, you're fine. If you're signed in, you're fine. It's only expired sessions that run into this issue, which is why it took so long for us to figure out what the deal was.

This will break existing apps.

Google knows it's an issue, but it isn't high priority, so they don't have a fix planned until September 30th. That's a long time to have a broken app, 'eh?

I highly highly recommend you use Flatware or table-service (or your own proxying solution) in the meantime. Proxies don't run into this bug since they never sign into a Google account, and they'll probably make your app faster, too.

I tried to make Flatware pretty easy to set up, but if you'd like a hand feel free to drop me a line at jonathan.soma@gmail.com.

To ease the pain I've set up a public instance of Flatware at http://flatware.herokuapp.com, although you're going to have to manually click Sync all spreadsheets when you want your updates to go out. If you plan on using it, you'll want to set proxy: 'https://s3.amazonaws.com/flatware-live' when initializing Tabletop. I'd also appreciate it if you dropped me an email or a tweet giving me a heads up.

Notes

Getting Started

You might have seen some instructions on http://builtbybalance.com/Tabletop/, but please ignore them, because they're super super out of date. Probably don't break anything, but they sure ain't current (not my domain, can't take them down). These docs here are the most up-to-date, so treat them as the gospel truth!

1) Getting your data out there

The first step is to get your data out into a form Tabletop can digest

Take a Google Spreadsheet. Give it some column headers, give it some content.

In Google Docs, then go up to the File menu and pick Publish to the web. Fiddle with whatever you want, then click Start publishing. A URL will appear, something like https://docs.google.com/spreadsheet/pub?hl=en_US&hl=en_US&key=0AmYzu_s7QHsmdDNZUzRlYldnWTZCLXdrMXlYQzVxSFE&output=html

Copy that! In theory you're interested in the part between key= and & but you can use the whole thing if you want.

2) Setting up Tabletop

Now you're going to feed your spreadsheet into Tabletop

Include Tabletop in your HTML, then try the following, substituting your URL for public_spreadsheet_url

Open up your console and check out the data that you got. All of those rows were turned right into objects! See how easy that was?

Please don't holdwindow.onload against me, you're free to use $(document).ready and all of that jQuery jazz.

3) Honestly, that's it.

Check out the reference and the examples, but basically you're set. The only thing to think about right now is if you want to deal with multiple sheets you can get rid of simpleSheet: true (more on that later).

You might also be interested in the publishing/republishing/publish-as-it-changes aspects of Google Spreadsheets, but you'll need to google that for any specifics.

Tabletop initialization

You pass in either key as the actual spreadsheet key, or just the full published-spreadsheet URL. It calls showInfo when done, passing an array of models. Options in general are

key is the key of the published spreadsheet or the URL of the published spreadsheet.

callback is the callback for when the data has been successfully pulled. It will be passed an object containing the models found in the spreadsheet (worksheets => models), and the tabletop instance. Each of these models contains the rows on that worksheet (see Tabletop.Model). If simpleSheet is turned on it simply receives an array of rows of the first worksheet.

simpleSheet can be true or false (default false). It assumes you have one table and you don't care what it's called, so it sends the callback an array of rows instead of a list of models. Peek at the examples for more info.

parseNumbers can be true or false (default false). If true, Tabletop will automatically parse any numbers for you so they don't run around as strings.

orderby asks Google to sort the results by a column. You'll need to strip spaces and lowercase your column names, i.e. {order: 'firstname'} for a column called First Name. You'll want to use this when you only have a single sheet, though, otherwise it will try to sort by the same column on every single sheet.

reverse reverses the order if set to true.

postProcess is a function that processes each row after it has been created. Use this to rename columns, compute attributes, etc. See the TimelineSetter example below.

wanted is an array of sheets you'd like to pull. If you have 20 sheets in a public spreadsheet you might as well only pull what you need to access. See the example in simple/multiple.html. Defaults to all.

endpoint is the protocol and domain you'd like to query for your spreadsheet. Defaults to https://spreadsheets.google.com.

singleton assigned the instantiated Tabletop object to Tabletop.singleton, implemented to simplify caching and proxying of requests. Defaults to false.

simple_url, if true, changes all requests to KEY and KEY-SHEET_ID. Defaults to false.

wait prevents tabletop from pulling the Google spreadsheet until you're ready. Used in the backbone.js example.

query sends a structured query along with the spreadsheet request, so you can ask for rows with age > 55 and the like. Right now it's passed with every request, though, so if you're using multiple tables you'll end up in Problem City. It should work great with simpleSheet situations, though.

debug returns on debug mode, which gives you plenty of messaging about what's going on under the hood.

parameterize changes the src of all injected scripts. Instead of src, src is URI encoded and appended to parameterize, e.g. set it to http://example.herokuapp.com/?url=. Mostly for gs-proxy.

callbackContext sets the this for your callback. It's the tabletop object by default.

Tabletop itself

Once you've initialized a tabletop object you can access its good parts.

.sheets() are the Tabletop.Models that were populated, one per worksheet. You access a sheet by its name.

.sheets(name) is how you access a specific sheet. Say I have a worksheet called Cats I Know, I'll access it via tabletop.sheets("Cats I Know")

.model_names are the names of the models [read: sheets] that Tabletop knows about

.data() returns the rows of the first model if you're in simpleSheet mode. It's the same as .sheets() otherwise. This is just a little sugar.

.fetch() manually initializes a pulling of the data

.addWanted(name) adds a sheet to the list that are updated with .fetch

Tabletop.Model

Tabletop.Model is pretty boring, let's be honest.

.name is the name of the worksheet it came from (the tab at the bottom of the spreadsheet)

Working with Tabletop and Backbone.js

Source is, of course, in /src, and you can check it out in action in /examples/backbone/

Working with Tabletop and TimelineSetter

Tabletop was originally built to work with ProPublica's TimelineSetter, a JS+Ruby library that creates timelines. You need some specifically-formatted JSON which is created by a Ruby script from a CSV, which means your workflow is usually spreadsheet -> CSV -> Ruby -> JSON -> JS.

With Tabletop, though, you get to hook right into a Google Spreadsheet for all of your info! You just need to massage your data a little bit, thanks to Google's API messing with column names and you needing a timestamp.

You can see this in the examples directory, but here are the important parts.

See the postProcess call? That's called on every row after Tabletop.Model gets done working on it. It allows you to rename columns or edit data points without having to messily do it outside of Tabletop. I'm sure it has uses outside of TimelineSetter, too.

A sample lives in /examples/timeline_setter/

Caching/Proxying Google Spreadsheets

Yeah, Google Spreadsheets can sometimes be slow or sometimes be overwhelmed or maybe one day Google will just up and disappear on us. So Tabletop.js now supports fetching your data from elsewhere, using options like endpoint and proxy.

proxy is the fun one, in that it rewrites your requests to be simpler-looking and plays nicely with the app & example I put together.

Using Flatware

If you don't mind running around with Heroku and AWS, Flatware is an app I built that uploads the spreadsheet JSON response to S3.

Using table-service

table-service hosts it on your own server using a python script, and auto-updates thanks to a tiny script you add to your spreadsheet.

Using gs-proxy

gs-proxy is another option that also uses Heroku. You'll set parameterize to something like http://example.herokuapp.com/?url= and off you go!

Using other caching

You can point proxy at anything you'd like as long as it has KEY and KEY-SHEET_ID files sitting in a directory. Feel free to host it on your own server! You can use /caching/local.rb if you want a pretty easy solution for generating the flat files.

Notes

Strange behavior

Empty tables are trouble. We can't get column names from them (c'mon, Google!), so don't be too confused when a table with 0 rows is coming back with an empty .column_names or your code starts throwing weird errors when processing the results.

Empty rows are trouble. If you have a row that's completely empty, Google doesn't return any rows after the empty row. As a result, you need to make sure every line in your spreadsheet has data in it.

If you are having trouble

Turn on debugging by passing debug: true when you initialize Tabletop. Check out the console, I try to keep my error messages chatty and informative. Or just email me at jonathan.soma@gmail.com, I'm happy to help!

Tabletop.js in the wild

The more examples the better, right? Feel free to fork or contact me if you have a good example of something you've done.

Other Options

If you aren't really feeling Tabletop, you should give Dataset a shot. It's "a JavaScript client-side data transformation and management library," which means it does a hell of a lot more than our dear Tabletop.