-
Notifications
You must be signed in to change notification settings - Fork 122
/
index.html
377 lines (265 loc) · 17.2 KB
/
index.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
<!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>