擴充 Google 試算表

Google Apps Script 可讓 Google 試算表發揮新奇妙用。您可以使用 Apps Script 在 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() {
  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()DataValidationBuilderRange.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 試算表使用者介面中,巨集是執行 Apps Script 程式碼的另一種方式。 與自訂函式不同,您可以透過鍵盤快速鍵或 Google 試算表選單啟用這些功能。詳情請參閱 Google 試算表巨集

Google 試算表外掛程式

外掛程式是經過特殊封裝的 Apps Script 專案,會在 Google 試算表中執行,且可從 Google 試算表外掛程式商店安裝。如果您已開發 Google 試算表指令碼,並且想要與全世界分享,Apps Script 可讓您以外掛程式的形式publish指令碼,讓其他使用者能夠從外掛程式商店安裝該指令碼。

觸發條件

繫結至 Google 試算表檔案的指令碼可以使用函式 onOpen()onEdit()簡易觸發條件,在具有試算表編輯權限的使用者開啟或編輯試算表時自動回應。

如同簡單的觸發條件,可安裝的觸發條件可讓 Google 試算表在特定事件發生時自動執行函式。但是,可安裝觸發條件比簡單的觸發條件更有彈性,並支援下列事件:開啟、編輯、變更、表單提交和時間導向 (時鐘)。