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. 按一下頂端的「save」。

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

從 取得自訂函式

提供多個自訂函式，可做為 Google 試算表外掛程式。如要使用或探索這些外掛程式：

1. 在 Google 試算表中建立或開啟試算表。
2. 依序按一下頂端的「外掛程式」>「取得外掛程式」。
3. 開啟後，按一下右上角的搜尋框。
4. 輸入「自訂函式」，然後按下 Enter 鍵。
5. 如果找到想使用的自訂函式外掛程式，請按一下「安裝」來安裝。
6. 對話方塊可能會告知你外掛程式需要授權。如果是，請詳閱通知，然後按一下「允許」。
7. 試算表中會顯示外掛程式。如要在其他試算表中使用外掛程式，請開啟其他試算表，然後依序按一下頂端的「外掛程式」>「管理外掛程式」。找出要使用的外掛程式，然後依序按一下「選項」圖示 more_vert >「用於本文件」。

使用自訂函式

編寫自訂函式或從安裝函式後，使用方式就跟內建函式一樣簡單：

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 試算表會根據資料的性質，以不同格式儲存資料。當這些值用於自訂函式時，Apps Script 會將其視為 JavaScript 中的適當資料類型。以下是常見的混淆點：

• 試算表中的時間和日期會成為 Apps Script 中的 Date 物件。如果試算表和指令碼使用不同的時區 (這是很少見的問題)，自訂函式就需要進行調整。
• 試算表中的時間長度值也會成為 Date 物件，但使用這些物件可能會很複雜。
• 在 Google 試算表中，百分比值會轉換為 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 服務，執行更複雜的工作。舉例來說，自訂函式可以呼叫 Language 服務，將英文短語翻譯成西班牙文。

與大多數其他類型的 Apps Scripts 不同，自訂函式絕不會要求使用者授權存取個人資料。因此，這些應用程式只能呼叫無法存取個人資料的服務，具體來說包括：

如果自訂函式擲回錯誤訊息 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;
}

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