Sheetrock

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 (filters, pivots, sorting, grouping, 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

Browser

Grab the latest version of Sheetrock for your project. Here’s an example request (using jQuery):

<table id="my-table"></table>
<script src="jquery.min.js"></script>
<script src="sheetrock.min.js"></script>

$("#my-table").sheetrock({
  url: "https://docs.google.com/spreadsheets/d/1qT1LyvoAcb0HTsi2rHBltBVpUBumAUzT__rhMvrz5Rk/edit#gid=0",
  query: "select A,B,C,D,E,L where E = 'Both' order by L desc"
});

For many more examples and accompanying jsFiddles, visit chriszarate.github.io/sheetrock.

Server

Sheetrock can also be used with Node.js:

npm install sheetrock

var sheetrock = require('sheetrock');

var myCallback = function (error, options, response) {
  if (!error) {
    /*
      Parse response.data, loop through response.rows, or do something with
      response.html.
    */
  }
};

sheetrock({
  url: "https://docs.google.com/spreadsheets/d/1qT1LyvoAcb0HTsi2rHBltBVpUBumAUzT__rhMvrz5Rk/edit#gid=0",
  query: "select A,B,C,D,E,L where E = 'Both' order by L desc",
  callback: myCallback
});

Version 1.0

In version 1.0, Sheetrock has introduced a few backwards-incompatible changes, although most basic requests will still work. These changes make it simpler to use; read the options below or the CHANGELOG for more details.

The previous 0.3.x branch is still available and maintained.

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?) It is possible to use Sheetrock to access a private Sheet for your own use if you are logged in to your Google account in the same browser session, but this is not a supported use case.

• One header row. Sheetrock expects a single header row of column labels in the first row of the Sheet. Any other configuration (e.g., no header row, multiple or offset header rows) can cause problems with the request and complicates templating. The header row values are used as keys in the cell object unless you override them using the labels option.

• Plain text. Sheetrock doesn’t handle formatted text. Any formatting you’ve applied to your data—including hyperlinks—probably won’t show up.

Options

Sheetrock expects a hash map of options as a parameter, e.g.:

sheetrock({/* options */});

Your options override Sheetrock’s defaults on a per-request basis. You can also globally override defaults like this:

sheetrock.defaults.url = "https://docs.google.com/spreadsheets/d/1qT1LyvoAcb0HTsi2rHBltBVpUBumAUzT__rhMvrz5Rk/edit#gid=0";

url

• Expects string

The URL of a public Google Sheet. (How do I make a spreadsheet public?) Make sure you include the #gid=X portion of the URL; it identifies the specific worksheet you want to use. If you want to access data from multiple worksheets, you will need to make multiple Sheetrock requests.

query

• Expects string
• Renamed from sql in 1.0.0

A Google Visualization API query string. Use column letters in your queries (e.g., select A,B,D).

target

• Expects DOM element

A DOM element that Sheetrock should append HTML output to. In a browser, for example, you can use document.getElementById to reference a single element. If you are using Sheetrock with jQuery, you can use the jQuery plugin syntax (e.g., $('#my-table').sheetrock({/* options */})) and ignore this option.

When data has been loaded and markup appended to the target element, a custom sheetrock:loaded event will be triggered on the element. You can listen for this event if you'd like to perform an action after the DOM is updated:

$('#my-table')
  .sheetrock({/* options */})
  .on('sheetrock:loaded', function () {
    /* do something */
  });

fetchSize

• Expects non-negative integer
• Renamed from chunkSize in 1.0.0

Use this option to load a portion of the available rows. When set to 0 (the default), Sheetrock will fetch all available rows. When set to 10, it will fetch ten rows and keep track of how many rows have been requested. On the next request with the same query, it will pick up where it left off.

labels

• Expects array of strings

Override the returned column labels with an array of strings. Without this option, if you use your own rowTemplate, you must reference column labels exactly as they are returned by Google’s API. If your sql query uses group, pivot, or any of the [manipulation functions][manip], you will notice that Google’s returned column labels can be hard to predict. In those cases, this option can prove essential. The length of this array must match the number of columns in the returned data.

rowTemplate

• Expects function
• Renamed from rowHandler in 1.0.0

By default, Sheetrock will output your data in simple HTML. Providing your own row template is an easy way to customize the formatting. Your function should accept a row object. A row object has four properties:

• num: The row number (header = 0, first data row = 1, and so on).

• cells: An object with properties named after the column labels from your header row or the labels option.

• cellsArray: An array of values that matches the column order of your Sheet or your query option. Provided as an alternative to the cells object.

• labels: An array of column labels in the same order as cellsArray that match the properties of the cells object.

Your function should return a DOM object or an HTML string that is ready to be appended to your target element. A very easy way to do this is to provide a compiled Handlebars or Underscore template (which is itself a function).

callback

• Expects function
• Renamed from userCallback in 1.0.0

You can provide a function to be called when all processing is complete. The function will be passed the following parameters, in this order:

• Error (object): If the request failed, this parameter will be a JavaScript error; otherwise, it will be null. Always test for an error before using the other parameters.

• Options (object): An object representing the options of the request. The user property will contain the options you originally provided (useful for identifying which request the callback is for) and a request property with information about the HTTP request to Google’s API.

• Response (object): An object containing response data properties:

  • .attributes (object): An object containing useful information about the response data, its structure, and its format.

  • .raw (object): This is the raw response data from Google’s API.

  • .rows (array): An array of row objects (which are also passed individually to the rowTemplate, if one is provided).

  • .html (string): A string of HTML representing the final presentational output of the request (which is also appended to the target or jQuery reference, if one was provided).

reset

• Expects Boolean

Reset request status. By default, Sheetrock remembers the row offset of a request, whether a request has been completely loaded already, or if it previously failed. Set to true to reset these indicators. This is useful if you want to reload data or load it in another context.

Caching

On large spreadsheets (~5,000 rows), the performance of Google’s API when using the query option can be sluggish and, in some cases, can severely affect the responsiveness of your application. At this point, consider caching the responses for reuse via a callback function.

Tips and troubleshooting

The best first step to troubleshooting problems with Sheetrock is to use a callback function to inspect any errors and response data. Here’s a simple example that logs all returned data to the console:

sheetrock({
  /* options */
  callback: function (error, options, response) {
    console.log(error, options, response);
  }
});

Projects using Sheetrock

Tell me about your project on the Wiki!

Change log

See CHANGELOG.md.

Credits and license

Sheetrock was written by Chris Zarate. It was inspired in part by Tabletop.js (which will teach you jazz piano). John Brecht came up with the name. Sheetrock is released under the MIT license.