【GAS】文字列を検索して該当データを取得するには？

訪問して頂きありがとうございます。まさふくろーです。

この記事では、文字列を検索して該当データを取得する方法をご紹介します。

文字列を検索して該当データを取得する

使用するシート名

「シート1」シート、「住所マスタ」シート

動作イメージ

ボタンをクリック
「シート1」シートの検索文字列の値を取得
2.で取得した値を条件に「住所マスタ」シートからデータを抽出するクエリを作成
「シート1」シートのセルに、3.で作成したクエリを設定し、実行

使用するデータ

都道府県一覧の全国一括.zip

参考郵便番号データ日本郵便

使用するメソッド

getActiveSpreadsheetメソッド

SpreadsheetApp.getActiveSpreadsheet()

説明

現在アクティブなSpreadsheetオブジェクトを返します。

getSheetByNameメソッド

Spreadsheetオブジェクト.getSheetByName(シート名)

説明

指定したシート名のSheetオブジェクトを返します。

getRangeメソッド

Sheetオブジェクト.getRange(a1Notation)

説明

セル範囲を取得します。

指定項目	必須	説明
a1Notation	〇	単一セルまたはセル範囲（複数セル）を指定します。

getValueメソッド

getRangeメソッドで取得した単一セル.getValue()

説明

getRangeメソッドで取得した単一セルの値を取得します。

setValueメソッド

getRangeメソッドで取得した単一セル.setValue(設定する値)

説明

getRangeメソッドで取得した単一セルに値を設定します。

指定項目	必須	説明
設定する値	〇	単一セルに設定する値を指定します。

使用する関数

QUERY関数

QUERY(データ, クエリ , [見出し])

説明

QUERY関数は、クエリ言語を使用してデータを抽出する関数です。

指定項目	必須	説明
データ	〇	クエリを実行するセルの範囲を指定します。
クエリ	〇	データを抽出するクエリを指定します。
クエリ	〇	クエリは二重引用符で囲みます。
見出し	－	見出しの行数を数値で指定します。
見出し	－	省略した場合は、全ての行を抽出対象のデータとして扱われます。

解説

住所マスタのA列～I列までをクエリの範囲にします。
クエリの「SELECT G,H,I」とは、G列、H列、I列を抽出するという意味です。
クエリの「WHERE」とは、条件を意味します。
クエリの「H LIKE \'%札幌%\'」とは、H列から文字列「札幌」が含まれるデータを抽出するという意味です。
クエリの「LIKE」と「%」を組み合わせることによって、完全一致ではなく部分一致させることができます。
クエリの「\'」とは、「'」（シングルクォーテーション）の前に「\」（バックスラッシュ）を付けることによって、「'」（シングルクォーテーション）を文字列として扱います。

手順

おおまかな流れ

Step.1

シートにボタンを作成

Step.2

スクリプトを作成

Step.3

ボタンにスクリプトを割り当てる

詳細

Step.1 シートにボタンを作成

参考 Step.1 シートにボタンを作成ボタンをクリックしたときにスクリプトを実行する

Step.2 スクリプトを作成

「ツール」をクリックします。

「スクリプトエディタ」を選択します。

すると、スクリプトエディタが起動します。

スクリプトを記述します。

function getHitData() {
 
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = spreadsheet.getSheetByName('シート1');

  sheet.activate();

  let range = sheet.getRange('B2');
  let SearchChar = range.getValue();

  sheet.getRange('A8').setValue('=QUERY(住所マスタ!A:I,"SELECT G,H,I WHERE H LIKE \'%' +SearchChar+'%\'")');
 
}

function getHitData() {

let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

let sheet = spreadsheet.getSheetByName('シート1');

sheet.activate();

let range = sheet.getRange('B2');

let SearchChar = range.getValue();

sheet.getRange('A8').setValue('=QUERY(住所マスタ!A:I,"SELECT G,H,I WHERE H LIKE \'%' +SearchChar+'%\'")');

}

1	「getHitData」関数を作成。
3	現在アクティブなスプレッドシートオブジェクトを取得し、変数「spreadsheet」に格納。
4	「シート1」シートオブジェクトを、変数「sheet」に格納。
6	「シート1」シートを選択状態にする。
8	「シート1」シートの「B2」セルの位置を取得。
9	「シート1」シートの「B2」セルの値を取得し、変数「SearchChar」に格納。
11	9行目で取得した変数「SearchChar」を条件に「住所マスタ」シートからデータを取得するクエリを、「シート1」シートの「A8」セルに設定し、実行します。（クエリの文字列をセルに設定することにより、自動的に実行されます。）