GSP240
概览
市面上为数据科学家提供的大数据分析工具不胜枚举，但究竟哪些工具能帮助您向管理层和利益相关方阐释并论证分析结果呢？纸面上或数据库中的原始数据显然难以做到。本 Google Apps 脚本实验将结合 Google Workspace 和 Google Cloud 这两大开发者平台，助您完成这最后一步。
借助 Google Cloud 开发者工具，您可以收集和处理数据，然后生成幻灯片和电子表格演示文稿，用令人惊叹的分析和富有洞察力的结论让管理层和利益相关方刮目相看。
本实验用到了 Google Cloud 的 BigQuery API（作为 Apps 脚本高级服务），以及适用于 Google 表格和 Google 幻灯片的内置 Apps 脚本服务。
本实验还搭建了一个高度贴近真实场景的模拟环境。所用应用展示了 Google Cloud 的各种功能和 API，目的是展示如何利用 Google Cloud 和 Workspace 为您的组织或客户解决难题。
学习内容
- 如何将 Google Apps 脚本与多项 Google 服务搭配使用
- 如何使用 BigQuery 执行大数据分析
- 如何创建 Google 表格并向其中填充数据，以及如何使用电子表格数据创建图表
- 如何将电子表格图表和数据导入 Google 幻灯片演示文稿的单独幻灯片中
设置
点击“开始实验”按钮前的注意事项
请阅读以下说明。实验是计时的，并且您无法暂停实验。计时器在您点击开始实验后即开始计时，显示 Google Cloud 资源可供您使用多长时间。
此实操实验可让您在真实的云环境中开展实验活动，免受模拟或演示环境的局限。为此，我们会向您提供新的临时凭据，您可以在该实验的规定时间内通过此凭据登录和访问 Google Cloud。
为完成此实验，您需要：
- 能够使用标准的互联网浏览器（建议使用 Chrome 浏览器）。
注意：请使用无痕模式（推荐）或无痕浏览器窗口运行此实验。这可以避免您的个人账号与学生账号之间发生冲突，这种冲突可能导致您的个人账号产生额外费用。
注意：请仅使用学生账号完成本实验。如果您使用其他 Google Cloud 账号，则可能会向该账号收取费用。
简介
Google Apps 脚本和 BigQuery
Google Apps 脚本是一个 Workspace 开发平台，其运行级别高于直接使用 Google REST API。它是一个无服务器开发和应用托管环境，适合各种技能水平的开发者。简单来说，“Apps 脚本是用于实现 Workspace 自动化、扩展和集成的无服务器 JavaScript 运行时。”
它是一种服务器端 JavaScript，类似于 Node.js，但侧重于与 Workspace 和其他 Google 服务的紧密集成，而不是快速的异步事件驱动型应用托管。它提供的开发环境可能也与您惯用的截然不同。借助 Apps 脚本，您可以：
- 在基于浏览器的代码编辑器中进行开发，但如果使用 clasp（Apps 脚本的命令行部署工具），则可以选择在本地开发
- 使用专用的 JavaScript 版本编写代码，该版本经过定制，可访问 Workspace 以及其他 Google 服务或外部服务（通过 Apps 脚本 URLfetch 或 Jdbc 服务）
- 避免编写授权代码，因为 Apps 脚本会为您处理
- 无需托管应用，它将在云端的 Google 服务器上驻留和运行
注意：如需详细了解 Apps 脚本，请参阅官方文档，其中还包含快速入门概览、教程以及视频。
Apps 脚本通过两种不同方式与其他 Google 技术进行交互：
内置服务提供高层方法，可用于访问 Workspace 或 Google 产品数据，或者其他实用程序方法。高级服务只是 Workspace 或 Google REST API 的一个轻量级封装容器。高级服务全面覆盖 REST API，通常比内置服务的功能更强大，但需要更复杂的代码实现（不过仍比 REST API 本身更易于使用）。
在使用高级服务之前，还必须先为脚本项目启用这些服务。在可能的情况下，建议优先使用内置服务，因为它们比高级服务更易于使用，并且可以处理更多繁重的工作。不过，某些 Google API 并未提供内置服务，此时高级服务可能是唯一选择。BigQuery 就是一个例子；虽无内置服务可用，但确实存在 BigQuery 高级服务。（总比没有服务强，对吧？）
注意：如果您刚开始接触 BigQuery，可以了解一下，BigQuery 是一项 Google Cloud 服务，可用于对海量数据集（例如数 TB 级）执行简单（或复杂）的查询，但仍可在数秒内（而非数小时或数天内）提供结果。
通过 Apps 脚本访问 Google 表格和 Google 幻灯片
BigQuery 仅作为 Apps 脚本高级服务提供。不过，Google 表格和 Google 幻灯片都有内置的 Apps 脚本服务以及高级服务；例如，有些高级服务用于访问仅存在于相应 API 中且未内置的功能。在可能的情况下，请优先选择内置服务，而非等效的高级服务，因为内置服务提供更高层次的构造和便捷的调用，可以简化开发过程。
注意：如需在开始编写代码之前回顾相关内容，请参阅表格服务以及幻灯片服务。
任务 1. 查询 BigQuery 并将结果记录到表格中
在第一项任务中，您将完成本实验的大部分内容。完成这一部分后，整个实验就完成了一半左右。
在本部分中，您将执行以下操作：
- 启动新的 Google Apps 脚本项目
- 启用对 BigQuery 高级服务的访问权限
- 前往开发编辑器，然后输入应用源代码
- 完成应用授权流程 (OAuth2)
- 运行向 BigQuery 发送请求的应用
- 查看使用 BigQuery 生成的结果创建的全新 Google 表格
新建 Apps 脚本项目
- 前往 script.google.com 创建新的 Apps 脚本项目。在本实验中，点击创建 Apps 脚本链接。
- 系统随即会打开 Apps 脚本代码编辑器：
-
点击顶部的项目名称（上图中的“未命名项目”），为项目命名。
-
在重命名项目对话框中，根据您的选择为项目命名（例如“BigQuery”“Sheets”“Slides demo”等），然后点击重命名。
启用 BigQuery 高级服务
为新项目启用 BigQuery 高级服务，并启用 BigQuery API。
- 点击服务旁边的添加服务图标。
- 在添加服务对话框中，选择所有适用的服务和 API。
- 前往 Cloud 控制台，然后依次选择导航菜单 > API 和服务 > 库。
- 在搜索框中输入或粘贴 BigQuery API，然后选择 BigQuery API。
- 根据需要点击启用，以启用 BigQuery API。
-
返回您的项目，添加服务对话框应仍处于打开状态。
-
选择 BigQuery API，然后点击添加以关闭。
输入并运行应用代码
现在，您已准备好输入应用代码、完成授权流程，并让此应用的第一个版本正常运行。
- 复制下方框中的代码并粘贴到代码编辑器中，覆盖所有现有内容：
/**
* 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 = '<YOUR_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);
Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
}
-
点击菜单栏中的保存项目图标或按 Ctrl + S，保存您刚刚创建的文件。
-
点击文件名旁边的三点状图标，然后点击重命名，以重命名文件。
- 将文件名更改为 bq-sheets-slides.gs，然后按 Enter 键。
这段代码的作用是什么呢？您知道它会查询 BigQuery 并将结果写入新的 Google 表格，但这个查询语句具体是什么？
- 查看
runQuery() 函数中的查询代码：
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
这个查询会遍历莎士比亚的作品（属于 BigQuery 公共数据集），并生成其所有作品中出现频率最高的 10 个词汇，按出现次数降序排列。想象一下手动完成这项工作会多么（不）有趣，您应该就能大致了解 BigQuery 的实用价值。
就快完成了！在 bq-sheets-slides.gs 开头设置 PROJECT_ID 变量时，必须提供有效的项目 ID。
- 将
<YOUR_PROJECT_ID> 替换为左侧面板中显示的项目 ID。
以下是包含示例项目 ID 的代码示例。实际 PROJECT_ID 值会有所不同。
示例代码：
// 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 = '<YOUR_PROJECT_ID>';
if (!PROJECT_ID) throw Error('Project ID is required in setup');
注意：在此代码段中，if 语句的作用是防止应用在未设置项目 ID 的情况下继续执行后续操作。
注意：如果菜单选择器卡住且无法操作，请重新加载页面。
-
保存文件，通过点击菜单栏中的运行选项来运行代码。
-
点击查看权限。
注意：如果您收到错误消息“Exception: Service BigQuery API has not been enabled for your Apps”（异常：您的应用尚未启用 BigQuery API 服务），请移除 BigQuery API 服务，然后重新添加。
- 在“从 qwiklabs.net 中选择账号”对话框中，点击您的用户名，然后点击允许。
注意：为该应用授权后，您无需在每次执行时重复此流程。直到本实验后续进入“将结果数据放入幻灯片演示文稿中”环节时，才会再次出现此对话框，要求用户授予创建和管理 Google 幻灯片演示文稿的权限。
- 当函数运行时，顶部会打开一个小消息框。
该消息框在函数执行完毕后会自动消失，因此如果您没有看到该消息框，则表示函数可能已完成运行。
- 前往 Google 云端硬盘，查找一个新 Google 表格，其名称为 Most common words in all of Shakespeare's works，或者您为
QUERY_NAME 变量分配的名称。
- 打开电子表格，您应该会看到 10 行字词及其出现总次数（按降序排序）。
点击“检查我的进度”以验证是否完成了以下目标：
查询 BigQuery 并将结果记录到表格中
总结
刚刚发生了什么？！您运行了一段代码，查询了莎士比亚所有所有作品（数据量虽不算庞大，但文本量绝对超出您自行浏览的承受范围，毕竟要逐字逐句地查看每部剧作，统计词频，再按出现次数降序排列）。您不仅让 BigQuery 代劳了这项工作，还利用适用于 Google 表格的内置 Apps 脚本服务整理了数据，使其易于使用。
您始终可以在 BigQuery 控制台中测试查询，然后再在 Apps 脚本中运行该查询。开发者可以使用 BigQuery 的界面。
打开 BigQuery 控制台
- 在 Google Cloud 控制台中，选择导航菜单 > BigQuery。
您会看到欢迎在 Cloud 控制台中使用 BigQuery 消息框，其中提供了指向快速入门指南和版本说明的链接。
- 点击完成。
BigQuery 控制台即会打开。
- 在查询编辑器中输入代码，然后点击运行：
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
任务 2. 在 Google 表格中创建图表
返回脚本编辑器。到目前为止，您编写了一个用于查询莎士比亚作品、对结果进行排序并通过表格呈现结果的应用。在代码中，runQuery() 函数与 BigQuery 通信，并将其结果发送到表格中。接下来，您将添加绘制数据图表的代码。在本部分中，您将创建一个名为 createColumnChart() 的新函数，该函数会调用表格的 newChart() 方法来绘制数据图表。
createColumnChart() 函数会获取包含数据的表格，并请求生成包含所有数据的柱状图。数据范围从单元格 A2 开始，因为第一行包含列标题，而不是数据。
- 创建图表：将
createColumnChart() 函数添加到 bq-sheets-slides.gs 中的 runQuery() 之后。{位于最后一行代码之后}：
/**
* 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);
}
-
返回 spreadsheet 对象：在上述代码中，createColumnChart() 需要 spreadsheet 对象，因此需修改应用，使其返回 spreadsheet 对象，以便传递给 createColumnChart()。在成功创建 Google 表格后进行日志记录，并在 runQuery() 的末尾返回该对象。
-
将最后一行（以 Logger.log 开头）替换为以下内容：
Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
// Return the spreadsheet object for later use.
return spreadsheet;
}
- 驱动
createBigQueryPresentation() 函数：从逻辑上分离 BigQuery 和图表创建功能是个好主意。创建 createBigQueryPresentation() 函数来驱动应用，该函数会同时调用 runQuery() 和 createColumnChart()。您添加的代码应如下所示：
/**
* Runs a BigQuery query, adds data and a chart in a Sheet.
*/
function createBigQueryPresentation() {
var spreadsheet = runQuery();
createColumnChart(spreadsheet);
}
- 将
createBigQueryPresentation() 函数放在以下代码块之后：
// 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 = '<YOUR_PROJECT_ID>';
if (!PROJECT_ID) throw Error('Project ID is required in setup');
- 提高代码的可重用性：您在前面执行了 2 个重要步骤：返回 spreadsheet 对象，以及创建驱动函数。如果同事想重复使用
runQuery()，但不希望记录网址，该怎么办？
为使 runQuery() 更适用于一般场景，请移动相应日志行。最适合将它移到哪里呢？如果您猜到 createBigQueryPresentation()，那就对了！
移动日志行后，代码应如下所示：
/**
* Runs a BigQuery query, adds data and a chart in a Sheet.
*/
function createBigQueryPresentation() {
var spreadsheet = runQuery();
Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
createColumnChart(spreadsheet);
}
完成上述更改后，您的 bq-sheets-slides.js 应如下所示（PROJECT_ID 除外）：
/**
* 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 = '<YOUR_PROJECT_ID>';
if (!PROJECT_ID) throw Error('Project ID is required in setup');
/**
* Runs a BigQuery query, adds data and a chart in a Sheet.
*/
function createBigQueryPresentation() {
var spreadsheet = runQuery();
Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
createColumnChart(spreadsheet);
}
/**
* 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);
Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
// 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);
}
-
保存文件。
-
在菜单栏中，点击 runQuery，并从下拉菜单中选择 createBigQueryPresentation。
-
然后，点击运行。
代码运行完毕后，您会在 Google 云端硬盘中获得另一个 Google 表格，但这次，表格中数据的旁边会显示一个图表：
任务 3. 将结果数据放入幻灯片演示文稿中
本实验的最后一部分涉及创建新的 Google 幻灯片演示文稿，在标题幻灯片上填写标题和副标题，然后添加两张新幻灯片：一张用于显示每个数据单元格，另一张用于显示图表。
- 创建幻灯片演示文稿：首先创建一个新的幻灯片演示文稿，然后为所有新演示文稿默认包含的标题幻灯片添加标题和副标题。幻灯片演示文稿的所有操作都在
createSlidePresentation() 函数bq-sheets-slides.gs 中，紧跟在 createColumnChart() 函数代码之后：
/**
* Create presentation with spreadsheet data & chart
* @param {Spreadsheet} Spreadsheet with results data
* @param {EmbeddedChart} Sheets chart to embed on slide
* @returns {Presentation} Slide deck with results
*/
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');
- 添加数据表：
createSlidePresentation() 中的下一步是将 Google 表格中的单元格数据导入到新幻灯片演示文稿中。将以下代码段添加到 createSlidePresentation() 函数中：
// 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]));
}
}
- 导入图表：
createSlidePresentation() 中的最后一步是再创建一张幻灯片，从电子表格中导入图表，然后返回 Presentation 对象。将最后这段代码添加到该函数中：
// 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;
}
- 返回图表：现在，我们的最终函数已完成，请再次查看其签名。没错，
createSlidePresentation() 需要同时接收 spreadsheet 和 chart 对象。您已调整 runQuery()，使其返回 Spreadsheet 对象；但现在，您需要对 createColumnChart() 进行类似更改，使其返回图表 (EmbeddedChart) 对象。为此，请返回您的应用，并在 createColumnChart() 的末尾添加最后一行代码：
// Return chart object for later use
return chart;
}
- 更新
createBigQueryPresentation()：由于 createColumnChart() 会返回图表，因此您需要将该图表保存为变量，然后将电子表格和图表都传递给 createSlidePresentation()。既然您会记录新创建的电子表格的网址，那么同样也可以记录新幻灯片演示文稿的网址。将以下代码块：
/**
* Runs a BigQuery query, adds data and a chart in a Sheet.
*/
function createBigQueryPresentation() {
var spreadsheet = runQuery();
Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
createColumnChart(spreadsheet);
}
替换为：
/**
* 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());
}
完成所有更新后，您的 bq-sheets-slides.gs 现在应如下所示，但 PROJECT_ID 除外：
bq-sheets-slides.gs - final version
// 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 = '<YOUR_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());
}
- 保存并再次运行
createBigQueryPresentation()。在执行之前，系统将提示您授予另一组权限，用于查看和管理您的 Google 幻灯片演示文稿。
- 前往“我的云端硬盘”，您会发现除了已创建的表格外，其中还新增了一个包含 3 张幻灯片（标题页、数据表格页、数据图表页）的演示文稿，如下所示：
恭喜！
现在，您创建了一个充分利用 Google Cloud 双重优势的应用：通过执行 BigQuery 请求来查询其公共数据集之一；创建新的 Google 表格来存储结果；根据检索到的数据添加图表；最后创建 Google 幻灯片演示文稿，其中包含结果以及电子表格中的图表。
这些是您在技术层面完成的操作。简而言之，您实现了从大数据分析到向利益相关方展示成果的全流程自动化。整个过程完全通过代码实现。现在，您可以将本实验应用到自己的项目中，并根据需求进行定制。
后续步骤/了解详情
您还可以在 GitHub 中找到本实验中使用的代码。本实验会尽量与代码库保持同步。下面提供了更多资源，可帮助您更深入地了解本实验涵盖的内容，并探索通过程序化方式访问 Google 开发者工具的其他方法。
文档
相关视频和一般视频
- 另一个 Google（Apps）秘诀 -（Apps 脚本简介视频）
- 从电子表格访问 Google 地图 -（视频）
- Google Apps 脚本中的其他内容 - 视频库
- Launchpad Online - 视频系列
- G Suite Dev Show - 视频系列
相关资讯和一般资讯及更新
上次更新手册的时间：2025 年 7 月 28 日
上次测试实验的时间：2025 年 7 月 28 日
版权所有 2026 Google LLC 保留所有权利。Google 和 Google 徽标是 Google LLC 的商标。其他所有公司名和产品名可能是其各自相关公司的商标。
