Google Apps Script 時短ツール

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

お仕事おつにゃッス🐈

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

あなたのスプレッドシートで作ったリストで、部分一致で検索結果を表示して保存したいことはありませんか?

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

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

  • 部分一致の検索機能を作りたい
  • 検索して一致したものをシートに表示して保存したい
  • プログラミングでJavaScriptを学んだけど、何を作っていいかわからない

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

部分一致を検索するイメージ

GASでスプレッドシートのセル内のデータが部分一致する機能を作成する概要

1クリックでシート内の値をすべて検索し、該当するものだけを抽出する機能を作成します。

例えば、氏名に「山」がつく人物のデータを出力する場合、「青山」や「木山」などの氏名をすべて抽出します。

また、一致した結果を別のシートに転記して見やすくします。

この記事は、indexOfメソッドを使った検索機能のコードを改良しています。詳細はこちら。

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

続きを見る

部分一致を検索する準備

GASで部分一致したデータを別シートに書く準備をします。ファイルは1つ必要です。

必要なファイル

  • Google スプレッドシート  ・・・ 顧客データの作成 & コードの貼り付け

スプレッドシートの作成

Google スプレッドシートを作成します。

スプレッドシートのシートを2つ用意し、それぞれのシート名を「顧客データ」と「検索結果」に変更します。

シートの数が足りない場合は赤丸の+字アイコンを押して新規追加します。

GASでスプレッドシートのセル内のデータが部分一致する機能を作成する準備 シート名を変更

「顧客データ」シートには顧客のリストを登録します。

この記事では以下の画像のように、2行目から11行目までに記入していきます。スプレッドシートの準備は以上です。

GASでスプレッドシートのセル内のデータが部分一致する機能を作成する準備 シートのリストを埋める

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

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

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

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

部分一致を別シートに反映するコード

GASで部分一致した文字を別シートに反映するサンプルコードの公開と解説を行います。

解説の項目

  • サンプルコード
  • A, 検索ワード記入
  • B,シートの定義
  • C, 複数のセルから値を取得
  • D, 部分一致すれば配列に格納
  • E,「検索結果」に部分一致した値を記入

サンプルコード

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

function partMatch(){
  //A,検索ワード記入
  const searchWord = "山";

  //B,シートの定義
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); // 現在のSpreadSheetを取得
  const dataSheet = spreadSheet.getSheetByName("顧客データ");
  const resultSheet = spreadSheet.getSheetByName("検索結果");
  const START_COL_NUM = 1; //getRangeメソッドで読み込み始める列番号
  const START_ROW_NUM = 1; //getRangeメソッドで読み込み始める行番号
  const lastCol = dataSheet.getLastColumn();
  const lastRow = dataSheet.getLastRow();

  //C,複数のセルから値を取得
  const nameArray =dataSheet.getRange(START_ROW_NUM, START_COL_NUM, lastRow, lastCol).getValues(); //「顧客データ」シート内のデータを全て取得
  const nameArrayLen = nameArray.length;
  const matchArray = []; //部分一致したものだけを格納

  //D,部分一致すれば配列に格納
  for(var i=0; i<nameArrayLen; i++){
    if( i===0 ){ //最初の要素は判定処理しない
      continue;
    }else{
      const valueArray = nameArray[i][0].indexOf(searchWord);
      
      if(valueArray != -1){
        matchArray.push(nameArray[i]);
      }
    }

  }

  //E,「検索結果」に部分一致した値を記入
  const matchArrayLen = matchArray.length;
  resultSheet.getRange(START_ROW_NUM, START_COL_NUM, matchArrayLen, lastCol).setValues(matchArray);

}

A, 検索ワード記入

  //A,検索ワード記入
  const searchWord = "山"; //検索する言葉

検索する文字を定数searchWordに記入します。

この例では「山」を検索対象にしていますが、どの単語に変更しても問題ありません

