Google 試算表的 Apps Script 基礎概念 #5：在 Google 簡報中以圖表呈現資料

1. 簡介

歡迎來到 Apps Script 基礎介紹的第五部分，內含 Google 試算表的程式碼研究室播放清單。本程式碼研究室可教您如何使用 Apps Script 中的試算表服務，以圖表呈現資料集。以及如何使用簡報服務將圖表匯出至 Google 簡報的新簡報。

課程內容

  • 如何使用 Apps Script 建立折線圖。
  • 如何將圖表匯出至新的簡報。
  • 如何在試算表中向使用者顯示對話方塊。

事前準備

這是「Google Apps 基礎課程：Apps 基礎入門」播放清單中的第五個程式碼研究室。開始進行這個程式碼研究室之前，請務必完成先前的程式碼研究室：

  1. 巨集和自訂函式
  2. 試算表、試算表和範圍
  3. 使用資料
  4. 資料格式設定

軟硬體需求

  • 上一次在這個播放清單研究室中探討的 Apps Script 基本主題。
  • 熟悉 Apps Script 編輯器的基本功能
  • Google 試算表的基本概念
  • 可讀取試算表 A1 標記法
  • 熟悉 JavaScript 及其 String 類別

2. 設定

如要繼續操作，請先建立一份內含部分資料的試算表。和以往一樣，我們已提供一份工作表供您複製。步驟如下：

  1. 按一下這個連結即可複製資料工作表，然後按一下 [建立副本]。新的試算表會存放在您的 Google 雲端硬碟資料夾中，並命名為「日期和美元匯率副本」。
  2. 按一下試算表標題，然後將標題從「日期和美元匯率」變更為「日期和美元匯率」。您的工作表看起來應該像這樣，其中包含一些不同美元匯率的不同基本資訊：

45a3e8814ecb07fc.png

  1. 如要開啟指令碼編輯器，請按一下 [擴充功能] > [Apps 指令碼]。

為了節省時間，我們在這份試算表中加入了一些自訂選單，供您快速自訂。您可能在系統開啟試算表副本時看到這個選單：

9b9caf6c1e9de34b.png

有了這份試算表和專案，您就可以開始使用程式碼研究室。請參閱下一節，開始瞭解圖表和時間觸發觸發條件。

3. 使用 Apps Script 在 Google 試算表中建立圖表

假設您要設計一個特定圖表以視覺化方式呈現資料集，您可使用 Apps Script 建立、編輯圖表，並將圖表插入 Google 試算表。如果試算表包含圖表，則稱為內嵌圖表。

圖表可用來以視覺化方式呈現一或多個資料序列。如果是內嵌圖表，相關資料則通常來自試算表。更新試算表中的資料通常會使 Google 試算表自動更新。

您可以使用 Apps Script 建立自訂的內嵌內嵌圖表，或是更新現有的圖表。本節說明如何使用 Google Apps 試算表和 Spreadsheet 服務在 Google 試算表中建立內嵌圖表的基本概念。

導入作業

這份資料試算表中的「日期和匯率」資料集會顯示不同貨幣為不同貨幣的匯率 (1 美元換算成美元)。您將要執行 Apps Script 函式，以圖表方式呈現此資料的一部分。

步驟如下：

  1. 使用 Apps Script 編輯器時，在 onOpen() 函式的後方，將以下函式加到指令碼專案的 Code.gs 指令碼結尾：
