皆さん、こんにちは。おさとです。
いまだ収束の気配が見えないコロナですが、皆さんお変わりなく過ごされているでしょうか。
さて、今回は「GAS」（都市ガスのことじゃないですよ笑）、Google Apps Script（以下、GAS）を少し触ってみて、ソッコーでGASをソッコーにする方法がわかったのでぜひお伝えしたいと思います笑
ちなみにプログラマー超初心者向け、ですのであしからず。
とりあえずVSCodeを開いておく
GASはJavaScriptでコーディングします。後述しますが、GASのエディタはブラウザベースのため非常に使いづらいです。
もっとより良い使用法については、こちら等をご覧いただくのが良いと思いますが、とりあえずVSCodeならJavaScriptのコードチェック等してくれるためオススメしておきます。
【6分】の壁
そもそもGASをなぜ使おうと思ったかというと、とあるGoogleスプレッドシートの数式をいじっていったらすさまじいスパゲッティ数式ができあがり、しかもそれを各セルにコピペするのですから、編集もままなりません。
「コードさえ書ければ…」
そんな戦闘力53万の強敵を前にしたようなセリフを吐きながら、歯噛みしているときに見つけたのがGASでした。
GASは、Googleが提供している各種Googleのサービスに紐づけることで様々な動作を行わせることができます。今回お話するサービスはGoogleスプレッドシートです。
Googleスプレッドシートでは、例えばプログラムでいうところのfor文のようなループ処理、変数、そういったものが使えません。
なので、for文ではなく各セルにコピペしたときにオートコレクションが働いているか、とか、見ないといけないですし、変数もないためがんばって書いた長～い数式が間違っていたりすると、もうやっていられないわけです。
GASを使い、スプレッドシートに対してそれらのような処理をしてくれれば、大幅な効率アップ間違いなしです！（というか自分がしんどい笑）
しかし、【6分】の壁、というものが存在しています。
それは、「処理が6分を超えると強制終了されること」です。
「処理が6分？6分もかかる処理にお目にかかる方が稀だろう」
そう思われる方もいることでしょう。
しかし、このGAS、処理がかなり遅い！
そのため、100行100列それぞれにfor文ネストでアクセスするような処理を書いていると、これがほぼ確実に6分を超過します。
ちなみに、6分はフリープランの時間。ビジネスプランに変更すれば制限時間は30分になります。
しかし、前述の通り、100行100列の処理をするだけでも6分以上待たされるわけです。
この苦痛自体を何とかしなければいけません。
答えは【テーブル化】
はい、もう答え出しますが、「テーブル化する」これを覚えておきましょう。
例えば、以下のようなシートがあって…
１つ目のシート、会員情報シートに会員のデータが入っており、２つ目のシート、キャンペーン情報シートに現在開催中のキャンペーンのデータが入っているとします。
これらのシートの情報をもとに…
この授業情報シートに、各会員のポイント還元率をすべて自動で入力したい！とします。
具体的な処理はここには書きませんが、シンプルに考えると
for文ネストで行キー「山田 太郎」の情報を会員情報シートから検索し、その情報がキャンペーン情報シートにあるキャンペーンのいずれかにヒットするなら、そのポイント還元率を授業情報シートのセルに埋めていく。
そのような処理になるかと思います。
しかし、各シートの各セルのデータを取得する「getRange(行数, 列数)メソッド」
これが本当に重い笑
そのため
for(var i = 1; i < imax+1; i++) {
for(var j = 1; j < jmax+1; j++) {
var value = sheet.getRange(i, j).getValue();
～valueを使った処理～
}
}
なんて書くと、メソッド呼び出しのたびにシートアクセスがあり、処理が超鈍足になります。
でも、これがないと、各シートの各セルのデータを取得できないわけで。
いくつか方法はあるのですが、初心者私が行ったことが、冒頭で言った「テーブル化」です。
getValues()メソッド
というものがあることを知り、もうこれで良いということにしました笑
getValues()メソッドは、getRangeメソッドで指定された範囲の値を、すべて配列で取得する、というメソッドです。
つまり、「最初からシートのすべての値を二次元配列として持っておく」ということです。配列へのアクセスはお察しの通り高速です。
前例を書き直すなら、このようになるでしょうか。
const arraySheet = sheet.getRange(1, 1, imax, jmax).getValues();
for(var i = 0; i < imax; i++) {
for(var j = 0; j < jmax; j++) {
var value = arraySheet[i, j];
～valueを使った処理～
}
}
これで超重GASとはおさらばです。
もちろん、すべてこの方法が良いというわけではないですが、今回の例のような簡単なループ処理を行いたい場合は、このようにすべて配列で取っておくのが良いと思います。
私はこんな簡単な問題につまづき、かなりの時間を無駄にしてしまいました。
同じ悩みを抱えている方はぜひ一度試してみてください。
また、他にも、6分の壁に挑んだものがあったり、高速化の記事がたくさんありますので、それらも適宜参考にしてみてくださいね。
それでは。
【参考URL】
https://tonari-it.com/
https://qiita.com/yamamow/items/72ba321651ba2274007d
https://qiita.com/s_maeda_fukui/items/d194c6408803229fe1b9