非IT企業に勤める中年サラリーマンのIT日記

非IT企業でしかもITとは全く関係ない部署にいる中年エンジニア。唯一の趣味がプログラミングという”自称”プログラマー。

スプレッドシート内をGASで検索するのはQUERY関数利用が桁違いに爆速だった

   

以前、こんな記事を書きました。スプレッドシート上にある膨大な量のデータを高速に検索するにはセル直接参照ではなく一旦配列に落としてからやりましょう、という内容です。これはExcelでも同じことが言えます。

それでも12万行データとはいえ12秒かかるのはまだちょっと不安だったので高速検索についていろいろ実験してみたところ新たな高速検索法が見つかったのでシェアします。

行った実験は以下の3つ。

  1. 前回と同じ方法(配列に落としてループ)
  2. 検索列を横配列にしてindexOf関数で検索
  3. QUERY関数利用

先に結論を行っておくとQUERY関数利用がめちゃくちゃ速いです。1と2ではわずかに2の方が速く、3は更に桁違いに速いって感じです。同じデータを用いて1〜3の結果を下記します。

今回使ったデータ

今回検索に使ったデータは郵便局の郵便番号データをダウンロードしたものです。12万行以上あるデータなのでテストには便利です。

 

プログラムによっては後半の方が検索時間がかかるという特徴があるので検索には後半に近い福岡県久留米市合川町を検索することで条件を合わせました。

 

1.前回と同じ方法(配列に落としてループ)

前回とまったく同じプログラムです。データ全体を引っ張ってきて配列に格納した上でループ処理で検索していくという原始的な方法です。セル参照よりはるかに速いことは前回証明済み。

function searchData1(){
  var keyword = 8390861;

  var mySheet = SpreadsheetApp.getActive().getSheetByName('KEN_ALL');
  var r = mySheet.getLastRow();
  var c = mySheet.getLastColumn();
  var data = mySheet.getRange(1, 1, r, c).getValues();

  data.some(function(value) {
    if(keyword==value[0]){
      console.log(value[4]+value[5]+value[6]);
      return true;
    }
  });
}
 

 

結果はこちら。124,519データへの検索処理で13秒かかったわけです。ちなみに一番処理のかかる行は7行目のdata配列変数にデータを格納するところです。ここでほとんどの時間を使いあとは1秒程度で終わりました。

 

2.検索列を横配列にしてindexOf関数で検索

次に行ったのは検索対象列を1列分引っ張ってきてflat()メソッドで横配列にした後にindexOfメソッドで対象を検索するという方式です。

function searchData(){
  var keyword = 8390861;

  var sheet = SpreadsheetApp.getActive().getSheetByName("KEN_ALL");
  var values = sheet.getRange(1, 1, sheet.getLastRow()).getValues().flat();
  r = values.indexOf(keyword)+1;
  Logger.log(sheet.getRange(r, 5).getValues() + sheet.getRange(r, 6).getValues() + sheet.getRange(r, 7).getValues());
}
 

 

結果はこちらです。9秒とわずかに改善されました。おそらく引っ張ってくるデータ数が少ない分速度が改善されたのでしょう。少し早くなったものの検索対象列が複数あった場合はその分時間かかることや、OR条件にどう対応すべきか検討が必要になります。

 

3.QUERY関数利用

最後にQUERY関数を利用した方法になります。

Googleスプレッドシートの関数には高速で検索できるQUERY関数が用意されていてこれをGASで利用しようというわけです。QUERY関数はかなり強力な検索関数で以下のようにSQL構文のような文法で検索することができます。(1セルに入力した数式で結果を他のセルに展開するのはGoogleスプレッドシートならでは)

 

これをGAS側で数式を生成して別シートのセルに書き込ませることで関数の結果を得ます。

function queryData(){
  var keyword = 8390861;

  var sheet = SpreadsheetApp.getActive().getSheetByName("QUERY");
  var values = sheet.getRange(1, 1).setValue('=QUERY(KEN_ALL!A:G,"WHERE A=' +keyword+'")');
  Logger.log(sheet.getRange(2, 5).getValues() + sheet.getRange(2, 6).getValues() + sheet.getRange(2, 7).getValues());
}
 

 

結果はご覧の通り。2秒という驚きの結果となりました。

 

まとめ

結果のまとめはこちらです。QUERY関数利用が圧倒的ですね。

 

実はExcelでも似たようなことができます。Match関数で検索してAddress関数でアドレス化下後にINDIRECT関数で値を読みに行くという方法です。たしかに速いですが他の方法でも割と速い(セルデータを配列に落とす時間が短い)のであまり恩恵がありません。

GASはWeb環境なのでデータを読みに行く時間がデータ量とともに顕著に長くなっていくので今回のようなテクニックは必要になってきます。

 

スポンサーリンク

 - Google Apps Script