Google Apps Script Advent Calendar三日目です。
今日はGoogle Apps ScriptでGoogle Spreadsheet（最近Sheetsに改名されましたが）の独自関数を作ってみたいと思います。
独自関数の作り方
Google Spreadsheetの独自関数の作り方は簡単です。
一日目に書いたようにSpreadsheetのGAS Editorでグローバルな所に関数を書きます。
今回は受け取った値を消費税計算して返却する関数を作ってみましょう。
function 消費税(num){
return 1.08 * num;
}
これだけで独自関数が完成しました。
なお税率のところをちゃんと別定義しろとかそういう話はなしでお願いいたします。
独自関数の呼び出し
Spreadsheetからこの関数を呼び出す場合は任意のセルに=消費税(100)などのように=関数名(引数)の形で記述します。
なお今回は関数名を日本語にしました。もちろんアルファベットで定義しても呼び出せますが、日本語(漢字やかな)でも呼び出し可能です。
細かい話
これだけではAdvent Calendarの意味が無いのでちょっと細かい話を書いていきます。
引数
独自関数の引数はいくつでも指定可能です。（多分）
また=関数(A1)や=関数(A1:B3)の様にセルの参照を渡すこともできます。
ただし、複数のセルの参照(A1:B1やA1:B2)を渡した場合は、一行であろうと一列であろうと2次元配列の形で返却されます。
例えば=関数(A1:B1)の様に渡した場合は[[A1の値, B1の値]]が渡され、
=関数(A1:A2)の様に渡した場合は[[A1の値],[B1の値]]が渡されます。
なお可変長引数にしたい場合は通常のjavascript同様にarguments変数を利用して
取得してください。
function 可変長引数(val1 , val2, args){
//第三引数を可変長引数とする
var var_args = [];
for(var i = 2; i < arguments.length;i++) {
var_args.push(arguments[i]);
}
return JSON.stringify(var_args);
}
なおもちろんSpreadsheetの関数結果自体を渡すことも可能です。
返却値
返却値には数値、日付、文字列、配列の3つのいずれかの型が返却可能です。
数値は普通に計算結果を返却してください。先ほどの消費税計算みたいな感じですね。
日付はDate型オブジェクトを返却すると、セルが日付として扱ってくれます。
文字列はまぁ並々に
そしてExcelには無いSpreadsheetの特徴として配列の返却があります。
Spreadsheetでは配列関数と呼ばれる一つのセルに記述した関数から複数のセルに結果を反映することができます。
例えばExcelでよくやる「行列を入れ替えて貼り付け」はSpreadsheetでは=TRANSPOSE(セルへの参照)という関数を利用します。
この関数をどこかのセルに書くとその結果が書いたセルを基準に複数のセルへ展開されます。
GASでも同様に配列値を返却すると関数が呼ばれた場所を基準に複数セルへ値が展開されます。
function returnArray(){
return [["1", "2"]];
}
上記スクリプトをGAS Editorに貼り付けて、セルから=returnArray()を呼び出してみてください。
関数を書いたセルに1が、隣のセルに2が反映されたと思います。
なおSpreadsheetではこの仕組を利用して、FILTER(特定データの抽出)やQUERY(SQLライクなクエリを利用した抽出)等の関数が利用できます。
今回はGASネタなのであまり書けないので是非関数のヘルプを見てみてください。
ログ
GASではLogger.log(表示したいもの)という形でログを表示しますが、
独自関数の場合はこのLoggerを利用してもログを見ることはできません。
なので対象の関数にLoggerを書いておきをGAS Editorで直接呼び出すことで挙動をテストして下さい。
ただ引数に関してはテストしづらいと思うので、
function logger() {
return JSON.stringify(arguments);
}
見たいのを作っておいて引数について見てみるとどういう値が渡ってきているかわかると思います。
権限
GASでは様々なサービスと連携可能なのですが、
独自関数については制限があります。
まず利用できるサービスはSOAP, UrlFetch, Google Translateと一部のSpreadsheetAppのみです。
またSpreadsheetAppは書き込み系の操作は不可能です。
なので独自関数を何かの書き込み処理の起点としては利用できないということを覚えておいてください。
グローバル変数
もし対象の独自関数が書いてあるGASプロジェクトにグローバル変数があった場合、
そのグローバル変数はセルの数だけ初期化されます。
つまり
var hoge = ScriptProperties.getProperty("初期値");
のような記述がグローバル変数にあり、これが書いてある対象のプロジェクト内の独自関数を100箇所のセルで呼び出すと、100回ScriptProperties.getProperty()が呼び出されます。
これは最大呼び出し数の制限に引っかかる原因となるので覚えておいてください。
まとめ
独自関数について書かせていただきました。
配列の扱いを如何にうまくやるかが独自関数の大事なポイントかなーと勝手に思ってます。
ExcelにあるけどSpreadsheetに無いみたいな関数を独自関数で作ると幸せかもしれません。