程式碼研究室簡介
1. 簡介
歡迎來到「使用 Google 試算表學習 Apps Script 基礎知識」程式碼研究室播放清單的第五部分。本程式碼研究室會說明如何使用 Apps Script 中的 試算表服務，繪製資料集圖表。您也會瞭解如何使用 Google 簡報服務，將圖表匯出至 Google 簡報中的新簡報。
課程內容
- 如何使用 Apps Script 建構折線圖。
- 如何將圖表匯出至新的 Google 簡報。
- 如何在 Google 試算表中向使用者顯示對話方塊。
事前準備
這是「Google 試算表 Apps Script 基礎知識」播放清單的第五個程式碼研究室。開始本程式碼研究室前，請務必先完成下列程式碼研究室：
軟硬體需求
- 瞭解本播放清單先前程式碼研究室中探討的基礎 Apps Script 主題。
- 熟悉 Apps Script 編輯器的基本概念
- 熟悉 Google 試算表的基本概念
- 可讀取試算表 A1 標記
- 熟悉 JavaScript 及其
String類別
2. 設定
繼續操作前，請先準備含有資料的試算表。和以往一樣，我們為這些練習提供可複製的資料表。按照下列步驟操作：
- 按一下這個連結複製資料表，然後點選「建立副本」。新的試算表會放在 Google 雲端硬碟資料夾中，並命名為「日期和美元匯率的副本」。
- 按一下試算表標題，將「日期和美元匯率的副本」變更為「日期和美元匯率」。您的工作表應如下所示，其中包含不同日期美元匯率的一些基本資訊：
- 如要開啟指令碼編輯器，請依序點選「擴充功能」>「Apps Script」。
為節省您的時間，我們已在這份試算表中加入一小段程式碼，方便您設定自訂選單。您可能在開啟試算表副本時看到這個選單：
有了這個試算表和專案，您就可以開始進行程式碼研究室。請前往下一節，開始瞭解圖表和時間觸發條件。
3. 使用 Apps Script 在 Google 試算表中建立圖表
假設您想設計特定圖表，將資料集視覺化。您可以使用 Apps Script 建立、編輯及插入 Google 試算表中的圖表。如果圖表位於試算表中，則稱為「內嵌圖表」。
圖表可用來呈現一或多個資料序列。內嵌圖表顯示的資料通常來自試算表。通常更新試算表中的資料時，Google 試算表也會自動更新圖表。
您可以使用 Apps Script 從頭建立自訂內嵌圖表，或更新現有圖表。本節將介紹如何使用 Apps Script 和 Spreadsheet 服務，在 Google 試算表中建構內嵌圖表。
導入作業
在資料試算表的副本中，「日期和匯率」資料集會顯示不同日期不同貨幣的匯率 (以 1 美元為單位)。您將實作 Apps Script 函式，製作圖表來呈現部分資料。
按照下列步驟操作：
- 在 Apps Script 編輯器中，將下列函式新增至指令碼專案的
Code.gs指令碼結尾，也就是onOpen()函式後方：
/**
* Creates and inserts an embedded
* line chart into the active sheet.
*/
function createEmbeddedLineChart() {
var sheet = SpreadsheetApp.getActiveSheet();
var chartDataRange = sheet.getRange(
'Dates and USD Exchange Rates dataset!A2:F102');
var hAxisOptions = {
slantedText: true,
slantedTextAngle: 60,
gridlines: {
count: 12
}
};
var lineChartBuilder = sheet.newChart().asLineChart();
var chart = lineChartBuilder
.addRange(chartDataRange)
.setPosition(5, 8, 0, 0)
.setTitle('USD Exchange rates')
.setNumHeaders(1)
.setLegendPosition(Charts.Position.RIGHT)
.setOption('hAxis', hAxisOptions)
.setOption("useFirstColumnAsDomain", true)
.build();
sheet.insertChart(chart);
}
- 儲存指令碼專案。
審查程式碼
您新增的程式碼會實作「日期和美元匯率資料集」選單項目呼叫的函式，以建立基本折線圖。我們來看看程式碼。
前幾行會設定下列三個變數：
sheet：參照目前使用中的工作表。chartDataRange：要顯示的資料範圍。這段程式碼使用 A1 標記，指定範圍涵蓋「Dates and USD Exchange Rates dataset」(日期和美元匯率資料集) 試算表中的 A2 到 F102 儲存格。明確命名工作表，可確保即使目前啟用其他工作表，功能表項目仍能正常運作，因為範圍一律涵蓋資料位置。從第 2 列開始表示我們納入了欄標題，且只會繪製最近 100 個日期 (列) 的圖表。hAxisOptions：基本的 JavaScript 物件，內含程式碼用來設定水平軸外觀的部分設定資訊。具體來說，這會將水平軸文字標籤設為 60 度傾斜，並將垂直格線數量設為 12。
下一行會建立折線圖建立工具物件。Apps Script 中的內嵌圖表是使用建構工具設計模式建構而成。本程式碼研究室不會完整說明這個設計模式，因此目前只要瞭解 Spreadsheet 服務提供多個 EmbeddedChartBuilder 類別即可。如要建立圖表，程式碼會先建立內嵌圖表建構工具物件，使用其方法定義圖表設定，然後呼叫 build() 方法建立最終的 EmbeddedChart 物件。您的程式碼絕不會直接修改 EmbeddedChart 物件，因為所有圖表設定都是透過建構工具類別管理。
Spreadsheet 服務提供父項 EmbeddedChartBuilder 類別，以及從該類別繼承的多個子項建構工具類別 (例如 EmbeddedLineChartBuilder)。子類別可讓 Apps Script 提供僅適用於特定圖表類型的建構工具圖表設定方法。舉例來說，EmbeddedPieChartBuilder 類別提供 set3D() 方法，但僅適用於圓餅圖。
在程式碼中，這行會建立建構工具物件變數 lineChartBuilder：
var lineChartBuilder = sheet.newChart().asLineChart();
程式碼會呼叫 Sheet.newChart() 方法來建立 EmbeddedChartBuilder 物件，然後使用 EmbeddedChartBuilder.asLineChart() 將建構工具類型設為 EmbeddedLineChartBuilder。
接著，程式碼會使用 lineChartBuilder 建構圖表。這部分程式碼只是一連串的方法呼叫，用於定義圖表設定，後面接著 build() 呼叫來建立圖表。如先前的程式碼研究室所述，程式碼會使用方法鏈結，確保程式碼容易閱讀。以下說明方法呼叫的用途：
addRange(range)：定義圖表顯示的資料範圍。setPosition(anchorRowPos, anchorColPos, offsetX, offsetY)：決定圖表在工作表中的位置。在這裡，程式碼會將圖表的左上角插入 H5 儲存格。setTitle(title)：設定圖表標題。setNumHeaders(headers)：決定資料範圍中應視為標題的列數或欄數。程式碼會將資料範圍中的第一列做為標題，也就是說，該列中的文字會做為圖表中個別資料序列的標籤。setLegendPosition(position)：將圖表圖例移至圖表右側。這個方法會使用 Charts.Position 列舉做為參數。setOption(option, value)：設定複雜的圖表選項。在這裡，程式碼會將hAxis選項設為hAxisOptions物件。您可以使用這個方法設定多種選項。如要瞭解各圖表類型的選項和可能值，請參閱 Charts API 圖表庫。舉例來說，您可為折線圖設定的選項，請參閱「折線圖設定選項」。setOption(option, value)方法是進階主題，建議您先熟悉如何使用 Apps Script 建立圖表，再考慮使用這個方法。build()：使用上述設定建立並傳回EmbeddedChart物件。
最後，程式碼會呼叫 Sheet.insertChart(chart)，將建構的圖表放入有效工作表。
結果
如要查看格式化函式的實際運作情形，請按照下列步驟操作：
- 如果尚未儲存指令碼專案，請在 Apps Script 編輯器中儲存。
- 按一下「Present dataset > Chart "Dates and USD Exchange Rates dataset"」選單項目。
指令碼現在會在資料右側放置新圖表：
恭喜！您已使用 Apps Script 建立內嵌折線圖。下一節將說明如何將圖表匯出至 Google 簡報。
4. 將圖表匯出至 Google 簡報
Apps Script 的一大優勢在於可讓您輕鬆將資料從一個 Google Workspace 應用程式移至另一個應用程式。這些應用程式大多都有專屬的「應用程式指令碼」服務，類似於 試算表服務。舉例來說，Gmail 有 Gmail 服務，Google 文件有 Document 服務，Google 簡報則有 Slides 服務。有了這些內建服務，您就能從一個應用程式擷取資料、處理資料，然後將結果寫入另一個應用程式。
本節將說明如何將 Google 試算表中的所有內嵌圖表匯出至新的 Google 簡報。您也會看到在 Google 試算表中顯示使用者自訂訊息的兩種方式。
導入作業
您將在此實作 Present dataset > Export charts to Slides 選單項目呼叫的函式。按照下列步驟操作：
- 在 Apps Script 編輯器中，將下列函式新增至指令碼專案的
Code.gs指令碼結尾，也就是createEmbeddedLineChart()函式後方：
/**
* Create a Slides presentation and export
* all the embedded charts in this spreadsheet
* to it, one chart per slide.
*/
function exportChartsToSlides() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Fetch a list of all embedded charts in this
// spreadsheet.
var charts = [];
var sheets = ss.getSheets();
for (var i = 0; i < sheets.length; i++) {
charts = charts.concat(sheets[i].getCharts());
}
// If there aren't any charts, display a toast
// message and return without doing anything
// else.
if (charts.length == 0) {
ss.toast('No charts to export!');
return;
}
// Create a Slides presentation, removing the default
// title slide.
var presentationTitle =
ss.getName() + " Presentation";
var slides = SlidesApp.create(presentationTitle);
slides.getSlides()[0].remove();
// Add charts to the presentation, one chart per slide.
var position = {left: 40, top: 30};
var size = {height: 340, width: 430};
for (var i = 0; i < charts.length; i++) {
var newSlide = slides.appendSlide();
newSlide.insertSheetsChart(
charts[i],
position.left,
position.top,
size.width,
size.height);
}
// Create and display a dialog telling the user where to
// find the new presentation.
var slidesUrl = slides.getUrl();
var html = "<p>Find it in your home Drive folder:</p>"
+ "<p><a href=\"" + slidesUrl + "\" target=\"_blank\">"
+ presentationTitle + "</a></p>";
SpreadsheetApp.getUi().showModalDialog(
HtmlService.createHtmlOutput(html)
.setHeight(120)
.setWidth(350),
"Created a presentation!"
);
}
- 儲存指令碼專案。
審查程式碼
這組代碼可能比預期短。我們將程式碼分成五個部分，逐一瞭解其作用：
1：取得圖表
前幾行會搜尋已開啟的試算表，找出所有內嵌圖表，並將這些圖表收集到 charts 陣列中。這些程式碼行會使用 Spreadsheet.getSheets() 方法和 Sheet.getCharts() 方法，取得工作表和圖表的清單。JavaScript Array.concat() 方法用於將每個工作表的圖表清單附加至 charts。
2：確認有圖表可供匯出
這段程式碼會驗證是否有任何圖表要匯出。我們想避免製作空白簡報，因此如果沒有圖表，程式碼會改用 Spreadsheet.toast(message) 建立 Toast 訊息。這是小型「快速查看」對話方塊，會彈出在 Google 試算表右下角，停留幾秒後消失：
如果沒有要匯出的圖表，程式碼會建立快顯訊息，然後結束，不會執行任何其他動作。如有要匯出的圖表，程式碼會在接下來的幾行中繼續建立簡報。
3：製作簡報
系統會建立 presentationTitle 變數，用來保留新簡報的檔案名稱。系統會將其設為試算表名稱，並在結尾串連「 Presentation」。然後，程式碼會呼叫 Slides 服務方法 SlidesApp.create(name) 來建立簡報。
新簡報會包含一張空白投影片。我們不希望在簡報中看到這個項目，因此程式碼會使用 Presentation.getSlides() 和 Slide.remove() 移除該項目。
4：匯出圖表
在下一節中，程式碼會定義 JavaScript 物件的 position 和 size，以設定匯入的圖表在投影片中的位置，以及圖表的大小 (以像素為單位)。
這段程式碼會對圖表清單中的每個圖表進行迴圈。系統會為每張圖表建立 newSlide，並使用 Presentation.appendSlide() 將投影片新增至簡報結尾。Slide.insertSheetsChart(sourceChart, left, top, width, height) 方法可用於將圖表匯入投影片，並指定 position 和 size。
5：分享簡報位置
最後，程式碼必須告知使用者新簡報的位置，最好提供可點選的連結，方便使用者開啟簡報。為此，程式碼會使用 Apps Script 的 HTML service 建立自訂模式對話方塊。強制回應對話方塊 (在 Apps Script 中也稱為「自訂對話方塊」) 是顯示在 Google 試算表介面上的視窗。顯示自訂對話方塊時，使用者無法與 Google 試算表互動。
如要建立自訂對話方塊，程式碼需要定義內容的 HTML。這項資訊會提供在 html 變數中。內容包含簡短段落和超連結。超連結是 presentationTitle 變數，連結至 Presentation.getUrl() 提供的簡報網址。超連結也會使用 target="_blank" 屬性，因此簡報會在新的瀏覽器分頁中開啟，而不是在對話方塊中開啟。
HTML 會由 HtmlService.createHtmlOutput(html) 方法剖析為 HtmlOutput 物件。程式碼可透過 HtmlOutput 物件，使用 HtmlOutput.setHeight(height) 和 HtmlOutput.setWidth(width) 設定自訂對話方塊的大小。
建立 htmlOutput 後，程式碼會使用 Ui.showModalDialog(htmlOutput, title) 方法顯示具有指定標題的對話方塊。
結果
您已實作第二個選單項目，現在可以看看實際運作情況。如要測試 exportChartsToSlides() 函式，請按照下列步驟操作：
- 如果尚未儲存指令碼專案，請在 Apps Script 編輯器中儲存。
- 開啟試算表，然後按一下「呈現資料集」>「圖表『日期和美元匯率資料集』」選單項目，建立要匯出的圖表。系統會將其錨定在目前工作表的 H5 儲存格。
- 按一下「呈現資料集」>「將圖表匯出至簡報」選單項目。系統可能會要求你重新授權指令碼。
- 您應該會看到指令碼處理要求，並顯示自訂對話方塊。
- 如要開啟新的 Google 簡報，請按一下「Dates and USD Exchange Rates Presentation」(日期和美元匯率簡報) 連結：
您也可以在試算表中新增更多圖表，然後重新選取選單項目，建立含有多張投影片的簡報。
您現在可以將 Google 試算表中的圖表匯出至 Google 簡報。您也可以撰寫程式碼來建立自訂對話方塊。
您已完成本程式碼研究室的最後一項練習。請前往下一節複習所學內容。
5. 結論
恭喜！您已完成本程式碼研究室，以及「Google 試算表 Apps Script 基礎知識」程式碼研究室播放清單中的所有內容。您可以運用本播放清單中學到的原則，擴展試算表體驗，並探索 Apps Script 的功能。
您覺得這個程式碼研究室實用嗎？
目前所學內容
- 如何使用 Apps Script 建構內嵌折線圖。
- 如何在 Google 試算表中向使用者顯示訊息和自訂對話方塊。
- 如何將圖表匯出到新的 Google 簡報。
後續步驟
您已順利完成這份播放清單。不過，您還可以進一步瞭解 Apps Script。
歡迎參考下列資源：
- Apps Script 開發人員說明文件
- Apps Script 指南
- Apps Script 參考說明文件
- 在 StackOverflow 上提問 Apps Script 相關問題
- GitHub 上的 Apps Script 程式碼範例
祝您編寫指令碼愉快！