Google 試算表的自訂函式

Google 試算表提供數百種 內建函式 AVERAGE、 SUM和 VLOOKUP。如果這些 你可以使用 Google Apps Script 編寫自訂函式 — 舉例來說，你可以將公尺換算成英里或擷取 網際網路上的直播內容，然後使用這些內容 ，就跟內建函式一樣

開始使用

自訂函式是使用標準 JavaScript 建立。如果您是初次使用 Codecademy 提供 適合初學者的課程。 (注意：這堂課程並非由 Google 製作，與 Google 無關)。

以下為名為 DOUBLE 的簡單自訂函式，可將 輸入值

/**
 * 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. 在選單項目中，選取「額外資訊」> Apps Script。
  3. 刪除指令碼編輯器中的任何程式碼。針對上述 DOUBLE 函式 複製程式碼並貼到指令碼編輯器中。
  4. 按一下頂端的「」。

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

從 Google Workspace Marketplace取得自訂函式

The Google Workspace Marketplace 提供多項自訂 作為 Google 試算表外掛程式。 如何使用或探索這些外掛程式：

  1. 建立 或是在 Google 試算表中開啟試算表
  2. 按一下位於頂端的「外掛程式」>取得外掛程式。
  3. Google Workspace Marketplace 開啟後，請按一下右上角的搜尋框。
  4. 輸入「自訂函式」然後按下 Enter 鍵
  5. 找到感興趣的自訂函式外掛程式後，請按一下「安裝」 安裝。
  6. 系統可能會顯示對話方塊，說明外掛程式需要授權。如果是， 請詳閱通知，然後按一下「允許」。
  7. 試算表會提供這個外掛程式。如何在 請開啟另一個試算表，然後在頂端點選 附加內容 >管理外掛程式。找到你要使用的外掛程式，然後按一下 選項 >用於這個模型 文件。

使用自訂函式

編寫自訂函式或安裝 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 試算表將資料儲存在 不同格式， 資料的本質在自訂函式中使用這些值時 指令碼會將這些變數視為 在 JavaScript 中使用適當資料類型。 最常見的誤解如下：

  • 試算表中的時間和日期會變成 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 服務。

分享

自訂函式一開始會繫結至 建立多個試算表這意味著 除非您使用其中一個試算表 方法如下：

  • 依序按一下「擴充功能」>「Apps Script」，然後 開啟指令碼編輯器，然後複製 原始試算表中的指令碼文字，貼到指令碼編輯器中 。
  • 按一下含有自訂函式的試算表副本： 檔案 >建立副本。複製試算表時，附加所有指令碼的 也會一併複製擁有試算表存取權的使用者 指令碼(如果協作者只具備檢視權限，就無法開啟指令碼編輯器 產生原始試算表但建立副本時 以及查看指令碼)。
  • 以 Google 試算表編輯器外掛程式發布指令碼。

最佳化

每次在試算表中使用自訂函式時，Google 試算表就會產生 分別呼叫 Apps Script 伺服器。如果您的試算表包含數十個 (或 數百或數千個) 自訂函式呼叫，這項程序可 但速度較慢

因此，如果您打算在大型主機上多次使用自訂函式 可以考慮修改函式，使其可接受 以二維陣列的形式輸入，然後傳回二維 可溢位至適當儲存格的陣列。

例如，您可以改寫上述 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;
}

這些技術適用於幾乎任何使用的自訂函式 會重複出現在試算表上，但實作細節會 會視函式的行為而定。