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.

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)
• Mix in your favorite templating system (Handlebars, Underscore, etc.)
• Customize to your heart’s content with your own handlers and callbacks

Visit chriszarate.github.io/sheetrock for examples and jsFiddles.

Using Bower?

bower install jquery-sheetrock

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

<script src="jquery.min.js"></script>
<script src="jquery.sheetrock.min.js"></script>

Shreetrock is also available on CloudFlare’s CDN:

<script src="//cdnjs.cloudflare.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/jquery-sheetrock/0.2.4/dist/jquery.sheetrock.min.js"></script>

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

$('#table').sheetrock({
  url: '[...]',
  sql: '[...]'
});

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

$.fn.sheetrock.options.url = '[...]';

• Default ''
• Expects string

The URL of a public Google spreadsheet. This is the only required option. (See How do I make a spreadsheet public?)

• Default ''
• Expects string

A Google Visualization API query string. By default, Google only allows column letters (e.g., A, B) in queries. If you prefer, you can use the column labels of your spreadsheet in your query and they will be swapped out with the corresponding column letters. To do this, wrap column labels in percent signs, e.g., select %name%,%age% where %age% > 21.

• Default 0
• Expects non-negative integer

Use this option to enable lazy-loading or chunking of the data. When set to 0, Sheetrock will fetch all available rows. When set to 10, for example, 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.

• Default {}
• Expects hash map of column letters to strings

If you use column labels instead of column letters in your sql query (e.g., select %name%), they must exactly match the ones used in your spreadsheet. If you don’t want to bother with that, you can supply your own column labels, e.g., {A: 'ID', B: 'FullName', C: 'Age'}. Then you can use your supplied column labels in your sql query (e.g., select %FullName%). This also avoids the overhead (additional AJAX request) of prefetching the column labels. Note: This option only applies to your sql query and has no effect on the column labels returned by Google’s API (see labels, below).

• Default []
• Expects array of strings

Override the returned column labels with an array of strings. If you use your own row handler or template, you must reference column labels exactly as they are returned by Google’s API. Further, 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.

• Default _toHTML (internal function; provides HTML table row output)
• Expects function

Providing your own row handler is the recommended way to override the default table row formatting. Your function should accept a row object. A row object has two properties: num, which contains a row number (header = 0, first data row = 1, and so on); and cells, which is itself an object. The properties of cells will be named after the column labels of the returned data (e.g., Team, RBI). Your function should return content (a DOM/jQuery 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).

• Default _trim (internal function; trims white space)
• Expects function

The cell handler is used to process every cell value. It should return a string.

• Default _parseData (internal function; creates HTML table)
• Expects function

Providing your own data handler means you don’t want any data processing to take place except for basic validation and inspection. The returned data, if valid, is passed to your data handler (with the options hash as this) and it will be completely up to you to do something with it. The cell handler and row handler will not be called.

• Default $.noop
• Expects function

If you provide your own error handler, it will be called if Sheetrock encounters any AJAX errors (e.g., no network connection). The returned data, if any, is passed to your error handler (with the options hash as this).

• Default $.noop
• Expects function

You can provide a function to be called when all processing is complete. The options hash is passed to this function.

• Default $()
• Expects jQuery object or selector

If you have a loading indicator on your page, provide a jQuery object or selector here. It will be shown when the request starts and hidden when it ends.

• Default 0
• Expects non-negative integer

The number of header rows in your spreadsheet. Sheetrock tries to identify this automatically but Google’s response format makes it difficult.

• Default false
• Expects Boolean

Set to true to suppress output of header row(s) to the row handler.

• Default true
• Expects Boolean

Set to false to disable use of row group tags (<thead> and <tbody>).

• Default false
• Expects Boolean

Google passes along HTML formatting intended to replicate any formatting you applied in the spreadsheet. When set to true, the default cell handler will wrap the cell value in a span with a style attribute. The formatting is usually a bit wacky, so take care when enabling this option.

• Default false
• 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.

• Default false
• Expects Boolean

Output raw request and response data to the browser console. Useful for debugging, especially when you are using your own handlers.

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

• If you need to change the Google API endpoint—maybe because you want to use a caching proxy like Amazon CloudFront—use the (undocumented) server option.

• Sheetrock provides a way to reuse manually cached data. It accepts a second parameter of response data to be used instead of making an API request (e.g., $('#table').sheetrock(options, cachedResponse);). Make sure you pass a JavaScript object and not a JSON string.

• Sheetrock sometimes outputs useful information to the browser console, including options validation problems and warnings and errors reported by Google’s API.

• When there is an outstanding AJAX request, $.fn.sheetrock.working will be set to true. This can be useful for infinite scroll bindings, for example.

• You can also latch onto the most recent jQuery promise via $.fn.sheetrock.promise. Make sure you return a another promise so that Sheetrock can continue to chain off of it.

• TAGS Viewer
• Equity
• Kuppi
• Tell me about your project on the Wiki!

See CHANGELOG.md.

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.