こんにちは、堺です。
GoogleスプレッドシートをGASで操作するとき、セルごとに個別にアクセスするコードを書くと、思いがけずとんでもなく低速な処理になってしまいます。
エクセル×VBAなどでは大した影響が出ないので見落とされがちですが、GASにおいては手戻りにも繋がりかねない重要なポイントです。今回はその点を共有していきます。
尚、様々なセル編集などについては、下の関連記事で網羅的にまとめておりますのでご参照ください。
◇目次
GASではセルへの読み書きはまとめて行うべし
GASスクリプトからスプレッドシートのセルへのアクセスでは、接続時のI/Oがさほど高速ではありません。（少なくとも現段階では。将来改善される可能性はあります。検証はこの記事の末尾で行っています。）
したがって、仮に1セルずつアクセスするコードを書いてしまうと、量によっては低速な実行速度になってしまいます。
そこで、セルにアクセスするgetRange関数の使用時に範囲を指定し、まとめて読み書きを行うようにコードを書くことが基本となります。
具体例を見てみる。まずはセルの読み込み処理。
個別にセルの値を取得するなら、次の通りです。
JavaScript
// 個別に値を取得する例（※注意：量が多くなると低速になります）
const b2_value = work_sheet.getRange("B2").getValue(); //'テスト1'
const b3_value = work_sheet.getRange("B3").getValue(); //'3000'
const b4_value = work_sheet.getRange("B3").getValue(); //'3000'
const c2_value = work_sheet.getRange("C2").getValue(); //'205'
const c3_value = work_sheet.getRange("C3").getValue(); //'テスト2'
この方法では、上でも述べた通りI/O速度の問題でかなり低速になってしまいます。
少量なら全く問題にはなりませんが、処理するセル数が1000を超えたあたりから体感で分かるくらい影響が出ます。（ご興味がある方は、簡易パフォーマンス実測をこの記事の下の方に載せています。良かったらご参考ください。）
そこで、getRangeをする際に次のように範囲を指定して一括で値を取得することがポイントです。getValue関数ではなくgetValues関数を使用しています。
JavaScript
// 範囲を指定して値を取得する例（推奨）
const values = work_sheet.getRange("B2:C4").getValues();
// valuesは次のような二次元配列で取得されます。
// [['テスト1',3000],[205,'テスト2'],['テスト3',150]]
上記の範囲取得処理は、次のように行列位置・サイズを指定しても同じ結果を得られます。
getRangeの引数は、順番に（起点となる行番号、起点となる列番号、起点から取得する行の長さ、起点から取得する列の長さ）です。
JavaScript
// 範囲をして取得。
// getRangeの引数は、順番に（起点となる行番号、起点となる列番号、起点から取得する行の長さ、起点から取得する列の長さ）
const values = work_sheet.getRange(2, 2, 3, 2).getValues();
//[['テスト1',3000],[205,'テスト2'],['テスト3',150]]
セルへの書き込み（値セットや書式設定など）の処理も、同じ要領でまとめてアクセスする
セル書き込み処理に関しても、同じようにgetRangeでまとめてセルにアクセスした上で、値などを指定します。
個別に値を指定するには、getValuesで返ってきたような二次元配列を作成し、関数の引数とします。
JavaScript
//範囲を指定してセルに値を入れる
work_sheet.getRange(2, 2, 3, 2).setValues([['あ','い'],['う','え'],['お','か']]);
指定した範囲に全て同じ値をセットするには、setValuesではなく、setValue関数を使います。
JavaScript
//範囲を指定してセルに値を入れる
work_sheet.getRange(2, 2, 3, 2).setValue('き');
その他、数式指定（setFormula/setFormulas）や背景色（setBackground/setBackgrounds）など、およそセル編集の全てに関して、一括で範囲を指定して処理することが推奨されます。
尚、関数名は複数形"s"で区別するので、ミスが起こりやすいポイントです。
"s"をつけ忘れても普通に動作してしまう場合が多いので、結果が変な場合はまず"s"つけ忘れを捜査してみましょう。
（おまけ）単一セル操作の繰り返しが実際どれくらい遅いのか試してみた
ここまで、単セル処理は遅いと述べてきましたが、実際どのくらいなのか、簡易的にパフォーマンス比較を行ってみます。
読み込みと書き込みを1万セル試して所要時間を確認してみます。個別の方は、パフォーマンスに極力影響が出ない様、forでループを回します。
結果は見ての通りで、予想通り大幅な差が得られました。
読み込み処理・・・個別だと25msec/cell＝1万セルで25秒。まとめて処理すると1秒未満。
書き込み処理・・・個別だと21msec/cell＝1万セルで21秒。まとめて処理すると1秒。
結び：開発者の時間を少しでも節約できれば幸いです
初めてでしんどい目にもあいましたが、個人的にはVBAよりはJavascriptの方が書きやすいと思いますので、この転換はありがたい限りです。
皆様も、ポイントをサクッと抑えて共に楽しいGASライフを送りましょう。お目通しありがとうございました。
記事筆者へのお問い合わせ、仕事のご依頼
当社では、IT活用をはじめ、業務効率化やM&A、管理会計など幅広い分野でコンサルティング事業・IT開発事業を行っております。
この記事をご覧になり、もし相談してみたい点などがあれば、ぜひ問い合わせフォームまでご連絡ください。
皆様のご投稿をお待ちしております。
