スプレッドシートQuery関数学習シリーズ⑤別シートのデータ活用

読んで欲しい人
Query関数（クエリ関数）シリーズ予定　（あくまで予定）
Query関数：データを取り扱っていこう
importrangeを使って他のSSからデータを持ってこよう
1. importrangeで取得したデータにクエリを作成しよう
  1. Colを使うときの注意点
複数のデータを結合してみよう
1. 結合：基本は{ ; }でくくる
2. クエリを書くときの留意点
まとめ
1. 【レクチャー関係紹介】
  1. MENTA
  2. 連載目次： spreadsheet_query関数を使いこなそう

読んで欲しい人

SQLに興味があるが、一から環境やデータ整備するのがめんどくさい人
vlookupとかでデータ抽出しているが、限界を感じている人
データ分析したい人
ノンプログラマーでもクエリぐらいは使いたいって人
クエリって美味しいの？って人（クエリは美味しいですよって意味も込めて）

Query関数（クエリ関数）シリーズ予定　（あくまで予定）

概要と目的
クエリ関数の構成
基本の基本　SELECT句（全部取得、特定カラムだけ取得）
抽出の基本　WHERE句を使ってみる(数値一致、大小、日付一致、大小etc）
GROUP　BY句を使ってみる
他のシートからデータを持ってきてみよう〜 ImportRange関数との組み合わせ〜←今回
並び替えの基本　ORDER BY句
SQLやるなら必須　サブクエリをやってみる
データに改行が入ってるぞ　likeで対応できない時はcontains/starts withが助けてくれる

Query関数：データを取り扱っていこう

Query関数の基本を覚えていますでしょうか？以下でしたよね。

QUERY関数の構文

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

データ	クエリを実行するデータ範囲（別シートも可能）
クエリ	Google Visualization APIで指定されたクエリ。ここが基本勉強範囲
見出し	省略可能。[データ]の上部にある見出し行の番号。指定しない場合や-1と指定した場合は、[データ]の内容に基づいて推測される。

忘れたしまった方は第2回を見直してみてください。

これまでは主にクエリ部分をみてきました。WHEREにしても、GroupByにしても全てクエリ部分でしたね。今回はデータの部分についてみていく形になります。

今までは、単純に同一シートや同一SS内の別シートからデータを持ってきていましたが、正直別シートに別れてるケースの方が多くないですか？それか、SSと別のSSを組み合わせて使いたいとか。

SQLに慣れた方なら、それならjoin(inner join/outer join)だよねってなりますよね。

ただ、Query関数はjoin関数がサポートされていません。できることかなり少なくなるじゃないか。。。使えないな。。。

と、なってしまう部分もあるのですが、query関数でサポートされている簡易な結合方法についても紹介していきます。

importrangeを使って他のSSからデータを持ってこよう

データの取り込み方は非常にシンプルで、データ部分にimportrange関数を入れるだけです。

実際に関数にすると以下のような形になります。赤マーカ部分が今までですと、シートや同一スプレッドシート内のデータでしたが、他のシートからとってこれるようになります。

=query(importrange("シートURL","データ範囲"),"select *")

なお、実行後はアクセス権の許可をしないと動きません。これは、importrangeと同様の仕様ですのでご注意ください。

importrangeで取得したデータにクエリを作成しよう

さて、importrangeでデータを取得できたとして、次はクエリがかけなければ単なるimportrange関数を使ったのと変わりません。

クエリは何か変わるのでしょうか？

いつもみたく下記のようにクエリを組んでみましょう。

=query(importrange("シートURL","データ範囲"),"select * where H is not null")

あれっ？エラーになってませんか？

エラー理由をみていくと、NO_COLUMN: Hとなっていますね。クエリでis not nullで指定したカラムですね。

そうなんです、同一シート内でデータ参照した場合と比べて、importrangeの場合、クエリで指定するカラムの表記方法が異なってきます。

では、何なら動くのか？以下の記法を試してみてください。

=query(importrange("シートURL","データ範囲"),"select * where Col8 is not null")

はい、結論からいうとColを使うことになります。

理由としては、シンプルでimportrangeで持ってくるとどこのカラムかってのはデータ上わからないですよね。別にインポートレンジで取得するデータがC列からE列で、関数自体はA列に貼ったらどこのデータがカラム何と言われても困ってしまいますよね？参照元か取込後のどちらをベースに話をしたらいいのかわからないのです。

Colを使うときの注意点

唯一の注意点は、１つ目のカラムのデータはCol１から始まるという点です。０から始まらないのでご注意ください。

データ1カラム目：Col1
データ2カラム目：Col2
データ3カラム目：Col3
以下元データカラムが１つづつcountupされていきます。

複数のデータを結合してみよう

さて、importrangeが使えるようになったとして、まだ１つのデータから検索してるのが実情です。

やはり複数のデータを取り扱いたいですよね、そのための手法を紹介していきます。これもデータ領域をどう扱うかになってきます。

結合：基本は{ ; }でくくる

基本的には２つのデータを統合して処理する場合、{}（波かっこ）で括って複数のデータを取り扱っていくことが可能です。

同一シート内から複数のデータを結合する

=query({A1:D5; A10:D14}, "select *")

シンプルにデータ範囲を;(セミコロン）でくっつけていけば対応可能です。

シート内と同一スプレッドシート内の別シートから複数データを結合する

=query({A1:D5; query(データ範囲)}, "select *")

別シートからデータをひっぱてくる際は、query関数化してひっぱてきてください。通常のシート指定はエラーになるはずです。なお、この方法の方がクエリ関数であるため、クエリも設定可能です。

importrangeを使って別シートのデータ＋スプレッドシート内の別シートデータ

=query({importrange("スプレッドシートURL","シート1!A1:H5");query(sample!A1:H5)},"select *")

上の複合技ですね。

クエリを書くときの留意点

結合したデータはimportrangeで取得してきたデータと同様、クエリを書く際にカラム（A・B・Hみたいなカラム名直接）ではエラーを起こします。

指定する方法は、importrangeの項で説明したColを利用していく必要があります。最初は出来上がりのデータがわからない時もあるかと思うので、指定をせずに”select *”で全表示させてから、カラムのNumberを数えてCol指定していくのが楽かと思います。

まとめ

データの結合や結合した場合のColでのカラム指定などをみてきました。

データの結合はよく使う方法ですし、importrangeを使った他シートからのデータ参照や結合はそれ以上の頻度で使います。

ぜひ慣れておくといい部分かと思います。

【レクチャー関係紹介】

MENTA

メンターを探すサイトも存在します。あまりGASやQuery関数を教えますってメンターはそこまで多くはないのが実情です。ノンプロエンジニア向けではなく、エンジニア向けor エンジニア志望者向けなので、Query関数はあまり対象にならないかもしれません。他方で純粋SQLを教えてほしいというリクエストであれば、多くのメンターが集まると思います。

ちなみに、私のレクチャーページも紹介しておきます。

【Query関数】　相談PLAN