便利な自作関数

カスタム関数とは、Excelなどでも大量に用意されているシート上で計算する時に用いているあの関数を自分で作ったものである。Excelではユーザ定義関数と言われているものである。

様々な関数を入れ子にしたり、組み合わせたりしてシートの計算を行っているわけだが、複雑すぎたり、あまりにも長くなってしまったりして、メンテナンス性を損ねているときに、それらの計算処理を単純化する為によく用いられるテクニックである。

Google Apps Scriptでは、これらの関数もJavaScriptを用いて作成することが可能である。Excelのように定義したシートに記述があれば、自作関数として使用することが可能である。外部ライブラリ化が出来れば、呼び出すだけで他のGoogle Spreadsheetでも利用できるので、同じようなものでよく使うオリジナル関数は、なるべくライブラリ化が出来れば、コーディングが楽になる。

作成事例

通常の引数を取って値を計算して返す関数

このケースはよくあるケースで、実際には２つから３つ、場合によっては範囲で指定し、値を返し、その返ってきた値で表計算上のフラグとするなど様々な使い方があると思います。しかし、これを既存の数式のみで作成した場合、数式とりわけIF文が入れ子になっていき、最後にはメンテナンス性の悪い数式となってしまいます。複雑な処理は関数でやらせるのではなく、スクリプトの機能を使って関数化することで、メンテナンス性が保たれ、また、エンドユーザは単純に作成されたカスタム関数を使えば良いだけなので、両者にメリットがあると言えます。

この例の場合、以下のような関数（viewcheck関数）を作りました。

function viewcheck(hosp,flag) {

  if (hosp != "") {

    return 1;

  } else if (flag==1) {

    return 1;

  } else {

    return 0;

  }

}

• 作成した関数はワークシート上で普通に関数として使えるだけでなく、あたりまえですが、スクリプト内でも使用することが可能です。
• 今回は、hospとflagという二つの入り口を用意し、２つの値の結果によって、returnで0か1を返すようにしてます。本来はエラーの場合の処理もつけるべきですが、非常に簡単なものなので、この場合は不要です。
• 関数は必ず引数という関数側が受け取る入口を用意するのが通常です（そうではない、なげっぱなしの場合もありますが）。

２つの範囲指定された範囲を結合させて配列で返す関数

Google Apps Scriptで作成する関数の大きな特徴の一つに、通常は１個値を返す関数なのですが、GASでは配列で返す関数を作成できる点です。今回のケースでは２つの範囲指定を引数として取り、その２つの範囲を合体させて、配列で答えを返しています。Filter関数なんかではおなじみの独特の関数です。引数で取った範囲指定は、普通に２次元配列として取得されるので、それらを予め用意しておいた配列変数にArray.prototype.push.applyにてぶっこんでるだけですので、お手軽ですが、２つ以上のシートに跨った、同じ列数の範囲を簡単に合体して返せるので、結構便利ですよ。

function mixRange(s1range,s2range) {

  //二次元配列を作成する

  var dataArray = new Array(); 

  //作成した２次元配列に２つのレンジの配列をぶっこむ

  Array.prototype.push.apply(dataArray,s1range);

  Array.prototype.push.apply(dataArray,s2range);

  //ぶっこんだ配列を返してあげる

  return dataArray;

}

受け取った値を評価して特定の値を返す関数

よく使うテクニックというか、計算結果が#N/Aであったりとか、#DIV/0!などが返って来てしまう事例があります。本来はデータ側できっちり整理させておくべきことなのですが、面倒ということもあって、引数としてこれらのセルの値を受け取って、これらエラー関係の表記の場合には0を返す関数を作って使っています。

function untina(targetval){

  var chgna = targetval;

  if(isFinite(chgna) == false){

    chgna = 0;

  }

  return chgna;

}

使用例

=viewcheck(A1,B1)

A1セルとB1セルにそれぞれの値が入っています。

今回作った関数の場合、

1. A1セルが空白の場合は1を返す
2. A1セルが空白ではなく、且つB1セルの値が1だったら1を返す
3. いずれの場合でもない場合には0を返す

という仕組みになっています。

注意事項

基本的に自作関数は、スプレッドシート上であろうとスクリプト上であろうと動作します。しかし、スプレッドシート標準の関数はスプレッドシート上でしか動作せず、また、これを迂回するテクニックである「GAS上でスプレッドシート標準関数を使う」テクニックは、スクリプト上では使えるのですが、スプレッドシート上で関数として呼び出して使おうとすると権限がないと怒られてエラーが帰ってきます。よって、スプレッドシート上で関数として呼び出す場合には、スプレッドシート標準関数は自作関数の中に含めて使うことが出来ません。

あまりない事例ではあるのですが、スクリプト上でvlookupやQuery関数を使いたくなるとぶつかる問題なので、注意が必要です。

参考URL

• スプレッドシートで使えるカスタム関数の作成
• Spreadsheetの独自関数をGASで作るよ☆