[Google Apps Script] 複数条件でのテーブルデータの検索（VLOOKUP）を多次元テーブルに対して行う

若槻龍太

2024.01.21

こんにちは、CX 事業本部製造ビジネステクノロジー部の若槻です。

Google スプレッドシートでテーブルから条件に合致するレコードの検索を行いたい場合は、VLOOKUP がよく使われると思います。

https://support.google.com/docs/answer/3093318 より引用

しかしこの VLOOKUP を複数のカラムの値を条件にして行いたい場合は、複数カラムの値を結合した文字列を取得するなど関数の記述が複雑になってしまいます。

そこで今回は、複数条件での vlookup を多次元テーブルに対して行う処理を Google Apps Script（GAS） で実装してみました。

対象のスプレッドシート

Source シート

検索対象のレコードが格納されている Source シートです。

Target シート

検索条件となる複数のカラムが格納されており、また検索結果の入力先となる Target シート（多次元テーブル）です。

GAS コード

VLOOKUP を行う GAS コードを作成するにあたり、いくつか改善を重ねていったのでそれぞれの段階でのコードを紹介します。

基本的な処理の実装

まずはじめに次のような基本的な処理を行うコードを作成しました。

  1. Source シートからテーブルデータを取得
  2. Target シートから検索条件を取得
  3. Target シートのセルごとに検索条件に合致するレコードを検索
  4. 検索結果を Target シートにセット

Code.gs

const SOURCE_START_CELL = 'B3';
const SOURCE_END_CELL = 'F52';
const TARGET_START_ROW_NUM = 3;
const TARGET_END_ROW_NUM = 29;
const TARGET_COLUMN = 'E';

function myFunction() {
  // スプレッドシートを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // Source シートの取得
  const sourceSheet = ss.getSheetByName('Source');

  // Source データのセル範囲の取得
  const sourceRange = sourceSheet.getRange(SOURCE_START_CELL + ':' + SOURCE_END_CELL);

  // Source データの二次元の値データの取得
  const sourceValues = sourceRange.getValues();

  // Source データの Json 形式のリストの取得
  const sourceDataList = sourceValues.map(value => {
    return {
      name: value[0],
      columnA: value[1],
      columnB: value[2],
      columnC: parseInt(value[3]),
      columnD: value[4]
    };
  });

  // Target シートの取得
  const targetSheet = ss.getSheetByName('Target');

  // 検索条件となる ColumnD の値の取得
  const conditionColumnD = targetSheet.getRange(TARGET_COLUMN + '2').getValue();

  // Target に指定した範囲をセルごとに処理
  for (let i = TARGET_START_ROW_NUM; i <= TARGET_END_ROW_NUM; i++) {
    // 検索条件のカラムの値を取得
    const conditionColumnA = targetSheet.getRange('B' + i).getValue();
    const conditionColumnB = targetSheet.getRange('C' + i).getValue();
    const conditionColumnC = parseInt(targetSheet.getRange('D' + i).getValue());

    // 検索結果
    const results = sourceDataList.filter(value =>
      value.columnA === conditionColumnA &&
      value.columnB === conditionColumnB &&
      value.columnC === conditionColumnC &&
      value.columnD === conditionColumnD
    );

    if (results.length > 0) {
      // 条件に合う結果がある場合は、先頭のデータの name をセット
      targetSheet.getRange(TARGET_COLUMN + i).setValue(results[0].name);
    } else {
      // 条件に合う結果がない場合は '-' をセット
      targetSheet.getRange(TARGET_COLUMN + i).setValue('-');
    }
  }
}

コードを実行すると、コード冒頭で指定した E3 から E29 の範囲のセルに値がセットされました。

setValues で複数のセルに一括で値をセットする

前述のコードの問題点として、setValue でセルごとに値をセットしているため処理が遅いという点があります。そこで次のコードでは setValues で複数のセルに一括で値をセットするようにしました。

Code.gs

const SOURCE_START_CELL = 'B3';
const SOURCE_END_CELL = 'F52';
const TARGET_START_ROW_NUM = 3;
const TARGET_END_ROW_NUM = 29;
const TARGET_COLUMN = 'E';

