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,但沒有時間學習, 查看外掛程式商店 看看是否有其他人建構了您需要的自訂函式。
建立自訂函式
如何編寫自訂函式:
- 建立 或是在 Google 試算表中開啟試算表
- 在選單項目中,選取「額外資訊」> Apps Script。
- 刪除指令碼編輯器中的任何程式碼。針對上述
DOUBLE
函式 複製程式碼並貼到指令碼編輯器中。 - 按一下頂端的「 」。
現在您可以使用自訂函式。
從 Google Workspace Marketplace取得自訂函式
The Google Workspace Marketplace 提供多項自訂 作為 Google 試算表外掛程式。 如何使用或探索這些外掛程式:
- 建立 或是在 Google 試算表中開啟試算表
- 按一下位於頂端的「外掛程式」>取得外掛程式。
- Google Workspace Marketplace 開啟後,請按一下右上角的搜尋框。
- 輸入「自訂函式」然後按下 Enter 鍵
- 找到感興趣的自訂函式外掛程式後,請按一下「安裝」 安裝。
- 系統可能會顯示對話方塊,說明外掛程式需要授權。如果是, 請詳閱通知,然後按一下「允許」。
- 試算表會提供這個外掛程式。如何在 請開啟另一個試算表,然後在頂端點選 附加內容 >管理外掛程式。找到你要使用的外掛程式,然後按一下 選項 >用於這個模型 文件。
使用自訂函式
編寫自訂函式或安裝 Google Workspace Marketplace,無論是要 內建函式:
- 按一下要使用函式的儲存格。
- 輸入等號 (
=
),後面接著函式名稱和任何輸入值: 例如=DOUBLE(A1)
,然後按下 Enter 鍵。 - 儲存格會立即顯示
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;
}
這些技術適用於幾乎任何使用的自訂函式 會重複出現在試算表上,但實作細節會 會視函式的行為而定。