Blog Posted March 2, 2013

Using Google Spreadsheets and Tabletop.js as a Web Application Back-end

At NICAR 2013, Tasneem Raja spoke on Smarter interactive Web projects with Google Spreadsheets and Tabletop.js. Tasneem is Mother Jones‘s Interactive Editor; she outlined how Mother Jones uses Google Spreadsheets to power some of its interactive features.

Beyond serving as a simple, easy-to-maintain datastore and CMS, Google Spreadsheets — used in concert with Tabletop.js — allows for the creation of dynamic web content in absence of server-side processing, in effect empowering a highly scalable and simple architecture.

The following offers a basic example.

1. Sign into Google Drive with your Google credentials and create a new Spreadsheet titled tabletop_example with the following content:

tabletop_spreadsheet

2. Click File > Publish to the Web > Start Publishing to publicly publish your spreadsheet. 3. Create a project directory with the following files and structure:

1
2
3
4
5
├── index.html
└── js
    ├── app.js
    └── vendor
        └── tabletop.js

Grab a copy of Tabletop.js to store in js/vendor/tabletop.js.

Set up the homepage by adding the following to index.html:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<!DOCTYPE html>
<html>
  <head>
    <title>Some Site Title</title>
  </head>
  <body>
    <header>
      <h1><a href="/">Some Site Title</a></h1>
    </header>
    <ul id="politicians"></ul>
    <script src="http://code.jquery.com/jquery-1.9.1.min.js"></script>
    <script src="js/vendor/tabletop.js"></script>
    <script src="js/app.js"></script>
  </body>
</html>

Retrieve the document key associated with your tabletop_example spreadsheet. This can be found in the document’s URL: docs.google.com/spreadsheet/ccc?key=YOUR_DOCUMENT_KEY_APPEARS_HERE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
$(document).ready(function () {
  Tabletop.init({
    key: 'YOUR_DOCUMENT_KEY_GOES_HERE',
    callback: function(data, tabletop) {
      var i,
          dataLength = data.length;

      for (i=0; i&<dataLength; i++) {
        $('#politicians').append(
          $('<li>', {
            text: data[i].politician + ', ' + data[i].position
          })
        );
      }
    },
    simpleSheet: true
  });
});

Open index.html in your web browser.

While the above code outlines a fairly simple example, tools like Backbone.js provide the opportunity to layer in functionality such as URL routing, filtering, and sorting. And again, because Tabletop.js requires no application server, this solution requires no technology beyond static HTML, CSS, and JavaScript. As such, it’s highly scalable when deployed to a web server like nginx or Apache.