読んで欲しい人
- 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を使うときの注意点
唯一の注意点は、1つ目のカラムのデータはCol1から始まるという点です。0から始まらないのでご注意ください。
データ1カラム目:Col1
データ2カラム目:Col2
データ3カラム目:Col3
以下元データカラムが1つづつcountupされていきます。
複数のデータを結合してみよう
さて、importrangeが使えるようになったとして、まだ1つのデータから検索してるのが実情です。
やはり複数のデータを取り扱いたいですよね、そのための手法を紹介していきます。これもデータ領域をどう扱うかになってきます。
結合:基本は{ ; }でくくる
基本的には2つのデータを統合して処理する場合、{}(波かっこ)で括って複数のデータを取り扱っていくことが可能です。
同一シート内から複数のデータを結合する
=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連載目次: spreadsheet_query関数を使いこなそう
SQL勉強もかねて、spreadsheetのquery関数の使い方に慣れていきます。簡単なselect文の使い方から、SQLとの違いも含めて勉強していきます。
- スプレッドシートQuery関数を使ってみよう ①概要と目的
- スプレッドシートQuery関数を使ってみよう ②クエリ関数の構成
- スプレッドシートQuery関数を使ってみよう ③SELECT句
- スプレッドシートQuery関数を使ってみよう ④WHERET句
- スプレッドシートQuery関数を使ってみよう ⑤GROUP BYを使ってみる
- スプレッドシートQuery関数を使ってみよう ⑥他のシートからデータを持ってきてみよう〜 ImportRange関数との組み合わせ〜
- スプレッドシートQuery関数を使ってみよう ⑦並び替えの基本 ORDER BY句
- スプレッドシートQuery関数を使ってみよう ⑧SQLやるなら必須 Query関数でもサブクエリをやってみる
- スプレッドシートQuery関数を使ってみよう ⑨データに改行が入ってるぞ etc クエリ関数エラー対策
- スプレッドシートQuery関数番外編 〜Query関数のエラー原因ランキング〜