【5分で楽】GASでVLOOKUP関数のような検索機能を作成

2022年2月4日2024年3月14日

お仕事おつにゃッス🐈

文系未経験から社内SEになった、あっきょ（@daily_code_JP）です！

作成したリストから希望するデータを探すのは多大な時間と労力を使いますよね？

しかし、GASのコードを使えば1クリックでデータの検索をできます。

この記事ではそのお悩みを5分でできるコピペOKのGoogle Apps Scriptで解決します。

こんな人におすすめ！

スプレッドシートでデータを検索したい
検索する機能をもっと便利にしたい
プログラミングでJavaScriptを学んだけど何を作っていいかわからない

簡単に誰でも無料で作れるのでお気軽に学んでいきましょう！

VLOOKUP関数のような機能を作るイメージ

Google Apps ScriptでVLOOKUP関数のような機能を作成するイメージ図

この記事では企業名と商品を入力すると購入した個数を検索表示するコードを作成。

例えば、株式会社Bが購入したキーボードの個数をスプシから検索して「100個」とログ出力します。

膨大なデータから探すのは手間がかかる作業をGASであっという間に検索結果を表示します。

あっきょ

1クリックで簡単に検索機能を作れるッスよ！

検索機能を作成する準備

Google Apps ScriptでVLOOKUP関数のような機能を作る準備。

ファイルは1つ必要です。

必要なファイル

Googleスプレッドシート…商品購入履歴の作成＆プログラムを実行

Google スプレッドシートの作成

検索機能を作成するGoogle スプレッドシートを作成します。

企業名
商品名
数量

の計3つの項目をシートの1行目に記入。

2行目からは企業の名前などをセルの列ごとに書き込みます。

シートの名前を変更します。

下のバーの左側にある「シート１」を右クリックして「リスト」に変更。

Google スプレッドシートの設定は以上です。

画像に alt 属性が指定されていません。ファイル名: make-list-sheet-vlookup.png

サンプルコードを貼り付ける方法

サンプルコードはGoogle スプレッドシートにコピペ。

上バーの「拡張機能」の中にある「Apps Script」をクリックしてコードを貼り付けます。

検索機能のサンプルコード

VLOOKUP関数のような検索機能をGASで再現するサンプルコードと解説をしていきます。

解説の項目

サンプルコード
A, 検索ワードを入力
B, 操作するシートの定義
C, 一致する値だけを取得してログ出力

サンプルコード

右上にあるマークを押すとコードのコピペが一瞬でできます。

コード内で文頭にアルファベットが付いているコメントごとに詳細を説明します。

function searchBoughtNum(){
  //A.検索ワードを入力
  const companyName = "株式会社B"; //検索する企業名
  const boughtItem = "キーボード"; //検索する商品名

  //B,定数の定義
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const listSheet = spreadSheet.getSheetByName('リスト');
  const listLastRow = listSheet.getLastRow();

  //C,一致する値だけを取得してログ出力
  for (var i=1; i<=listLastRow; i++){
    const listedCompany = listSheet.getRange("A"+ i).getValue(); //A列の i 行目の企業名
    const listedItem = listSheet.getRange("B"+ i).getValue(); //B列の i 行目の商品名

    //値が一致していたらログ出力
    if(companyName == listedCompany && boughtItem == listedItem){
      const getNum = listSheet.getRange("C" + i).getValue();
      Logger.log(getNum + "個");
    }else{
      continue;
    }
  }
}

A, 検索ワードを入力

//A.検索ワードを入力
const companyName = "株式会社B"; //検索する企業名
const boughtItem = "キーボード"; //検索する商品名

検索する値を定義。

この例では、「株式会社B」と「キーボード」を検索対象に設定しています。

「リスト」シートに存在する値なら定数内の値を自由に変更できます。

B, 操作するシートの定義

//B,操作するシートの定義
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
const listSheet = spreadSheet.getSheetByName('リスト');
const listLastRow = listSheet.getLastRow();

操作するスプレッドシートを定義します。

定数SpreadSheetで現在使っているスプレッドシートを取得、定数listSheetで「リスト」シートを取得。

また、getLastRowメソッドでセルが空白でない最終行の番号を取得します。

最終行を取得する方法の詳細は以下の記事から。

C, 一致する値だけを取得してログ出力

 //C,一致する値だけを取得してログ出力
  for (var i=1; i<=listLastRow; i++){
    const listedCompany = listSheet.getRange("A"+ i).getValue(); //A列の i 行目の企業名
    const listedItem = listSheet.getRange("B"+ i).getValue(); //B列の i 行目の商品名

    //値が一致していたらログ出力
    if(companyName == listedCompany && boughtItem == listedItem){
      const getNum = listSheet.getRange("C" + i).getValue();
      Logger.log(getNum + "個");
    }else{
      continue;
    }
  }

for文で「リスト」シート内の行を１つずつ取り出します。

条件を満たす場合は「リスト」シート内のC列の i 行目にある値を取得し定数getNumに代入してログ出力。

一致しなければcontinueで次の処理に移ります。

コードの説明は以上です。