Googleスプレッドシートのスクリプト(GAS)の備忘録
2021-05-08
こんにちは、0371です。
今回はGoogleスプレッドシートでJavaScriptを使用して作成するスクリプトの備忘録を残しておきます。
Googleスプレッドシート とは？
Googleスプレッドシートとは、簡単に言うとGoogleが提供しているブラウザ版のExcelです。
インターネットに接続していればいつでも使用することができ、他のユーザーと同時にファイルの編集を行うことが可能です。
ただし、機能はExcelの方が多いです。
Googleスプレッドシートのメリット
- 無料で使用できる
- 複雑な処理でなければ、十分な機能を備えている
- 各種
Googleサービスとの連携が容易
この三つが大きなメリットです。
Googleスプレッドシートのデメリット
- 複雑な処理を行う場合は、機能が足りない場合がある
- スクリプトの実行時間が最長で6分まで（無料枠）
- そんなにインターネットに情報が載っていない
デメリットはこんな感じですが、個人的にはメリットの方が大きいと思っています。
スクリプトについて
Googleスプレッドシートのスクリプトは、Google App Script (GAS)と呼ばれ、JavaScriptを使用して記述します。
一部のライブラリは使用できるようですが、基本的には生のJavaScriptを使用しなければならないと考えて良いでしょう。
また、GAS独自の関数もありますが、難しくはないのでコツをつかめば簡単にコードを記述していくことができます。
備忘録
さて、ここからは実際にGASを使用して気がついたことを書き連ねていきます。
forのネストが多すぎて、わけがわからなくなる
これはコードの可読性が著しく劣悪になることから、問題になりました。
基本的に表形式のデータを扱うので、必然的に二次元配列を多用します。
それを各グループに分け、分けたグループをさらに配列に入れる・・・などとしていると、4~5次元配列になってしまいます（なるな）
そのような多次元の配列の要素にアクセスするとなると、配列の次元の数だけforで回す必要が出てきます（しろめ）
知らないうちに、forのネスト地獄が形成されてしまうのです。
恐ろしくて夜も眠れません。
解決策としては、
- 処理を分離し、関数内のコードを細分化することでネストを浅くする。
- ラムダ式などのコードを簡潔にするテクニックを使用する。
for文を使用しなくても良い処理方法を考えてみる。
当たり前かもしれませんが、これらが一番効果的だと思います。
setValue よりも setValuesを使え
これは、GASの実行時間に制限があることから、問題になりました。
setValueとsetValuesは、どちらもGoogleスプレッドシートのセルに値を書き込むGASの独自関数です。
setValueはセルを一つ一つ選択して書き込みを行い、setValuesはセルの範囲を選択して、二次元配列として一気に値を書き込む関数です。
このGASの独自関数は、生のJavaScriptの関数よりも処理時間が多くかかってしまいます。
そのため、GASの独自関数を極力記述しないようなプログラムを書くことを意識する必要があります。
具体的に言うと、「あるシートの値を10000個取得し、整形し、別のシートに結果をセル100個分書き込む」というプログラムを書きました。
setValueだと、制限時間である6分をオーバーします。
setValuesだと、40秒ほどで完了します。
約9倍くらいの差です！ すごい！！！！
ということで、setValuesを積極的に使用することにしましょう。
セルから取得した値には、明示的に型の指定をしろ
これは、主に数値の計算や、日付の比較をするときに問題になります。
スプレッドシート内では、「数値」として表示されていた値をgetValueで取得すると、なぜか「文字列」として扱われることが多々発生しました。
同じように、日付を「文字列」として取得したいのに、「日付」型として取得されたことによって、条件分岐がうまくいかないことがあります。
自分が行った対策としては
- 取得した値を使用する時は、明示的に型を指定してあげる この一点です。
ほかにいいアイデアがある方は、TwitterなどのDMで教えてください（しろめ）
まとめ
今回は、Googleスプレッドシートに関する知識を得ることができました。
最初は多次元配列に悪戦苦闘、白目を剥きながらも最終的には慣れることができました。
IT系以外の職種でも、Googleスプレッドシートを使用する機会はあると思うので、今後も需要が増すGASを勉強する価値は十分にあると感じました。