擴充 Google 試算表

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

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

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

開始使用

Apps Script 包含專屬 API，可讓您透過程式輔助方式建立、讀取及編輯 Google 試算表。Apps Script 可透過兩種方式與 Google 試算表互動：如果指令碼的使用者具備試算表的適當權限，任何指令碼都能建立或修改試算表；此外，指令碼也可以繫結至試算表，賦予指令碼變更使用者介面或在試算表開啟時做出回應的特殊能力。如要建立繫結指令碼，請在 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']);
}

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

自訂選單和使用者介面

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

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

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

連結至 Google 表單

透過 Apps Script，您可以透過 Forms 和 Spreadsheet 服務，將 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.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  const 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.
  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(rule)

圖表

您可以使用 Apps Script，在試算表中嵌入圖表，呈現特定範圍內的資料。假設儲存格 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 分鐘快速入門指南，或參閱更深入的自訂函式指南。

巨集

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

Google 試算表外掛程式

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

效能與擴充性

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

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

觸發條件

繫結至 Google 試算表檔案的指令碼可以使用簡易觸發條件，例如 onOpen() 和 onEdit() 函式，在使用者開啟或編輯試算表時自動回應。使用者必須具備試算表的編輯存取權。

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

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