Google Apps Script 時短ツール

【3分で完成】GASで検索結果をスプレッドシートに一覧表示

お仕事おつにゃッス🐈

文系未経験から社内SEになった、あっきょ(@daily_code_JP)です!

スプレッドシートで作成したリストから、特定のデータを探して別シートに貼り付けるのは大変ですよね?

この記事ではそのお悩みを、コピペOKのGoogle Apps Scriptのコードで解決します。

あっきょ
この記事は以下で悩んでいる人におすすめ!

  • シート内のデータを検索して別シートに貼り付けたい
  • 簡単にデータを管理して、見やすくしたい
  • プログラミングでJavaScriptを学んだけど、何を作っていいかわからない

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

検索結果を一覧表示するイメージ

GASで検索結果をスプレッドシートに一覧表示する概要

シートを2つ用意し、リストから検索した結果を転記する機能を作成します。

一致するデータを抽出する場合、リストから探してコピペしてたら膨大な時間と労力が必要で、ミスも発生しやすくなります。

しかし、今回開発するコードを利用すれば一瞬かつ1クリックでリスト内を検索して別シートにまとめます。

結果として、労力の削減とミスの減少につながります。

検索結果を一覧表示する準備

GASで検索結果を操作する準備をします。ファイルを1点用意します。

必要なファイル

  • Google スプレッドシート ・・・ リストを作成 & プログラムをコピペ

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

検索結果を一覧表示するスプレッドシートを作成していきます。

「企業名」,「商品名」,「数量」の3項目を1行目に記入します。2行目からは、個別のデータをそれぞれ行ごとに記入します。

GASで検索結果をスプレッドシートに一覧表示する準備

シートの名前を変更します。下バーの「シート1」を右クリックし、「リスト」にシート名を変えます。

シート名をリストに変更

検索用のスプレッドシートを新しく作成します。赤丸で囲った「+」ボタンを押し、スプレッドシートを生成します。また、シート名を「検索」に変更します。

シート名を「シート2」から「検索」に変更

「検索」シート内を編集します。A2に「企業名」、D2に「商品名」、E2に「数量」の項目を記入します。

「検索」シートの準備

検索する企業名をB2セルに入力します。今回は例として「株式会社B」を記入します。スプレッドシートの作成は以上です。

「検索」シートのB2に「株式会社B」を入力

サンプルコードを貼り付ける場所

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

上バーの「拡張機能」の中にある「Apps Script」をクリック。ここにコードを書きます。

GASをスプレッドシートに記入する際の「Apps Script」の表示

検索結果を表示するサンプルコード

検索結果を一覧で一斉表示するサンプルコードの公開と解説をします。

解説の項目

  • サンプルコード
  • A, 操作するスプレッドシートの定義
  • B, 一致する値を配列に格納
  • C,「検索」シートに一致したデータを入力

サンプルコード

右上にあるマークを押すとコードのコピペを一瞬でできます。また、コード内で文頭にアルファベットが付いているコメントごとに、詳細を説明していきます。

function searchBoughtItemsList(){

  //A,スプレッドシートの定義
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const listSheet = spreadSheet.getSheetByName('リスト');
  const listLastRow = listSheet.getLastRow();
  const searchSheet = spreadSheet.getSheetByName('検索');
  const companyName = searchSheet.getRange("B2").getValue(); //指定された「検索」シートのセルに企業名を記入
  const matchArray = []; //一致する値を格納する配列
  const START_WRITE_ROW = 3; //値を書き始める「検索」シートの行
  const START_WRITE_COL = 4; //値を書き始める「検索」シートの列

  //B,一致する値を配列に格納
  for (var i=1; i<=listLastRow; i++){
    const listedCompany = listSheet.getRange("A"+ i).getValue(); //「リスト」シートの「企業名」
    if(companyName == listedCompany){      
      const listEach = listSheet.getRange("B" + i + ":" + "C" + i ).getValues().flat();
      matchArray.push(listEach);
    }else{
      continue;
    }
  }

  //C,「検索」シートに一致したデータを入力
  const matchArrayLen = matchArray.length;
   for(var y=0; y<matchArrayLen; y++){ //外側のfor
    const elemLen = matchArray[y].length;
    for(var z=0; z<elemLen; z++){ //内側のfor
      searchSheet.getRange(START_WRITE_ROW+y,START_WRITE_COL+z).setValue(matchArray[y][z]);
    }
  }

}

