スプレッドシートで使えるカスタム関数の作成

前回は、「Hello World!」というメッセージを表示する簡単なスクリプト(関数)を例に、Google Apps Scriptの基本となる作成や保存、実行の流れを紹介した。スクリプトの実行では、スクリプトマネージャから行う方法のほか、メニュー項目に追加する方法や、ボタン図形を作成してクリックで実行する方法などは覚えておくと役に立つだろう。

今回から2回にわたって、スプレッドシートでのカスタム関数の作成と、文書をメールに添付して送るスクリプトを営業日報をサンプルとして紹介する。

カスタム関数は、マイクロソフトのExcelでいうところの「ユーザー定義関数」に相当するものだ。Google Appsの中ではスプレッドシートに特化した機能だが、スクリプトを実行例として典型的な使用例のひとつとも言えるだろう。

さっそくだが、下記のような営業日報シートを用意した。あくまでサンプルなので、入力する項目も訪問先・訪問時刻・報告内容と非常にシンプルなものだ。

スプレッドシートでは、ご存知の通りExcelと同様に関数が使える。たとえば、セルB2には、今日の日付を表示する「=today()」関数が入力されている。

ちょっと余談になるが、Google Appsのスプレッドシートで使用できる関数には、SUM関数やVLOOKUP関数、IF関数などExcelでお馴染みのものを含め、多数用意されている(ヘルプメニューの関数リストからも閲覧できる)。

前回のスクリプトは、メニュー項目の選択やボタンのクリックなど、実行のタイミングをユーザーが決めるものだった。カスタム関数を使うと、スプレッドシートを開いたり編集したりした際に、自動で計算して値を表示することが可能になる。

とはいっても、それほど特別な操作が必要なわけではない。

まず、セルに入力するカスタム関数の簡単な例として、日報の訪問時刻と退出時刻から滞在時間を計算してみよう。実際のところはセルE5に引き算式「=E4-E3」を入力してもいいのだが、これをスクリプトで実行する場合には、日付から経過ミリ秒を求めて計算するあたりがポイントだ。

以下がそのスクリプト内容。スクリプトエディタで記述する方法は前回と同様だ。異なるのは、最初の行で関数名「houmonJikan」に続いて、仮引数となる「hou1」と「hou2」が追加されていること。ここに、スプレッドシートで使用する際に関数で指定したセルの値が渡されるわけだ。

function houmonJikan(hou1,hou2) {
    var houmon = (hou2.getTime()-hou1.getTime())/(1000*60*60*24);
    return houmon;
}

2行目では、シートから「hou2」に渡された終了時刻から「getTime()」で経過ミリ秒を得ている。ここから同じく訪問時刻の「hou1」を経過ミリ秒で引けば、滞在時間が計算できることになる。ただし、滞在時間をミリ秒で表示しても意味が無い。最後に、計算結果のミリ秒(1/1000秒)を時刻に戻すために、「1000ミリ秒×60秒×60分×24時間」で割っている。

スプレッドシートでは、カスタム変数に設定したスクリプト名を使用して、通常の関数のようにセルに入力すればよい。今回の場合では、セルE5に「=houmonJikan(C5,D5)」と入力している。

カスタム関数は、あらかじめ用意された関数ではできないことを、スクリプトで記述することで自由に拡張可能だ。

一方で、今回のスクリプトでは省略しているが、仮引数としてスクリプトに渡す値のチェックなどをきちんと行わないと予想外のエラーが発生したりもする。上記の例では、時間の差を求めているので、本来であれば計算する2つの時刻の前後が正しいかなどをチェックしないと、エラーになったり思わぬ結果が表示されたりといった可能性もある。

今回はあくまで使用例として簡単な例を取りあげたが、実際に利用するには(特に自分1人だけで利用するのではなく、他の人にも渡す場合には)、エラー処理や分岐処理などを加えておいた方がよい。

次回は、この営業日報をスクリプトを利用し、スプレッドシートを添付ファイルとしてメールで送る方法を扱う予定だ。