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 sheetrockvar 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
labelsoption. -
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
sqlin 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
chunkSizein 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
rowHandlerin 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 thelabelsoption. -
cellsArray: An array of values that matches the column order of your Sheet or yourqueryoption. Provided as an alternative to thecellsobject. -
labels: An array of column labels in the same order ascellsArraythat match the properties of thecellsobject.
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
userCallbackin 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
userproperty will contain the options you originally provided (useful for identifying which request the callback is for) and arequestproperty 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 therowTemplate, if one is provided). -
.html(string): A string of HTML representing the final presentational output of the request (which is also appended to thetargetor 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.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent