Google Apps Scriptのスプレッドシート読み書きを格段に高速化をする方法

★気に入ったらシェアをお願いします!


speedup

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

Google Apps Scriptでスプレッドシートの操作をしていて、こう感じたことはありませんか?

実行速度が遅い…遅いぞ!

スプレッドシートへの読み書きが多いと、どうやらすごく遅くなってしまいます。

エクセルVBAの場合は画面表示の更新を停止するなどをして高速化をすることができましたが

遅い…重い…そんなエクセルVBAプログラムの処理速度を劇的に改善する方法
エクセルVBAの実行速度が遅い・重いって時ありませんか?そんな時にエクセルVBAのプログラムの処理速度を速くするテクニックを紹介します。プログラムの実行時間を測定する方法も合わせてお伝えします。

Google Apps Scriptの場合はどうすれば良いでしょうか…?

今回はGoogle Apps Scriptでスプレッドシートを操作する場合に処理速度を格段に速くする方法をお伝えしていきます。

スポンサーリンク

どれくらい遅いか?

例えば、以前紹介しましたこちらの記事のスクリプト。

【GAS】名刺管理アプリの出力データを活用してメルマガ配信リストを自動更新する
Google Apps Scriptでメルマガ配信システムを作りました。今回はスプレッドシートにインポートした名刺管理アプリのデータを使ってメルマガ配信リストを自動更新する方法を紹介いていきます。

名刺管理サービスCAMCARDから出力した連絡先リストを取り込んで、メルマガ配信リストを更新するものです。

このスプリプトに時間を測定する処理をちょちょっと追加します。

例えばCAMCARDのデータが50行あったとして、測定しながら実行してみますと

メルマガリスト更新のGoogle Apps Scriptの実行速度を測定

このように約42秒もかかります。

これが500件、5000件となったら…ちょっと実用に耐えないですよね。

今回はこの課題を解決していきます。

APIの呼び出し回数が多いと遅くなる

なぜこのスクリプトが遅いか?ということなのですが、ヒントはAPIの呼び出し回数にあります。

ここで言うAPIは他でもないSpreadsheetAppです。

つまり

  • getActiveSpreadsheet()
  • getSheetByName()
  • getDataRange()
  • getLastRow()
  • getRange()
  • getValue()
  • setValue()

などを使ってスプレッドシートにアクセスをするたびにAPIが呼び出されています。

ですから例えば

このifの条件比較には一気に4つのAPI呼び出しがあります。これが入れ子状態のfor文の繰り返しの処理に含まれていていたら…Google Apps Script的にはたまったもんじゃないわけです。

お題:シート範囲を別シートにコピーする

今回はこのようなお題を使ってスプレッドシートを扱うGoogle Apps Scriptの高速化の方法をお伝えしていきます。

まずシート1にこのようなデータを用意します。

Google Apps Script高速化検証用シート

ランダムの数値が横3列×縦50行分並んでいまして、見出しを入れると全部で51行です。

これをシート2にコピーしたいと思います。

高速化を考えない場合はこのようなスクリプトになります。

わかりやすさのために実行時間測定の部分は省いています。

さて、このスプリプトを実行してみますと

高速化対応前のGoogle Apps Script実行時間

21.8秒もかかっていますね。

API呼び出しが多いところを確認する

遅くなる原因でいうとスプレッドシートのAPI呼び出し回数ということになります。

前半の2~8行でシート1やシート2の準備で合計4回のAPI呼び出しがありますが、なんといっても12行目ですね。

入れ子になっている二つのfor文の中に、たったの一行で4つもAPIを呼び出すメソッドが含まれています。

行数が51、列数が3、読み書きのAPI呼び出しが1周あたり4回ですから、全部で612回のAPI呼び出しが発生していることになります。

セル一つ一つに対して読み書きをするメソッドであるgetValueやsetValueを何も考えずに使いまくると必然的に遅くなってしまうということになります。

APIリクエストを減らす方法

セル一つ一つに読み書きをすると遅くなるということですから、例えばデータのある範囲全体を一気に読み書きしたらよいのではないか?

ということになります。

では、その路線で検証してみましょう。

