-
Notifications
You must be signed in to change notification settings - Fork 0
/
BigQuerySPDemoApp.html
462 lines (387 loc) · 18.4 KB
/
BigQuerySPDemoApp.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
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
<!--
Article
http://bitvectors.blogspot.com/2015/11/page1.html
uses this file . . .
-->
<HTML>
<HEAD>
<SCRIPT src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></SCRIPT>
<SCRIPT src="//code.jquery.com/ui/1.10.3/jquery-ui.js"></SCRIPT>
<!--
CSS style to right-align the quantiles dropdown . . .
-->
<STYLE TYPE="TEXT/CSS">
SELECT {
TEXT-ALIGN:RIGHT;
}
</STYLE>
<SCRIPT TYPE="text/javascript">
// Multiple Javascript functions will use
// this array, so make it global . . .
var DDLArray = [
"#DDLZIP",
"#DDLEMP",
"#DDLQP1",
"#DDLAP",
"#DDLEST",
"#DDLQUERY",
"#DDLQUANTILES"
];
function callBack(resultSetParam) {
// First, clean out all the result set rows from the
// previous run. These start after the TR row with
// ID = 'lastRow' . . .
$('#lastRow').nextAll().remove();
// The first element of array resultSetParam is itself a two-element
// array. For a quantiles function result set, resultSetParam[0] has
// this structure
//
// resultSetParam[0][0] = 'QUANTILE NUMBER'
// resultSetParam[0][1] = 'QUANTILE VALUE'
//
// and starting at element 2, resultSetParam has one-element arrays
// that will then switch between the QUANTILE NUMBER column on the
// left
//
// resultSetParam[i]
//
// and the QUANTILE VALUE column on the right
//
// resultSetParam[i + 1]
if (resultSetParam.length == 1) {
// For a single-value result set, bold / underline the function name . . .
$('#mainTable tr:last').after('<tr><td></td><td></td><<td COLSPAN="2" valign="top"><b>' + resultSetParam[0][0] + ':</b></td><td COLSPAN="2" valign="top">' + resultSetParam[0][1] + '</td></tr>');
}
else if (resultSetParam.length > 1) {
// A quantiles function result set will have the column headings bolded / underlined . . .
$('#mainTable tr:last').after('<tr><td></td><td></td><<td COLSPAN="2" valign="top"><b><u>' + resultSetParam[0][0] + '</u></b></td><td COLSPAN="2" valign="top"><b><u>' + resultSetParam[0][1] + '</u></b></td></tr>');
// Append the result set values to mainTable . . .
for (var i = 1; i < resultSetParam.length; i++) {
$('#mainTable tr:last').after('<tr><td></td><td></td><td COLSPAN="2" valign="top">' + resultSetParam[i][0] + '</td><td COLSPAN="2" valign="top">' + resultSetParam[i][1] + '</td></tr>');
}
}
}
function submitData() {
// The paramArray array will become a parameter passed to the returnFormParams
// function in file
//
// Code.gs
//
// That function will show the query parameter(s) and result set on the
// associated spreadsheet.
//
// Except for element six, initialize the array with "ANY" values as a default
// because all dropdowns themselves initialize with a value of "ANY". This value
// in the BigQuery query string means the user did not make a pick in the corresponding
// dropdown. In paramArray, element six depends on the quantiles function of the
// DDLQUERY dropdown, so default this element to " " . . . .
// Element 6 has the DDLQUERY value. Remember that arrays have zero-based numbering ! ! !
var paramArray = [
"ANY",
"ANY",
"ANY",
"ANY",
"ANY",
"ANY",
" ",
"ANY"
];
var form = document.getElementById('BigQuerySPDemoApp');
var alertString = '';
// Map the values in DDLArray above to DOM elements on the form
// through jQuery.
//
// Starting with element five (the sixth element) in DDLArray (the
// queries dropdown), build a jQuery statement that returns the
// value picked in the queries dropdown itself. Then, use that
// value in the nearby switch statement. Here, case "4" maps to
// the BigQuery quantiles function. This function needs a quantiles
// value, and this value will come from the quantiles dropdown
// DDLQUANTILES.
//
// Array element
//
// paramArray[5]
//
// has the DDLQUERY text. Starting with this value, place its mapped
// text value in paramArray[6] and its mapped OPTION VALUE in
// paramArray[7] . . .
paramArray[5] = $(DDLArray[5] + " option:selected").text();
paramArray[7] = $(DDLArray[5] + " option:selected").val();
// Array element paramArray[6] holds the QUANTILES value. Since only the
// quantiles function in the DDLQUERY dropdown needs this value, default
// paramArray[6] to " " and have case "4" in the switch block set the
// value for this array element . . .
paramArray[6] = " ";
// Fill the DDLQUERY dropdown, used to pick the BigQuery function.
// In the column aliases, map the "formatting" values with Unicode
// equivalents
//
// $ <-> x24
// ( <-> x28
// ) <-> x29
// = <-> x3d
// ^ <-> x5e
//
// because character escapes won't work in BigQuery SELECT column
// aliasing . . .
// Start placing values in DDLArray[] . . .
if ($(DDLArray[5] + " option:selected").val() === '4') {
paramArray[6] = $(DDLArray[6] + " option:selected").text();
}
if ($(DDLArray[0] + " option:selected").val() != 'ANY') {
paramArray[0] = $(DDLArray[0] + " option:selected").text();
}
// Use the slice() method to copy paramArray values
// over to headerArray. A copy here works because
// the header array will match paramArray at this
// point . . .
var headerArray = paramArray.slice();
for (var i = 1; i < (DDLArray.length - 2); i++) {
// Now loop through the dropdowns DDLArray maps, after ZIP and
// before the DDLQuery and DDLQuantiles dropdowns. Ignore the
// last two values in DDLArray because they deal with the query
// and quantiles dropdowns. The switch / case block above handled
// these dropdowns. The zip code dropdown is DDLArray[0], so
// start this for-loop at 1. Grab the value, not the text,
// from each dropdown for paramArray. Place the DDL dropdown
// text value(s) in headerArray because these text values have
// " < " instead of " AND " . . .
if (($(DDLArray[i] + " option:selected").val()) != 'ANY') {
paramArray[i] = $(DDLArray[i] + " option:selected").val();
headerArray[i] = $(DDLArray[i] + " option:selected").text();
}
}
// This statement calls returnFormParams in Code.gs to make the calculations on the
// spreadsheet. The syntax might look a little strange, but when called, function
// returnFormParams returns the BigQuery calculation results (as returnParam within
// that function); this line then passes that parameter to function callBack, located
// in this HTML file.
//
// Function callBack writes the value(s) in this "invisible" parameter starting
// just after the HTML element at DOM ID location
//
// tr:last
//
// in the HTML file that this file draws . . .
google.script.run.withFailureHandler(alertString).withSuccessHandler(callBack).returnFormParams(paramArray, headerArray);
}
// This jQuery function runs as soon as the HTML page
// completely loads. It first hides the quantiles
// dropdown by default. Then, it fills the dropdown
// with 98 integers from 3 to 100 . . .
$(document).ready(function() {
// Directly hide the DDLQUANTILES dropdown
// and the label associated with it . . .
$("#DDLQUANTILES").hide();
$('LABEL[FOR="DDLQUANTILES"]').hide();
var ddlQuantilesString = '<SELECT NAME="DDLQUANTILES" CLASS="DDLQUANTILES" ID="DDLQUANTILES" SIZE="4" DIR="RTL">';
ddlQuantilesString += '<OPTION SELECTED="true" VALUE="2">2</OPTION>'
for (var i = 3; i < 101; i++){
ddlQuantilesString += '<OPTION VALUE="' + i + '">' + i + '</OPTION>';
}
ddlQuantilesString += '</SELECT>';
$('#DDLQUANTILES').html(ddlQuantilesString);
});
// This jQuery function ties the DDLQUANTILES quantiles dropdown and associated
// labels to the DDLQUERY query dropdown. DDLQUANTILES becomes visible when the
// user picks
//
// "QUANTILES EMPLOYEE COUNT" <=> 4
//
// and invisible for the other picks . . .
$(document).ready(function () {
// This file has array DDLArray globally visible and this function maps
// array DDLArray values to DOM elements on this form
//
// DDLArray[5] -> #DDLQUERY
//
// DDLArray[6] -> #DDLQUANTILES
//
// through array DDLArray . . .
$(DDLArray[5]).change(function () {
// First, build a jQuery statement that returns the value
// picked in the queries dropdown itself and use that
// value in the switch statement.
// Use fadeTo because the hide() and show() methods will
// physically remove / replace the DDLQuery dropdown and
// the label associated with it and the controls / cells / etc.
// below them will start moving around vertically. In fadeTo, the
// first parameter is speed. Zero -> instant; the second
// parameter is opacity. One -> 100% opacity (AKA fully
// non-transparent); zero -> 0% opacity (AKA clear) . . .
if (($(DDLArray[5] + " option:selected").val()) === "4") {
$(DDLArray[6]).fadeTo(0, 1);
$('LABEL[FOR="DDLQUANTILES"]').fadeTo(0, 1);
} else {
$(DDLArray[6]).fadeTo(0, 0);
$('LABEL[FOR="DDLQUANTILES"]').fadeTo(0, 0);
}
});
});
// This jQuery function resets all the dropdowns to their first / top
// values. To run this function, click the RESET button . . .
$(function() {
$("#RESET").click( function() {
// The actual dropdown resets happen here . . .
for (var i = 0; i < DDLArray.length; i++) {
$(DDLArray[i]).val( $(DDLArray[i] + " option:first-child").val() );
}
// When DDLQUERY resets, hide everything associated
// with DDLQUANTILES, because the selected pick in
// DDLQUERY will become
//
// STTDEV Q1 PAYROLL
//
// and this query function does not use a
// DDLQUANTILES value . . .
$('#DDLQUANTILES').hide();
$('LABEL[FOR="DDLQUANTILES"]').hide();
// Remember to clean out the
// result set cells . . .
$('#lastRow').nextAll().remove();
google.script.run.withFailureHandler().withSuccessHandler().clearSheet();
});
});
</SCRIPT>
</HEAD>
<BODY>
<FORM ID="BigQuerySPDemoApp">
<!--
Use
http://www.tablesgenerator.com/html_tables#
to build the HTML for the form . . .
-->
<STYLE type="text/css">
.tg {border-collapse:collapse;border-spacing:0;border:none;}
.tg TD{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:0px;overflow:hidden;word-break:normal;}
.tg TH{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:0px;overflow:hidden;word-break:normal;}
.tg .tg-DROPDOWNHEADING{font-weight:bold;font-size:18px;font-family:"Times New Roman", Times, serif !important; text-align:center;}
.tg .tg-TITLE{font-weight:bold;font-size:28px;font-family:"Times New Roman", Times, serif !important;text-align:center}
.tg .CENTER_DROPDOWN{text-align:center;}
</STYLE>
<TABLE CLASS="tg" STYLE="undefined;table-layout: fixed; WIDTH: 1031px" ID="mainTable">
<COLGROUP>
<COL STYLE="WIDTH: 131px">
<COL STYLE="WIDTH: 218px">
<COL STYLE="WIDTH: 217px">
<COL STYLE="WIDTH: 191px">
<COL STYLE="WIDTH: 131px">
<COL STYLE="WIDTH: 250px">
</COLGROUP>
<TR>
<TH CLASS="tg-TITLE" COLSPAN="5">CENSUS BUREAU<BR>COMPLETE ZIP CODE TOTALS FILE<BR>BIG QUERY APPLICATION</TH>
<TH ROWSPAN="3"></TH>
</TR>
<TR>
<TD CLASS="tg-DROPDOWNHEADING">First Zip<BR>Digit</TD>
<TD CLASS="tg-DROPDOWNHEADING">Employee<BR>Count</TD>
<TD CLASS="tg-DROPDOWNHEADING">Q1 Payroll<BR>(1 = $ 1K)</TD>
<TD CLASS="tg-DROPDOWNHEADING">Total Annual<BR>Payroll (1 = $ 1K)</TD>
<TD CLASS="tg-DROPDOWNHEADING">Total<BR>Establishment<BR>Count</TD>
</TR>
<TR>
<TD CLASS="CENTER_DROPDOWN">
<SELECT NAME="DDLZIP" ID="DDLZIP" SIZE="6">
<OPTION SELECTED="true" VALUE="ANY">ANY</OPTION>
<OPTION VALUE="0">0</OPTION>
<OPTION VALUE="1">1</OPTION>
<OPTION VALUE="2">2</OPTION>
<OPTION VALUE="3">3</OPTION>
<OPTION VALUE="4">4</OPTION>
<OPTION VALUE="5">5</OPTION>
<OPTION VALUE="6">6</OPTION>
<OPTION VALUE="7">7</OPTION>
<OPTION VALUE="8">8</OPTION>
<OPTION VALUE="9">9</OPTION>
</SELECT>
</TD>
<TD CLASS="CENTER_DROPDOWN">
<SELECT NAME="DDLEMP" TYPE="SELECT" ID="DDLEMP" SIZE="6">
<OPTION SELECTED="true" VALUE="ANY">ANY</OPTION>
<OPTION VALUE="0 AND 10">0 < 10</OPTION>
<OPTION VALUE="10 AND 100">10 < 100</OPTION>
<OPTION VALUE="100 AND 1000">100 < 1 K</OPTION>
<OPTION VALUE="1000 AND 10000">1 K < 10 K</OPTION>
<OPTION VALUE="10000 AND 100000">10 K < 100 K</OPTION>
<OPTION VALUE="100000 AND 1000000">100 K < 1 M</OPTION>
</SELECT>
</TD>
<TD CLASS="CENTER_DROPDOWN">
<SELECT NAME="DDLQP1" ID="DDLQP1" SIZE="6">
<OPTION SELECTED="true" VALUE="ANY">ANY</OPTION>
<OPTION VALUE="0 AND 10">0 < 9</OPTION>
<OPTION VALUE="10 AND 100">10 < 100</OPTION>
<OPTION VALUE="100 AND 1000">100 < 1 K</OPTION>
<OPTION VALUE="1000 AND 10000">1 K < 10 K</OPTION>
<OPTION VALUE="10000 AND 100000">10 K < 100 K</OPTION>
<OPTION VALUE="100000 AND 1000000">100 K < 1 M</OPTION>
<OPTION VALUE="1000000 AND 10000000">1 M < 10 M</OPTION>
<OPTION VALUE="10000000 AND 100000000">10 M < 100 M</OPTION>
<OPTION VALUE="100000000 AND 1000000000">100 M < 1 B</OPTION>
</SELECT>
</TD>
<TD CLASS="CENTER_DROPDOWN">
<SELECT NAME="DDLAP" ID="DDLAP" SIZE="6">
<OPTION SELECTED="true" VALUE="ANY">ANY</OPTION>
<OPTION VALUE="0 AND 10">0 < 9</OPTION>
<OPTION VALUE="10 AND 100">10 < 100</OPTION>
<OPTION VALUE="100 AND 1000">100 < 1 K</OPTION>
<OPTION VALUE="1000 AND 10000">1 K < 10 K</OPTION>
<OPTION VALUE="10000 AND 100000">10 K < 100 K</OPTION>
<OPTION VALUE="100000 AND 1000000">100 K < 1 M</OPTION>
<OPTION VALUE="1000000 AND 10000000">1 M < 10 M</OPTION>
<OPTION VALUE="10000000 AND 100000000">10 M < 100 M</OPTION>
<OPTION VALUE="100000000 AND 1000000000">100 M < 1 B</OPTION>
<OPTION VALUE="1000000000 AND 10000000000">1 B < 10 B</OPTION>
</SELECT>
</TD>
<TD CLASS="CENTER_DROPDOWN">
<SELECT NAME="DDLEST" ID="DDLEST" SIZE="6">
<OPTION SELECTED="true" VALUE="ANY">ANY</OPTION>
<OPTION VALUE="0 AND 10">0 < 9</OPTION>
<OPTION VALUE="10 AND 100">10 < 99</OPTION>
<OPTION VALUE="100 AND 1000">100 < 999</OPTION>
<OPTION VALUE="1000 AND 10000">1 K < 10 K</OPTION>
<OPTION VALUE="10000 AND 100000">10 K < 100 K</OPTION>
</SELECT>
</TD>
</TR>
<TR>
<TD COLSPAN="5"></TD>
<TD><INPUT TYPE="button" ID="SUBMIT" ONCLICK="submitData();" VALUE="SUBMIT"/></TD>
</TR>
<TR>
<TD COLSPAN="5"></TD>
<TD><INPUT TYPE="button" ID="RESET" VALUE="RESET"/></TD>
</TR>
<TR>
<TD CLASS="tg-DROPDOWNHEADING" COLSPAN="2"><LABEL>Function for<BR>Select Clause</LABEL></TD>
<TD></TD>
<TD CLASS="tg-DROPDOWNHEADING" VALIGN="TOP"><LABEL FOR="DDLQUANTILES"; text-align:center;>Number of<BR>Quantiles</LABEL></TD>
<TD ROWSPAN="2"></TD>
<TD></TD>
</TR>
<TR ID="lastRow">
<TD CLASS="CENTER_DROPDOWN" COLSPAN="2" VALIGN="TOP">
<SELECT NAME="DDLQUERY" CLASS="DDLQUERY" ID="DDLQUERY" SIZE="4">
<OPTION VALUE="1" SELECTED="true">STTDEV Q1 PAYROLL</OPTION>
<OPTION VALUE="2">AVG TOTAL ANNUAL PAYROLL</OPTION>
<OPTION VALUE="3">VARIANCE TOTAL ESTABLISHMENT COUNT</OPTION>
<OPTION VALUE="4">QUANTILES EMPLOYEE COUNT</OPTION>
</SELECT>
</TD>
<TD CLASS="tg-DROPDOWNHEADING" VALIGN="top"><LABEL FOR="DDLQUANTILES" style="color: #FFFFFF; background-color:gray"; >(pick the number of quantiles ->)</LABEL></TD>
<TD CLASS="CENTER_DROPDOWN">
<SELECT NAME="DDLQUANTILES" CLASS="DDLQUANTILES" ID="DDLQUANTILES" SIZE="10">
<OPTION VALUE=1 SELECTED="true">1</OPTION>
<OPTION VALUE=2>2</OPTION>
<OPTION VALUE=3>3</OPTION>
<OPTION VALUE=4>4</OPTION>
</SELECT>
</TD>
</TR>
</TABLE>
</FORM>
</BODY>
</HTML>