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


speedup

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

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

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

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

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

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

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

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

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

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

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

ランダムの数値が横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クラスのメソッドで実行時間をログ出力する方法を紹介します。

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

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

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には実行時間についていくつかの制約があって、それを超えるとエラーを起こしてしまうという仕様になっています。

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

項目 説明 gmail.com G Suite Basic G Suite Business以上
Script runtime スクリプトの実行時間 6 min / execution 6 min / execution 30 min / execution
Triggers total runtime トリガーによる合計実行時間 90 min / day 6 hr / day 6 hr / day
Custom function runtime カスタム関数の実行時間 30 sec / execution 30 sec / execution 30 sec / execution

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

Quotas for Google Services  |  Apps Script  |  Google Developers

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

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

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

ということになります。

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

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

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

書き方はこちらです。

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

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

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

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

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

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

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

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

Sheetオブジェクト.getDataRange()

このとき、定数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で範囲を指定する

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

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

getRange(行番号, 列番号, 行数, 列数)

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

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

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

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

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

配列.length

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

Google Apps Scriptの高速化の結果

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

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

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

すると…

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

688ミリ秒…!

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

まとめ

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

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

ぜひご活用下さいね!


コメント

  1. サトウ より:

    タカハシ様
    初めてコメントさせていただきます。
    グーグルスプレッドシートの高速化をしたいと考えていたところ、
    この記事にたどり着きました。

    スクリプトの修正とはどのように行えば良いのでしょうか?
    たとえば
    JavaScript

    if(sheetAddress.getRange(ai,4).getValue() === sheetContacts.getRange(ci,9).getValue()){
    // 処理
    }

    1

    2

    3
    if(sheetAddress.getRange(ai,4).getValue() === sheetContacts.getRange(ci,9).getValue()){
    // 処理
    }

    など、入力する箇所がわからず途方にくれています。

    知識に乏しく恐縮なのですが、
    具体的な入力箇所もしくはそれがわかる記事を紹介していただければ幸いです。

    宜しくお願いいたします。

タイトルとURLをコピーしました