GASをセットするのはめんどくさいので、ほんとに簡単なものなら関数ですませたい。
やりたいこと
別のGoogle Spread SheetファイルからSheetを転記して特定のデータを抽出したい!!
この記事の一番大事なポイント
▶ QUERYとIMPORTRANGEを組み合わせて使う場合は、列がexampleで紹介されてるA等の様な指定ではなくてCol[列番号]に代わる。
IMPORTRANGE
指定したスプレッドシートからセルの範囲を読み込みます。
IMPORTRANGEを使うことで、別のGoogle Spread SheetファイルからSheetを読み込むことができる。
使用例
注意:例で示してる[]は置換の印です。[]含んで置き換えてください。
IMPORTRANGE("[読み込むSheetファイルのURL]", "[シート名]![開始セル]:[終了セル]")
例えばこう使う
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "シート1!A1:C10")
その他、開始セルと終了セルはシート1!A:Xみたいに列のみの指定もできる。
QUERY
Google Visualization API のクエリ言語を使用して、データ全体に対するクエリを実行します。
つまり、SQLライクなGoogle Visualization API のクエリ言語とやらを使って欲しい行列を指定したり抽出したりできる。
使用例(IMPORTRANGEと組み合わせて)
selectで抽出する列を指定する。
とりあえず全部表示するには
QUERY(IMPORTRANGE("[読み込むSheetファイルのURL]", "[シート名]![開始セル]:[終了セル]"),"select * ")
select Col1, Col2等とすることで列を指定して抽出できるが、自分は、*で全部抽出して非表示にした方が楽で好き。
whereで抽出する行の条件を指定する。
例えば、A列が1の行のみを抽出する
SQLライクな言語なので等価が=一つなのはだいぶ感覚とズレる。そして、IMPORTRANGEで読み込んだシートに対して列を指定するときはA、BはCol1、Col2等Col[列番号]に置き換えて指定しなければいけない。
QUERY(IMPORTRANGE("[読み込むSheetファイルのURL]", "[シート名]![開始セル]:[終了セル]"),"select * where Col1 = '○' ")
特定の列の値が文字列の場合等
文字列のデータは完全等価なものは少ないので、matchかlike %[文字列]%を使うときが多い
QUERY(IMPORTRANGE("[読み込むSheetファイルのURL]", "[シート名]![開始セル]:[終了セル]"),"select * where Col1 like '%完了%' ")
文字列検索の色々
公式ドキュメントを適当に日本語訳したのを置いとく
contains - 部分文字列のマッチです。 whole contains partが全体の中のどこかに含まれている場合はtrueになります。例：name が 'John' を含む場合、'John'、'John Adams'、'Long John Silver' にマッチしますが、'john adams' にはマッチしません。
starts with文字列が値の先頭にある場合に真です。例:where dept start with engineeringは 'engineering' と 'engineering managers' にマッチします。
end with文字列が値の最後にある場合は真です。例:where role ends withy`は'cowboy'、'boy'、および 'y' にマッチします。
matches- 正規表現を使う。 例:where country matches '.*ia'はIndiaとNigeriaにマッチします。 これはグローバルな検索ではないので、where country matches 'an'は 'Canada' にはマッチしないことに注意してください。
like- 2つのワイルドカードをサポートするテキスト検索です: % (0文字以上の任意の文字にマッチします) と _ (アンダースコア) です。これは SQL の LIKE 演算子に似ています。例:where name like fre%はfre、fred、freddyにマッチします。
order byで列を指定してソートする。
注意 -> selectとwhereはセットなので、order byはwhereの後ろに書く
QUERY(IMPORTRANGE("[読み込むSheetファイルのURL]", "[シート名]![開始セル]:[終了セル]"),"select * where Col1 = '○' order by Col2 asc ")