GASでVLOOKUPを自在に！複数シートから配列まで完全ガイド

GASでVLOOKUPを再現しよう

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

しかし、全く同じ処理を再現できます。

それではGASコードを使ってどう書くのか見ていきましょう。

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

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

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:B10", 2);
Logger.log(result);

– SpreadsheetApp.getActiveSpreadsheet(): 現在のスプレッドシートを取得します。
– sheet.getRange(range).getValues(): 指定した範囲のデータを配列として取得します。
– forループ: 指定されたキーが見つかるまで、データ配列を検索します。

GASで別シートのデータを処理する方法

GASでは簡単に別シートからデータを参照できます。

別のシートからデータを使い、VLOOKUPのように検索可能です。

function vlookupDifferentSheet(searchKey) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("別シート名");
  const range = sheet.getRange("A1:C10");
  const data = range.getValues();

  // データの中から検索キーを探し、対応する値を返す
  for (let i = 0; i < data.length; i++) {
    if (data[i][0] === searchKey) {
      return data[i][1];  // 必要に応じて列番号を変更
    }
  }
  return "Not Found";
}

const result = vlookupDifferentSheet("検索キー");
Logger.log(result);

– getSheetByName(“別シート名”): 特定の名前のシートを取得します。

配列を使ってカスタム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";
}

// 使用するデータ配列
const dataArray = [
  ["キー1", "値1"],
  ["キー2", "値2"],
  ["キー3", "値3"]
];

const result = arrayVlookup("キー2", dataArray);
Logger.log(result);

– forループ: 与えられたデータの配列の中で検索キーに該当する値を探す処理です。

練習問題

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: 二つの配列をつなぎ合わせます。データが複数のシートにある場合に有効です。