GoogleAppsScriptでスプレッドシートのコピーを高速に実行する方法

Google Apps Scriptでスプレッドシートのコピーを高速に実行する方法

Google Apps Scriptでスプレッドシートのコピーを高速に行うにはSheets APIを使おう

GASでスプレッドシートのコピーを実装したものの、10万件、100万件などの大量データをコピー&ペーストしようとしたら、処理に10分くらい時間がかかってしまった。そんなお悩みをもつ方に朗報です(もしまだ下記の方法を試していなければ、ですが)。もし下記の方法を試せば、データ量によっては、実行時間をかなり短縮することができます。

その方法とは、見出しの通りなのですが、Sheets APIを使うというやり方です(以降ややテクニカルな内容になります)。

通常、GASでスプレッドシートを取り扱う際はSpreadsheetAppクラスを使うことが一般的です。ですが、後述する参考サイトの報告によると、Sheets APIを使う方が大量データの読み書きについては高速になるということでした。

実際に筆者も以下のようなデータを用いて試してみました。コピー元となるスプレッドシートに10万行×13列(130万セル)、10万行×26列(230万セル)のデータを用意し、それぞれコピーを実行してみました。

  • SpreadsheetAppを用いた場合
    • 130万セル:1分44秒
    • 260万セル:5分25秒
  • Sheets APIを用いた場合
    • 130万セル:36秒
    • 260万セル:1分05秒

このように上記のデータですと、Sheets APIを使うSpreadsheetAppを使った場合の時間の20-35%で済みました。逆に、SpreadsheetAppを使うとSheets APIよりも2.9-5.0倍時間がかかってしまうということになります。

SpreadsheetAppとSheets APIの比較ベンチマークテスト

こちらのサイトに詳細な比較結果がでています。以下にサマリーの部分の和訳を抜粋します。

  • Spreadsheetから値を読み出すための処理コストについて
    • Spreadsheet ServiceのgetValues()とgetSheetValues()の処理コストは、ほぼ同じである。
    • Sheets APIのvalues.getとvalues.batchGetの処理コストは、ほぼ同じである。
    • Sheets APIのメソッドは、Spreadsheet Serviceのメソッドから約35%の処理コストの削減が可能である。
  • Spreadsheetから値を書き込む際の処理コストについて
    • Sheets APIのvalues.update、values.batchUpdate、values.appendはほぼ同じ処理コストである。
    • Sheets APIのメソッドは、Spreadsheet Serviceのメソッドより約19%処理コストを削減することができる。
    • Spreadsheet ServiceのsetValues()とSheets APIのメソッドの間には、点対称が存在する。
      • データサイズが小さい場合は、setValues()が値の書き込みに適している。
      • データサイズが大きくなると、Sheets APIのメソッドが値の書き込みが適している。

上記の結果はGoogle Apps Scriptでの実行結果であるため、他の環境だと結果が異なるかもしれないという点についても言及しています。

面白い点としては、書き込みについてはデータサイズが小さい場合は、SpreadSheet Serviceの方がSheet APIより優れているという点ですね。

なので、なんでもかんでもSheet APIを使うというよりも、大量データの書き込みが必要になってきたら、Sheet APIを使う(切り替える)などの使い方をすることで高速化を実現できるということになりそうです。

Sheets APIを使う方法

Google Sheets APIはAdvanced Google servicesの中の1つのサービスです。例えば、Googleドライブを操作する際に利用するDrive APIなんかもこの一部です。
Advanced Google serviceを使うにはスクリプトエディタを開いてServicesから追加する必要があります。具体的には下記の図のように左側のメニューから「サービス」を選択します。
 
Google Sheets APIを利用する方法1

そしてダイアログが表示されたらGoogle Sheets APIを選択して追加をクリックします。

Google Sheets APIを利用する方法2

左側メニューに「Sheets」が追加されていれば成功です。なお、appsscript.jsonを用いて追加する方法もあります。

Google Sheets APIを利用する方法3

Sheetsをクリックすると、現在のバージョンやIDの確認が行えます。

Google Sheets APIを利用する方法4

Sheets APIのリクエスト制限

公式ドキュメントによればリクエスト制限は下記のようになります。

  • 1プロジェクトあたりの読み込み
    • 1日あたりの制限:無制限
    • 1分あたりの制限:300リクエスト
    • 1ユーザー/1分あたりの制限:60リクエスト
  • 1プロジェクトあたりの書き込み
    • 1日あたりの制限:無制限
    • 1分あたりの制限:300リクエスト
    • 1ユーザー/1分あたりの制限:60リクエスト

割当を超過した場合は、429エラーがかえってくるようです(超過するようなケースでは以前の本ブログでも一部触れていたexponential backoff algorithmの実装で後で試すことが推奨されています)。

Sheets APIのコスト

同じく上記の公式ドキュメントによれば、追加でかかるコストないとのことです。

コメントする

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

CAPTCHA