A, 操作するスプレッドシートの定義

  //A,スプレッドシートの定義
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const listSheet = spreadSheet.getSheetByName('リスト');
  const listLastRow = listSheet.getLastRow(); 
  const searchSheet = spreadSheet.getSheetByName('検索');
  const companyName = searchSheet.getRange("B2").getValue(); //指定された「検索」シートのセルに企業名を記入
  const matchArray = []; //「リスト」シートで一致する値を格納する配列
  const START_WRITE_ROW = 3; //値を書き始める「検索」シートの行
  const START_WRITE_COL = 4; //値を書き始める「検索」シートの列

「リスト」シートと「検索」シートを定義します。

定数 companyNameは「検索」シートのB2の値を取得します。

また、定数 matchArray は「リスト」シートの「企業名」列と「検索」シートのB2が一致した場合、次のBの処理で一致した値を格納します。

B, 一致する値を配列に格納

//B,一致する値を配列に格納
  for (var i=1; i<=listLastRow; i++){
    const listedCompany = listSheet.getRange("A"+ i).getValue(); //「リスト」シートの「企業名」
    if(companyName == listedCompany){      
      const listEach = listSheet.getRange("B" + i + ":" + "C" + i ).getValues().flat();
      matchArray.push(listEach);
    }else{
      continue;
    }
  }

for文で「リスト」シートを1行ずつ読み込み、一致する値をmatchArray配列に格納します。

if文で企業名が一致した場合、getValuesメソッドで取得した二次元配列の「商品」と「数量」を一次元化して格納します。

また、一致しない場合はcontinueで処理を飛ばします。

二次元配列を普通の配列に変換するflatメソッドの詳細はこちら。

【初心者必須】GASで二次元配列を一次元化するflatメソッド

続きを見る

C,「検索」シートに一致したデータを入力

//C,「検索」シートに一致したデータを入力
  const matchArrayLen = matchArray.length;
   for(var y=0; y<matchArrayLen; y++){ //外側のfor
    const elemLen = matchArray[y].length;
    for(var z=0; z<elemLen; z++){ //内側のfor
      searchSheet.getRange(START_WRITE_ROW+y,START_WRITE_COL+z).setValue(matchArray[y][z]);
    }
  }

一致したデータを格納した二次元配列のmatchArrayを「検索」シートに入力します。

このfor文内の流れを下の画像で説明します。外側のfor文は赤枠のmatchArray内の要素を取得しますが、赤枠内の要素も配列です。

内側のfor文で黄色マーカーの要素を取得してシートに値を入力します。この処理を上限まで行います。

matchArray配列内の説明

この記事では省略しますが、二次元配列の要素の取り出し方はこちらから。コードの説明は以上です。

【5分で便利】GASで二次元配列内の配列から要素を取り出し

続きを見る

結果を一覧表示するプログラムの実行

実際に「検索」シートに転記できるかテストします。

Apps Scriptの上バーの文字が「searchBoughtItemsList」になっていたら「実行」を押します。

searchBoughtItemsList関数を実行

「このアプリはGoogleで確認されていません」と表示される場合はこちら

【3分で解決】GASの「このアプリはGoogleで確認されていません」を許可する方法

続きを見る

「検索」シートに結果を出力するので確認します。画像のように表示されていたら成功です。

GAS 検索結果をスプレッドシートに転記完了

【無料】GASを使った検索機能の作り方の紹介

この記事では、一致するデータを別シートに転記するコードを紹介しました。

GASを使えば他にも部分一致の検索にも使えます。

もちろんコピペOKなので、いつもの業務にお使いください!

【作業3割減】GASで部分一致した値を別シートに書き込むサンプルコード

続きを見る

【5分で解決】GASで文字列を検索する2つのサンプルコード

続きを見る

おわりに | GASで業務効率化できる!

以上がGoogle Apps Scriptで検索結果をスプレッドシート上で一覧表示する方法でした。

そのままコピペして日常業務で使いやすいように改修したり、プログラミングの練習や動作確認用に使ってみましょう。

GASでさらに業務効率化する場合、参考書やプログラミングスクールを使うとあなた自身でアプリを作るスキルが身に付きます

プログラミング未経験者にもわかりやすく解説しています。

GASユーザーのあっきょがおすすめの勉強方法はこちら↓

プログラミングスクールは82社を分析して5つ厳選してるッスよ!
あっきょ

次回の記事もご期待ください!

-Google Apps Script, 時短ツール
-, , , , , , , , , , , , , , , , , , ,