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 templating systems and anything else that is hip and cool. It will also help you make new friends and play jazz piano.

But wait, don't use Tabletop!!!!

Tabletop was created in about 1995, back when we had to remember to disable call waiting when dialing into Compuserve. Now that it's 2020, Google is shutting down the infrastructure that Tabletop relies on.

Big surprise, right? But luckily, it's pretty easy to switch to a more modern way of doing things!

Instead of Tabletop, we're going to use Papa Parse. You still need to share your spreadsheet and all of that (see below), but the changes are actually pretty tiny! I've added a file where you can see a full example of doing it, but the quick version is:

Old Tabletop style

function init() {
  Tabletop.init( { key: 'https://docs.google.com/spreadsheets/d/0AmYzu_s7QHsmdDNZUzRlYldnWTZCLXdrMXlYQzVxSFE/pubhtml',
                    callback: function(data, tabletop) { 
                      console.log(data)
                    },
                    simpleSheet: true } )
}
window.addEventListener('DOMContentLoaded', init)

Updated version

function init() {
          Papa.parse('https://docs.google.com/spreadsheets/d/e/2PACX-1vRB4E_6RnpLP1wWMjqcwsUvotNATB8Np3OntlXb7066ULcAHI9oqqRhucltFifPTYNd7DRNRE56oTdt/pub?output=csv', {
          download: true,
          header: true,
          complete: function(results) {
            var data = results.data
            console.log(data)
          }
        })
window.addEventListener('DOMContentLoaded', init)

One important note is this won't work with Publish to Web if you have an organizational account. Like if I use my @columbia.edu account Google pretends I'm a terrible criminal and won't give me access via Papa Parse.

This also won't work if you're opening up an html file on your computer like a Normal Person, it requires you to either put it on the internet or run a local server. There are worse things, I guess.

But hey, as of September 2020 it's this way or the highway!

Google broke some MORE stuff, so you just plan might not be able to do this any more. If it's a PRIVATE project, you can get an API key and jump through a few hoops. But beware that this exposes your API key to anyone visiting the site, which allows people to do whatever they want with your data and run up bills on your end, so you probably don't want this on something facing the world.

But let's pretend Tabletop isn't leaving

Like how easy?

Step One: make a Google Spreadsheet and "Publish to Web." You'll need to pick CSV to be forwards-compatible for when Google shuts down the old way of doing things.

Step Two: Write a page that invokes Tabletop with the published URL Google gives you.

function init() {
  Tabletop.init( { key: 'https://docs.google.com/spreadsheets/d/0AmYzu_s7QHsmdDNZUzRlYldnWTZCLXdrMXlYQzVxSFE/pubhtml',
                    callback: function(data, tabletop) { 
                      console.log(data)
                    },
                    simpleSheet: true } )
}
window.addEventListener('DOMContentLoaded', init)

Step Two, modern-er version: We've moved to the future (aka like a decade ago) by supporting promises.

function init() {
  Tabletop.init( {
    key: 'https://docs.google.com/spreadsheets/d/0AmYzu_s7QHsmdDNZUzRlYldnWTZCLXdrMXlYQzVxSFE/pubhtml',
    simpleSheet: true }
  ).then(function(data, tabletop) { 
    console.log(data)
  })
}
window.addEventListener('DOMContentLoaded', init)

Step Three: Enjoy your data!

[ { name: "Carrot", category: "Vegetable", healthiness: "Adequate" }, 
  { name: "Pork Shoulder", category: "Meat", healthiness: "Questionable" }, 
  { name: "Bubblegum", category: "Candy", healthiness: "Super High"} ]

Yes, it's that easy.

NOTE: If your share URL has a /d/e in it, try refreshing the page to see if it goes away. If it doesn't, try this.

Getting Started

1) Publishing your Google Sheet

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

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

Name            Category   Healthiness
Carrot          Vegetable  Adequate
Pork Shoulder   Meat       Questionable
Bubblegum       Candy      Super High

Now go up to the File menu and pick Publish to the web. Fiddle with the options, then click Start publishing. A URL will appear, something like https://docs.google.com/spreadsheets/d/e/2PACX-1vQ2qq5UByYNkhsujdrWlDXtpSUhh7ovl0Ak6pyY3sWZqEaWS2lJ0iuqcag8iDLsoTuZ4XTiaEBtbbi0/pubhtml .

IGNORE THIS URL! You used to be able to use it, you can't anymore (you still need to do this step, though).

Now that you've published your sheet, you now need to share it, too.

1. Click the Share link in the upper right-hand corner
2. Click the very pale Advanced button
3. Change... access to "On - Anyone with a link"
4. Make sure Access: Anyone says Can view, since you don't want strangers editing your data
5. Click Save

Copy the Link to Share. Your URL should look something like https://docs.google.com/spreadsheets/d/1Io6W5XitNvifEXER9ECTsbHhAjXsQLq6VEz7kSPDPiQ/edit?usp=sharing. It should not have a /d/e in it.

2) Setting up Tabletop

Now you're going to feed your spreadsheet into Tabletop

Include the Tabletop JavaScript file in your HTML, then try the following, substituting your URL for publicSpreadsheetUrl

<script src='https://cdnjs.cloudflare.com/ajax/libs/tabletop.js/1.5.1/tabletop.min.js'></script>
<script type='text/javascript'>    
  var publicSpreadsheetUrl = 'https://docs.google.com/spreadsheets/d/1sbyMINQHPsJctjAtMW0lCfLrcpMqoGMOJj6AN-sNQrc/pubhtml';

  function init() {
    Tabletop.init( { key: publicSpreadsheetUrl,
                     callback: showInfo,
                     simpleSheet: true } )
  }

  function showInfo(data, tabletop) {
    alert('Successfully processed!')
    console.log(data);
  }

  window.addEventListener('DOMContentLoaded', init)
</script>

After Tabletop reads your Sheet, it hops to the showInfo function with your data. Open up your console and check out the data it retrieved. All of those rows were turned right into objects! See how easy that was?

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.

A note on node

To use this in a node environment:

npm install tabletop -save

Copy and paste this in your index.js file:

 const Tabletop = require('tabletop');

 var publicSpreadsheetUrl = 'URL OF SPREADSHEET AS YOU FIND IN THE BROWSER ADDRESS BAR';

function init() {
    Tabletop.init( { key: publicSpreadsheetUrl,
                     callback: showInfo,
                     simpleSheet: false } )
  }

 function showInfo(data, tabletop) {
  // do something with the data
  console.log(JSON.stringify(data, null, 2));
}

//initialise and kickstart the whole thing.
init()

Reference

Tabletop initialization

The simplest Tabletop initialization works like this:

var tabletop = Tabletop.init({ 
  key: '1sbyMINQHPsJctjAtMW0lCfLrcpMqoGMOJj6AN-sNQrc', 
  callback: showInfo 
})

You pass in either key as the actual spreadsheet key, or just the full published-spreadsheet URL.

showInfo is a function elsewhere in your code that gets called with your data.

Depending on how recently you've published your spreadsheet, your key comes from different places. Either the spreadsheet's URL in the address bar, the Publish URL, or the Share URL. Read this

Tabletop initialization options

key

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

callback

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

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

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.

error

error is the callback for when something goes wrong. I'm uncertain how well it works in the browser in all situations, but in our Modern World I'm pretty sure it can be relied on.

orderby

orderby asks Google to sort the results by a column. You'll need to strip spaces a