【GAS】スプレッドシートの読み書きが遅い！を高速化するコツ

【GAS】スプレッドシートの読み書きが遅い！を高速化するコツ

Google Apps Scriptsでスプレッドシートの読み書きをするとき、何も考えずにプログラムを書くと、動作速度が異常に遅くなります。

動作速度を上げる一番のコツとして、スプレッドシートAPIをできるだけ呼び出さないようにするということが挙げられます。これで、動作速度は劇的に速くなります。

ただ、実は、スプレッドシートAPIの呼び出し回数を減らさないでも、適切にAPIを呼び出せば、呼び出し回数が多くても、まあまあ実用的な速度でプログラムを実行することができます。

そこで、この記事では、「スプレッドシートAPIを呼び出さないようにすればいい」という話から、さらに一歩踏み込んで、「APIを使わざるを得ない場合には、どういうふうにAPIを呼び出せば、実行速度の低下を最小限で防げるのか」について解説をしていきます。

まずは、下記のように、A列・B列に、それぞれ数値が入っているときに、C列にその合計を表示するプログラムを例に解説をしていきます。

↓

「最も遅い方法」と「最も速い方法」

例1: 1セルずつ読み込み、書き込みを行う（最も遅い方法）

動作速度が最も遅くなるのは、次のように、1セルずつ読み込んで、1セルずつ書き込みを行う場合です。

function main1() {
  console.time('test');

  const sheet = SpreadsheetApp.getActiveSheet();
  for (let y = 1; y <= 100; y++) {
    const a = sheet.getRange(y, 1).getValue();
    const b = sheet.getRange(y, 2).getValue();
    sheet.getRange(y, 3).setValue(a + b);
  }

  console.timeEnd('test');
}

このプログラムの実行時間は24,123msでした。

例2: まとめて読み込み、まとめて書き込む（最も速い方法）

一方で、一番実行速度が速くなるのは、次のように処理をするケースです。

• セルの内容を、一気に配列に読み込む
• その配列を加工して出力用の配列を作成
• その配列の内容を、セルに一気に書き込む

たとえば、次のプログラムを見てみてください。

function main2() {
  console.time('test');

  const sheet = SpreadsheetApp.getActiveSheet();

  const iValues = sheet.getRange(1, 1, 100, 2).getValues();

  const oValues = [];
  for (let y = 0; y < 100; y++) {
    oValues.push([iValues[y][0] + iValues[y][1]]);
  }

  sheet.getRange(1, 3, 100, 1).setValues(oValues);

  console.timeEnd('test');
}

このプログラムでは、以下のようなことを行っています。

6行目	getValuesメソッドで、セルの内容を、配列「iValues」に一気に読み込み
8行目～11行目	forループで、出力するデータを配列「oValues」に作成
13行目	setValuesメソッドを使って、配列「oValues」の内容を、セルに一気に書き込む

このプログラムの実行時間は335msでした。

セルの値を一気に読み込み、一気に書き込むことで、「24,123ms」→「335ms」と、約72倍高速になりました。

実行速度が速くなった原因は「スプレッドシートAPI」の呼び出し回数が減ったから？

実行速度が速くなった理由として、よく言われるのがスプレッドシートAPIの呼び出し回数が減ったからという理由です。

「スプレッドシートAPI」とは

「スプレッドシートAPI」というのは、「SpreadsheetApp」に続けて書くメソッドを呼び出すことをいいます。

「SpreadsheetApp」直後に呼び出すメソッドだけでなく、「.」でつないで書いていくメソッドは、すべてスプレッドシートAPIに含まれます。

たとえば、冒頭の「例1」のプログラムで、「スプレッドシートAPI」を何回呼び出しているか考えてみましょう。

「例1」では、どこで「スプレッドシートAPI」を呼び出しているか？

function main1() {
  console.time('test');

  const sheet = SpreadsheetApp.getActiveSheet();
  for (let y = 1; y <= 100; y++) {
    const a = sheet.getRange(y, 1).getValue();
    const b = sheet.getRange(y, 2).getValue();
    sheet.getRange(y, 3).setValue(a + b);
  }

  console.timeEnd('test');
}

この中では、次の箇所で「スプレッドシートAPI」を呼び出しています。