/**
 * 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);  
}
  1. 儲存指令碼專案。

程式碼審查

您新增的程式碼會實作「圖表日期與美元匯率資料集」選單所呼叫的函式，建立基本折線圖。我們來檢查程式碼。

第一行會設定下列三個變數：

  • sheet：目前有效工作表的參照。
  • chartDataRange：系統要視覺化呈現的資料範圍。程式碼使用 A1 標記法指定名稱為 Dates and USD Exchange Rates 資料集工作表中的儲存格 A2 到 F102。專門為工作表命名時，即使其他工作表處於有效狀態，我們還是會確保選單項目能夠正常運作，因為範圍一律會涵蓋資料位置。第 2 行開始意味於我們已包含列標頭，我們只將 100 個最近的日期（行）顯示。
  • hAxisOptions：基本 JavaScript 物件，包含一些用來設定橫軸外觀的部分設定資訊。具體來說，學生將橫軸文字標籤設為 60 度的斜邊，並將垂直格線的數目設為 12。

下一行會建立折線圖製作工具物件。Apps Script 的內嵌圖表是使用 Builder 設計模式所建構。此程式碼模式的完整說明不在本程式碼研究室的範圍內，因此目前只瞭解 Spreadsheet 服務提供了數種 EmbeddedChartBuilder 類別。如要建立圖表，您的程式碼首先會建立嵌入圖表建構工具物件，並使用該方法定義圖表設定，然後呼叫 build() 方法來建立最終的 EmbeddedChart 物件。您的程式碼永遠不會直接修改 EmbeddedChart 物件，因為所有圖表設定都是透過建構工具類別管理。

試算表服務提供上層 EmbeddedChartBuilder 類別和多個繼承此類別的子項建構工具類別 (例如 EmbeddedLineChartBuilder)。子類別可讓 Apps Script 提供僅適用於特定圖表類型的建構工具圖表設定方法。舉例來說，EmbeddedPieChartBuilder 類別提供的 set3D() 方法僅適用於圓餅圖。

在您的程式碼中，此行會建立建構工具物件變數 lineChartBuilder：

var lineChartBuilder = sheet.newChart().asLineChart();

程式碼會呼叫 Sheet.newChart() 方法來建立 EmbeddedChartBuilder 物件，然後使用 EmbeddedChartBuilder.asLineChart() 將建構工具類型設為 EmbeddedLineChartBuilder。

接下來，程式碼會使用 lineChartBuilder 建構圖表。這部分只是關於定義圖表設定的一系列呼叫方法，後面則是 build() 呼叫來建立圖表。正如先前的程式碼研究室所見，程式碼採用方法鏈，讓程式碼看起來容易理解。以下是方法呼叫的執行方式：

最後，程式碼會呼叫 Sheet.insertChart(chart)，將建立的圖表放入有效工作表。

結果

您可以按照下列步驟，查看格式設定函式：

  1. 如果尚未儲存指令碼專案，請先前往 Apps Script 編輯器儲存。
  2. 按一下 [簡報資料集] (圖表) > [日期和美元匯率資料集] 選單項目。

您的指令碼現在會將新圖表放置在資料右側：

bbf856699b6d2b45.gif

恭喜！您已使用 Apps Script 建立內嵌折線圖。下一節將說明如何將圖表匯出至 Google 簡報。

4. 將圖表匯出至簡報

Apps Script 的其中一項強大功能，可讓你輕鬆在不同 Google Workspace 應用程式之間移動資料。其中大部分應用程式都有專屬的 Apps Script 服務，類似試算表服務。舉例來說，Gmail 有 Gmail 服務，Google 文件服務提供 Google 文件服務，Google 簡報則提供簡報服務。透過這些內建服務，您可從單一應用程式中擷取資料、處理資料，然後將結果寫入其他應用程式。

在這個部分中，您將瞭解如何將 Google 試算表中的所有內嵌圖表匯出至新的 Google 簡報。您也會在 Google 試算表中以兩種方式顯示使用者的自訂訊息。

導入作業

在這裡，您會導入展示資料集 > 將圖表匯出至簡報選單項目所呼叫的函式。步驟如下：

  1. 使用 Apps Script 編輯器時，在 createEmbeddedLineChart() 函式的後方，將以下函式加到指令碼專案的 Code.gs 指令碼結尾：
/**
 * 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. 儲存指令碼專案。

程式碼審查

這個程式碼可能會比您預期的更短。將程式碼分成五部分，讓我們瞭解其內容：

1：取得圖表

前幾行會搜尋有效試算表，找出所有內嵌圖表，並將這些圖表收集到陣列 charts 中。這些線條使用 Spreadsheet.getSheets() 方法和 Sheet.getCharts() 方法取得工作表和圖表清單。JavaScript Array.concat() 方法可用來將每張工作表中的圖表清單附加到 charts 中。

2：檢查匯出的圖表

這個程式碼會驗證是否有可匯出的圖表。我們希望避免製作空白的簡報，因此如果沒有任何圖表，程式碼就會改為使用 Spreadsheet.toast(message) 建立浮動訊息。這是小型的「短暫顯示畫面」對話方塊，它會在試算表右下角彈出，停留數秒，然後消失：

db7e87dcb8010bef.gif

如果沒有可匯出的圖表，程式碼則會建立訊息訊息，然後結束執行，而不執行任何動作。如果有可匯出的圖表，則程式碼會在接下來幾行持續製作簡報。

3：建立簡報

變數 presentationTitle 是用來存放新簡報的檔案名稱。它被添加為電子表格的名稱，““0”。 Presentation”“連合。接著，程式碼會呼叫「簡報」服務方法 SlidesApp.create(name) 以建立簡報。

系統會以單一空白投影片建立新的簡報。我們不希望在簡報中做到這點，因此程式碼會將該程式碼從 Presentation.getSlides() 和 Slide.remove() 中移除。

4：匯出圖表

在下一部分，程式碼會定義 JavaScript 物件的 position 和 size 以設定匯入圖表在投影片中的放置位置，以及圖表大小 (以像素為單位)。

這個程式碼會在圖表清單內的各個圖表中循環播放。系統會為每一張圖表使用 Presentation.appendSlide() 建立一個 newSlide，並將投影片新增至簡報的結尾。Slide.insertSheetsChart(sourceChart, left, top, width, height) 方法可將圖表匯入指定的 position 和 size。

5：共用簡報位置

最後，程式碼需要告知使用者新簡報的位置，最好使用點選連結開啟簡報。如要這麼做，該程式碼會使用 Apps Script' 的 HTML service 建立自訂互動對話方塊。強制回應對話方塊 (在 Apps Script 中又稱為自訂對話方塊) 是顯示在試算表介面上方的視窗。顯示時，自訂對話方塊會禁止使用者與試算表互動。

如要建立自訂對話方塊，程式碼需要定義其內容的 HTML。這會在 html 變數中提供。內容包含一段簡短段落和超連結。超連結是 presentationTitle 變數，連結至 Presentation.getUrl() 提供的簡報網址。超連結也會使用 target="_blank" 屬性，因此系統會在新的瀏覽器分頁中 (而非對話方塊) 中開啟簡報。

系統透過 HtmlService.createHtmlOutput(html) 方法將 HTML 剖析為 HtmlOutput 物件。HtmlOutput 物件可讓程式碼設定含有 HtmlOutput.setHeight(height) 和 HtmlOutput.setWidth(width) 的自訂對話方塊大小。

htmlOutput 建立後，程式碼會使用 Ui.showModalDialog(htmlOutput, title) 方法顯示含有指定標題的對話方塊。

結果

您已經導入第二個選單項目，可以立即查看。如要測試 exportChartsToSlides() 函式，請按照下列步驟操作：

  1. 如果尚未儲存指令碼專案，請先前往 Apps Script 編輯器儲存。
  2. 開啟試算表，然後按一下 [播放資料集(&G) 圖表] > [日期和美元匯率] 選單項目，即可建立匯出的圖表。它會在工作表中固定在儲存格 H5 中。
  3. 按一下 [簡報資料集 > 將圖表匯出至簡報] 選單項目。系統可能會要求你重新授權指令碼。
  4. 畫面會顯示指令碼處理要求並顯示自訂對話方塊。
  5. 如要開啟新版簡報，請按一下 [日期和美元匯率簡報] 連結：

51326ceaeb3e49b2.gif

您也可以視需求在試算表中加入更多圖表，並再次選取選單項目，即可製作含有多張投影片的簡報。

現在，您可以將 Google 試算表中建立的圖表匯出為簡報。您也可以撰寫程式碼來建立自訂對話方塊。

您已完成這個程式碼研究室的最終練習。請參閱下一節，看看您學到了哪些內容。

5. 結語

恭喜！您已完成這個程式碼研究室和整個「Google Apps 試算表」基礎知識「Google 試算表」程式碼研究室播放清單。您可以根據這份播放清單提供的原則，擴充 Google 試算表服務，並探索 Apps Script 的功能。

這個程式碼研究室是否對您有幫助？

是 否

涵蓋內容

  • 如何使用 Apps Script 建立內嵌折線圖。
  • 如何在試算表中向使用者顯示訊息和自訂對話方塊。
  • 如何將圖表匯出至新的簡報。

後續步驟

您已經成功建立這份播放清單。不過，您仍需進一步瞭解 Apps Script。

歡迎查看以下資源：

祝您使用指令碼的愉快！

這個程式碼研究室播放清單對您有幫助嗎？

是 否

希望日後能查看更多 Apps Script 程式碼研究室嗎？

是 否