【GAS】スプレッドシートの"大量データ"を"びっくりするくらい高速"で自動転記する!方法

スプレッドシートからスプレッドシートへ大量のデータを自動転記したい場合いくつかの方法論があります。

おそらく最初は手動でやっていて「この作業なんとかならんもんかね・・」と思い、IMPORTRANGE関数に辿り着くっていうのがよくある流れなんじゃないでしょうか。

「IMPORTRANGE関数」は悪手

しかしです、何度でも言いますがimportrangeは長期的に利用するシートにおいては完全に悪手です。

しかし、この関数は常時自動更新するため、とにかく重いんです。
いろんな関数を組んでいくと一生読み込み中になった人も少なくないのではないでしょうか？少なからず私はこの関数に何度冷や汗をかかされたか。
長期的にそのスプレッドシートを使っていくのなら、間違いなくIMPORTRANGE関数は悪手です。では代替手段はどうするかと言う話になります。今更手動ではやりたくないですよね。
そんな時に使えるのがGASを使ったコピー&貼り付けです。

通常のGASの書き方だと限界がある

じゃあどうするかというと、上記の記事でお伝えしたGASでコピーして貼り付けるという方法です。ある程度の量のデータだったり、スプシが軽いうちはこの方法でいいでしょう。

しかし、ある程度運用していくと大量のデータを取り扱うことが増えてきます。そこである壁にぶつかっていきます。
まず初めに「大量データのコピー貼り付けに時間がかかる」という壁です。これは直接的には問題になりません。なぜなら待っていれば目的は遂行できるからです。

そして「大量データのコピー貼り付けが時々エラーになる」という第2の壁にぶつかります。当初は「時間帯が悪いのかな〜・・」とか思いながら再実行して事なきを得るのですが、徐々にエラーの頻度の方が多くなっていきます。

更に恐ろしいのが、そういった特定スクリプトのエラーのせいで、次のスクリプトの実行を妨げるということです。ドミノ倒し的にエラーを起こし、1日に設定していたトリガーの半数がエラーになった時期があり、顔面蒼白になったこともありました。。

そういったエラーを避けるための場当たり的な手段

とにかく場当たり的に回避してしまいたい場合はこちらの記事が参考になるかと思います。

そもそもスクリプトのどこでつまるのか

具体的な手法をご紹介する前に、今までのスクリプトのどこでこんなに時間が掛かっているのか気になりませんか？

以前この点を調べたことがあります。詳細は割愛しますが、「コピー」はどんな量でもそこまで問題になることはありません。「貼り付けの動作」でとんでもなく時間が掛かっていることがほとんどでした

今回の手法で何が改善されるか

こちらでご紹介したスクリプトのイメージは、コピーした後に、手動でスプレッドシートに貼り付けることを細かく繰り返しているようなイメージです。そう聞けば時間がかかるのも納得だし、重くなったスプシではエラーが起きやすくなる点も納得がいきますよね。

そもそもエラーが起きる時は、貼り付けの動作に時間が掛かっていることを踏まえると、この貼り付け方法にメスを入れる必要があるということです。

そして今回ご紹介する方法が、配列としてデータをまるっと貼り付ける方法です！

「データまとめといたから、この範囲にデータをまるっと貼り付けてね！」ってやるので、スプシの状態にあまり影響を受けないんです。ちょっとイメージ湧きにくいかもしれませんが、実際にスクリプト稼働していただければ全て納得いただけると思います。

なんせ5分以上かかっていたスクリプトがものの数十秒で終わるんですから😭では詳細をみていきましょう。