背景と概要
スプレッドシートのセルへ配置したカスタム関数をリフレッシュ（再計算）させようとすると、カスタム関数を一度削除して再度設置する、カスタム関数のスクリプトをスクリプトエディタ上で保存する、あるいはカスタム関数へ与えている引数の値を変化させるなどの処理が必要です。ここでは、2019年4月5日に追加されたClass TextFinderを使って少しシンプルな方法を紹介させていただきます。この場合、カスタム関数だけでなくbuilt-in関数もリフレッシュできます。
原理
これまでの経験で、カスタム関数は、一度削除して再度配置することでリフレッシュできることが分かっています。これをClass TextFinderで実現します。具体的には、カスタム関数の一部分、例えば=を一度別の文字に置き換えてから再度元に戻すことでカスタム関数をリフレッシュさせます。
サンプルスクリプト
パターン 1
このパターンでは、スプレッドシート内の全てのシートの全てのカスタム関数、built-in関数がリフレッシュされます。
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var temp = Utilities.getUuid();
ss.createTextFinder("=").matchFormulaText(true).replaceAllWith(temp);
ss.createTextFinder(temp).matchFormulaText(true).replaceAllWith("=");
}
また、TextFinderはシンプルトリガーでも実行可能です。これを利用することで、例えばスプレッドシートを開いた際にカスタム関数をリフレッシュすることも可能です。実は、Sheets APIのFindReplaceでも同様のことは可能なのですが、この場合、シンプルトリガーでは実行できなかったり、Advanced Google servicesでSheets APIを有効にする必要があるという点で少し面倒を感じていましたが、TextFinderが追加されたことでこれらが解消されました。このためのサンプルスクリプトは下記の通りです。これを実行するには、スクリプトをコピーペーストして保存し、スプレッドシートを一度閉じてから再度開きます。これによりonOpenが実行されます。
function onOpen(e) {
var temp = Utilities.getUuid();
e.source.createTextFinder("=").matchFormulaText(true).replaceAllWith(temp);
e.source.createTextFinder(temp).matchFormulaText(true).replaceAllWith("=");
}
パターン 2
このパターンでは、特定のカスタム関数のみをリフレッシュします。このためのサンプルスクリプトは下記の通りです。
function myFunction() {
var customFunctions = ["CUSTOM1", "CUSTOM2"]; // ここにリフレッシュしたいカスタム関数名をセットします。
var ss = SpreadsheetApp.getActiveSpreadsheet();
var temp = Utilities.getUuid();
customFunctions.forEach(function(e) {
ss.createTextFinder("=" + e).matchFormulaText(true).replaceAllWith(temp);
ss.createTextFinder(temp).matchFormulaText(true).replaceAllWith("=" + e);
});
}
追記
- TextFinderは特定のrangeやシート内での検索も可能です。上記のサンプルはスプレッドシート内の全てのシートで実行されますが、これを制限することも可能です。