Sheetrock.js

Sheetrock is a JavaScript library for querying, retrieving, and displaying data from Google Sheets. In other words, use a Google spreadsheet as your database! Load entire worksheets or leverage SQL-like queries to sort, group, and filter data. All you need is the URL of a public Google Sheet.

Sheetrock can be used in the browser or on the server (Node.js). It has no dependencies—but if jQuery is available, it will register as a plugin.

Basic retrieval is a snap but you can also:

  • Query sheets using the SQL-like Google Visualization query language—grouping, sorting, filters, pivots, and more
  • Lazy-load large data sets (infinite scroll with ease)
  • Easily mix in your favorite templating system (Handlebars, Underscore, etc.)
  • Customize to your heart’s content with your own callbacks

Documentation and development

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

Usage

Grab the latest version of Sheetrock for your project. The examples on this page use jQuery, but Sheetrock doesn’t require it.

Sheetrock can also be used with Node.js; see the documentation for details.

Expectations

Sheetrock is designed to work with any Google Sheet, but makes some assumptions about the format and availability.

  • Public. In order for others to access the data in your Sheet with Sheetrock, the Sheet must be public. (How do I make a spreadsheet public?)
  • One header row. Sheetrock expects a single header row of column labels in the first row of the Sheet.
  • Plain text. Sheetrock doesn’t handle formatted text. Any formatting you’ve applied to your data—including hyperlinks—probably won’t show up.

Examples

These are real, live examples. View source! Extend and adapt them!

Specify a spreadsheet.

Sheetrock’s only required option is the URL of a public Google Spreadsheet. (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.

Make sure you include the #gid=X portion of the URL; it identifies the specific worksheet you want to use.

Hello, world!

The most basic use case of Sheetrock simply fetches the an entire worksheet and loads it into a <table>.


              

            // Load an entire worksheet.
            $('#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 fetchSize 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.

Note that the header row doesn’t show up here; Sheetrock only passes header rows to your template if the target element is a <table>.

Also note that the template does not support referencing cells by column letter—instead, use the column label from the header row (e.g., cells.Team). Sheetrock also provides the template with cellsArray, an array that matches the column order of your Sheet or query option—so instead of cells.Team, we might also have used cellsArray.[0].

NL Home Run Leaders

Show a jsFiddle for this example.

Grouping

Next, let’s group some data. For a more straightforward template, we’ll specify the labels we want Sheetrock to use when it returns the data using the labels option.

Team RBI

Show a jsFiddle for this example.

DataTables

It's also easy to use Sheetrock with other libraries like DataTables.

In this example, we load the entire sheet and then use DataTables to create a sortable, paginated table. We can't call DataTables right away because the data is fetched asynchronously. Instead, we wait for Sheetrock to emit a sheetrock:loaded event before calling .DataTables().

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.

Fork me on GitHub