Googleスプレッドシート月末までの日付で検索した一覧を取得したい

やりたいこと。

有効期限とかの日付の入っている次のようなデータのマスターシートがあるとして、検索月を入力したら有効期限が今月末までのデータをマスターシートから取得して一覧が表示されるようにしたい。

A	B	C	D	E	F
区分	ID	タイトル	URL	有効期限	備考

特定のシートからデータを取得するにはIMPORTRANGE関数でスプレッドシートのデータを取得し、QUERY関数で表示するカラムを設定し、WHERE節で表示するデータの絞り込みを行えばOKです。
検索月を入力するセルの値から、当月末の日付を作成してWHERE節で有効期限セル <= 今月末とすれば良さそうです。

データを表示するシートは次のような想定です

	A	B
1	検索月を入力=>	{入力エリア}
2	{データを取得する関数}

B1セルに月を入力し、A2セルでB1セルの値を元にデータを取得する関数を作成します。

1. 入力値から当月末の日付を作成する

まず、検索セルB1に入力された月から、該当する月の月末の日付を作成します。

1-1. `DATE` 関数で今月の日付データを作成する

入力値が月のみなので、DATE関数を使って日付の形式にします。

DATE
指定した年、月、日を日付に変換します。
DATE(年, 月, 日)

= DATE( YEAR(TODAY()), B1, 1 )

年数は YEAR(TODAY())で本日の日付から年だけを取り出しています。
日付は、後で月末日に変換するので、間違いのない1日を指定しています。

1-2. `EOMONTH`関数で月末の日付を取得する

EOMONTH
起算日から指定した月数だけ前または後ろの月の最終日の日付を返します。
EOMONTH(開始日, 月数)

第二引数を0にすると今月末が取得できます。

= EOMONTH( DATE( YEAR(TODAY()),B1, 1 ), 0 )

これで、検索条件に使う月末の日付が完成しました。

2. `IMPORTRANGE`関数でマスターシートからデータを取得する

IMPORTRANGE
指定したスプレッドシートからセルの範囲を読み込みます。
IMPORTRANGE(スプレッドシートキー, 範囲の文字列)

スプレッドシートキーはスプレッドシートのURL https://docs.google.com/spreadsheets/d/< spreadsheet_key >/ から取得できます。
今回はA列(区分)とF列(備考)は不要なので、B列〜E列を取得したいとします。第二引数の「範囲の文字列」はシート名!B:Eとなります。

マスターシートからデータ取得はこんな感じ。

= importrange("spreadsheet_key", "シート名!B:E")

3. `QUERY`関数で表示したいセルを絞り込み

QUERY
Google Visualization API のクエリ言語を使用して、データ全体に対するクエリを実行します。
QUERY(データ, クエリ, [見出し])

データは 2 のIMPORTRANGEで取得したデータになるので、次のような感じになります。

= query( importrange("spreadsheet_key", "シート名!B:E"), "SELECT Col1, Col1 ... WHERE ..." )

QUERY関数で別のシートのデータを参照させた場合、SELECT文内で列の指定はAやBではなく、取り出した範囲に対してCol1、Col2という形式で記述しないとエラーになってしうようです。
この場合範囲をB:Eとしているので、Col1がB、Col2がCという扱いになります。

B:E列でD列(Col3)を除いて表示させるのは次のような感じ (長くなるので、SELECT文のみ書いています)

SELECT Col1, Col2, Col4

全列そのまま表示させるなら、* を使えばOKです

SELECT *

4. `WHERE` 節に条件を追加して有効期限が今月末までのデータのみを表示する

今回の例ではE列(Col4)が有効期限なので、WHERE Col4 <= 月末の日付とすれば良さそうです。

WHERE節で条件に日付を使う場合は

SELECT * WHERE Col4 <= date '2018-08-31'

のように date 'YYYY-MM-DD' という形式にする必要があります。 (この形式でないとエラーになる)

1.で作成した月末の日付をTEXT関数でYYYY-MM-DDの形式にフォーマットします。

TEXT
指定した表示形式に従って、数値をテキストに変換します。
TEXT(数値, 表示形式)

= TEXT( EOMONTH(DATE(YEAR(TODAY()),B1,1),0), "YYYY-MM-DD" )

これをWHERE節の条件に入れます。
QUERY関数のWHERE節内に他のセルの参照や式を入れるには、"& A1 &"の形式にします。 (QUERY関数内では、SELECT文全体が"(ダブルコーテーション)で囲まれているので、文字列連結の形にしないとエラー)

SELECT * WHERE Col4 <= date '"& TEXT( EOMONTH(DATE(YEAR(TODAY()),B1,1),0), "YYYY-MM-DD" ) &"'

最終的な関数は次のような感じになっているかと思います。

= query( importrange("spreadsheet_key", "シート名!B:E"), "SELECT * WHERE Col4 <= date '"& TEXT( EOMONTH(DATE(YEAR(TODAY()),B1,1),0), "YYYY-MM-DD" ) &"'" )

検索条件の月を変更しても意図したデータが表示できていればOKです。
これで、入力された月を条件に一致するデータだけが表示されるようになりました。

[参考]