GASで合計2600セルを検索したときにAPIリクエストの遅延がどれだけ発生するのか試してみた

この記事は、N高等学校 (1) Advent Calendar 2020 8日目の記事です。

こんにちは、N高等学校2年のたっちです。

今回はGoogle Apps Scriptで、スプレッドシートのA1からZ100までの合計2600セルの検索時間が、
APIリクエストの回数でどれだけ遅くなるのか、はたまたAPIリクエストの回数を減らしたらどれほど速くなるのか、検証してみました。

はじめに

あくまでも、お遊びでやった実験です。正確な検証ではありません。

しかも超初心者なので、間違っている点があるかもしれません。
その際は、優しく指摘していただけますと幸いです。

検証方法

スクリーンショット内に収まっていませんが、A1セルからZ100セルまでの100行×26列、セル一つひとつにA1形式の文字列を入力しました。

その中から、「Z100」という文字列を探し出すまでの時間を計測して、どれくらいの差が出るのか、試してみました。

検証用コード

この2つのコードを使用して検証しました。

セルを一つずつ取得(getValue)していく方法

function oneByOneCellGetSearch() {
  //スプレッドシート取得
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const mainSheet = spreadSheet.getSheetByName("シート1");

  //検索する値
  const searchValue = "Z100";

  //検索
  loop:
  for (var i = 1; i <= 100; i++) {
    for (var j = 1; j <= 26; j++) {
      //セルの値を取得
      var getCellValue = mainSheet.getRange(i, j).getValue();

      if (getCellValue === searchValue) {
        Logger.log(searchValue + "はありました");
        break loop;
      }
    }
  }
}

二次元配列(getValues)を使用する方法

function searchUsingTwoDimensionalArray() {
  //スプレッドシート取得
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const mainSheet = spreadSheet.getSheetByName("シート1");

  //検索する値
  const searchValue = "Z100";

  //A1からZ100までのセルを二次元配列で取得して一次元化
  const getCellValues = mainSheet.getRange(1, 1, 100, 26).getValues().flat();

  //検索
  if (!(getCellValues.indexOf(searchValue) == -1)) {
    Logger.log(searchValue + "はありました");
  }
}

結果

oneByOneCellGetSearch() time: 10277ms
searchUsingTwoDimensionalArray() time: 594ms


・セルを一つずつ取得(getValue)していく方法
10.277秒
・二次元配列(getValues)を使用する方法
0.594秒

10.277秒 - 0.594秒
= 9.683秒差

流石に、合計2600セルを読み込ませようとすると、見えやすい差が生まれました。
9秒もの差が出てくるとは予想していませんでしたが、実験が成功して良かったです。

時間計測に使用したコード

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

参考にさせて頂いたサイト
→GASのconsoleクラスのメソッドで実行時間をログ出力する方法