-
Notifications
You must be signed in to change notification settings - Fork 11
/
bq-sheets-slides.js
177 lines (157 loc) · 6.16 KB
/
bq-sheets-slides.js
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
/**
* Copyright 2018 Google LLC
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at apache.org/licenses/LICENSE-2.0.
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
// Filename for data results
var QUERY_NAME = "Most common words in all of Shakespeare's works";
// Replace this value with your Google Cloud API project ID
var PROJECT_ID = '';
if (!PROJECT_ID) throw Error('Project ID is required in setup');
/**
* Runs a BigQuery query; puts results into Sheet. You must enable
* the BigQuery advanced service before you can run this code.
* @see http://developers.google.com/apps-script/advanced/bigquery#run_query
* @see http://github.com/gsuitedevs/apps-script-samples/blob/master/advanced/bigquery.gs
*
* @returns {Spreadsheet} Returns a spreadsheet with BigQuery results
* @see http://developers.google.com/apps-script/reference/spreadsheet/spreadsheet
*/
function runQuery() {
// Replace sample with your own BigQuery query.
var request = {
query:
'SELECT ' +
'LOWER(word) AS word, ' +
'SUM(word_count) AS count ' +
'FROM [bigquery-public-data:samples.shakespeare] ' +
'GROUP BY word ' +
'ORDER BY count ' +
'DESC LIMIT 10'
};
var queryResults = BigQuery.Jobs.query(request, PROJECT_ID);
var jobId = queryResults.jobReference.jobId;
// Wait for BQ job completion (with exponential backoff).
var sleepTimeMs = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId);
}
// Get all results from BigQuery.
var rows = queryResults.rows;
while (queryResults.pageToken) {
queryResults = BigQuery.Jobs.getQueryResults(PROJECT_ID, jobId, {
pageToken: queryResults.pageToken
});
rows = rows.concat(queryResults.rows);
}
// Return null if no data returned.
if (!rows) {
return Logger.log('No rows returned.');
}
// Create the new results spreadsheet.
var spreadsheet = SpreadsheetApp.create(QUERY_NAME);
var sheet = spreadsheet.getActiveSheet();
// Add headers to Sheet.
var headers = queryResults.schema.fields.map(function(field) {
return field.name.toUpperCase();
});
sheet.appendRow(headers);
// Append the results.
var data = new Array(rows.length);
for (var i = 0; i < rows.length; i++) {
var cols = rows[i].f;
data[i] = new Array(cols.length);
for (var j = 0; j < cols.length; j++) {
data[i][j] = cols[j].v;
}
}
// Start storing data in row 2, col 1
var START_ROW = 2; // skip header row
var START_COL = 1;
sheet.getRange(START_ROW, START_COL, rows.length, headers.length).setValues(data);
// Return the spreadsheet object for later use.
return spreadsheet;
}
/**
* Uses spreadsheet data to create columnar chart.
* @param {Spreadsheet} Spreadsheet containing results data
* @returns {EmbeddedChart} visualizing the results
* @see http://developers.google.com/apps-script/reference/spreadsheet/embedded-chart
*/
function createColumnChart(spreadsheet) {
// Retrieve the populated (first and only) Sheet.
var sheet = spreadsheet.getSheets()[0];
// Data range in Sheet is from cell A2 to B11
var START_CELL = 'A2'; // skip header row
var END_CELL = 'B11';
// Place chart on Sheet starting on cell E5.
var START_ROW = 5; // row 5
var START_COL = 5; // col E
var OFFSET = 0;
// Create & place chart on the Sheet using above params.
var chart = sheet.newChart()
.setChartType(Charts.ChartType.COLUMN)
.addRange(sheet.getRange(START_CELL + ':' + END_CELL))
.setPosition(START_ROW, START_COL, OFFSET, OFFSET)
.build();
sheet.insertChart(chart);
// Return the chart object for later use.
return chart;
}
/**
* Create presentation with spreadsheet data & chart
* @param {Spreadsheet} Spreadsheet with results data
* @param {EmbeddedChart} Sheets chart to embed on slide
* @returns {Presentation} Returns a slide deck with results
* @see http://developers.google.com/apps-script/reference/slides/presentation
*/
function createSlidePresentation(spreadsheet, chart) {
// Create the new presentation.
var deck = SlidesApp.create(QUERY_NAME);
// Populate the title slide.
var [title, subtitle] = deck.getSlides()[0].getPageElements();
title.asShape().getText().setText(QUERY_NAME);
subtitle.asShape().getText().setText('via GCP and G Suite APIs:\n' +
'Google Apps Script, BigQuery, Sheets, Slides');
// Data range to copy is from cell A1 to B11
var START_CELL = 'A1'; // include header row
var END_CELL = 'B11';
// Add the table slide and insert an empty table on it of
// the dimensions of the data range; fails if Sheet empty.
var tableSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
var sheetValues = spreadsheet.getSheets()[0].getRange(
START_CELL + ':' + END_CELL).getValues();
var table = tableSlide.insertTable(sheetValues.length, sheetValues[0].length);
// Populate the table with spreadsheet data.
for (var i = 0; i < sheetValues.length; i++) {
for (var j = 0; j < sheetValues[0].length; j++) {
table.getCell(i, j).getText().setText(String(sheetValues[i][j]));
}
}
// Add a chart slide and insert the chart on it.
var chartSlide = deck.appendSlide(SlidesApp.PredefinedLayout.BLANK);
chartSlide.insertSheetsChart(chart);
// Return the presentation object for later use.
return deck;
}
/**
* Runs a BigQuery query, adds data and a chart in a Sheet,
* and adds the data and chart to a new slide presentation.
*/
function createBigQueryPresentation() {
var spreadsheet = runQuery();
Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
var chart = createColumnChart(spreadsheet);
var deck = createSlidePresentation(spreadsheet, chart);
Logger.log('Results slide deck created: %s', deck.getUrl());
}