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

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

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

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

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

そして、Googleのサーバーをみんなで使用しているGASのルールとして、実行時間の壁もあります。

Google Apps Scriptの場合、どうすれば実行速度を改善できるでしょうか？

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

お題：セル範囲の値を別シートにコピーする

今回は、セル範囲の値を別シートのコピーするというお題を使って、スプレッドシートを扱うGASの高速化の方法を考えていきましょう。

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

ランダムの数値が横3列×縦100行分並んでいます。

これらのデータを、シート2にコピーしたいと思います。

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

function speedTest() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName('シート1');
  const row1 = sheet1.getDataRange().getLastRow();
  const sheet2 = ss.getSheetByName('シート2');

  for(let i = 1; i <= row1; i++){
    for(let j = 1; j <= 3; j++){
      sheet2.getRange(i, j).setValue(sheet1.getRange(i, j).getValue());
    }
  }
}

このスクリプトの実行時間を測定するために、以下のような関数logTimeを用意しました。

function logTime() { 
  const label = 'speedTest time'
  console.time(label); 
  speedTest();
  console.timeEnd(label);
}

consoleクラスのtimeメソッド、timeEndメソッドで測定できます。詳しくは以下の記事をどうぞ！

GASのconsoleクラスのメソッドで実行時間をログ出力する方法

GASでconsoleクラスを使ってApps Scriptダッシュボードにログを出力する方法を伝えしています。今回はGASのconsoleクラスのメソッドで実行時間をログ出力する方法を紹介します。

tonari-it.com

2020.01.12

さて、この関数logTimeを実行してみますと、以下のような結果になりました。

131028ミリ秒…ということは、131秒もかかっていますね。

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

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

ここで言うAPIは、Spreadsheetサービスです。

つまり、以下のようなメソッドなどを使ってスプレッドシートにアクセスをするたびにAPIが呼び出されています。

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

二重のfor文の中にある、以下の一文について考えてみましょう。

sheet2.getRange(i, j).setValue(sheet1.getRange(i, j).getValue());

ここには、getRangeメソッド、setValueメソッド、getRangeメソッド、getValueメソッドと4回もスプレッドシートへのアクセスがあります。

これが行数×列数分、つまり300回実行されていますから、この一行だけでも1200回のAPIアクセスとなります。

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

実行時間が遅いと何が問題なのか

GASにおいて、実行時間の遅さはときに重要です。

というのも、GASには実行時間についていくつかの制約があって、それを超えるとエラーを起こしてしまうという仕様になっています。

例えば、以下のようなものです。

その他、以下のページに実行時間のほか、さまざまな割り当てや制約についてまとめられているのでご覧ください。

Quotas for Google Services  |  Apps Script  |  Google for Developers

developers.google.com

Googleのサーバーをみんなで使っているわけですから、無駄に専有しないようにということですね。

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

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

ということになります。

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

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

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

書き方はこちらです。

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

なお、getValuesメソッドで戻り値として取得できるデータ、setValuesメソッドでシートに書き出す際に引数に渡すデータは、いずれも二次元配列となります。

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

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

シート1の使用しているセル範囲の値を二次元配列valuesに格納するには、以下のようにします。

const values = sheet1.getDataRange().getValues();

なお、getDataRangeメソッドは、Sheetオブジェクトの使用しているセル範囲をRangeオブジェクトとして取得する超便利メソッドです。

このとき、定数valuesに格納した二次元配列はどんな様子になっているかというと、こうです。

values[0]=[27, 41, 53];
values[1]=[79, 0, 73];
values[2]=[50, 1, 4];
values[3]=[87, 99, 87];
values[4]=[53, 59, 14];
// 以下続く

それぞれの行が配列で構成されていて、さらにその配列の中に各セルの値が格納されているというわけです。

ここで注意すべき点として、行も列もそのインデックスは「0」からスタートするということです。

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

getValuesメソッド、setValuesメソッドを使ったスクリプトに修正

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

function speedTest2() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const sheet1 = ss.getSheetByName('シート1');
  const values = sheet1.getDataRange().getValues();
  const sheet2 = ss.getSheetByName('シート2');

  sheet2.getRange(1, 1, values.length, values[0].length).setValues(values);
  
}

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

getRangeメソッドで範囲を指定する

9行目のgetRangeメソッドですが引数が4つあります。

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

最初の2つの引数の行番号、列番号で開始セルの位置を決めます。

そこから、行数分、列数分に拡張した範囲が、取得できるRangeオブジェクトとなります。

今回の場合は、以下のようなステートメントでしたね。

sheet2.getRange(1, 1, values.length, values[0].length).setValues(values);

lengthプロパティは、配列の要素数を求めるプロパティです。

ですから、開始セルは(1,1)であり、行数は二次元配列の要素数（＝つまり行数）、列数は二次元配列の1要素目の要素数（つまり一行目の要素数=列数）になります。

Google Apps Scriptの高速化の結果

では、この高速化をしたスクリプトについて速度を測ってみましょう。

関数logTimeを以下のように変更して実行します。

function logTime() { 
  const label = 'speedTest2 time'
  console.time(label); 
  speedTest2();
  console.timeEnd(label);
}

すると…

688ミリ秒…！

つまり、1秒もかかってないということです。メチャメチャ速くなりましたね。

まとめ

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

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

ぜひご活用下さいね！