Google 試算表的自訂函式

Google 試算表提供數百種內建函式，例如 AVERAGE、SUM 和 VLOOKUP。如果上述方法不符合您的需求，您可以使用 Google Apps Script 編寫自訂函式 (例如將公尺轉換為英里或從網際網路擷取即時內容)，然後像內建函式一樣在 Google 試算表中使用這些函式。

開始使用

自訂函式是以標準 JavaScript 建立。如果您是 JavaScript 新手，Codecademy 提供一個適合新手的課程。(注意：這堂課程並非由 Google 製作，與 Google 無關)。

以下是名為 DOUBLE 的簡單自訂函式，會將輸入值乘以 2：

/**
 * Multiplies an input value by 2.
 * @param {number} input The number to double.
 * @return The input multiplied by 2.
 * @customfunction
*/
function DOUBLE(input) {
  return input * 2;
}

如果您不知道如何編寫 JavaScript，但沒時間學習，請查看外掛程式商店，看看是否已有人建構您需要的自訂函式。

建立自訂函式

如要編寫自訂函式：

  1. 在 Google 試算表中建立或開啟試算表。
  2. 在選單項目中依序選取「Extensions」>「Apps Script」。
  3. 刪除指令碼編輯器中的任何程式碼。針對上述 DOUBLE 函式，您只需要複製程式碼並貼到指令碼編輯器中。
  4. 按一下頂端的「」。

您現在可以使用自訂函式。

從 Google Workspace Marketplace取得自訂函式

Google Workspace Marketplace 提供數個自訂函式做為 Google 試算表的外掛程式。如要使用或探索這些外掛程式：

  1. 在 Google 試算表中建立或開啟試算表。
  2. 依序按一下頂端的 [外掛程式] > [取得外掛程式]。
  3. Google Workspace Marketplace 開啟後，按一下右上角的搜尋框。
  4. 輸入「自訂函式」，然後按下 Enter 鍵。
  5. 如有感興趣的自訂函式外掛程式，請按一下「Install」(安裝) 進行安裝。
  6. 系統會顯示對話方塊，說明外掛程式需要授權。如果是這種情況，請詳閱通知，然後按一下「Allow」(允許)。
  7. 即可在試算表中使用外掛程式。如要在其他試算表中使用外掛程式，請開啟其他試算表，然後依序點選頂端的「add-ons」>「Manage Add-ons」。找出要使用的外掛程式，然後依序點選「選項」圖示 >「用於本文件」。

使用自訂函式

編寫自訂函式或從Google Workspace Marketplace安裝函式後，就和內建函式一樣輕鬆：

  1. 按一下您要使用函式的儲存格。
  2. 輸入等號 (=)，後面加上函式名稱和任何輸入值 (例如 =DOUBLE(A1))，然後按下 Enter 鍵。
  3. 儲存格會短暫顯示 Loading...，然後傳回結果。

自訂函式指南

在自行編寫自訂函式之前，請先瞭解以下幾點。

命名

除了遵循 JavaScript 函式命名慣例外，也請留意下列事項：

  • 自訂函式的名稱必須與內建函式的名稱 (例如 SUM()) 不同。
  • 自訂函式的名稱結尾不得為底線 (_)，也就是 Apps Script 中的私人函式。
  • 自訂函式的名稱必須使用 function myFunction() 語法 (而非 var myFunction = new Function()) 進行宣告。
  • 大小寫無關緊要，不過試算表函式的名稱通常都是大寫。

引數

自訂函式就像內建函式一樣，可以使用引數做為輸入值：

  • 如果您呼叫函式時參照單一儲存格做為引數 (例如 =DOUBLE(A1))，引數將是儲存格的值。

  • 如果您呼叫函式時參照了儲存格範圍做為引數 (例如 =DOUBLE(A1:B10))，引數將是儲存格值的二維陣列。例如，在以下螢幕截圖中，Apps Script 會將 =DOUBLE(A1:B2) 中的引數解讀為 double([[1,3],[2,4]])。請注意，以上的 DOUBLE 程式碼範例需要修改為接受陣列做為輸入內容。


  • 自訂函式引數必須具有確定性。也就是說，內建試算表函式會在每次計算時傳回不同的結果 (例如 NOW() 或 RAND())，不允許做為自訂函式的引數。如果自訂函式嘗試根據其中一個揮發性內建函式傳回值，便會無限期顯示 Loading...。

傳回值

每個自訂函式都必須傳回要顯示的值，如下所示：

  • 如果自訂函式傳回值，該值會顯示在呼叫該函式的儲存格中。
  • 如果自訂函式傳回值的二維陣列，只要這些儲存格為空白，值就會溢位至相鄰儲存格。如果這會導致陣列覆寫現有的儲存格內容，自訂函式會改為擲回錯誤。如需範例，請參閱「最佳化自訂函式」一節。
  • 自訂函式不會影響除了傳回值的目標儲存格。換句話說，自訂函式無法編輯任意儲存格，只能編輯呼叫該函式的儲存格和相鄰儲存格。如要編輯任意儲存格，請改用自訂選單執行函式。
  • 自訂函式呼叫必須在 30 秒內傳回。否則儲存格將顯示錯誤：Internal error executing the custom function.

