Google 試算表的自訂函式

Google 試算表提供數百種內建函式,例如 AVERAGESUMVLOOKUP。如果上述方法不符合您的需求,您可以使用 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;
}

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