Google 試算表的自訂函式

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

開始

請使用標準 JavaScript 建立自訂函式。如果您是第一次使用 JavaScript，程式碼研究室提供新手入門課程。(注意：本課程並非由 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. 如果發現感興趣的自訂外掛程式外掛程式，請按一下 [安裝] 進行安裝。
6. 系統會顯示對話方塊，說明外掛程式需要授權。在這種情況下，請詳閱通知內容，然後按一下「允許」。
7. 試算表即會提供外掛程式。如要在其他試算表中使用外掛程式，請開啟其他試算表，然後按一下頂端的「外掛程式 &管理外掛程式」。找出您要使用的外掛程式，然後按一下「選項」圖示 &gt；在這份文件中使用。

使用自訂函式

編寫自訂函式或從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))，則引數將是儲存格的二維陣列和 #39；值。例如，在下方螢幕截圖中，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 中的適當資料類型。最常見的困境如下：

• Google 試算表中的時間和日期會轉換為 Apps Script 中的 日期物件。如果試算表和指令碼使用不同的時區 (極少數的問題)，自訂函式就必須補償。
• 試算表中的時間長度值也會變成 Date 物件，但處理這些物件可能相當複雜。
• Google 試算表中的百分比值在 Apps Script 中會變成十進位數字。舉例來說，在 Apps Script 中，值為 10% 的儲存格會變為 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;
}

進階

使用 Apps Script 服務

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

與大多數其他類型的 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 開啟指令碼編輯器，然後複製原始試算表中的指令碼文字，然後貼到其他試算表的指令碼編輯器中。
• 按一下「File > Make a copy」，建立包含自訂函式的試算表副本。複製試算表時，也會複製附加的所有試算表指令碼。所有可存取試算表的使用者都能複製指令碼。(只有具備檢視權限的協作者就無法開啟原始試算表中的指令碼編輯器。不過，建立副本時，他們會成為副本的擁有者，且可以查看指令碼)。
• 將指令碼發布為 Google 試算表外掛程式。

所有容器繫結的指令碼都會與其容器具有相同存取權清單。這表示任何有權編輯試算表的使用者都可以編輯附加至試算表的任何 Apps Script 程式碼。

最佳化

每次在試算表中使用自訂函式時，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 呼叫) 來完成相同動作)。

同樣地，下方的自訂函式可以從網際網路擷取有效內容，並使用單一維度陣列顯示單一函式呼叫，並顯示兩欄的結果。如果每個儲存格都需要自己的函式呼叫，由於作業每次必須下載及剖析 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;
}

這些技巧可以套用至幾乎可在試算表中使用的重複自訂函式，但實作細節會因函式行為而異。