function myFunction() {
  // スプレッドシートを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // Source シートの取得
  const sourceSheet = ss.getSheetByName('Source');

  // Source データのセル範囲の取得
  const sourceRange = sourceSheet.getRange(SOURCE_START_CELL + ':' + SOURCE_END_CELL);

  // Source データの二次元の値データの取得
  const sourceValues = sourceRange.getValues();

  // Source データの Json 形式のリストの取得
  const sourceDataList = sourceValues.map(value => {
    return {
      name: value[0],
      columnA: value[1],
      columnB: value[2],
      columnC: parseInt(value[3]),
      columnD: value[4]
    };
  });

  // Target シートの取得
  const targetSheet = ss.getSheetByName('Target');

  // 検索条件となる ColumnD の値の取得
  const conditionColumnD = targetSheet.getRange(TARGET_COLUMN + '2').getValue();

  // 処理結果を格納するための配列
  let resultsArray = [];

  // Target に指定した範囲をセルごとに処理
  for (let i = TARGET_START_ROW_NUM; i <= TARGET_END_ROW_NUM; i++) {
    // 検索条件のカラムの値を取得
    const conditionColumnA = targetSheet.getRange('B' + i).getValue();
    const conditionColumnB = targetSheet.getRange('C' + i).getValue();
    const conditionColumnC = parseInt(targetSheet.getRange('D' + i).getValue());

    // 検索結果
    const results = sourceDataList.filter(value =>
      value.columnA === conditionColumnA &&
      value.columnB === conditionColumnB &&
      value.columnC === conditionColumnC &&
      value.columnD === conditionColumnD
    );

    if (results.length > 0) {
      // 条件に合う結果がある場合は、先頭のデータの name を配列に追加
      resultsArray.push([results[0].name]);
    } else {
      // 条件に合う結果がない場合は '-' を配列に追加
      resultsArray.push(['-']);
    }
  }

  // 複数のセルに一括で値をセット
  targetSheet.getRange(TARGET_COLUMN + TARGET_START_ROW_NUM + ':' + TARGET_COLUMN + TARGET_END_ROW_NUM).setValues(resultsArray);
}

複数の列に一括でセットする

前述までのコードでは一度の実行で処理できる列数が 1 列のみでしが、複数の列を一括で処理できるようにしました。行と列それぞれに対して For ループを行うことで複数列に対する処理を実現しています。

Code.gs

const SOURCE_START_CELL = 'B3';
const SOURCE_END_CELL = 'F52';
const TARGET_START_ROW_NUM = 3;
const TARGET_END_ROW_NUM = 29;
const TARGET_START_COLUMN = 'E';
const TARGET_END_COLUMN = 'N';

function myFunction() {
  // スプレッドシートを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // Source シートの取得
  const sourceSheet = ss.getSheetByName('Source');

  // Source データのセル範囲の取得
  const sourceRange = sourceSheet.getRange(SOURCE_START_CELL + ':' + SOURCE_END_CELL);

  // Source データの二次元の値データの取得
  const sourceValues = sourceRange.getValues();

  // Source データの Json 形式のリストの取得
  const sourceDataList = sourceValues.map(value => {
    return {
      name: value[0],
      columnA: value[1],
      columnB: value[2],
      columnC: parseInt(value[3]),
      columnD: value[4]
    };
  });

  // Target シートの取得
  const targetSheet = ss.getSheetByName('Target');


  // 処理結果を格納するための配列
  let resultsArray = [];

  // Target に指定した範囲をセルごとに処理
  for (let i = TARGET_START_ROW_NUM; i <= TARGET_END_ROW_NUM; i++) {
    let rowResults = [];
    for (let j = TARGET_START_COLUMN.charCodeAt(0); j <= TARGET_END_COLUMN.charCodeAt(0); j++) {
      // 検索条件のカラムの値を取得
      const conditionColumnA = targetSheet.getRange('B' + i).getValue();
      const conditionColumnB = targetSheet.getRange('C' + i).getValue();
      const conditionColumnC = parseInt(targetSheet.getRange('D' + i).getValue());
      const conditionColumnD = targetSheet.getRange(String.fromCharCode(j) + '2').getValue();

      // 検索結果
      const results = sourceDataList.filter(value =>
        value.columnA === conditionColumnA &&
        value.columnB === conditionColumnB &&
        value.columnC === conditionColumnC &&
        value.columnD === conditionColumnD
      );

      if (results.length > 0) {
        // 条件に合う結果がある場合は、先頭のデータの name を配列に追加
        rowResults.push([results[0].name]);
      } else {
        // 条件に合う結果がない場合は '-' を配列に追加
        rowResults.push(['-']);
      }
    }
    resultsArray.push(rowResults);
  }

  // 複数のセルに一括で値をセット
  targetSheet.getRange(TARGET_START_COLUMN + TARGET_START_ROW_NUM + ':' + TARGET_END_COLUMN + TARGET_END_ROW_NUM).setValues(resultsArray);
}

コードを実行すると、コード冒頭で指定した E3 から E29 の範囲のセルに値がセットされました。

AA 列以降も対応可能とする

前述までのコードには、AA 列以降は処理できないという問題点があります。

