VLOOKUP関数をGoogle Apps Scriptでやってみた

前回、他ファイルのデータを参照するVLOOKUP関数をやったものの、現状の様々な要素を考えたら、Google Apps Scriptでやった方が良いと結論づけました。

と、いうわけで、今回は「Google Apps Script」でVLOOKUP関数を実現する方法をまとめてみました。

どんな処理をするの？

調べてみると様々な方法があり、押さえるポイントとして「タイムアウト」を意識する必要があります。
公式リファレンスによると「Google Apps Script」は「Script runtime」が1実行当たり6分という制限を設けているとのこと。
特にセルのデータ取得や書き込み、メソッドの多用が時間を取る傾向にあるので、極力無駄を省いていきたいです。

現時点で私の持っている知識を使って考えたのは次のような構想。

メリットは、VLOOKUP関数では「キーが左、データは右」の範囲しか取得できませんが、「Google Apps Script」なら「キーが右、データは左」でも取得できます。
デメリットは、検索キーが配列に格納されたキーと「一致」しなければ、返ってくる行番号が「-1」になってしまうことでしょう。

コードで書くとどうなる？

前提条件は次の通り。

シート名「データ」に参照したい情報が入っています。
この情報は疑似個人情報データ生成サービスで作成しました。

シート名「シート1」にあるカタカナから漢字の氏名を取り出してみます。

コードはこんな風に書いてみました。

function MyVLOOKUP() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const SearchSheet = ss.getSheetByName('シート1');
  const LastRow = SearchSheet.getLastRow();
  const DataSheetValues = ss.getSheetByName('データ').getDataRange().getValues();
  const Key = [];
  const Data = [];
 
// キーと返したいデータを配列に格納
  for (let i = 0; i < DataSheetValues.length; i++){
    Key.push(DataSheetValues[i][1]);
    Data.push(DataSheetValues[i][0]);
  }
 
// 検索キーを取得し、対応したデータを返す
  let SearchKey = SearchSheet.getRange(2,1,LastRow - 1,2).getValues();
 
  for (let i = 0; i < SearchKey.length; i++){
    SearchKey[i][1] = Data[Key.indexOf(SearchKey[i][0])];
  }
 
// 対応データの入った配列「SearchKey」をシートに書き込む
  SearchSheet.getRange(2,1,LastRow - 1,2).setValues(SearchKey);
}

結果はこちら。

ちゃんと取得できていますね。
ちなみに、検索キーと参照データを両方とも10000件に増やして実行しても、あっという間に終わりました。

まとめ

「Google Apps Script」でタイムアウトにならないようにVLOOKUPを実現してみました。
私の場合は10000件も処理しないので、これで十分と思われます。
functionの引数を設定すれば、もっと使いやすい関数になりそうです。