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,但沒有時間學習,請查看外掛程式商店,確認是否已有其他人建構了您需要的自訂函式。
建立自訂函式
如何編寫自訂函式:
- 建立或在 Google 試算表中開啟試算表。
- 在選單項目中依序選取「Extensions」>「Apps Script」。
- 刪除指令碼編輯器中的任何程式碼。針對上方的
DOUBLE
函式,只要複製程式碼並貼到指令碼編輯器中即可。 - 按一下頂端的「 」。
現在您可以使用自訂函式。
從 Google Workspace Marketplace取得自訂函式
Google Workspace Marketplace 提供多個自訂函式做為 Google 試算表外掛程式。如何使用或探索這些外掛程式:
- 建立或在 Google 試算表中開啟試算表。
- 依序點選頂端的「外掛程式」>「取得外掛程式」。
- Google Workspace Marketplace 開啟後,按一下右上角的搜尋框。
- 輸入「自訂函式」,然後按下 Enter 鍵。
- 找到您感興趣的自訂函式外掛程式時,按一下「Install」即可安裝。
- 系統可能會顯示對話方塊,說明外掛程式需要授權。如果有,請詳閱該通知,然後按一下「Allow」(允許)。
- 試算表會提供這個外掛程式。如要在其他試算表中使用這個外掛程式,請開啟另一個試算表,然後依序點選頂端的「外掛程式」>「管理外掛程式」。找出您要使用的外掛程式,然後依序點選「選項」圖示 >「用於這份文件」。
使用自訂函式
寫入自訂函式或從Google Workspace Marketplace安裝自訂函式之後,就可以將其當做內建函式輕鬆使用:
- 按一下要使用函式的儲存格。
- 輸入等號 (
=
),後面接著函式名稱和任何輸入值,例如=DOUBLE(A1)
,然後按下 Enter 鍵。 - 儲存格會立即顯示
Loading...
,然後傳回結果。
自訂函式指南
在編寫自己的自訂函式之前,您需要先瞭解一些準則。
命名
除了為 JavaScript 函式命名的標準慣例外,請注意下列事項:
- 自訂函式的名稱必須與內建函式 (例如
SUM()
) 的名稱不同。 - 自訂函式的名稱不能以底線 (
_
) 結尾,代表 Apps Script 中的私人函式。 - 自訂函式的名稱必須使用
function myFunction()
語法宣告,而非使用var myFunction = new Function()
。 - 雖然試算表函式的名稱傳統上為大寫,但不必區分大小寫。
引數
如同內建函式,自訂函式可以使用引數做為輸入值:
- 如果呼叫函式時,將參照單一儲存格做為引數 (例如
=DOUBLE(A1)
),則引數會是儲存格的值。 如果使用某一範圍儲存格的參照做為引數 (例如
=DOUBLE(A1:B10)
),引數就會是儲存格值的二維陣列。例如,在以下螢幕截圖中,=DOUBLE(A1:B2)
中的引數會由 Apps Script 解讀為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 中的十進位數字。舉例來說,在 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;
}
進階
使用 Google Apps Script 服務
自訂函式可以呼叫特定 Google 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 服務。
分享
自訂函式一開始會繫結至建立時所在的試算表。也就是說,除非您使用下列其中一種方法,否則以一份試算表編寫的自訂函式無法用於其他試算表:
- 依序按一下「擴充功能」>「Apps Script」開啟指令碼編輯器,然後複製原始試算表中的指令碼文字,並貼到另一個試算表的指令碼編輯器中。
- 依序點選「File」>「Create a copy」,即可複製包含自訂函式的試算表。複製試算表時,附加的所有指令碼也會一併複製。任何有權存取試算表的使用者都能複製指令碼。(如果協作者只有檢視權限,就無法開啟原始試算表中的指令碼編輯器。然而,當他們建立副本時,他們就會成為該副本的擁有者,並且可以看見該指令碼。)
- 以 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;
}
這些技巧適用於幾乎整份試算表使用的任何自訂函式,不過實作細節會因函式行為而異。