2021-05-08
こんにちは、0371です。
今回はGoogleスプレッドシートでJavaScriptを使用して作成するスクリプトの備忘録を残しておきます。
Googleスプレッドシートとは、簡単に言うとGoogleが提供しているブラウザ版のExcelです。
インターネットに接続していればいつでも使用することができ、他のユーザーと同時にファイルの編集を行うことが可能です。
ただし、機能はExcelの方が多いです。
Googleサービスとの連携が容易この三つが大きなメリットです。
デメリットはこんな感じですが、個人的にはメリットの方が大きいと思っています。
Googleスプレッドシートのスクリプトは、Google App Script (GAS)と呼ばれ、JavaScriptを使用して記述します。
一部のライブラリは使用できるようですが、基本的には生のJavaScriptを使用しなければならないと考えて良いでしょう。
また、GAS独自の関数もありますが、難しくはないのでコツをつかめば簡単にコードを記述していくことができます。
さて、ここからは実際にGASを使用して気がついたことを書き連ねていきます。
これはコードの可読性が著しく劣悪になることから、問題になりました。
基本的に表形式のデータを扱うので、必然的に二次元配列を多用します。
それを各グループに分け、分けたグループをさらに配列に入れる・・・などとしていると、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を勉強する価値は十分にあると感じました。