Google 試算表的自訂函式

Google 試算表提供數百種 內建函式 AVERAGESUMVLOOKUP。如果這些 你可以使用 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 秒內傳回。如果沒有, 儲存格顯示 #ERROR!,儲存格附註為 Exceeded maximum execution time (line 0).

資料類型

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;
}

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