GASでスプレッドシートの値を検索する方法はどれが高速か

動機

そもそもは、開くのにも時間がかかるスプレッドシートを相手に値の検索をして行の情報を更新する行番号を取得したかったのが始まり。

検索するといってもやり様は様々あるなと思い、今後のために比較できるようにしておこうと実行時間の計測を行ってみようと作成した。

検証する方法

• 検索方法1
  • スプレッドシートの値を一変数に全部格納して走査する

/**
 * 値を検索する手法A
 * シートの値を変数に収めてから一致する値を探す
 */
function testSearch_A(key) {
  var dat = sheet.getDataRange().getValues();

  for(var i = 1; i < dat.length; i++){
    if(dat[i][0] === key){
      return i + 1;
    }
  }
  return 0;
}

• 検索方法2
  • スプレッドシートのmatch関数を使って検索する

/**
 * 値を検索する手法B
 * スプレッドシートのmatch関数を使って一致する値を探す
 */
function testSearch_B(key) {
  // 計算用のセルを検索列以外に設ける
  var tmprange = sheet.getRange("B1")
  // 計算用のセルに入っている値を一時変数に保存
  var tmpvar = tmprange.getValue();

  // 計算用のセルに関数文字列をセット
  tmprange.setValue('=MATCH("'+key+'", A:A, 0)');
  // セルの結果を取得し、一致しない場合に-1を返す様に値を修正
  var result = tmprange.getValue();
  result = result === "#N/A" ? -1 : result;

  // 計算用のセルにもともと入っていた値に戻す
  tmprange.setValue(tmpvar);
  return result;
}

結果

方法	場合	実行時間(ms)
検索方法1	値がある	1.271
検索方法1	値がない	1.038
検索方法2	値がある	0.399
検索方法2	値がない	0.548

• Logger.logの出力

17:16:43	情報	検索方法1: 値がある場合
17:16:44	情報	検索結果: 50000
17:16:44	情報	1.271 ms
17:16:44	情報	検索方法1: 値がない場合
17:16:45	情報	検索結果: 0
17:16:45	情報	1.038 ms
17:16:45	情報	検索方法2: 値がある場合
17:16:46	情報	検索結果: 50000
17:16:46	情報	0.399 ms
17:16:46	情報	検索方法2: 値がない場合
17:16:46	情報	検索結果: -1
17:16:46	情報	0.548 ms

使用したスクリプト

const SPREADSHEET_NAME = "GAS_検索速度調査";
const ROW = 50000;
const COLUMN = 5;

const SEARCH_WORD = "TEST";

var sheet;

/**
 * 計測の準備をする関数
 * 計測用のスプレッドシートを作成し、値を列挙しておく
 */
function setUp() {
  // 新しいスプレッドシートを作成
  sheet = SpreadsheetApp.create(SPREADSHEET_NAME, ROW, COLUMN).getActiveSheet();

  // 1列目に数字を列挙する
  for(var i = 1; i < sheet.getMaxRows(); i++){
    sheet.getRange(i, 1).setValue(i);
  }
  // 最終行に検索対象の文字を入れる
  sheet.getRange(sheet.getMaxRows(), 1).setValue(SEARCH_WORD);
}

/**
 * 計測の後始末をする関数
 * 作成したスプレッドシートを削除する
 */
function tearDown() {
  var files = DriveApp.getFilesByName(SPREADSHEET_NAME);
  while (files.hasNext()) {
    var file = files.next();
    file.setTrashed(true);
  }
}

/**
 * 値を検索する手法A
 * シートの値を変数に収めてから一致する値を探す
 */
function testSearch_A(key) {
  var dat = sheet.getDataRange().getValues();

  for(var i = 1; i < dat.length; i++){
    if(dat[i][0] === key){
      return i + 1;
    }
  }
  return 0;
}

/**
 * 値を検索する手法B
 * スプレッドシートのmatch関数を使って一致する値を探す
 */
function testSearch_B(key) {
  // 計算用のセルを検索列以外に設ける
  var tmprange = sheet.getRange("B1")
  // 計算用のセルに入っている値を一時変数に保存
  var tmpvar = tmprange.getValue();

  // 計算用のセルに関数文字列をセット
  tmprange.setValue('=MATCH("'+key+'", A:A, 0)');
  // セルの結果を取得し、一致しない場合に-1を返す様に値を修正
  var result = tmprange.getValue();
  result = result === "#N/A" ? -1 : result;

  // 計算用のセルにもともと入っていた値に戻す
  tmprange.setValue(tmpvar);
  return result;
}

function testMain() {
  setUp();
  
  Logger.log("検索方法1: 値がある場合");
  var start = new Date();
  var result = testSearch_A(SEARCH_WORD);
  var end = new Date();
  Logger.log("検索結果: " + result);
  Logger.log((end - start) / 1000 + " ms");

  Logger.log("検索方法1: 値がない場合");
  start = new Date();
  result = testSearch_A("てすと");
  end = new Date();
  Logger.log("検索結果: " + result);
  Logger.log((end - start) / 1000 + " ms");

  Logger.log("検索方法2: 値がある場合");
  start = new Date();
  result = testSearch_B(SEARCH_WORD);
  end = new Date();
  Logger.log("検索結果: " + result);
  Logger.log((end - start) / 1000 + " ms");

  Logger.log("検索方法2: 値がない場合");
  start = new Date();
  result = testSearch_B("てすと");
  end = new Date();
  Logger.log("検索結果: " + result);
  Logger.log((end - start) / 1000 + " ms");

  tearDown();
}

結論

（いまのところ）検索方法2が速そう

他にも値の検索のやり方があれば教えてほしいです