擴充 Google 試算表

Google Apps Script 可讓您透過 Google 試算表執行新穎又酷炫的操作。您可以使用 Apps Script 在 Google 試算表中新增自訂選單、對話方塊和側欄。您也可以使用這個 API 為試算表編寫自訂函式，並將試算表與 Google 日曆、雲端硬碟和 Gmail 等其他Google 服務整合。

大多數為 Google 試算表設計的指令碼會操控陣列，以便與試算表中的儲存格、列和欄互動。如果您不熟悉 JavaScript 中的陣列，Codecademy 提供陣列的優質訓練模組。(請注意，本課程並非由 Google 開發，也與 Google 無關)。

如要快速瞭解如何搭配使用 Google 試算表和 Apps Script，請參閱 巨集、選單和自訂函式的 5 分鐘快速入門指南。

開始使用

Apps Script 包含特殊 API，可讓您透過程式輔助方式建立、讀取及編輯 Google 試算表。Apps Script 可以透過兩種方式與 Google 試算表互動：如果指令碼使用者擁有試算表的適當權限，任何指令碼都能建立或修改試算表；此外，指令碼也可以繫結至試算表，讓指令碼具備特殊能力，可變更使用者介面或在開啟試算表時做出回應。如要建立已繫結的指令碼，請在 Google 試算表中依序選取「擴充功能」「Apps Script」。

試算表服務會將 Google 試算表視為格狀，並以二維陣列運作。如要從試算表擷取資料，您必須取得儲存資料的試算表存取權，取得試算表中儲存資料的範圍，然後取得儲存格的值。Apps Script 會讀取試算表中的結構化資料，並為這些資料建立 JavaScript 物件，方便存取資料。

讀取資料

假設您有一份產品名稱和產品編號清單，並儲存在試算表中，如以下圖片所示。

以下範例說明如何擷取及記錄產品名稱和產品編號。

function logProductInfo() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for (var i = 0; i < data.length; i++) {
    Logger.log('Product name: ' + data[i][0]);
    Logger.log('Product number: ' + data[i][1]);
  }
}

查看記錄

如要查看已記錄的資料，請在指令碼編輯器頂端按一下「執行記錄」。

寫入資料

如要儲存資料 (例如新產品名稱和編號) 到試算表，請在指令碼結尾新增下列程式碼。

function addProduct() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);
}

上述程式碼會在試算表底部附加新資料列，並指定值。執行這個函式後，試算表就會新增一列。

自訂選單和使用者介面

你可以新增自訂選單、對話方塊和側欄，自訂 Google 試算表。如要瞭解建立選單的基本概念，請參閱選單指南。如要瞭解如何自訂對話方塊的內容，請參閱 HTML 服務指南。

您也可以將指令碼函式附加至試算表中的圖片或繪圖，當使用者點選圖片或繪圖時，系統就會執行該函式。如需更多資訊，請參閱「Google 試算表中的圖片和繪圖」。

如果您打算將自訂介面發布為外掛程式的一部分，請遵循樣式指南，確保與 Google 試算表編輯器的樣式和版面配置一致。

連結至 Google 表單

您可以透過 Apps Script 的表單和試算表服務，將 Google 表單與 Google 試算表連結。這項功能可根據試算表中的資料，自動建立 Google 表單。您也可以使用 Apps Script 的觸發事件 (例如 onFormSubmit)，在使用者回應表單後執行特定動作。如要進一步瞭解如何將 Google 試算表連結至 Google 表單，請試試 Google 表單回覆管理 5 分鐘快速上手指南。

格式設定

Range 類別提供 setBackground(color) 等方法，可存取及修改儲存格或儲存格範圍的格式。以下範例說明如何設定範圍的字型樣式：

function formatMySpreadsheet() {
  // Set the font style of the cells in the range of B2:C2 to be italic.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var cell = sheet.getRange('B2:C2');
  cell.setFontStyle('italic');
}

驗證資料

您可以透過 Apps Script 存取 Google 試算表中的現有資料驗證規則，或建立新的規則。舉例來說，以下範例說明如何設定資料驗證規則，讓使用者只能在儲存格中輸入 1 到 100 之間的數字。

function validateMySpreadsheet() {
  // Set a rule for the cell B4 to be a number between 1 and 100.
  var cell = SpreadsheetApp.getActive().getRange('B4');
  var rule = SpreadsheetApp.newDataValidation()
     .requireNumberBetween(1, 100)
     .setAllowInvalid(false)
     .setHelpText('Number must be between 1 and 100.')
     .build();
  cell.setDataValidation(rule);
}

如要進一步瞭解如何使用資料驗證規則，請參閱 SpreadsheetApp.newDataValidation()、DataValidationBuilder 和 Range.setDataValidation(rule)。

圖表

您可以使用 Apps Script 在試算表中嵌入圖表，以便呈現特定範圍的資料。以下範例會產生嵌入式長條圖，假設您在 A1:B15 儲存格中使用可繪製的資料：

function newChart() {
  // Generate a chart representing the data in the range of A1:B15.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var chart = sheet.newChart()
     .setChartType(Charts.ChartType.BAR)
     .addRange(sheet.getRange('A1:B15'))
     .setPosition(5, 5, 0, 0)
     .build();

  sheet.insertChart(chart);
}

如要進一步瞭解如何在試算表中嵌入圖表，請參閱 EmbeddedChart 和特定圖表建構工具 (例如 EmbeddedPieChartBuilder)。

Google 試算表的自訂函式

自訂函式與內建的試算表函式 (例如 =SUM(A1:A5)) 類似，但您必須使用 Apps Script 定義函式的行為。舉例來說，您可以建立自訂函式 in2mm()，將值從英寸轉換為毫米，然後在試算表中使用公式，方法是將 =in2mm(A1) 或 =in2mm(10) 鍵入儲存格。

如要進一步瞭解自訂函式，請試試 選單和自訂函式 5 分鐘快速入門，或參閱更深入的自訂函式指南。

巨集

巨集是另一種透過 Google 試算表 UI 執行 Apps Script 程式碼的方式。與自訂函式不同，您可以透過鍵盤快速鍵或 Google 試算表選單啟用這些函式。詳情請參閱「Google 試算表巨集」。

Google 試算表外掛程式

外掛程式是經過特別封裝的 Apps Script 專案，可在 Google 試算表中執行，並可從 Google 試算表外掛程式商店安裝。如果您已開發 Google 試算表的腳本，並想與全世界分享，可以透過 Apps Script 發布腳本做為外掛程式，讓其他使用者從外掛程式商店安裝。

觸發條件

綁定至 Google 試算表檔案的程式碼可使用 onOpen() 和 onEdit() 等簡易觸發事件，在擁有試算表編輯權限的使用者開啟或編輯試算表時，自動回應。

與簡單觸發條件一樣，可安裝的觸發條件可讓 Google 試算表在特定事件發生時自動執行函式。不過，可安裝的觸發條件比簡單觸發條件提供更大的彈性，且支援下列事件：開啟、編輯、變更、表單提交和時間驅動 (時鐘)。