資料類型

Google 試算表會根據資料性質，以不同的格式儲存資料。當這些值用於自訂函式時，Apps Script 會將這些值視為在 JavaScript 中適當的資料類型。以下是最常見的混淆問題：

  • 試算表中的時間和日期會變成 Apps Script 中的 Date 物件。如果試算表和指令碼使用不同的時區 (罕見問題)，則自訂函式需要補償。
  • 試算表中的時間長度值也會變成 Date 物件，但使用這些值可能會很複雜。
  • 在 Apps Script 中，試算表中的百分比值會以十進位數字表示。舉例來說，值為 10% 的儲存格會在 Apps Script 中變為 0.1。

自動完成

Google 試算表支援自訂函式的自動完成功能，與內建函式類似。在儲存格中輸入函式名稱時，您會看到符合輸入內容的內建和自訂函式清單。

如果自訂函式的指令碼包含 JsDoc @customfunction 標記，此清單就會顯示於此清單 (如下方的 DOUBLE() 範例所示)。

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

進階

使用 Google Apps Script 服務

自訂函式可呼叫特定 Google Apps Script 服務以執行更複雜的工作。例如，自訂函式可以呼叫語言服務，將英文詞組翻譯成西班牙文。

自訂函式與大多數其他類型的 Apps Script 不同，不會要求使用者授予個人資料的存取權。因此只能呼叫無法存取個人資料的服務，特別是以下內容：

支援的服務 附註
快取 可以運作，但在自訂函式中特別實用
HTML 可以產生 HTML，但無法顯示 (非常實用)
JDBC
語言
鎖定 可以運作，但在自訂函式中特別實用
地圖介面集 可以計算路線，但無法顯示地圖
資源 getUserProperties() 只會取得試算表擁有者的屬性。試算表編輯者無法在自訂函式中設定使用者屬性。
試算表 唯讀 (可使用大多數 get*() 方法，但無法使用 set*())。
無法開啟其他試算表 (SpreadsheetApp.openById() 或 SpreadsheetApp.openByUrl())。
網址擷取
實用工具
XML

如果您的自訂函式擲回 You do not have permission to call X service. 錯誤訊息，表示該服務需要使用者授權，因此無法用於自訂函式。

如要使用上方未列出的服務，請建立執行 Apps Script 函式的自訂選單，而非編寫自訂函式。從選單觸發的函式會在必要時要求使用者提供授權，進而使用所有 Apps Script 服務。

分享

自訂函式會開始繫結至其建立的試算表。也就是說，除非使用以下其中一種方法，否則使用一份試算表編寫的自訂函式無法用於其他試算表：

  • 按一下「Extensions」(擴充功能) >「Apps Script」(Apps Script) 以開啟指令碼編輯器，接著複製原始試算表的指令碼文字，並貼到其他試算表的指令碼編輯器中。
  • 依序點選「File」>「Make a copy」，建立包含自訂函式的試算表副本。複製試算表時，也會一併複製任何附加的指令碼。凡是有權存取試算表的使用者，都能複製指令碼。(只有檢視權限的協作者無法在原始試算表中開啟指令碼編輯器。然而，當使用者建立副本時，他們就會成為副本的擁有者，因此也能看到指令碼)。
  • 將指令碼發布為 Google 試算表編輯器外掛程式。

最佳化

每次在試算表中使用自訂函式時，Google 試算表都會另外呼叫 Apps Script 伺服器。如果試算表包含數十 (甚至數千個或數千個) 的自訂函式呼叫，這項程序可能會相當緩慢。

因此，如果您打算多次在大量資料上使用自訂函式，請考慮修改函式，讓函式接受以二維陣列形式輸入的範圍，然後傳回可以溢位到適當儲存格的 2D 陣列。

舉例來說，您可以將上述的 DOUBLE() 函式重新撰寫為接受單一儲存格或儲存格範圍，如下所示：

/**
 * Multiplies the input value by 2.
 *
 * @param {number|Array<Array<number>>} input The value or range of cells
 *     to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;
}

上述方法使用 JavaScript 的 Array 物件的 map 方法，以遞迴方式對二維儲存格陣列中的每個值呼叫 DOUBLE。這樣會傳回包含結果的二維陣列。如此一來，您只要呼叫 DOUBLE 一次，就能立即計算大量儲存格，如下方螢幕截圖所示。(您可以使用巢狀 if 陳述式，而非 map 呼叫來完成相同的作業)。

同樣地，以下的自訂函式會透過單一函式呼叫，有效擷取網際網路中的即時內容，並使用二維陣列來顯示兩欄結果。如果每個儲存格都需要專屬的函式呼叫，則作業會需要更多時間才能完成，因為 Apps Script 伺服器每次都必須下載和剖析 XML 動態饋給。

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

這些技巧適用於試算表中幾乎所有重複的自訂函式，但實作細節會因函式的行為而異。