Google Apps Scriptでユーザ定義関数を作る【GAS】
あまり知られていないのか、実はExcelでもGoogle Spreadsheetでも、自分で関数を作って使う事ができます。所謂ユーザ定義関数という奴です。どちらも、VBAやGoogle Apps Scriptというプログラミング言語を用いて作るわけなのですが、スクリプト内だけでなくワークシート上でも使えるのが味噌です。もちろん、スクリプト内でも使用できますので、プログラミングの入門として学ぶには調度良いサイズの課題とも言えます。
Google Apps Scriptでは、これらの関数もJavaScriptを用いて作成することが可能である。Excelのように定義したシートに記述があれば、自作関数として使用することが可能である。外部ライブラリ化が出来れば、呼び出すだけで他のGoogle Spreadsheetでも利用できるので、同じようなものでよく使うオリジナル関数は、なるべくライブラリ化が出来れば、コーディングが楽になります。また、扱うのがやっかいなクラスやメソッドをラッピングした関数を自作すれば、普段使う上で頭を悩ませながら、メソッドを記述しなくて済むというメリットもあります。
目次
使用するクラス・メソッド、スプレッドシート
作成事例
通常の引数を取って値を計算して返す関数
非常にポピュラーなタイプの関数です。いくつかの引数を括弧の中で受け取り、値を返す。今回のサンプルはif文ではいくつもの入れ子状態になってメンテナンス性の悪くなった状態を解消するためにswtich文を使った関数を紹介します。ユーザは通常のスプレッドシート関数のように扱えるので、頭を悩ませながら数式を組む必要がありません。引数は単なる値を受け取ったり、セルの値や範囲指定、フラグ的な値を格納する事ができます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
function choice(selectval){ var temp = ""; switch(selectval){ case "キノコ": temp = 1; break; case "野菜": temp = 2; break; case "果物": temp = 3; break; } return temp; } |
- selectvalで単純に３種類の文字列を受け取ります。
- 受け取った値を判定し、tempに値を格納する。色々な判定をさせる事が可能である。
- 最後にreturnで返して上げるとセルに結果が表示される。
引数を元に計算や加工をして返す関数
sum関数のように、引数に受け取った値を特定の演算加工をして返してあげるタイプの関数です。主に計算式をこの関数内で構築して計算結果を返してあげるのが目的です。シンプルな例では、消費税の計算をするような関数がこれに該当します。さらに、フラグ的な値を受け取り、税込み金額で返すか？税額だけ返すか？なんて条件分岐をさせて１つの関数で２つ以上の機能を持たせるなんてことも出来ます。
今回は、Google SpreadsheetのExcelにない特徴を活かした関数を紹介します。それは、配列で受け取り配列で返すという関数です。Excelは関数で配列を返すような真似はできませんが、JavaScriptベースのGoogle Apps Scriptはそれが可能です。それを利用した関数が独自関数であるFilter関数などです。配列で返すので、１個のセルに計算式を入れると、複数のセルに結果が展開されるわけです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
function mixRange(s1range,s2range) { //二次元配列を作成する var dataArray = new Array(); //作成した２次元配列に２つのレンジの配列をぶっこむ Array.prototype.push.apply(dataArray,s1range); Array.prototype.push.apply(dataArray,s2range); //ぶっこんだ配列を返してあげる return dataArray; } |
- ２つの配列を合体させることが可能です。
- 引数に範囲指定した２つのエリアを取ります。
- Array.prototype.push.applyというJavaScriptのメソッドを利用しています。
- 配列で返す関数の場合、計算式の入ってるセルさえ消さなければ、ソレ以外のセルをいくら編集しても、再計算されて値は復元されます。計算式のセルだけロックを掛けておくなんて事も有効です。
扱いの面倒なメソッドをラッピングして使いやすくする関数
Google Apps Scriptのメソッドは少々やっかいな仕組みのメソッドがたくさんあります。他のメソッドのように直感的ではないものがたくさんあり、それをそのままコードとして書いてると冗長になったり、いちいち頭を悩ませるなど面倒です。とりわけ最近廃止されたDocsListではgetParent()[0].getId()でそのファイルの親フォルダのIDを取得できていたのに、DriveAppに置き換えられてからは、直接的なそのファイルの所属する親フォルダのIDを取得できなくなりました。
このDriveAppというものでsearchFileやgetParentsなどはイテレータと呼ばれる配列とは異なるタイプで値を返すものはちょっと加工をしてあげなければなりません。毎回書くのが面倒臭いのでこうした処理を一纏めにして、あちこちでファイルIDだけ渡して親フォルダのIDを取得させていたりします。この関数はスプレッドシートでは使うものではなく、Google Apps Script内で使用する関数です。
|
1 2 3 4 5 6 7 8 9 |
//親フォルダのIDを返すラッピング関数 function driveman(id){ var parentsfolder = DriveApp.getFileById(id).getParents(); while (parentsfolder.hasNext()) { var folder = parentsfolder.next(); var idval = folder.getId(); return idval; } } |
- このサンプルは、スプレッドシートのメニューより、【親フォルダ調査】を開き、ID入力画面を入れて実行すると親フォルダのIDが帰ってきます。（要アカウント）
- idにはファイルのIDを渡してあげます。
- フォルダイテレータが返ってくるので、hasNextとnextメソッドでひとつひとつループで値を取り出します。
- 今回はひとつのファイルを複数のフォルダには所属させていないので、これでファイルの親フォルダが取得できます。
- ファイルを複数のフォルダに所属させている場合には、もう一手間必要になります。
- getNameでフォルダ名、getUrlでフォルダのURLを取得できます。
注意事項
- 現在、Google Apps Script上では、Spreadsheet標準関数をGAS内で使うといった事ができません。擬似的にやる方法は、関連リンクのScriptでスプレッドシートにvlookupやcountifのような関数を埋め込む方法を参照して下さい。
- ちなみに、ExcelはVBA内でワークシート標準関数を呼び出して使うことが可能です。 – ワークシート関数をVBAで使用する by MOUG
- なるべくスクリプトを実行時には、自作で関数を作って値を操作するよりも、表示系の計算はスプレッドシート標準の関数を使って計算をさせたほうが早いです。
- あまりに複雑な何重ものiｆの入れ子などにはとても有効です。
- ライブラリ化を行うと、そのスプレッドシート以外でも自作関数を利用することが出来るようになります。
- Google Apps Scriptで提供されてるクラスやメソッド（DriveAppなど）は、承認しないと使用することが出来ないので、通常スプレッドシート関数では使用しない。GAS内で参照させる関数として使用します。
- 条件付き書式設定のカスタム数式でユーザ定義関数は利用できません。
- ユーザ定義関数は通常は、GASの他のクラスサービス（つまり承認を要求するようなもの）を含めず、基本演算と判定にのみ使うべきです。こちらに使えるクラス一覧がありますが、本来のユーザ定義関数は小規模なものに留めなければ、余計なトラブルを招きます（トラブル事例）
