市面上有很多工具可以協助數據資料學家執行大數據分析，但是在一天的執行結束後，還是要證明這些結果是否值得管理？許多相關人員通常很難在紙上或資料庫中查看許多號碼。這種中階的 Google Apps Script 程式碼研究室利用一組 Google 的開發人員平台 (G Suite 和 Google Cloud Platform (GCP) 來協助您完成最終工作。
Google Cloud 的開發人員工具可讓您執行深度資料分析，並運用這些結果將試算表製作成試算表，並透過投影片來呈現這些資料，進而更妥善地將結果提供給管理人員。本程式碼研究室涵蓋 GCP 的 BigQuery API (做為 Apps Script 進階服務)，以及 Google 試算表和 Google 簡報的內建 Apps Script 服務。
動力/前衛藝術
這個程式碼研究室中的範例應用程式受到下列其他程式碼範例的啟發...
- Google Apps Script BigQuery Service 範例應用程式和 GitHub 上的開放原始碼
- 從試算表資料中產生投影片投影片網誌文章的精選應用程式範例
- Google 簡報 API 程式碼研究室提供的範例應用程式
「簡報 API 程式碼研究室」範例應用程式同時提供 BigQuery 和簡報，但與這個程式碼研究室的範例應用程式有幾種不同之處：
- Node.js 應用程式與 Apps Script 應用程式的比較
- 在我們使用 Apps Script 服務時，使用 REST API
- 使用 Google 雲端硬碟但不使用 Google 試算表，但這個應用程式使用的是 Google 試算表，而非 Google 雲端硬碟
在這個程式碼研究室中，我們希望能將眾多技術整合到單一應用程式中，並結合 Google Cloud 中的功能和 API，以更符合實際使用案例的方式。我們鼓勵您運用想像力，並考慮運用 GCP & G Suuite 來解決機構或客戶的挑戰性問題。
課程內容
- 如何將 Google Apps Script 與多項 Google (GCP 和 G Suite) 服務搭配使用
- 如何使用 Google BigQuery 進行大數據分析
- 如何建立 Google 試算表並填入資料
- 如何在試算表中建立新圖表
- 如何將 Google 試算表中的圖表和資料轉移到 Google 簡報
軟硬體需求
- 上網和網路瀏覽器
- Google 帳戶 (G Suite 帳戶可能需要管理員核准)
- JavaScript 基本技巧
- 對 Apps Script 開發作業有所幫助，但並非必要條件
您會使用這個程式碼研究室/教學課程嗎？
您對於 G Suite 開發人員工具與 API 的體驗有什麼評價？
您對於 Apps Script 的使用體驗有什麼特別評價？
您對於 GCP 開發人員工具與 API 的體驗有什麼評價？
現在您已瞭解這個程式碼研究室的內容，接下來該怎麼做呢？
- 取得現有的 Apps Script-BigQuery 範例
- 從該範例中，瞭解如何向 BigQuery 傳送查詢並取得結果。
- 建立 Google 試算表，並將 BigQuery 的結果填入工作表
- 稍微修改程式碼，稍微修改傳回的資料並將其加入工作表
- 使用 Apps Script 中的試算表服務，針對 BigQuery 的資料建立圖表
- 使用「簡報」服務建立新的投影片簡報
- 針對所有新投影片自動建立的預設投影片投影片，新增標題和副標題
- 使用資料表格建立新投影片，然後將工作表的資料儲存格匯入其中
- 新增其他投影片並新增試算表圖表
讓我們先從一些有關 Apps Script、BigQuery、試算表和簡報的背景資訊開始說明。
Google Apps 指令碼和 BigQuery
Google Apps Script 是 G Suite 開發平台，與 Google REST API 相比，它比更高等級更高。這是一種無伺服器開發和應用程式託管環境，可供各種開發人員技能的層級使用。簡單來說，「Apps Script」是一種無伺服器的 JavaScript 執行階段，可用於 G Suite 自動化、擴充功能和整合作業。
它類似於伺服器端的 JavaScript，與 Node.js 類似，但會專注於與 G Suite 和其他 Google 服務緊密整合，而不是以非同步的非同步事件驅動應用程式託管。其中還有一個開發環境，可能和您習慣的環境完全不同。Apps Script 可讓您：
- 使用瀏覽器的程式碼編輯器進行開發，但若使用
clasp(適用於 Apps Script 的指令列工具)，則可選擇在本機開發應用程式 - 以特別的 JavaScript 程式碼編寫程式碼，以便存取 G Suite 和其他 Google 服務或外部服務 (透過 Apps Script
URLfetch或Jdbc服務) - Apps Script 會為您處理授權碼，因此請避免編寫授權碼
- 無需託管您的應用程式，應用程式可在 Google 的雲端伺服器上運作及執行
注意：大部分是本程式碼研究室介紹的一項功能，可教您瞭解 Apps Script。有很多線上資源可協助您解決這個問題。官方說明文件也提供了快速入門導覽課程、教學課程和影片的總覽。最後，別忘了先完成 Apps Script 入門程式碼研究室；開始本活動前，請務必完成這個步驟。
Apps Script 介面與其他 Google 技術搭配使用的方式有兩種：
- 內建/原生服務
- 進階服務
內建服務提供存取 G Suite 或 Google 產品資料的概略方法，以及其他實用公用程式方法。「進階服務」只是 G Suite 或 Google REST API 的精簡包裝函式。進階服務可提供 REST API 的完整功能，而且通常比內建服務多更多，但需要更複雜的程式碼 (而且仍比 REST API 本身更加簡單)。此外，您必須先為指令碼專案啟用進階服務，才能使用該服務。
可以的話，開發人員會偏好使用內建服務，因為比起進階服務，這類服務更易於使用，且執行起來更繁重。不過，部分 Google API 並未內建服務，因此可能是進階服務的唯一選擇。Google BigQuery 就是這個例子的一種... 沒有可用的內建服務，但有「BigQuery 進階服務」。(而不是「沒有」服務，對吧？)如果您是 BigQuery 新手，可以運用這項 GCP 服務針對極大的資料資料庫進行簡單 (或複雜) 查詢 (例如以數 TB 為單位)，但您仍可在數秒內提供結果。
從 Apps Script 存取 Google 試算表和簡報
有別於 BigQuery，「Google 試算表」和「簡報」皆內建服務 (以及進階服務，僅可用於存取 API 中所列的功能)。查看內建程式碼的 Google 試算表和 Google 簡報服務。當然，我們也提供進階服務的專用文件，分別適用於試算表和簡報。
引言
我們得先完成第一項任務，才能順利完成這個程式碼研究室。事實上，一旦在這裡完成，您就完成整個程式碼研究室的一半。細分成幾個子區段，您需要執行以下所有動作：
- 建立新的 Google Apps Script 專案
- 啟用 BigQuery 進階服務的存取權
- 前往開發編輯器並輸入應用程式原始碼
- 進行應用程式授權程序 (OAuth2)
- 執行會傳送要求至 BigQuery 的應用程式
- 查看利用 BigQuery 結果建立的全新 Google 試算表
設定
- a) 前往
script.google.com建立新的 Apps Script 專案。G Suite 產品系列的功能各不相同，另外，新建專案的方式也可能因您使用的版本而異。如果您剛開始使用 Gmail 帳戶並剛著手開發專案，就會看到一個空白畫面以及建立第一個專案的按鈕：
b) 否則，您會在畫面左上方看到所有的專案和一個大型 +[新增] 按鈕，因此請按一下該按鈕。
c) 如果以上皆非，您的畫面可能如下所示。如果是的話，請查看左上角的「漢堡」選單圖示，然後選取 [+ 新增指令碼]。
工具是「clasp」，具體來說，您會執行「clasp create」指令。
e) 建立新指令碼專案的最終方法就是前往捷徑連結：https://script.google.com/create。
- 無論採用新技術來開發新專案，你應該都可以看到 Apps Script 程式碼編輯器的畫面，如下所示：
- 按一下 [File > Save]，然後為專案命名。
- 接下來，您必須建立 Google Cloud Console 專案才能執行 BigQuery 查詢。
- 建立新專案，為專案命名並選取帳單帳戶，然後按一下 [建立]。
- 專案建立完畢後，網頁右上角會顯示通知。按一下 [Create Project: <Project Name>] 項目以開啟專案。
- 按一下左上角的選單圖示
，然後前往 [API 與服務] > [憑證]。按一下 [OAuth 同意畫面] 分頁標籤 ([直接連結])。 - 在「Application name」(應用程式名稱) 欄位中輸入「quog Data Codelab」；按一下底部的 [Save] 按鈕。
- 按一下右上角的三點圖示
展開選單，然後選取 [專案設定] ([直接連結])。 - 複製「專案編號」之下所列的值。(另外還有一個產品 ID 欄位，稍後會用在程式碼研究室中)。
- 返回 App Script 編輯器，按一下 [Resources > Cloud Platform project] (資源 > Cloud Platform 專案)。
- 在文字方塊中輸入專案編號，然後按一下 [Set Project] (設定專案)。系統顯示提示訊息時，按一下 [確認]。
- 完成後，按一下 [關閉] 按鈕，即可關閉對話方塊。
- 現在您已完成新的專案設定，現在需要啟用 BigQuery 進階服務，因此請下拉「Resources -> Advanced Google Services」，然後在 BigQuery API 的切換部分展開。
- 最下層的注意事項：「這類服務也必須在「Google Cloud Platform API 資訊主頁」中啟用，因此請按一下連結來開啟另一個瀏覽器分頁，前往開發人員控制台或「devconsole」。
- 在 devconsole 中，按一下頂端的 [+啟用 API 和服務] 按鈕，搜尋「BigQuery」並選取 BigQuery API (不是 BigQuery Data Transfer API)，然後按一下 [啟用] 開啟該 API。請勿關閉這個瀏覽器分頁。
注意：啟用 API 後，你可能會在這個頁面中看到「&tt;」使用這個 API 的附註 - 返回程式碼編輯器瀏覽器分頁的 [進階 Google 服務] 選單，然後按一下 [確定] 關閉對話方塊，並離開程式碼編輯器。按一下頂端的專案名稱，然後視需求為專案命名，例如「BigQuery 示範」，或命名為類似「最終 1 英里」。
現在，您可以開始輸入應用程式代碼、完成授權程序，讓這個應用程式開始運作。
上傳並執行應用程式
- 複製以下方塊中的程式碼，然後貼到程式碼編輯器中，貼上該程式碼：
// 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/googleworkspace/apps-script-samples/blob/main/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());
}
現在請儲存您剛建立的檔案，但將該檔案從「Code.gs」重新命名為「bq-sheets-slides.js」。這個程式碼有什麼用途？我們先前曾得知這個查詢會查詢 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
這項查詢會參考 Shakespeare 的作品 (屬於 BigQuery 的公開資料集的一部分)，並依據其所有作品產生前 10 大最常出現的字詞，並依熱門程度遞減排序。想像一下，這樣做可以 (手動) 完成，而且你應該對 BigQuery 的實用程度有所瞭解。
- 我們幾乎可以開始嘗試看看這項功能了。如這個程式碼片段頂端附近，您必須輸入有效的專案 ID，因此我們必須將這個 ID 加進應用程式程式碼中。如要進行這項操作，請返回具有開發人員控制台頁面的瀏覽器視窗或分頁。(提醒您，請保持開啟，不要記得？)
- Google 帳戶顯示圖片左上方的下拉式選單是「選取器」選單 (
)。按一下該圖示並選取 [專案設定]。你會看到專案名稱、ID 和編號。複製專案 ID，並將 bq-sheets-slides.js頂端的PROJECT_ID變數設為透過開發人員控制台取得的值。注意：如果選單選取工具無法固定且無法運作，請重新載入頁面。 if陳述式旨在防止應用程式在沒有專案 ID 的情況下繼續執行。新增檔案、儲存檔案，然後到選單列選取 [執行] > [執行函式 > RunQuery]，接著點選 [查看權限] 對話方塊，這個應用程式未經驗證，下方是 GIF 動畫 (適用於其他應用程式)：
- 要求檢視權限後，您會看見一個新的對話方塊，如上所示。選擇你要執行指令碼的正確 Google 帳戶，選取 [進階]，向下捲動，然後按一下 [前往 <專案名稱>> (不安全)]，以便進入 OAuth2 應用程式授權畫面。(進一步瞭解驗證程序，瞭解這個畫面為何在您下方與 OAuth2 授權對話方塊中同時顯示)。
注意：您授權應用程式後，不必每次執行時都重複執行這個程序。除非您進入本教學課程的「工作 3」，否則會再次看到這個對話方塊，要求取得權限以建立和管理 Google 簡報。 - 在 OAuth2 對話方塊上按一下 [允許] 後，指令碼就會開始執行...您會在頂端看到一個黃色黃色的對話方塊。測試過程相當快速，因此你可能會發現目前並未執行完畢或執行作業已完成。
- 這個對話方塊會在完成後消失，所以如果畫面上沒有顯示這個選項，表示問題可能已完成，請前往 Google 雲端硬碟 (
drive.google.com) 尋找新的「Google 試算表」，內容是「莎士比亞所有作品」中的常見字詞，或者您為QUERY_NAME變數指派的任何字詞：
- 開啟試算表後，您會看到 10 個資料列和其總計資料，並以遞減順序排序：
工作 1 摘要
辨識出了什麼... 您執行了一些程式碼來查詢「莎士比亞」的所有作品 (非大量資料，但當然比其他文字更簡潔，您可以輕易在每段時間中仔細瀏覽每篇文字；管理這類字詞的數量，並依照排列順序以遞減方式排序。您不僅是要求 BigQuery 代您進行分析，還能利用 Google Apps 的 Apps Script 內建的服務將這些資料放在網頁中，方便日後使用。
您可以在上述程式碼研究室的 GitHub 存放區step1中，前往 bq-sheets-slides.jsgithub.com/googlecodelabs/bigquery-sheets-slidesPROJECT_ID 找到「bq-sheets-slides.js」(我們選擇的檔案名稱) (不含 PROJECT_ID 是實際專案 ID 的) 程式碼。程式碼的靈感是來自 BigQuery 進階服務網頁上的原始範例。這個查詢執行的查詢略有不同，但是莎士比亞最常用的字詞，長度至少 10 個字元。您也可以在 GitHub 存放區中查看此範例。
如果您想使用其他方式查詢 Shakespeare 公司或其他公開資料表的資料，請查看這個網頁和這個網頁。無論您使用哪一種查詢，您可以隨時在 BigQuery 主控台中測試查詢，然後再於 Apps Script 執行查詢。BigQuery 的使用者介面可供開發人員前往 bigquery.cloud.google.com 使用。舉例來說，以下查詢使用 BigQuery 使用者介面來呈現我們查詢的內容：
雖然以上步驟使用 Apps Script 的程式碼編輯器，但您也可以選擇透過指令列在本機開發應用程式。如有需要，您可以建立一個名為 bq-sheets-slides.js 的指令碼，然後將上方的程式碼貼到指令碼中，再使用 clasp push 指令上傳至 Google。(如果您之前還未填寫，這裡也提供了 clasp 連結和使用方式)。
runQuery() 的用途是與 BigQuery 交談，並將結果傳送到工作表。現在，我們必須使用資料建立圖表。讓我們建立一個名為 createColumnChart() 的新函式，用於呼叫工作表的 newChart() 方法可達到這個目的。
- 建立圖表。請在
runQuery()後，將下方精選的createColumnChart()新增至bq-sheets-slides.js。這項工具會取得包含資料的工作表，並要求包含所有資料的柱狀圖。資料範圍從儲存格 A2 開始，因為第一列包含欄標題，而不是資料。
/**
* 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()需要試算表物件，因此必須更新runQuery()以傳回spreadsheet物件，這樣就可以將其傳遞給createColumnChart()。記錄成功建立 Google 試算表後，請在runQuery()末端的記錄行後方傳回spreadsheet物件，該行應在記錄行後方：
Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
// NEW: 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);
}- 讓程式碼更易於使用。您採取了兩個重要步驟：傳回試算表物件以及建立駕駛函式。如果同事想要重複使用
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()); // MOVED HERE
createColumnChart(spreadsheet);
}透過上述的這些變更 (再次出現 PROJECT_ID 除外)，您的 bq-sheets-slides.js 現在應該會如下所示 (也可以在 GitHub 存放區的 step2 資料夾中找到)：
// 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/googleworkspace/apps-script-samples/blob/main/advanced/bigquery.gs
*
* @returns {Sheet} Returns a sheet with results
* @see http://developers.google.com/apps-script/reference/spreadsheet/sheet
*/
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);
}
/**
* 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);
}儲存檔案，然後在程式碼編輯器頂端向上滑動，接著執行 createBigQueryPresentation() 而非 runQuery()。執行完畢後，您會得到另一份「Google 試算表」，但這次的圖表旁邊會出現一個圖表：
程式碼研究室的最後一個部分包括建立新的「Google 簡報」投影片，並在標題投影片中填入標題和副標題，然後加入 2 個新投影片，分別用於每個資料儲存格和圖表。
- 建立投影片。投影片中的所有工作將於
createSlidePresentation()進行，我們將在createColumnChart()後加入bq-sheets-slides.js。請先建立新的投影片，然後將標題和副標題加入所有新簡報的預設標題投影片中。
/**
* 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 試算表匯入新的簡報投影片。請將這段程式碼加進函式：
// 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()的最後一行新增程式碼，以完成上述動作：
// NEW: Return chart object for later use
return chart;
}- 更新
createBigQueryPresentation()。由於createColumnChart()會傳回圖表，因此我們必須將該圖表儲存至變數，再將試算表和圖表同時傳遞至createSlidePresentation()。由於我們會記錄新建立的試算表網址，因此也會記錄新投影片簡報的網址。更新您的createBigQueryPresentation()，使其如下所示：
/**
* 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); // UPDATED
var deck = createSlidePresentation(spreadsheet, chart); // NEW
Logger.log('Results slide deck created: %s', deck.getUrl()); // NEW
}- 儲存並再次執行
createBigQueryPresentation()。不過在執行之前，你必須辨識應用程式是否具備一組使用者的權限，才能查看及管理你的 Google 簡報。這項權限允許後，就會像以往一樣執行。
- 除了已建立的工作表外，您也應該收到包含 3 張投影片 (標題、資料表、資料圖表) 的新簡報，如下所示：
恭喜！現在，您已經建立了一個應用程式，可藉由執行 Google BigQuery 要求來查詢其中一個公開資料集、建立新的 Google 試算表來儲存結果、根據該擷取的資料新增圖表，最後建立 Google 簡報來呈現結果和試算表中的圖表。
這是您的技術層面。大致來說，您從龐大的資料分析，到可向相關人士呈現的一切內容，而且都完全採用程式碼設計。我們希望本範例能讓您進行此範例，並根據自己的專案進行自訂。完成這個程式碼研究室後，我們會提供一些建議，協助您進一步改進範例應用程式。
在上一項工作 (包含 PROJECT_ID 除外) 中，除了上述變更外，您的 bq-sheets-slides.js 現在看起來應該會如下所示 (也可以在 GitHub 存放區的 final 資料夾中找到)：
bq-sheets-slides.js
/**
* 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/googleworkspace/apps-script-samples/blob/main/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());
}在這個程式碼研究室中，並未扮演角色，而是「第二個檔案」；在這個程式碼研究室中，這是 Apps Script 資訊清單檔案「appsscript.json」。如要存取程式碼，請前往程式碼編輯器瀏覽器分頁，並在頂端的選單中選取 [檢視 > 顯示資訊清單檔案]。內容應如下所示：
應用程式指令碼
{
"timeZone": "America/Los_Angeles",
"dependencies": {
"enabledAdvancedServices": [{
"userSymbol": "BigQuery",
"serviceId": "bigquery",
"version": "v2"
}]
},
"exceptionLogging": "STACKDRIVER"
}資訊清單檔案是系統層級的設定檔，可讓 Apps Script 瞭解您的應用程式提供哪些執行環境。遮蓋資訊清單檔案的內容不在這個程式碼研究室的範圍內，但您可以瞭解該檔案的功能。
以下提供額外的資源，可協助您深入瞭解本程式碼研究室所涵蓋的相關內容，並探索透過程式存取 Google 開發人員工具的其他方法。我們的目標是讓這個程式碼研究室與存放區保持同步。
這個應用程式的資源
- 這個程式碼研究室連結： g.co/codelabs/bigquery-sheets-slides
- 原始碼 repo
- 開發人員影片 (和) 網誌文章
- Google Cloud Next '18 講座
說明文件
- Google Apps Script 說明文件網站
- Apps Script 試算表服務
- Apps Script 簡報服務
- Apps Script BigQuery 進階服務
相關影片和一般影片
- 有其他 Google (應用程式) 秘密嗎？(Apps Script 簡介影片)
- 從試算表存取「Google 地圖」？！？(影片)
- Google Apps Script 影片庫
- Launchpad Online 系列影片 (前身為...)
- G Suite Dev Show 的系列影片
相關新聞和一般新聞
- BigQuery 適用的 Google 試算表資料連接器 (公告)
- Google BigQuery 已與 Google 雲端硬碟整合 (link1、link2)
- Google 開發人員網誌
- Google Cloud Platform 網誌
- Google Cloud 的大數據與網站簡介
- Google Developers Twitter (@GoogleDevs)
- G Suite 開發人員網誌
- G Suite 開發人員 Twitter (@googleworkspace)
- G Suite 開發人員每月電子報
其他程式碼研究室
入門
- [REST API] G Suite 與 Google API (Drive API)
- [應用程式製作工具] 在應用程式製作工具中建立資料庫網頁應用程式
進階
- [Apps Script] CLASP Apps Script 指令列工具
- [Apps Script] Gmail 外掛程式
- [Apps Script] Google 文件外掛程式 (& GCP Natural Language API)
- [Apps Script] Hangouts Chat 機器人架構
- [REST API] 自訂報告工具 (Sheets API)
- [REST API] 適用於 Hubhu 授權 BigQuery 分析工具的自訂投影片產生器 (Slides + BigQuery API)
參考應用程式
- Markdown to-Google 簡報轉換器 (Slides API)
以下將說明各種「程式碼挑戰」，也就是能夠強化或增強我們在本程式碼研究室中建構的範例。這份清單不一定包含所有內容，但仍有一些靈感建議，協助您進行後續步驟。
- 應用程式。不想使用 JavaScript 或 Apps Script 設下的限制嗎？使用 Google BigQuery、試算表和簡報的 REST API，將此應用程式轉移至您慣用的程式設計語言。
- BigQuery：您可以對莎士比亞資料集的不同查詢進行實驗... 也許會找到您感興趣的查詢。您可以在原本的 Apps Script BigQuery 範例應用程式中找到其他查詢範例。
- BigQuery：建議您使用其他 BigQuery 的其他公開資料集... 尋找可能更有意義的資料集。
- BigQuery：我們先前提過，您可以嘗試查詢其他與 Shakespeare 作品或其他公開資料表有關的查詢，也想要轉貼這個網頁和這個網頁。
- 試算表。您可以測試其他圖表類型，
- Sheets &&; BigQuery。轉動各別的表格...您的試算表可能已有大量資料集。BigQuery 在 2016 年推出了一項功能，讓開發人員能使用試算表來當做資料來源 (詳情請參閱一篇和兩則網誌文章)。
- 簡報。在系統產生的簡報中新增其他投影片，例如與大數據分析相關的圖片或其他資產。請參閱簡報內建服務指南，瞭解如何開始使用這項功能。
- G Suite。在 Apps Script 尋找其他 G Suite 或 Google 內建服務 (例如Gmail、Google 雲端硬碟、日曆、文件、地圖、Analytics (分析) 和 YouTube 等，以及其他進階服務。詳情請參閱內建和進階服務的參考總覽。