B,シートの定義

  //B,シートの定義
  const spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); // 現在のSpreadSheetを取得
  const dataSheet = spreadSheet.getSheetByName("顧客データ");
  const resultSheet = spreadSheet.getSheetByName("検索結果");
  const START_COL_NUM = 1; //getRangeメソッドで読み込み始める列番号
  const START_ROW_NUM = 1; //getRangeメソッドで読み込み始める行番号
  const lastCol = dataSheet.getLastColumn(); //最終列の番号
  const lastRow = dataSheet.getLastRow(); //最終行の番号

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

定数dataSheetには「顧客データ」、定数resultSheetに「検索結果」のシートを指定します。

また、getRangeメソッドで読み込み始める番号を定義して処理するセルの位置を決めます。

最終行と最終列を取得するメソッドの詳細はこちらから。

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

続きを見る

C, 複数のセルから値を取得

  //C,複数のセルから値を取得
  const nameArray =dataSheet.getRange(START_ROW_NUM, START_COL_NUM, lastRow, lastCol).getValues(); //「顧客データ」シート内のデータを全て取得
  const nameArrayLen = nameArray.length;
  const matchArray = []; //部分一致したものだけを格納

「顧客データ」シート内の値を全て取得します。

getValuesメソッドで顧客情報をすべて取得した後、二次元配列内の要素の個数をかぞえます。

また定数matchArrayで空配列を定義し、のちのDの処理で部分一致したデータを格納します。

複数のセル内の値を取得するgetValuesメソッドの詳細はこちら。

【1行で簡単】GASでシートの複数のセルの値を一括で取得

続きを見る

D, 部分一致すれば配列に格納

  //D,部分一致すれば配列に格納
  for(var i=0; i<nameArrayLen; i++){

    if( i===0 ){ //最初の要素のみ別の処理
      continue;
    }else{
      const valueArray = nameArray[i][0].indexOf(searchWord);
      if(valueArray != -1){
        matchArray.push(nameArray[i]);
      }
    }

  }

取得した二次元配列内の値に、検索する文字が含まれていれば空配列に格納します。

配列の1番目に、1行目の「顧客データ」の文字が入っているのでcontinueで除外します。

その他の要素は一致する文字があるか判定します。

1つでも一致する文字があれば、pushメソッドmatchArrayに格納します。

一致する文字を検索するindexOfメソッドの詳細は以下の記事をご参考ください。

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

続きを見る

E,「検索結果」に部分一致した値を記入

 //E,「検索結果」に部分一致した値を記入
  const matchArrayLen = matchArray.length;
  resultSheet.getRange(START_ROW_NUM, START_COL_NUM, matchArrayLen, lastCol).setValues(matchArray);

部分一致した値を格納した配列を「検索結果」シートのセル内に記入します。

複数のセルに値を記入するsetValuesメソッドの詳細は以下の記事から。

【3分で完了】GASシートの複数のセルに値を書き込む方法

続きを見る

サンプルコードの説明は以上です。

部分一致した値を書き込むテスト

実際に、部分一致を別シートに書き込むテストをします。

Apps Scriptの上部にあるバーの文字が「partMatch」なら、左側の「実行」を押します。

GASでスプレッドシートのセル内のデータが部分一致を別シートに転記するpartMatch関数を実行

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

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

続きを見る

画像のように、「検索結果」シートに反映されてたら成功です。

氏名が「山」と部分一致するデータが結果として表示されました。

GASでスプレッドシートのセル内のデータが部分一致を別シートに転記するpartMatch関数を実行した結果

【業務効率化】セル内を検索するGASのコード一覧

この記事では、部分一致のコードを紹介しました。

GASを使えば、完全一致VLOOKUP関数のような検索機能の作成も可能です。

誰でも無料で使えるコードを掲載しているので、ぜひご参考ください。

もちろん、コピペOKです。

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

続きを見る

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

続きを見る

おわりに | GASで市場価値を高めるには

以上、GASで部分一致した値を検索して別シートに反映する方法でした。

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

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

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

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

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