シート範囲を配列にまとめて読み書きするgetValuesとsetValues

シートの範囲をまとめて読み書きをするgetValues、setValuesというメソッドがあります。

書き方はこちらです。

配列 = Rangeオブジェクト.getValues()
Rangeオブジェクト.setValues(配列)

これを使えばセル一つ一つではなくて、シート範囲のデータをまとめて読んてきたり、まとめて書き出したりすることができますね。

しかしgetValuesで取得した範囲のデータは二次元配列に入り、setValuesでシートに書き出す場合は二次元配列を指定しなければいけません。

getValuesで取得したデータの二次元配列について

二次元配列…ちょっと不慣れな場合もあるかも知れませんね。

今回のシート1を二次元配列var1に格納する場合

とします。

このとき、二次元配列はどんな様子になっているかというと

とした場合と同じになります。

それぞれの行に対してまず一つ目の配列が用意されて、その中にそれぞれのセルの値が配列で格納されているというわけです。

ここで注意すべき点として、行も列も配列番号は「0」からスタートするということです。

1行目のデータはvar1[0]に格納され、1行目の1列目のセルはvar1[0][0]に格納されます。

getValues、setValuesを使ったスクリプトに修正

では、前述のスクリプトをgetValues、setValuesを使ったスクリプトに書き直してみましょう。

…入れ子のfor文だったあたりが、12行目に集約されました。超シンプルなたったの一行になっちゃいましたね。

getRangeで範囲を指定する

15行目のgetRangeですが引数が4つあります。

一つのセルの場所を指定する場合は引数は2つでしたが、getRangeで範囲を表す方法としては、範囲の開始セルの行数、列数とともに、その範囲の行数、列数も指定をします。

getRange(開始セルの行数, 開始セルの列数, 範囲の行数, 範囲の列数)

今回の場合は、開始セルは(1,1)、範囲の行数はシート1の行数、範囲の列数は3になりますので

となったわけです。

Google Apps Scriptの高速化の結果

getValues、setValuesを使って高速化をしたスクリプトを実行してみますと

高速化を施したGoogle Apps Scriptの実行時間

0.57秒…!

実行速度はなんと38倍に。メチャメチャ速くなりましたね。

まとめ

Google Apps Scriptでスプレッドシートを操作する際に処理速度を格段に速くする方法についてお伝えしました。

二次元配列の取り扱いは慣れないと少しわかりづらいところもあるかも知れませんが、プログラムの処理速度を考えると絶対に使うべきですよね。

ぜひご活用下さい。

さて、次回ですが、今回の高速化のテクニックを活用してメルマガ配信リストの更新のスクリプトを修正していきたいと思います。

Google Apps Scriptのメルマガ配信リスト自動更新スクリプトを高速化する
Google Apps Scriptを使ってメルマガ配信システムを作成しています。今回はスプレッドシートのAPI呼び出し回数を抑えることで、メルマガ配信リストの自動更新スクリプトの高速化を図ります。

どうぞお楽しみに!

連載目次:Google Apps Scriptでメルマガシステムを作っちゃおう!

Google Apps ScriptでGmail、スプレッドシート、ドキュメントを連携させて操作することでメルマガシステムが簡単に実現できます。思ったより2割くらい簡単にできますよ。ちなみに無料です…さすがGoogle。
  1. 初心者でも簡単!Google Apps ScriptでGmailを操作してメールを送る方法
  2. 初心者でも簡単!Google Apps Scriptでドキュメントを取得して表示する方法
  3. Google Apps Scriptでスプレッドシートのリストをもとに宛名を差し込んだメール本文を作る
  4. たったの38行!Google Apps Scriptで超簡易メルマガ配信システム
  5. 名刺管理アプリCAMCARDの連絡先リストをスプレッドシートに取り込む
  6. 【GAS】名刺管理アプリの出力データを活用してメルマガ配信リストを自動更新する
  7. Google Apps Scriptのスプレッドシート読み書きを格段に高速化をする方法
  8. Google Apps Scriptのメルマガ配信リスト自動更新スクリプトを高速化する
  9. Google Apps Scriptでスプレッドシートに独自のメニューを追加する方法