この記事はGoogle Apps Script Advent Calendar 2021の5日目の記事です
はじめに
GASのカスタム関数を開発する機会があり 、想像以上にカスタム関数を開発するときに注意しなければならないポイントがあったので、この記事で備忘録を兼ねてご紹介したいと思います。
カスタム関数について
この記事で取り扱うカスタム関数 = スプレッドシートのカスタム関数のことを指します。
(ドキュメントはこちら)
↓の画像の様に、GASのトップレベルに関数を記述すると、数式として呼び出すことが可能になります。
関数の中では、シートの値を取得して計算した結果を返したり、APIリクエストを送って結果を返すなど、自由に記述することが可能です。
ちなみに、JSDocで@customfunctionとつけると、数式内で補完してくれるようになります。
(@customfunctionがついていなくても、カスタム関数として呼び出せるので、注意が必要です)
カスタム関数を開発するときの注意点
一見、何でもできそうなカスタム関数ですが、実際に開発してみると、注意すべきポイントがいくつか出てきました。
今回は大きく3つの注意点をご紹介したいと思います。
1. できないこともある
GASでは、標準で利用できるAPIがいくつか用意されていますが、カスタム関数では使えないものがほとんどです。
↓ドキュメント(翻訳)からの抜粋
できること、できないことをまとめてみます。
- できること
- APIリクエストを送る
- Propertyから値を取得する
- Propertyに値をセットする
- シートの値を読み取る
- できないこと
- UIを表示する
- サイドバーの表示
- ダイアログの表示
- 隣接するセル以外に値を書き込む
- リダイレクトさせる (GAS全般)
- UIを表示する
自分の場合は、「隣接するセル以外に値を書き込めない」という仕様にハマりました。
裏側でAPI経由で値を取得し、結果を返すカスタム関数を開発しており、リクエストの結果を別のシートにキャッシュしておいて、API側の負荷軽減をしようと思っていたのですが、そもそも別シートに値を書き込めないことに気が付き、別の案を検討する羽目になりました。
2. カスタム関数の再実行のタイミングについて
2つ目の注意点は、カスタム関数の再実行タイミングについてです。
実は、カスタム関数が実行されると、一定時間自動で実行結果がキャッシュされます。
キャッシュが有効な場合は、更新したり、他の人がシートを開いたとしても、カスタム関数が再実行されることはありません。
そのため、仮に認証が必要なAPIを裏側で叩くようなカスタム関数を作った場合には、そのカスタム関数を実行する権限がない人 (認証してない人) でも、結果が見えてしまう可能性があるので、注意が必要です。
キャッシュの有効期限が切れると、シートをアクティブにした時点で自動でカスタム関数が再実行されてしまうので、再実行を考慮した設計を心がける必要があります。
特に、裏側で認証が必要なAPIを叩くようなカスタム関数の場合は、再実行時にログアウト状態だとエラーになってしまうので、注意が必要です。
例えば、1日でセッションが切れてしまうような設計にしていると、毎日シートを開くと、カスタム関数が再実行されて、エラーになってしまいます。
ユーザー体験的には最悪ですね。
この場合は、リフレッシュトークンを有効にして、オフラインでセッションを更新するなどで対処します。
任意のタイミングで明示的に再実行させたい場合は、以下で再実行が可能です。
- カスタム関数が入ったセルを削除して、戻す
- カスタム関数の引数を変更する
- カスタム関数で参照しているセルの値を書き換える
全てのカスタム関数のセルを再実行させる場合、↑を全セル分手動でやるのはキツイので、スクリプトを書いてあげると便利です。
(この記事が参考になります。)
3. 同時実行数について
最後の注意点は、同時実行数についてです。
以下のような場合で、大量のカスタム関数が実行されてしまう可能性があるため、それを考慮した設計を心がける必要があります。
1． カスタム関数が入ったセルを大量にコピーする
2. カスタム関数が大量のセルにかかれているシートでカスタム関数が再実行される
ドキュメントにもあるように、カスタム関数が短時間に何度も実行されるとエラーが起こる可能性があります。
Script invoked too many times per second for this Google user account.これは、スクリプトが短期間に何度も実行を開始したことを示しています。これは、単一のスプレッドシートで繰り返し呼び出されるカスタム関数で最も一般的に発生します。このエラーを回避するには、カスタム関数のガイドで説明されているように、データの範囲ごとに1回だけ呼び出す必要があるようにカスタム関数をコーディングします 。
回避方法としては、一回の実行で複数セルに書き込むようにするなどが考えられますが、無理な場合もあるので、この辺の設計は難しいです。
一方で、同時実行数にも制限があるので、裏側でAPIリクエストを叩くようなカスタム関数が大量に実行されたとしても、同時にリクエストが送られるのは30個までとなるため、思ったよりAPI側の負荷は高まりませんでした。
しかし、複数のシートで大量にカスタム関数が実行されると一気に負荷は高まる可能性があるため、API側でスロットリングを検討する必要があります。
まとめ
カスタム関数を開発するときの注意点をご紹介しました。
大きく分けて考えることは3つです。
- できないこともある
- 再実行タイミングについて
- 同時実行数について
これらを念頭に置くことで、カスタム関数を開発する際の予期せぬ挙動を防ぐことができれば幸いです 