Sheetrock

Sheetrock is a jQuery plugin for querying, retrieving, and displaying data from Google Spreadsheets. Use it to load entire sheets or leverage SQL-like queries to treat Google Spreadsheets as a quick-and-dirty JSON datastore. All you need is the URL of a public Google Spreadsheet. It requires no backend code whatsoever—everything is done in the browser.

Now works with the new version of Google Sheets!

Basic retrieval is a snap but you can also:

Documentation and development

Please see the GitHub repository for detailed documentation of the available options or to contribute, explore, or raise an issue.

Usage

Using Bower?

bower install jquery-sheetrock

Otherwise, just grab the latest version (unminified) and stash it in your project. Sheetrock requires jQuery >= 1.6.

Examples

These are real, live examples. View source! Extend and adapt them! Table styling on this page is provided by Bootstrap.

Specify a spreadsheet.

Sheetrock’s only required option is the URL of a public Google Spreadsheet. (Short version: Click “Share” and set visibility to “Anyone with the link” or “Public on the web.”) As an example, let’s use this spreadsheet, which contains 1986 National League batting statistics.

Hello, world!

The most basic use case of Sheetrock simply grabs the spreadsheet and makes a table out of it.


      

      // Load an entire sheet.
      $('#statistics').sheetrock({
        url: mySpreadsheet
      });
    

Show a jsFiddle for this example.

However, that’s not very exciting, so we won’t show the result inline here—especially since the spreadsheet has a couple hundred rows.

Basic example

Let’s move on to something slightly more interesting. Using Sheetrock, we can limit our scope and analyze the data using SQL-like queries. We can also provide a chunkSize to load just a portion of the data. We can always grab more data later—Sheetrock keeps track of how many rows you’ve requested and the next request will pick up where you left off.

Let’s look at switch hitters and rank them by batting average. We’ll only grab the columns we care about and fetch just the top ten to help focus the reader’s attention.

Show a jsFiddle for this example.

Templating

Tables are nice, but we might want to represent the data in different ways. Let’s generate an ordered list by passing in a Handlebars template. This time we’ll rank the top five players by home runs.

NL Home Run Leaders

Show a jsFiddle for this example.

Grouping

Next, let’s group some data. For a more readable query, we’ll use column labels (%Team% and %RBI%) instead of column letters, and we’ll specify the labels we want Sheetrock to use when it returns the data (labels: ['TeamName', 'TeamRBI']).

Team RBI

Show a jsFiddle for this example.

More?

Detailed documentation is available at the GitHub repository. Please feel free to raise an issue if you are having a problem with Sheetrock.

Credits and license

Sheetrock was written by Chris Zarate and is released under the MIT license.

A peek under the hood

This has nothing to do with Sheetrock, but if you’re interested, here’s how the examples on this page were generated. There’s no mockup code—everything including the code below is generated from the actual code that runs on this page.

Fork me on GitHub