GSP240
總覽
市面上有許多能讓資料科學家用來分析大數據的工具，但有哪些能協助您向管理團隊和利害關係人說明分析結果並提出論證呢？單純展示紙本或資料庫中的原始數據，效果相當有限。本 Google Apps Script 實驗室將運用 Workspace 和 Google Cloud 這兩個 Google 開發人員平台，協助您跨越最後一道關卡。
您可以運用 Google Cloud 開發人員工具收集並處理資料，然後生成投影片和試算表簡報，以精闢的分析和獨到的重點總結，讓管理團隊和利害關係人刮目相看。
本實驗室會介紹 Google Cloud 的 BigQuery API (Apps Script 的進階服務)，以及 Google 試算表和 Google 簡報內建的 Apps Script 服務。
此外，本實驗室也設計了非常貼近現實的情境。使用的應用程式將展示 Google Cloud 的各項功能和 API，目標是示範如何運用 Google Cloud 和 Workspace 兩種工具，為組織或客戶解決棘手問題。
課程內容
- 如何將 Google Apps Script 與多項 Google 服務搭配使用
- 如何使用 BigQuery 執行大數據分析
- 如何建立 Google 試算表並填入資料，並使用試算表資料建立圖表
- 如何將試算表圖表和資料分別放入 Google 簡報檔案的不同投影片中
設定
瞭解以下事項後，再點選「Start Lab」按鈕
請詳閱以下操作說明。實驗室活動會計時，且中途無法暫停。點選「Start Lab」後就會開始計時，顯示可使用 Google Cloud 資源的時間。
您將在真正的雲端環境完成實作實驗室活動，而不是模擬或示範環境。為此，我們會提供新的暫時憑證，供您在實驗室活動期間登入及存取 Google Cloud。
為了順利完成這個實驗室，請先確認：
- 可以使用標準的網際網路瀏覽器 (Chrome 瀏覽器為佳)。
注意事項：請使用無痕模式 (建議選項) 或私密瀏覽視窗執行此實驗室，這可以防止個人帳戶和學員帳戶之間的衝突，避免個人帳戶產生額外費用。
- 是時候完成實驗室活動了！別忘了，活動一旦開始將無法暫停。
注意事項：務必使用實驗室專用的學員帳戶。如果使用其他 Google Cloud 帳戶，可能會產生額外費用。
簡介
Google Apps Script 和 BigQuery
Google Apps Script 是 Workspace 開發平台，比起使用 Google REST API，能提供更高層次的操作方式。無論開發人員的技術程度如何，都能使用這個無伺服器開發與應用程式代管環境。簡單來說，Apps Script 是無伺服器 JavaScript 執行階段，可自動執行、擴充及整合 Workspace 功能。
Apps Script 是伺服器端 JavaScript，類似 Node.js，但著重於與 Workspace 和其他 Google 服務緊密整合，而非快速的非同步事件導向應用程式代管。此外，開發環境可能與您以往慣用的環境截然不同。Apps Script 能讓您：
- 透過瀏覽器上的程式碼編輯器開發，若使用 clasp (Apps Script 的指令列部署工具)，也可選擇在本機開發
- 以量身打造的 JavaScript 專用版本編寫程式碼，透過 Apps Script 的 URLfetch 或 Jdbc 服務存取 Workspace 和其他 Google 或外部服務
- 不必編寫授權程式碼，由 Apps Script 代為處理即可
- 不必託管應用程式，應用程式會存放在雲端上的 Google 伺服器上並直接運行
備註：如要進一步瞭解 Apps Script，請參閱官方說明文件，當中有提供快速入門導覽課程的概覽介紹、教學課程和影片。
Apps Script 與其他 Google 技術的互動方式有兩種：
內建服務提供可用於存取 Workspace 或 Google 產品資料的高階方法，或其他實用的公用程式方法。進階服務則只是 Workspace 或 Google REST API 的精簡 wrapper，完整支援 REST API，且功能通常比內建服務多，但需要撰寫較複雜的程式碼 (不過還是比直接使用 REST API 簡單)。
進階服務在使用前，也必須在指令碼專案中啟用。內建服務比進階服務更容易使用，且能處理更多繁重的工作，因此建議盡可能使用這類服務。不過，部分 Google API 沒有內建服務，在這種情況下，進階服務可能是唯一選擇。BigQuery 就是一個例子，雖然沒有內建服務，但是有 BigQuery 進階服務 (總比沒有好，對吧？)。
注意：如果您剛接觸 BigQuery，請參考以下簡單說明：這項 Google Cloud 服務可對非常龐大的資料集 (例如數 TB 的資料) 執行簡單或複雜的查詢，幾秒內就能提供結果，無需耗費幾小時或幾天。
透過 Apps Script 存取 Google 試算表和簡報
BigQuery 僅以 Apps Script 進階服務的形式提供。不過，像是 Google 試算表和簡報，就同時有內建的 Apps Script 服務和進階服務，讓您可以使用進階服務，存取未內建、僅透過 API 提供的功能。內建服務提供層次較高的結構和便利的呼叫方式，可簡化開發作業，因此建議盡可能選用內建服務，而非功能相同的進階服務。
備註：開始編寫程式碼前，如要先複習相關知識，請參閱 Google 試算表服務和 Google 簡報服務的說明。
工作 1：查詢 BigQuery 並將結果記錄在 Google 試算表中
第一個工作是本實驗室的重頭戲，這個部分結束後，差不多就完成了整個實驗室的一半。
在本節中，您將執行下列操作：
- 建立新的 Google Apps Script 專案
- 啟用 BigQuery 進階服務的存取權
- 前往開發編輯器，輸入應用程式原始碼
- 完成應用程式授權程序 (OAuth2)
- 執行會將要求傳送至 BigQuery 的應用程式
- 查看使用 BigQuery 結果建立的全新 Google 試算表
建立新的 Apps Script 專案
- 前往 script.google.com 建立新的 Apps Script 專案。在本實驗室中，請點選「建立 Apps Script」連結。
-
Apps Script 程式碼編輯器便會開啟：
-
按一下頂端的專案名稱 (上圖中的「Untitled project」)，為專案命名。
-
在「重新命名專案」對話方塊中，依個人喜好命名專案 (例如「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 有多實用。
就快完成了！您必須提供有效的專案 ID，才能在 bq-sheets-slides.gs 頂端設定 PROJECT_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 服務，然後再次新增。
- 在「Choose an account from qwiklabs.net」對話方塊中，按一下使用者名稱，然後按一下「允許」。
注意：授權應用程式後，就不需要在每次執行時重複這個程序。等進入本實驗室稍後的「將結果資料放入投影片簡報」部分，您才會再次看到這個對話方塊畫面，要求授予建立及管理 Google 簡報檔案的使用者權限。
- 執行函式時，頂端會開啟小型訊息方塊。
函式執行完成後，訊息方塊就會消失，因此如果沒有看到，表示函式可能已執行完畢。
- 前往 Google 雲端硬碟，尋找新 Google 試算表，名稱可能是「Most common words in all of Shakespeare's works」，或您指派給
QUERY_NAME 變數的名稱。
- 開啟試算表，您應該會看到 10 列字詞及對應的出現總次數，這些項目會依出現次數由高到低排列。
點選「Check my progress」，確認目標已達成。
查詢 BigQuery 並將結果記錄在 Google 試算表中
摘要
所以剛剛發生了什麼？您執行了程式碼，查詢所有莎士比亞的作品。雖然這樣的資料量不算大，但一定超過您自己能輕鬆瀏覽的文字量，因為如果以肉眼檢查，需要逐字查看每部戲劇中的內容、統計字詞的出現次數，並按照出現頻率遞減排序。)您不僅要求 BigQuery 代為執行這項操作，還能使用 Google 試算表的 Apps Script 內建服務整理資料，好方便參考。
您隨時可以在 BigQuery 控制台中測試查詢，然後在 Apps Script 中執行。開發人員可以使用 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 通訊，並將結果傳送至 Google 試算表。接下來，準備加入程式碼，將資料繪製成圖表。在這個部分，您會建立名為 createColumnChart() 的新函式，透過呼叫 Google 試算表的 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);
}
-
傳回試算表：在上述程式碼中，createColumnChart() 需要試算表物件，因此請調整應用程式以傳回 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 個重要步驟，也就是傳回試算表物件，以及建立主導流程的函式。如果同事想重複使用
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 簡報檔案、在標題投影片上填入標題和副標題，然後新增 2 張投影片，一張用來放各個儲存格的資料，另一張則用來呈現圖表。
- 建立簡報：先建立新的簡報，然後在預設的標題投影片 (所有新簡報都會附帶這張投影片) 中新增標題和副標題。要對簡報執行的所有操作都會集中透過
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() 需要試算表和圖表物件。您已將 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 簡報檔案。
- 前往「我的雲端硬碟」，您會發現除了建立的 Google 試算表外，還會看到新的簡報檔案，內含 3 張投影片 (標題、資料表和資料圖表)，如下所示：
恭喜！
您建立了一個應用程式，能同時運用 Google Cloud 兩大能力：您執行了 BigQuery 要求以查詢其中一個公開資料集、建立新 Google 試算表來儲存結果、根據擷取的資料新增圖表，最後建立 Google 簡報檔案，呈現結果和試算表中的圖表。
這些是您所完成的技術操作。以宏觀的角度來看，就是執行大數據分析，然後將結果轉化成能向利害關係人展示的內容，而且全都是透過程式碼自動完成。現在您可以將本實驗室中學到的內容應用在自己的專案，並按需求加以調整。
後續步驟/瞭解詳情
本實驗室使用的程式碼也會放在 GitHub 上，實驗室會盡量與存放區保持同步。您也可以參考下方的額外資源，深入瞭解本實驗室涵蓋的內容，並探索其他透過程式存取 Google 開發人員工具的方法。
說明文件
相關和一般影片
- Another Google (Apps) secret - (Apps Script 簡介影片)
- Accessing Google Maps from a spreadsheet - (影片)
- Others in Google Apps Script - 影片庫
- Launchpad Online - 影片系列
- G Suite Dev Show - 影片系列
相關和一般新聞與最新消息
使用手冊上次更新日期：2025 年 7 月 28 日
實驗室上次測試日期：2025 年 7 月 28 日
Copyright 2026 Google LLC 保留所有權利。Google 和 Google 標誌是 Google LLC 的商標，其他公司和產品名稱則有可能是其關聯公司的商標。
