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

お仕事おつにゃッス🐈

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

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

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

あっきょ

この記事は以下で悩んでいる人におすすめ！

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

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

タップできる目次

VLOOKUP関数のような機能を作るイメージ
検索機能を作成する準備
- Google スプレッドシートの作成
- サンプルコードを貼り付ける方法
検索機能のサンプルコード
一致する値を検索するテスト
【無料】スプシで使える検索機能の作り方
おわりに | GASでオリジナルの検索機能を作れる

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

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

リストからVLOOKUP関数のような検索機能のイメージです。

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

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

膨大なデータをスプレッドシートから探すのは労力と時間が必要ですが、GASで開発すればあっという間に検索結果を表示できます。

検索機能を作成する準備

Google Apps ScriptでVLOOKUP関数のような機能を作る準備をします。ファイルは1つ必要です。

必要なファイル

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

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

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

「企業名」、「商品名」、「数量」の計3つの項目をシートの1行目に記入します。2行目からは、企業の名前などをセルの列ごとに記入します。

シートの名前を変更します。下のバーの左側にある「シート１」を右クリックして「リスト」に変更します。Google スプレッドシートの設定は以上です。

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

サンプルコードは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メソッドはセルが空白でない最終行の番号を取得します。最終行を取得する方法については以下の記事から。

: 【5分で解決】GASでスプレッドシートの最終行と最終列を取得
続きを見る

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文を使いって「リスト」シート内の行を１つずつ取り出していきます。

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

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

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