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

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

GASでスプレッドシートのコピーを実装したものの、1０万件、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を使う（切り替える）などの使い方をすることで高速化を実現できるということになりそうです。