行	スプレッドシートAPI
4行目	getActiveSheet()
6行目	getRange(y, 1)、getValue()
7行目	getRange(y, 2)、getValue()
8行目	getRange(y, 3)、setValue(a + b)

6行目～8行目の文には「SpreadsheetApp」という文が含まれていません。ただ、変数「sheet」には「SpreadsheetApp.getActiveSheet()」の結果が格納されています。

そのため、変数「sheet」を「SpreadsheetApp.getActivesheet()」と同視する必要があります。それ以降「.」でつないで呼び出しているメソッドもすべて「スプレッドシートAPI」の呼び出しと考えることに注意してください。

「例1」で「スプレッドシートAPI」を呼び出した回数は？

今回のプログラムでは、6行目～8行目は、forループで100周分実行されます。

それを加味すると、「スプレッドシートAPI」は、下記のように、合計で「601回」呼び出されることになります。

スプレッドシートAPI	呼び出し回数	補足
getActiveSheet()	1	4行目
getRange()	300	6行目～8行目で1回ずつ（＝3回）× 100周
getValue()	200	6行目、7行目で1回ずつ（＝2回）× 100周
setValue()	100	8行目で1回 × 100周
合計	601

引数が違っていても、動作速度に大きな影響は与えません。そこで、上の表では、引数の違いを無視して集計をしています。

「例2」で「スプレッドシートAPI」を呼び出した回数は？

同じように、「例2」のプログラムで「スプレッドシートAPI」を呼び出した回数を数えてみましょう。

function main2() {
  console.time('test');

  const sheet = SpreadsheetApp.getActiveSheet();

  const iValues = sheet.getRange(1, 1, 100, 2).getValues();

  const oValues = [];
  for (let y = 0; y < 100; y++) {
    oValues.push([iValues[y][0] + iValues[y][1]]);
  }

  sheet.getRange(1, 3, 100, 1).setValues(oValues);

  console.timeEnd('test');
}

このプログラムで、「スプレッドシートAPI」を呼び出しているのは、下記の「5回」だけです。

スプレッドシートAPI	呼び出し回数	補足
getActiveSheet()	1	4行目
getRange()	2	6行目、13行目
getValues()	1	6行目
setValues()	1	13行目
合計	5

「例1」と「例2」を比較すると、「スプレッドシートAPI」の呼び出し回数が「601回」→「5回」に激減しています。

一見、API呼び出し回数が激減したから、実行速度が向上したように見えます。もちろん、それは間違いではありません。でも、実際には、別の要因が、実行速度に大きな影響を与えています。

「スプレッドシートAPI」の呼び出し回数を減らさなくても、動作速度は劇的に速くなる

次のプログラムを見てみてください。

例3: 必要なデータを「getValue」した後に「setValue」を実行する

function main3() {
  console.time('test');

  const sheet = SpreadsheetApp.getActiveSheet();

  const oValues = [];
  for (let y = 1; y <= 100; y++) {
    const a = sheet.getRange(y, 1).getValue();
    const b = sheet.getRange(y, 2).getValue();
    oValues[y - 1] = a + b;
  }

  for (let y = 1; y <= 100; y++) {
    sheet.getRange(y, 3).setValue(oValues[y - 1]);
  }

  console.timeEnd('test');
}

このプログラムでは、あえて「getValues」「setValues」を使わないで、次のようなプログラムを組んでいます。

• 「getValue」メソッドで、1セルずつ値を読み込んで、計算結果を配列に格納（6～11行目）
• 「setValue」メソッドで、配列の要素を、1セルずつ書き込み（13～15行目）

このプログラムの実行にかかった時間は、1,581msでした。

「例2」の「335ms」に比べると遅いですが、「例1」の「24,123ms」に比べれば、十分実用的な速度です。

APIの呼び出し回数は「例1」とまったく同じ

実は、このプログラムの「スプレッドシートAPI」の呼び出し回数は、「例1」とまったく同じです。

スプレッドシートAPI	呼び出し回数	補足
getActiveSheet()	1	4行目
getRange()	300	6行目～8行目で1回ずつ（＝3回）× 100周
getValue()	200	6行目、7行目で1回ずつ（＝2回）× 100周
setValue()	100	8行目で1回 × 100周
合計	601