例えば、次のように AA 列以降の列を処理対象に指定してみます。

Code.gs

const SOURCE_START_CELL = 'B3';
const SOURCE_END_CELL = 'F52';
const TARGET_START_ROW_NUM = 3;
const TARGET_END_ROW_NUM = 29;
const TARGET_START_COLUMN = 'Y';
const TARGET_END_COLUMN = 'AB';

すると次のようなエラーが発生します。

Exception: The number of columns in the data does not match the number of columns in the range. The data has 0 but the range has 4.
myFunction @ Code.gs:68

これは AA 列以降は 2 文字の組み合わせとなるため、 charCodeAt および fromCharCode で想定通りの値が取得できず、setValues で設定したいデータのレンジと設定対象のレンジが一致しないためです。

そこで次のようにカラムを番号から文字列へ変換する columnToLetter および文字列から番号へ変換する letterToColumn を実装します。

Code.gs

const SOURCE_START_CELL = 'B3';
const SOURCE_END_CELL = 'F52';
const TARGET_START_ROW_NUM = 3;
const TARGET_END_ROW_NUM = 29;
const TARGET_START_COLUMN = 'Y';
const TARGET_END_COLUMN = 'AB';

// カラムを番号から文字列へ変換
function columnToLetter(column) {
  let temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

// カラムを文字列から番号へ変換
function letterToColumn(letter) {
  let column = 0, length = letter.length;
  for (var i = 0; i < length; i++) {
    column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
}

function myFunction() {
  // スプレッドシートを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // Source シートの取得
  const sourceSheet = ss.getSheetByName('Source');

  // Source データのセル範囲の取得
  const sourceRange = sourceSheet.getRange(SOURCE_START_CELL + ':' + SOURCE_END_CELL);

  // Source データの二次元の値データの取得
  const sourceValues = sourceRange.getValues();

  // Source データの Json 形式のリストの取得
  const sourceDataList = sourceValues.map(value => {
    return {
      name: value[0],
      columnA: value[1],
      columnB: value[2],
      columnC: parseInt(value[3]),
      columnD: value[4]
    };
  });

  // Target シートの取得
  const targetSheet = ss.getSheetByName('Target');

  // 処理結果を格納するための配列
  let resultsArray = [];

  // Target に指定した範囲をセルごとに処理
  for (let i = TARGET_START_ROW_NUM; i <= TARGET_END_ROW_NUM; i++) {
    let rowResults = [];
    for (let j = letterToColumn(TARGET_START_COLUMN); j <= letterToColumn(TARGET_END_COLUMN); j++) {
      // 検索条件のカラムの値を取得
      const conditionColumnA = targetSheet.getRange('B' + i).getValue();
      const conditionColumnB = targetSheet.getRange('C' + i).getValue();
      const conditionColumnC = parseInt(targetSheet.getRange('D' + i).getValue());
      const conditionColumnD = targetSheet.getRange(columnToLetter(j) + '2').getValue();

      // 検索結果
      const results = sourceDataList.filter(value =>
        value.columnA === conditionColumnA &&
        value.columnB === conditionColumnB &&
        value.columnC === conditionColumnC &&
        value.columnD === conditionColumnD
      );

      if (results.length > 0) {
        // 条件に合う結果がある場合は、先頭のデータの name を配列に追加
        rowResults.push([results[0].name]);
      } else {
        // 条件に合う結果がない場合は '-' を配列に追加
        rowResults.push(['-']);
      }
    }
    resultsArray.push(rowResults);
  }

  // 複数のセルに一括で値をセット
  targetSheet.getRange(TARGET_START_COLUMN + TARGET_START_ROW_NUM + ':' + TARGET_END_COLUMN + TARGET_END_ROW_NUM).setValues(resultsArray);
}

すると次のように Y3 から AB29 の範囲のセルに値がセットされました。

おわりに

Google Apps Script で複数条件でのテーブルデータの検索（VLOOKUP）を多次元テーブルに対して行う方法をご紹介しました。

Google スプレッドシートのデータでよく行うパターンの処理だと思うので、ぜひ参考にしてみてください。

参考

以上

AWS

関連記事

Amazon QuickSight の分析結果を Google Apps Script （GAS） でメッセージを整形して Slack へ通知する方法

大村 保貴

2024.03.26

意外とハマる！ Google Apps Script で関数が期待通りに呼び出されない場合の確認ポイント

若槻龍太

2024.03.16

Googleスプレッドシートの値が変更されたらSlackに通知される仕組みを作ってみた

黒木 祐馬

2024.02.20

Googleスプレッドシートの英単語をクリックしたら音声で聞き取れる実装してみたが車輪の再発明だった件

yosh-k

2023.12.16