擴充 Google 試算表

使用 Google Apps Script 擴充試算表功能。在 Google 試算表中新增自訂選單、對話方塊和側欄。為 Google 試算表編寫自訂函式，並與其他 Google 服務 (例如 Google 日曆、Google 雲端硬碟和 Gmail) 整合。

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

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

開始使用

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

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

讀取資料

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

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

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

查看記錄

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

寫入資料

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

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

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

自訂選單和使用者介面

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

將指令碼函式附加至試算表中的圖片或繪圖，使用者點選圖片或繪圖時，系統就會執行該函式。詳情請參閱「Google 試算表中的圖片和繪圖」。

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

連結至 Google 表單

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

設定資料格式

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

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

資料驗證

在 Google 試算表中存取現有的資料驗證規則，或建立新規則。舉例來說，下列範例說明如何設定資料驗證規則，只允許在儲存格中輸入 1 到 100 之間的數字。

function validateMySpreadsheet() {
  // Set a rule for the cell B4 to be a number between 1 and 100.
  const cell = SpreadsheetApp.getActive().getRange('B4');
  const 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

圖表

在試算表中嵌入圖表，呈現特定範圍內的資料。假設儲存格 A1:B15 中有可繪製圖表的資料，下列範例會產生內嵌長條圖：

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

  const 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 分鐘的選單和自訂函式快速入門指南，或參閱更深入的自訂函式指南。

巨集

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

Google 試算表外掛程式

外掛程式是特別封裝的 Apps Script 專案，可在 Google 試算表中執行，並從試算表外掛程式商店安裝。如果您已開發 Google 試算表專用的指令碼，並想與他人共用，可以透過 Apps Script 將指令碼發布為外掛程式，供其他使用者安裝。

效能與擴充性

隨著資料集增加，您可能會遇到效能問題。如要將試算表和指令碼最佳化，請按照下列指示操作：

遵循最佳做法：請參閱「最佳做法指南」，瞭解如何盡量減少服務呼叫次數及使用批次作業。
最佳化公式：如果試算表因複雜公式 (例如 VLOOKUP、ARRAYFORMULA 或 IMPORTRANGE) 而延遲，建議使用 Apps Script 在記憶體中執行這些計算，然後分批寫回結果。
考慮使用其他資料庫：如果資料集非常龐大 (接近 1 千萬個儲存格)，或是資料輸入頻率很高 (例如許多已連結的表單)，建議使用 JDBC 搭配 Google Cloud SQL 或 BigQuery。

觸發條件

繫結至 Google 試算表檔案的指令碼可以使用簡易觸發程序 (例如 onOpen() 和 onEdit() 函式)，在具備試算表編輯權限的使用者開啟或編輯試算表時，自動做出回應。與簡單觸發條件類似，可安裝的觸發條件可讓 Google 試算表在發生特定事件時自動執行函式。不過，可安裝的觸發條件比簡單觸發條件更具彈性，且支援下列事件：開啟、編輯、變更、表單提交和時間驅動 (時鐘)。

擴充 Google 試算表 透過集合功能整理內容 你可以依據偏好儲存及分類內容。