GASでVLOOKUP関数を再現する方法〜別シートから値を取得しよう〜

スプレッドシートでVLOOKUP関数にはお世話になってきたけど、GASでも使えるの？

猫男

catman

おし、catmanがGASの世界を案内しよう！

GASでVLOOKUP関数を再現する方法

GASにVLOOKUP関数があるわけではありません。

がしかし、

VLOOKUP関数と全く同じ処理を再現できます。

それではGASのコードを見ていきましょう。

GASでVLOOKUPを再現する基本構文

VLOOKUPを実現するには、別のスプレッドシートのデータを取得して検索処理をします。

function vlookupExample(searchKey, sheetName, range, columnIndex) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const data = sheet.getRange(range).getValues();

  for (let i = 0; i < data.length; i++) {
    if (data[i][0] == searchKey) {
      return data[i][columnIndex - 1];
    }
  }
  return "Not Found";
}

const result = vlookupExample("りんご", "フルーツシート", "A1:B4", 2);
Logger.log(result);

スプレッドシートの指定範囲内で、1列目に「検索キー（searchKey）」が一致する行を探し、指定された列番号の値を取得しています。

const result = vlookupExample("りんご", "フルーツシート", "A1:B4", 2);

では、検索キーを「りんご」、取得先のシート名を「フルーツシート」、検索範囲を「A1:B4」、そして2列目の値を取得していますね。

たとえば、以下のようなスプレッドシートの「フルーツシート」タブがあるとします。

その場合、今回の関数で取得できるのは、検索キー「りんご」の2列目の値の「赤」です。

この「赤」が変数resultに格納できるはずです。

配列でVLOOKUPを再現する

GASでVLOOKUPを使う際に、配列を利用してデータを自由に扱うことができます。

function arrayVlookup(searchKey, dataArray) {
  for (let i = 0; i < dataArray.length; i++) {
    if (dataArray[i][0] == searchKey) {
      return dataArray[i][1];  // インデックスを適切に調整
    }
  }
  return "Not Found";
}

今回のスクリプトは、スプレッドシートの値を直接参照するのではありません。

すでに手元にある 2次元配列（dataArray）を検索対象として、VLOOKUP 関数のような動作を再現していますね。

いってしまえば、

配列に対してVLOOKUPっぽいことができる簡易検索関数

です。

const fruits = [
  ["りんご", "赤"],
  ["みかん", "オレンジ"],
  ["バナナ", "黄色"]
];

const result = arrayVlookup("バナナ", fruits);
Logger.log(result);  // "黄色"

たとえば、このスクリプトなら

黄色

と出力されるはずです。

練習問題

1. 「従業員名」というシートから「ID」をキーに「部署名」を取得するスクリプトを書いてみましょう。
2. 複数のシート（「シート1」と「シート2」）のデータをつなぎ合わせ、特定のキーに基づいて検索するスクリプトを書いてみましょう。

解答・解説

function getDepartmentById(employeeId) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("従業員名");
  const range = sheet.getRange("A2:C10");  // AにID, Cに部署名があると仮定
  const data = range.getValues();

  for (let i = 0; i < data.length; i++) {
    if (data[i][0] === employeeId) {
      return data[i][2]; // 部署名を返す
    }
  }
  return "Not Found";
}

function combineSheetsAndSearch(searchKey) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("シート1");
  const sheet2 = ss.getSheetByName("シート2");
  const data1 = sheet1.getRange("A1:B10").getValues();
  const data2 = sheet2.getRange("A1:B10").getValues();
  
  const combinedData = data1.concat(data2);

  for (let i = 0; i < combinedData.length; i++) {
    if (combinedData[i][0] === searchKey) {
      return combinedData[i][1];
    }
  }
  return "Not Found";
}

– getRange(“A2:C10”): 特定の範囲のデータを取得しています。
– concat: 二つの配列をつなぎ合わせます。データが複数のシートにある場合に有効です。

concatの使い方の復習はこちらから！