Google Apps ScriptでSheets APIを使ったら爆速だった【GAS】

Twitterを見ていた所、こちらのサイトにて「SpreadsheetAppよりもSheets APIを使ったほうが全然早い」という耳寄りな情報が流れてきて、「そんな事ってあるの?」と思い、自分でテストしてみることにしました。もし、これが事実であれば特に大量のデータの集約などをする場合、標準のSpreadsheetAppを使うよりも、6分の制限のあるGoogle Apps Scriptに於いては速度を稼ぐ優良な手段となりえます。

ということで、実際につかって検証してみます。

今回使用するスプレッドシート

今回の元データは診療報酬情報提供サービスの医薬品マスタ(今年度と令和2年度)のデータを利用して、この2つのデータを結合したものを、コピペするといった事で、SpreadsheetAppとSheets APIの間で速度差を計測してみたいと思います。

医薬品マスタをtempdata1,tempdata2にそれぞれ貼り付けてください。今回はテストで5000件に押さえていますが、実データは22000件ほどあります。

ちなみに、Google Apps Scriptで速度を稼ぐ・6分の壁を突破する項目は以下のエントリーになるので合わせてご覧ください。

Google Apps Scriptを高速化するテクニックまとめ

Google Apps Scriptで6分の壁(タイムアウト)を突破する

Sheets APIについて

概要

本来Sheets APIはGoogle Apps Scriptから利用する為のものというものではなく、様々な外部アプリやウェブサービスからAccess Tokenを使って認証し、操作する為の汎用のREST APIです。しかし、Google Apps Scriptからでも標準でサービスから「Google Sheets API」を使う事で利用する事が可能になっています(この手法はDrive APIを追加する際によく利用しますね)

よって様々な言語からREST APIの作法に従ってHTTPリクエストをする事で、Googleスプレッドシートを操作する事が可能になっています。特徴としては

  • 様々なプログラミング言語からGoogleスプレッドシートのデータ操作を行う事が可能になる
  • バッチ処理が可能で、一括処理で行わせる事が可能
  • Google Apps Scriptから利用する場合は、Access Token取得のための認証作業や、リクエスト時に追加する必要が無い

故に、大容量データや複数シートを沢山扱う場合には、SpreadsheetAppではなく、Sheets APIのほうが全然早く処理が可能になる驚きの結果でした。こんなことなら前から使っておけばよかった。

リクエスト制限

GoogleのAPIは当然のようにリクエスト制限が設けられています。以下の制限があるので利用する場合は考慮する必要があります。

  • 読み出しに関して、プロジェクト1日辺り制限は無制限。1分辺りは300リクエスト、ユーザ毎1分辺り60リクエスト
  • 書き出しに関して、プロジェクト1日辺り制限は無制限。1分辺りは300リクエスト、ユーザ毎1分辺り60リクエスト

割当を超過すると、429エラー(To Many Request)が返ってきますが、正直かなり上記の制限は緩いので、早々超過する事はないのではないかと思います。

ソースコード

今回は集める先の「シート1」そして、2つのtempdataシート。最低でも3回はスプレッドシートにアクセスしなければなりません。このパターンに於いて処理開始時の時刻と終了時刻との差を取って、どちらが処理コストが掛かるのか?を10回程度計測してみることにします。

データは5000件*2シート分、列数は35列分(セルの数に換算して、35万セル分に該当します)。

SpreadsheetAppの場合

  • SpreadsheetAppはシートの読み出しは1シート1リクエスト消費するので、API呼び出しコストがシートの数だけ比例してしまい、ここがボトルネックになる
  • また、ちょいちょい「ページが応答しません」となって止まるケースに遭遇した。
  • 全体的にコードが見にくい上に、書き出しはRangeに4点を指定して出力が必要であるため、分かりづらい
  • どうも、最終行を調べるendrowを使ったり、配列を結合するArray.prototype.push.applyを使うと「ページが応答しない」状態になってしまうようだ。endrowを調べるコードは以下の通り。故に今回のコードでは最終行はわかってるので、getRangeで直書きしてる。

Sheets APIの場合

事前準備

Google Apps ScriptでSheets APIを利用する場合には事前準備が必要です。以下の手順で追加します。

  1. スクリプトエディタの画面にて、左サイドのサービスの+をクリックする
  2. Google Sheets APIを選択して追加をクリックする

これで、SheetsにつなげてAPIリクエストを送ることが可能になります。

図:APIの追加をする必要があります。

ソースコード

  • シートの読み込みも書き出しもValues.batchGetやValues.batchUpdateにて一括で処理が行えるため、リクエスト回数が1回で済みます。
  • コード全体がスッキリしていて非常に読みやすいです。

計測結果(秒)

読み込みのみのテスト

回数 SpreadsheetApp Sheets API
1 3.902 1.942
2 3.941 1.573
3 5.394 2.036
4 3.962 2.034
5 4.607 1.626
6 4.142 1.817
7 3.862 1.948
8 5.261 1.946
9 4.070 1.853
10 4.555 1.912

シート1に書き出しをせずに、変数に値を格納する所までの数値ですが、Sheets APIのほうが2.8倍ほど早いですね。ここまで差があると、SpreadsheetAppで大量データを処理するのは鬼門とも言えるかもしれません。

読み書きのテスト

回数 SpreadsheetApp Sheets API
1 29.334 9.849
2 30.207 10.711
3 29.180 10.600
4 27.785 10.913
5 29.222 9.839
6 26.364 12.059
7 26.382 10.616
8 27.143 9.648
9 28.465 11.676
10 32.204 10.775

シート1への書き出しまで含めて計測しています。やはりSheets APIのほうが2.7倍ほど早い結果になりました。また、Sheets APIは確実高速で処理できるため、ページが応答しないといった事もない為、これは驚きの結果です。なんでGASのメソッドのほうが遅いのだろうか。。。

関連リンク

コメントを残す

メールアドレスが公開されることはありません。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。