それにもかかわらず、「例1」に比べると、実行速度は約15倍になっています。

つまり、APIの呼び出し回数が同じでも、速度が大きく異なる場合があるのです。

ポイントは「setValue」の後に「getValue」を使わないこと

実行速度に大きな影響を与えるポイントは、「getValue」と「setValue」の順番です。

• 最初に（必要な回数だけ）getValueを行う
• その後に（必要な回数だけ）setValueを行う

この順番を守ることで、「スプレッドシートAPI」を大量に呼び出しても、プログラムの実行速度がそれほど落ちなくなるのです。

言い換えると、「setValue」の後に「getValue」を行うと、実行速度が大きく落ちてしまうのです。

「setValue」の後に「getValue」をすると遅くなる理由

※この項の内容は、私の推測です。

「setValue」メソッドを実行したときに、シートがどう変化するかを観察してみてください。

たぶん、「setValue」メソッドが実行された瞬間にセルに値が書き込まれず、少し遅れてセルに書き込まれているのではないかと思います。

このように、「setValue」メソッドによる書き込みが完了するまでには、少し時間がかかります。ちょっとした時間ですが、この書き込みを待ってから、次の行の処理を行うと、処理時間がムダに増えてしまいます。

そこで、GASでは、「setValue」メソッドによるセルへの書き込みが完了していなくても、セルへの書き込み指示を出した段階で、次の行の処理が実行される仕組みになっているのです（いわゆる「ライトバックキャッシュ」が効いた状態）。

このような処理が行われることで、「setValue」メソッドは、比較的短時間で終わり、次の行の処理に移ることができます。

ところが、「setValue」メソッドの後に「getValue」メソッドが出てくる場合には、この手法が使えません。というのは、「setValue」メソッドの結果が反映した状態まで待たないと、「getValue」メソッドで誤った値を読み込む可能性があるからです。

ですから、「setValue」メソッドの後に「getValue」メソッドが呼び出されたときには、「setValue」メソッドの処理を待つことになります。その結果、処理に時間がかかってしまうのです。

実務的に出てきそうなパターン

実務的には、「getValues」を使って一括でデータを読み込むことはできるけれど、「setValues」を使って一括でデータを書き込ものは大変、というケースがたまに出てきます。

その場合には、次のように処理をすることで、大きな速度低下を防ぎつつ、シンプルにプログラムを書くことができます。

• 最初に「getValues」で一気にセルを読み込む
• その後「setValue」で個別に書き込みを行う

実際の例を見てみましょう。

例4: setValuesを使いにくいケース

たとえば、次のような処理をするプログラムを書くことを考えてみます。

• A列・B列に数値が入っている場合には、C列にその合計を表示する
• ただし、D列に何か値が入力されているときには、C列の内容は変更せずに、あらかじめ入力してある内容をそのまま表示する

↓

この場合、最初に、「getValues」メソッドで必要なすべてのセルの値を取得するようにしましょう（6行目）。

あとは、無理に「setValues」メソッドを使わずに、都度「setValue」メソッドで必要なセルに値を書き込む（13行目）だけでも、十分な速度を出すことができます。

function main4() {
  console.time('test');

  const sheet = SpreadsheetApp.getActiveSheet();

  const iValues = sheet.getRange(1, 1, 100, 4).getValues();

  for (let y = 1; y <= 100; y++) {
    //「y」行「4」列目のセルの値は、iValues[y - 1][4 - 1]に格納されている
    if (iValues[y - 1][4 - 1] === '') {
      const a = iValues[y - 1][1 - 1];
      const b = iValues[y - 1][2 - 1];
      sheet.getRange(y, 3).setValue(a + b); // 条件に合うセルだけ値を書き込む
    }
  }

  console.timeEnd('test');
}

このプログラムを実行したときの実行速度は、840msでした。

まとめ

「スプレッドシートAPI」の使用回数を減らすことは重要ですが、だからといって、無理に減らす必要はありません。

それよりも、「getValue(s)」の後に「setValue(s)」を使う、という順番を意識するようにしましょう。

これだけで、十分な速度が出ることが多いです。