スプレッドシートのカスタム関数でお手軽処理しましょう

この記事は ユニークビジョン株式会社 Advent Calendar 2021 13日目の記事です。

はじめに

Spreadsheetで割とよくやるけど、あらかじめ備わっている SUMやAVERAGEの関数だけではできない、あるいは職人芸的な関数式を書かないといけないことがあると思います。
そんな時に活用できる手段の一つとしてカスタム関数があります。
簡単に言うとSUMやAVERAGEのような関数を自作して好きな時に利用できる機能です。

まずは作ってみる

新しいシートを開いたら、メニューから 拡張機能 -> Apps Script を選択してスクリプトエディタを開きます。
コード.gs に空の関数 myFunction() が実装されている状態だと思うので、以下のようにします。

コード.gs

function helloWorld() {
  return 'Hello World';
}

保存したらシートの適当なセルで関数を実行します。

すると以下のようになります。

helloWorld()関数が返している Hello World という値がセルに表示されました。カスタム関数とはこのように、GASのトップレベルに定義した関数をシートから実行できる機能です。

引数を渡す

単一セルの引数

コード.gs

function helloWorld(value) {
  return `Hello ${value}!!`;
}

helloWorld を上記のように書き換えて実行してみます。

②の例では引数に指定したA3列が空文字なので結果には空文字が埋め込まれています。
④の例では引数に何も指定していないので undefined になっていますね。

デフォルト引数

先ほどの関数を以下のように書き換えると④の結果は変わります。

コード.gs

function helloWorld(value = 'ワールド') {
  return `Hello ${value}!!`;
}

複数セルの引数

=SUM(A1:A5) のように引数に範囲を指定することもできます。
範囲指定した場合引数には行の配列の中に列の配列がある2次元配列が渡されます。

コード.gs

function sushi(values) {
  return `私は ${values} を食べたい。`;
}

上記の sushi 関数の引数に①単一行複数列、②複数行単一列、③複数行複数列を指定したときの valuesの値と実行結果は以下のようになります。

戻り値

戻り値の形式によってシート上でどのように表示されるかが変わります。

単一の値

最初の例のように単一の値を返す場合、返された値が関数を呼び出したセルに表示されます。

コード.gs

function helloWorld() {
  return 'Hello World';
}

配列

配列を返した場合には、カスタム関数を実行したセルを起点に任意の範囲に値が表示されます。
以下の2つの関数を実際に実行してみます。
（余談ですが関数名は日本語でもOKです。）

コード.gs

function 一次元の配列() {
  return ['りんご','みかん','ばなな'];
}

function 二次元の配列() {
  return [['りんご','みかん','ばなな'], ['大根','人参','玉ねぎ']];
}

ちょっとわかりにくいのでカスタム関数を実行しているセルをハイライトしています。
以下のように、配列の各要素は行に展開されます（①）。配列の要素が配列だった場合には列に展開されます（②）。

この状態で例えば E7 セルに値を入力するとカスタム関数の出力結果とバッティングしてしまうため関数がエラーになります。この辺りは組み込みの IMPORTRANGE関数などと同じ挙動です。

カスタム関数のデバッグ

GASのエディタで関数をデバッグする場合、通常はエディター上でブレイクポイントを切って変数の中身を確認したり、Loggerに出力してログを確認したりすることができます。

しかし、シートから実行するカスタム関数の場合この機能が使えず、例えば実際にシートから任意の引数を渡した場合に処理がどのようになっているか？といったことを確認することができません。
代替案としてエディターのメニューから「実行数」を開くと、カスタム関数として実行された関数の履歴も確認することができます。

　↓

関数のドキュメント

JsDoc形式に対応しており、@customfunction　タグをつけるとシート上でカスタム関数名の入力補完や引数の説明を参照できるようになります。
非エンジニアの方が使うときに困らないように記載しましょう。

コード.gs

/**
 * Hello {value}!! の文字列を返します。
 *
 * @param {'ワールド'} value 埋め込みたい文字列
 * @return Hello ${value}!!
 * @customfunction
 */
function helloWorld(value) {
  return `Hello ${value}!!`;
}

おわりに

スプレッドシートの良いところは非エンジニアの方でも数式を使っていろいろな処理を手軽にできることです。そしてGASを実装できるエンジニアであればさらに複雑なことができます。カスタム関数機能はそんな非エンジニアとエンジニアの懸け橋になるような機能です。便利なカスタム関数を作って作業を効率化したいですね。