index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width">
<!-- Bootstrap and Bootstrap.js (for ScrollSpy) -->
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/css/bootstrap.min.css" rel="stylesheet" type="text/css">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.4/js/bootstrap.min.js" defer></script>
<!-- Prism.js -->
<script src="https://unpkg.com/prismjs@0.0.1/prism.js" defer></script>
<link href="https://unpkg.com/prismjs@0.0.1/dist/prism-default/prism-default.css" rel="stylesheet" type="text/css">
<link href="https://unpkg.com/sheetrock@1.2.0/css/sheetrock.css" rel="stylesheet" type="text/css">
<title>Sheetrock</title>
</head>
<body data-spy="scroll" data-target="#toc">
<div class="container">
<div class="row">
<div class="col-md-3 hidden-xs hidden-sm">
<nav id="toc" class="affix">
<ul class="nav">
<li><a href="#about">About</a></li>
<li><a href="#documentation">Documentation</a></li>
<li><a href="#usage">Usage</a></li>
<li class="nested"><a href="#expectations">Expectations</a></li>
<li><a href="#examples">Examples</a></li>
<li class="nested"><a href="#hello-world">Hello, world!</a></li>
<li class="nested"><a href="#basic">Basic example</a></li>
<li class="nested"><a href="#templating">Templating</a></li>
<li class="nested"><a href="#grouping">Grouping</a></li>
<li class="nested"><a href="#datatables">DataTables</a></li>
<li><a href="#credits">Credits</a></li>
</ul>
</nav>
</div>
<div class="col-md-9">
<h1 id="about">Sheetrock.js</h1>
<noscript><p class="error">This page requires JavaScript.</p></noscript>
<p><strong>Sheetrock</strong> 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.</p>
<p>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.</p>
<p>Basic retrieval is a snap but you can also:</p>
<ul>
<li>Query sheets using the SQL-like <a href="https://developers.google.com/chart/interactive/docs/querylanguage">Google Visualization query language</a>—grouping, sorting, filters, pivots, and more</li>
<li>Lazy-load large data sets (infinite scroll with ease)</li>
<li>Easily mix in your favorite templating system (<a href="http://handlebarsjs.com">Handlebars</a>, <a href="http://underscorejs.org">Underscore</a>, etc.)</li>
<li>Customize to your heart’s content with your own callbacks</li>
</ul>
<h2 id="documentation">Documentation and development</h2>
<p>Please see the <a href="https://github.com/chriszarate/sheetrock">GitHub repository</a> for detailed documentation of the available options or to contribute, explore, or raise an issue.</p>
<h2 id="usage">Usage</h2>
<p>Grab the <a href="https://chriszarate.github.io/sheetrock/dist/sheetrock.min.js">latest version of Sheetrock</a> for your project. The examples on this page use jQuery, but Sheetrock doesn’t require it.</p>
<div class="example-render" data-language="markup">
<!-- Load jQuery and Sheetrock from Unpkg -->
<script src="https://unpkg.com/jquery@3.6.0/dist/jquery.slim.min.js"></script>
<script src="https://unpkg.com/sheetrock@1.2.0/dist/sheetrock.min.js"></script>
</div>
<p>Sheetrock can also be used with Node.js; <a href="https://github.com/chriszarate/sheetrock">see the documentation</a> for details.</p>
<h3 id="expectations">Expectations</h3>
<p>Sheetrock is designed to work with any Google Sheet, but makes some assumptions about the format and availability.</p>
<ul>
<li><strong>Public.</strong> In order for others to access the data in your Sheet with Sheetrock, the Sheet must be public. (<a href="https://support.google.com/drive/answer/2494822">How do I make a spreadsheet public?</a>)</li>
<li><strong>One header row.</strong> Sheetrock expects a single header row of column labels in the first row of the Sheet.</li>
<li><strong>Plain text.</strong> Sheetrock doesn’t handle formatted text. Any formatting you’ve applied to your data—including hyperlinks—probably won’t show up.</li>
</ul>
<h2 id="examples">Examples</h2>
<p>These are real, live examples. View source! Extend and adapt them!</p>
<h3>Specify a spreadsheet.</h3>
<p>Sheetrock’s only required option is the URL of a <a href="https://support.google.com/drive/answer/2494822">public Google Spreadsheet</a>. (Click “Share” and set visibility to “Anyone with the link” or “Public on the web.”) As an example, let’s use <a href="https://docs.google.com/spreadsheet/ccc?key=0AlRp2ieP7izLdGFNOERTZW0xLVpROFc3X3FJQ2tSb2c#gid=0">this spreadsheet</a>, which contains 1986 National League batting statistics.</p>
<p>Make sure you include the <code class="inline">#gid=X</code> portion of the URL; it identifies the specific worksheet you want to use.</p>
<script class="example-render" data-language="javascript">
// Define spreadsheet URL.
var mySpreadsheet = 'https://docs.google.com/spreadsheets/d/1qT1LyvoAcb0HTsi2rHBltBVpUBumAUzT__rhMvrz5Rk/edit#gid=0';
</script>
<h3 id="hello-world">Hello, world!</h3>
<p>The most basic use case of Sheetrock simply fetches the an entire worksheet and loads it into a <code class="inline"><table></code>.</p>
<!-- We’re not actually running this code, so it’s mocked up. -->
<div class="operator-plus">
<pre class="language-markup"><code class="mockup">
<table id="statistics" class="table table-condensed table-striped"></table>
</code></pre>
</div>
<pre class="language-javascript"><code class="mockup">
// Load an entire worksheet.
$('#statistics').sheetrock({
url: mySpreadsheet
});
</code></pre>
<p class="fiddle">
<a href="https://jsfiddle.net/p1kxjwgq/">Show a jsFiddle for this example.</a>
</p>
<p>However, that’s not very exciting, so we won’t show the result inline here—especially since the spreadsheet has a couple hundred rows.</p>
<h3 id="basic">Basic example</h3>
<p>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 <code class="inline">fetchSize</code> to load just a portion of the data. We can always grab more data later—Sheetrock <strong>keeps track of how many rows you’ve requested</strong> and the next request will pick up where you left off.</p>
<p>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.</p>
<div class="example-render-group">
<div class="example-render output" data-language="markup" data-example-class="operator-plus">
<table id="switch-hitters" class="table table-condensed table-striped"></table>
</div>
<script class="example-render" data-language="javascript" data-example-class="operator-equals">
// Load top ten switch hitters.
$('#switch-hitters').sheetrock({
url: mySpreadsheet,
query: "select A,B,C,D,E,L where E = 'Both' order by L desc",
fetchSize: 10
});
</script>
</div>
<p class="fiddle">
<a href="https://jsfiddle.net/7krs6q0q/">Show a jsFiddle for this example.</a>
</p>
<h3 id="templating">Templating</h3>
<p>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.</p>
<p>Note that the header row doesn’t show up here; Sheetrock only passes header rows to your template if the target element is a <code class="inline"><table></code>.</p>
<p>Also note that the template does not support referencing cells by column letter—instead, use the column label from the header row (e.g., <code class="inline">cells.Team</code>). Sheetrock also provides the template with <code class="inline">cellsArray</code>, an array that matches the column order of your Sheet or <code class="inline">query</code> option—so instead of <code class="inline">cells.Team</code>, we might also have used <code class="inline">cellsArray.[0]</code>.</p>
<div class="example-render-group">
<div class="example-render" data-language="markup" data-example-class="operator-plus">
<!-- Load Handlebars.js from Unpkg. -->
<script src="https://unpkg.com/handlebars@4.5.0/dist/handlebars.min.js"></script>
</div>
<div class="example-render output" data-language="markup" data-example-class="operator-plus">
<h4>NL Home Run Leaders</h4>
<ol id="hr">
<script id="hr-template" type="text/x-handlebars-template">
<li>
<strong>{{cells.First}} {{cells.Last}}</strong>,
{{cells.Team}}, {{cells.HR}}
</li>
</script>
</ol>
</div>
<script class="example-render" data-language="javascript" data-example-class="operator-equals">
// Compile the Handlebars template for HR leaders.
var HRTemplate = Handlebars.compile($('#hr-template').html());
// Load top five HR leaders.
$('#hr').sheetrock({
url: mySpreadsheet,
query: "select A,C,D,I order by I desc",
fetchSize: 5,
rowTemplate: HRTemplate
});
</script>
</div>
<p class="fiddle">
<a href="https://jsfiddle.net/09sknz54/">Show a jsFiddle for this example.</a>
</p>
<h3 id="grouping">Grouping</h3>
<p>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 <code class="inline">labels</code> option.</p>
<div class="example-render-group">
<div class="example-render output" data-language="markup" data-example-class="operator-plus">
<h4>Team RBI</h4>
<ol id="team-rbi">
<script id="team-rbi-template" type="text/x-handlebars-template">
<li><strong>{{cells.TeamName}}</strong>, {{cells.TeamRBI}}</li>
</script>
</ol>
</div>
<script class="example-render" data-language="javascript" data-example-class="operator-equals">
// Compile Handlebars template for team RBI leaders.
var RBITemplate = Handlebars.compile($('#team-rbi-template').html());
// Load top five team RBI leaders.
$('#team-rbi').sheetrock({
url: mySpreadsheet,
query: "select A,sum(J) group by A order by sum(J) desc",
fetchSize: 5,
labels: ['TeamName', 'TeamRBI'],
rowTemplate: RBITemplate
});
</script>
</div>
<p class="fiddle">
<a href="https://jsfiddle.net/Lq9eLyko/">Show a jsFiddle for this example.</a>
</p>
<h2 id="datatables">DataTables</h2>
<p>It's also easy to use Sheetrock with other libraries like <a href="https://datatables.net/" target="_blank">DataTables</a>.</p>
<div class="example-render" data-language="markup">
<!-- Load DataTables script and stylesheet from Unpkg -->
<script src="https://unpkg.com/datatables@1.10.18/media/js/jquery.dataTables.min.js"></script>
<link href="https://unpkg.com/datatables@1.10.18/media/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css">
</div>
<p>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 <code class="inline">sheetrock:loaded</code> event before calling <code class=:inline">.DataTables()</code>.
<div class="example-render-group">
<div class="example-render output" data-language="markup" data-example-class="operator-plus">
<table id="raw-table" class="table table-condensed table-striped"></table>
</div>
<script class="example-render" data-language="javascript" data-example-class="operator-equals">
// Load all hitters and format with DataTables.
$('#raw-table').sheetrock({
url: mySpreadsheet,
query: "select A,B,C,D,E,L order by L desc",
}).on('sheetrock:loaded', function () {
$(this).DataTable();
});
</script>
</div>
<p class="fiddle">
<a href="https://jsfiddle.net/chriszarate/hr2j4wxp/">Show a jsFiddle for this example.</a>
</p>
<h2>More?</h2>
<p>Detailed documentation is available at the <a href="https://github.com/chriszarate/sheetrock">GitHub repository</a>. Please feel free to <a href="https://github.com/chriszarate/sheetrock/issues">raise an issue</a> if you are having a problem with Sheetrock.</p>
<h2 id="credits">Credits and license</h2>
<p>Sheetrock was written by <a href="http://chris.zarate.org">Chris Zarate</a> and is released under the <a href="http://opensource.org/licenses/MIT">MIT license</a>.</p>
</div>
</div>
</div>
<!--
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 is generated from the actual code that runs on this page.
-->
<!-- Local helper code -->
<script>
/*
* WE'LL DO IT LIVE!!!
* Create source code examples using this page's actual content!
*/
// Mirror the "source" of a content block in its own <pre><code> block.
var addCodeBlock = function () {
// Get attributes to be copied to container elements.
var language = $(this).data('language');
var classes = $(this).data('example-class');
// Create container tags.
var codeTag = $('<code></code>').text(cleanMargins($(this).html()));
var preTag = $('<pre></pre>').addClass('language-' + language).append(codeTag);
var containerTag = $('<div></div>').addClass(classes).append(preTag);
// Append.
var $parent = $(this).parent('.example-render-group');
if ($parent.length) {
$parent.before(containerTag);
} else {
$(this).before(containerTag);
}
};
// Clean up the margins and leading/trailing newlines.
var cleanMargins = function (str) {
var lines = str.replace(/^\n+/, '').replace(/[\n\t ]+$/, '').split(/\n/);
var firstLineIndent = lines[0].match(/^[\t ]+/);
if (firstLineIndent) {
firstLineIndent = new RegExp(firstLineIndent[0], 'g');
lines = $.map(lines, function (line) {
return line.replace(firstLineIndent, '');
});
}
return lines.join('\n');
};
// Show a jsFiddle when clicked.
var showFiddle = function () {
var $this = $(this);
var attributes = ' width="100%" height="400" allowfullscreen="allowfullscreen" frameborder="0"';
$this.before($('<iframe src="' + $this.attr('href') + 'embedded/"' + attributes + '></iframe>'));
$this.remove();
return false;
};
// Trim margins of specified content blocks.
$('.mockup').each(function () {
$(this).text(cleanMargins($(this).html()));
});
// Mirror "source" for specified tags.
$('.example-render').each(addCodeBlock);
// Show jsFiddles inline.
$('.fiddle a').on('click', showFiddle);
</script>
<div class="github">
<a href="https://github.com/chriszarate/sheetrock"><img src="https://camo.githubusercontent.com/38ef81f8aca64bb9a64448d0d70f1308ef5341ab/68747470733a2f2f73332e616d617a6f6e6177732e636f6d2f6769746875622f726962626f6e732f666f726b6d655f72696768745f6461726b626c75655f3132313632312e706e67" alt="Fork me on GitHub" data-canonical-src="https://s3.amazonaws.com/github/ribbons/forkme_right_darkblue_121621.png"></a>
</div>
</body>
</html>