【スプレッドシート】カスタム関数を作る

GoogleスプレッドシートはGAS側に定義した関数(カスタム関数)を呼び出すことができます。
これを使いこなせたら最強だなと思ったのでいろいろ調べました。

GASに定義したカスタム関数を呼ぶ

GAS側に

function hello() {
  return 'hello';
}

という関数があるとしたら、シートには

=hello()

と書けば呼び出せます。
上記の結果としてhelloと表示されます。

簡単すぎる！

カスタム関数に引数を渡す

カスタム関数には引数も渡せます。

• GAS

function hello(name) {
  return 'hello ' + name;
}

• シート

=hello("youtube")

• シート側の結果

hello youtube

補完候補に表示する

カスタム関数はGAS側に定義するだけで使えて便利ですが、現状はシートへの入力時に「不明な関数hello」と表示されるため、うまくいってない感じがします。(実際は問題なし)
なので正しく補完候補が表示されるようにするためにメソッドにコメントをつけます。
最小構成としてはコメントに@customfunctionと書くだけです。

/**
 * @customfunction
 */
function hello(name) {
  return 'hello ' + name;
}

公式ドキュメントには「コメントの記述方法はJSDocと同じです。」と書いてありますが、似てるようで全然違います...
helloメソッドのコメントを真面目に書くとこうなります。

/**
 * helloを表示する
 * @param {'youtube'} name 'hello'の後に表示したい文字列
 * @customfunction
 */
function hello(name) {
  return 'hello ' + name;
}

ここまで書いて補完候補を表示するとこうなります。

ポイント

@paramの波括弧に入力例を書く

JSDocでは@paramの波括弧には型を書きます。
JSDocの例:

@param {string} name 'hello'の後に表示したい文字列

ですが、なぜかGASでは波括弧に入力例を書きます。

@returnは不要

@returnを書きたくなりますが、書いてもどこにも表示されません。

カスタム関数の引数

カスタム関数の引数について詳細をまとめます。

ケース	シート入力例	引数
値	=hello("youtube")	値が入る。 例の場合、youtube
セル	=hello(A1)	セルの値が入る。 例の場合、A1にyoutubeと書いてあるならば、youtubeの文字列です。
範囲	=hello(A1:C3)	指定範囲の2次元配列が入る。
範囲(行)	=hello(1:1)	指定範囲の2次元配列が入る。 ただし1次元目の要素数は1。
範囲(列)	=hello(A:A)	指定範囲の2次元配列が入る。 ただし2次元目の要素数は1。

カスタム関数の戻り値

カスタム関数の戻り値について詳細をまとめます。

値を返す場合

カスタム関数の例

function hello() {
  return 'hello';
}

シート側の結果

hello

1次元配列を返す場合

カスタム関数の例

function hello() {
  return ['hello', 'bye-bye'];
}

シート側の結果

1列に表示されます

2次元配列を返す場合

カスタム関数の例

function hello() {
  return [['hello', 'bye-bye'], ['こんにちは', 'さようなら']];
}

シート側の結果

2行2列になって表示されます

注意: カスタム関数をいっぱい呼ぶな

公式ドキュメントの通りですが、カスタム関数は呼ぶ回数が多くなると処理時間がドンドン長くなります。 関数呼び出しはセルごとではなく、範囲指定にして配列を返すようにして呼び出し頻度を減らしましょう。

まとめ

スプレッドシートからカスタム関数を呼び出す方法を書きました。
範囲指定でドカっと一括処理できるのは便利そう。
ピボットテーブルとか難しすぎて使える気がしないので、こっち路線で攻めようと思います。