Googleスプレッドシート 他のシートから特定のセルを条件付きで取ってきて表示させたい

他のスプレッドシートで作成してあるリストから必要なセルだけを取ってきて、条件に合うものだけを表示させる方法のメモ。
IMPORTRANGEとQUERYという関数を使って実現することができました。

他のスプレッドシートからデータを取ってくる関数 IMPORTRANGE

IMPORTRANGE - ドキュメント エディタ ヘルプ

IMPORTRANGE(spreadsheet_key, range_string)

• spreadsheet_key - URL の「key=」属性に含まれる長い文字列です。新しい Google スプレッドシートでは URL 全体を指定します。
  • spreadsheet_key の値は二重引用符で囲むか、適切なテキストを含むセルへの参照にする必要があります。
• range_string - 読み込む範囲を指定する文字列で、"[シート名!] 範囲" の形式で指定します（例: "シート1!A2:B6"、"A2:B6"）。
  • range_string のシート名の要素は省略可能です。デフォルトでは、IMPORTRANGE 関数は最初のシートの指定範囲から読み込みます。
  • range_string の値は二重引用符で囲むか、適切なテキストを含むセルへの参照にする必要があります。

特定のセルを表示させる&条件に合うものだけを表示させる

QUERY - ドキュメント エディタ ヘルプ

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

• データ - クエリを実行するセルの範囲です。
  • データの各列に指定できるのは、ブール値、数値（日付/時刻など）、文字列の値のみです。
  • 1 つの列に異なる種類のデータが含まれている場合は、その列に大多数含まれる種類のデータをクエリに使用します。小数の種類のデータは NULL 値とみなされます。
• クエリ - Google Visualization API のクエリ言語で記述された、実行対象のクエリです。
  • クエリの値は二重引用符で囲むか、適切なテキストを含むセルへの参照にする必要があります。
  • クエリ言語について詳しくは、https://developers.google.com/chart/interactive/docs/querylanguage をご覧ください。
• 見出し - [省略可] - データの上部にある見出し行の数です。省略した場合や -1 と指定した場合は、データの内容に基づいて推測されます。

　
要するにIMPORTRANGEで他のスプレッドシートからデータを引っ張ってきてQUERY関数のselectで表示するセルを選択しwhereで表示させる条件をつけてあげればOKな訳です。

例

次のようなシートを作成しました。

1. IMPORTRANGE関数でシートを読み込む

spreadsheet_keyはurlに表示されている次の部分です。

複数シートがある時はシートのタブに表示されている名前!でシートの指定をします。
今回は「シート1」となっているのでシート1!とします。

読み込ませたいスプレッドシートに次のような式を記述します。

= importrange("spreadsheet_key", "シート1!A1:G")

データを取ってきただけなので、次ように表示されました。どうやらセルの連結は解除されてしまうようです。

2. QUERY関数で表示させるセルを絞り込む

名前と出生・死去の欄だけを表示させたいと思います。
QUERY関数のselect節を使います。名前は1番目のセル、出生は5番目、死去は6番目のセルです、セル名はそれぞれCol1, Col5, Col6となります。先ほどの式を下記のように修正します。

= query(importrange("spreadsheet_key", "シート1!A1:G"), "select Col1, Col5, Col6")

次のように指定したセルが表示されるようになりました。

3. 空白の行を除いて、1900年より前に産まれた人だけを表示させる

QUERY関数のWHERE節を使い表示させる条件を加えます。

= query(importrange("spreadsheet_key", "シート1!A1:G"), "select Col1, Col5, Col6 where Col1 != '' AND YEAR(Col5) < 1900")

空行が除去されCol5の出生の年が1900年より小さいデータだけが表示されるようになります。 ※日付の月をWHERE節で使う時はMONTH(Col5)の様な形で取得できますが、1月が0、12月が11というように取得されるので注意が必要です

4. 条件を変数にする

○○年より前の部分をセルに記入した値で変更できるようにします。
QUERY関数の文字列内で、セルの入力値を使うには"& セル名 &"のような形で指定すればOKです。

式を修正します。

= query(importrange("spreadsheet_key", "シート1!A1:G"), "select Col1, Col5, Col6 where Col1 != '' AND YEAR(Col5) < "& A1 &"")

これでA1セルに記入した年数より前の年に表示を変更することができるようになりました。

　

感想

Google Apps Scriptを書かなくてもここまで色々できるのには驚きました。
QUERY関数に変数を使うのは&が無いと上手く動作しませんでした。ここは少しハマりどころなので忘れないようにメモを残しておきます。 　

---

[参考]

• spreadsheet - Google Sheets: QUERY using cell contents as SQL variables - Stack Overflow

ヨーロッパの解放×ガールズ&パンツァー コラボレーションHDリマスターDVDパック(5枚組)

『ガールズ&パンツァー』劇場版 オリジナルサウンドトラック