【GAS入門】QUERYコード一覧！スプレッドシート

<スポンサーリンク>

グーグルの公式の説明では分かりにくい、QUERYのコード一覧を
説明していきます。

Query Language Reference (Version 0.7) | Charts | Google for Developers

Learn how to use this language and discover detailed documentation for its classes, functions, and element.

select

特定の列だけ抽出したい場合に使用します。

C列	D列	E列	F列	G列	H列	I列
name	dept	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1000	2005-03-19	35	TRUE
Dave	Eng	12:00:00	500	2006-04-19	27	FALSE
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE
Dana	Sales	12:00:00	350	2004-09-08	25	FALSE
Mike	Marketing	13:00:00	800	2005-01-10	24	TRUE

=QUERY($C$4:$J$10,"select D,F",-1)
↓D列、F列を抽出

dept	salary
Eng	1000
Eng	500
Eng	600
Sales	400
Sales	350
Marketing	800

select avg(),count(),max(),min(),sum()

特定の列の集計を行いたい場合に使用します。

C列	D列	E列	F列	G列	H列	I列
name	dept	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1000	2005-03-19	35	TRUE
Dave	Eng	12:00:00	500	2006-04-19	27	FALSE
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE
Dana	Sales	12:00:00	350	2004-09-08	25	FALSE
Mike	Marketing	13:00:00	800	2005-01-10	24	TRUE

=QUERY($C$4:$J$10,"select min(F)",1)
↓F列の最低値を抽出

min salary

350

使える条件は、

名前	説明
avg()	グループの列のすべての値の平均値を返します。
count()	グループの指定された列の要素数を返します。ヌルセルはカウントされません。
max()	グループの列の最大値を返します。日付は古いものと比較され、文字列は大文字と小文字を区別してアルファベット順に比較されます。
min()	グループの列の最小値を返します。日付は古いものと比較され、文字列は大文字と小文字を区別してアルファベット順に比較されます
sum()	グループの列のすべての値の合計を返します。

where

特定の条件でデータを抽出したい場合に使用します。

C列	D列	E列	F列	G列	H列	I列
name	dept	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1000	2005-03-19	35	TRUE
Dave	Eng	12:00:00	500	2006-04-19	27	FALSE
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE
Dana	Sales	12:00:00	350	2004-09-08	25	FALSE
Mike	Marketing	13:00:00	800	2005-01-10	24	TRUE

=QUERY($C$4:$J$10,"where F>=600",-1)
↓F列(給料)が600以上のデータを抽出します。

name	dept	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1000	2005-03-19	35	TRUE
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Mike	Marketing	13:00:00	800	2005-01-10	24	TRUE

使える条件式は、下記の通りとなっています。

数式
<=	以下
<	未満
>	より大きい
>=	以上
=	等しい
!=	等しくない

また、テキストも検索可能です。
テキストを使う時は、検索したい文字を「'(シングルクオーテーション)」で囲ってください。

NAME	説明	例
contains	部分文字列の一致	例： where A contains 'John'
starts with	頭文字の一致	例： where dept starts with 'engineer'
ends with	末尾後の一致	例： where role ends with 'y'
matches	全一致【.*】でワイルドカード	例： where country matches 'india'
like	ワイルドカード％(文字数関係なし) _(文字数分)	例： where name like fre%

and検索も可能

検索はand検索も可能です。

C列	D列	E列	F列	G列	H列	I列
name	dept	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1000	2005-03-19	35	TRUE
Dave	Eng	12:00:00	500	2006-04-19	27	FALSE
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE
Dana	Sales	12:00:00	350	2004-09-08	25	FALSE
Mike	Marketing	13:00:00	800	2005-01-10	24	TRUE

=QUERY($C$3:$J$9,"where D != 'Eng' and date '2005-01-09' > G",-1)
↓D列(職種)がEngでなく、かつ2005年1月9日以降に雇った人を抽出

name	dept	lunchTime	salary	hireDate	age	isSenior
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE
Dana	Sales	12:00:00	350	2004-09-08	25	FALSE

or検索も可能

or検索も可能です。
下記例は、and検索とor検索を組み合わせています。

C列	D列	E列	F列	G列	H列	I列
name	dept	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1000	2005-03-19	35	TRUE
Dave	Eng	12:00:00	500	2006-04-19	27	FALSE
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE
Dana	Sales	12:00:00	350	2004-09-08	25	FALSE
Mike	Marketing	13:00:00	800	2005-01-10	24	TRUE

=QUERY($C$3:$J$9,"where (D<>'Eng' and I=true) or (D='Sales') or J is null",-1)
↓D列がEngではなく、I列がtrueの場合
　または
　D列がSalesで、かつJ列がnullの場合　を抽出

name	dept	lunchTime	salary	hireDate	age	isSenior
Dave	Eng	12:00:00	500	2006-04-19	27	FALSE
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE
Dana	Sales	12:00:00	350	2004-09-08	25	FALSE
Mike	Marketing	13:00:00	800	2005-01-10	24	TRUE

group by　と　pivot

集計のために使用する関数になります。

そのため、selectの中に、集計文(sum(),avg()などが必要です。)

集計条件は、2パターン設定でき、
・gropu byは集計結果を行方向に出力。
・pivotは集計結果を列方向に出力します。

group by　の例

下記例では、所属ごとに、収入の合計を計算しています。

C列	D列	E列	F列	G列	H列	I列
name	dept	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1000	2005-03-19	35	TRUE
Dave	Eng	12:00:00	500	2006-04-19	27	FALSE
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE
Dana	Sales	12:00:00	350	2004-09-08	25	FALSE
Mike	Marketing	13:00:00	800	2005-01-10	24	TRUE

=QUERY($C$3:$J$9,"select D, sum(F) group by D",-1)
↓
・表題としてD列(所属)を表示
・sum(F)で収入の合計を表示
・集計条件は、gropu by Dで所属ごとで設定。

dept	sum salary
Eng	2100
Marketing	800
Sales	750

group byの例2

gropu byの条件を二つ並べると、
・○○かつ△△の人は、何人。
のように検索をかけることができます。

C列	D列	E列	F列	G列	H列	I列
name	dept	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1000	2005-03-19	35	TRUE
Dave	Eng	12:00:00	500	2006-04-19	27	FALSE
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE
Dana	Sales	12:00:00	350	2004-09-08	25	FALSE
Mike	Marketing	13:00:00	800	2005-01-10	24	TRUE

=QUERY($C$4:$J$10,"select I,E, avg(F), count(H) group by I,E",-1)
↓
・表題として、I,E列(シニア判定、ランチタイムの時間)と表示
・収入の平均と、人数を集計。
・集計条件は、シニア判定と、ランチタイムの時間

isSenior	lunchTime	avg salary	count age
FALSE	12:00:00	425	2
FALSE	13:00:00	600	1
TRUE	12:00:00	700	2
TRUE	13:00:00	800	1

PIVOTの使い方

PIVOTはgroup byと似ていますが、
列方向に集計結果を展開します。

C列	D列	E列	F列	G列	H列	I列
name	dept	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1000	2005-03-19	35	TRUE
Dave	Eng	12:00:00	500	2006-04-19	27	FALSE
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE
Dana	Sales	12:00:00	350	2004-09-08	25	FALSE
Mike	Marketing	13:00:00	800	2005-01-10	24	TRUE

=QUERY($C$3:$J$9,"select sum(F) pivot D",-1)
↓
・収入の合計を集計
・集計条件は、D列(部署)で列方向に展開

Eng	Marketing	Sales
2100	800	750

group by と pivot の複合技

group byとpivotを組み合わせると、
2つの条件の集計結果を見栄えよく表示させることができます。

C列	D列	E列	F列	G列	H列	I列
name	dept	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1000	2005-03-19	35	TRUE
Dave	Eng	12:00:00	500	2006-04-19	27	FALSE
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE
Dana	Sales	12:00:00	350	2004-09-08	25	FALSE
Mike	Marketing	13:00:00	800	2005-01-10	24	TRUE

=QUERY($C$3:$J$9,"select E, sum(F) group by E pivot D",1)
↓
・表題として、E列(ランチタイム)を設定。
・収入の合計を集計。
・集計条件は、「E列(ランチタイムの時間ごと)」かつ「D列(所属ごと)」

lunchTime	Eng	Marketing	Sales
12:00:00	1500		750
13:00:00	600	800

group byと pivot の注意点

pivot,group byは条件が一致しないと表示されませんので、ご注意ください。

基本的に、

・集計したい条件①＝〇
・集計したい条件②＝◇
・集計対象を設定する。▲

として、

select 〇,sum(▲) group by 〇 pivot ◇

の形で使うとわかりやすいと思います。
sumの部分は、avg,minなどなどを変更してお使いください。

order by (desc) or (asc)

出力を昇順、降順で並び替える場合に使います。

C列	D列	E列	F列	G列	H列	I列
name	dept	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1000	2005-03-19	35	TRUE
Dave	Eng	12:00:00	500	2006-04-19	27	FALSE
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE
Dana	Sales	12:00:00	350	2004-09-08	25	FALSE
Mike	Marketing	13:00:00	800	2005-01-10	24	TRUE

=QUERY($C$4:$J$10,"order by D, F desc",1)
・D列→F列の順番で降順に並び替える。

name	dept	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1000	2005-03-19	35	TRUE
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Dave	Eng	12:00:00	500	2006-04-19	27	FALSE
Mike	Marketing	13:00:00	800	2005-01-10	24	TRUE
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE
Dana	Sales	12:00:00	350	2004-09-08	25	FALSE

order by と group by のコラボ　

gropu byなどと組み合わせると集計結果も並び替えることができます。

C列	D列	E列	F列	G列	H列	I列
name	dept	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1000	2005-03-19	35	TRUE
Dave	Eng	12:00:00	500	2006-04-19	27	FALSE
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE
Dana	Sales	12:00:00	350	2004-09-08	25	FALSE
Mike	Marketing	13:00:00	800	2005-01-10	24	TRUE

=QUERY($C$4:$J$10,"select D, max(F) group by D order by max(F)",1)
↓D列を表題にして、
各業種ごとの、F列の最大値を取得、昇順で並べています。

dept	max salary
Sales	400
Marketing	800
Eng	1000

limitで検索結果に表示数に制限をかける

limitを使うと、表示数に制限をかけることができます。

C列	D列	E列	F列	G列	H列	I列
name	dept	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1000	2005-03-19	35	TRUE
Dave	Eng	12:00:00	500	2006-04-19	27	FALSE
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE
Dana	Sales	12:00:00	350	2004-09-08	25	FALSE
Mike	Marketing	13:00:00	800	2005-01-10	24	TRUE

=QUERY($C$4:$J$10,"limit 2",1)
2行まで表示。

name	dept	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1000	2005-03-19	35	TRUE
Dave	Eng	12:00:00	500	2006-04-19	27	FALSE

offset で行をスキップする。

これはあまり使うタイミングが分からないのですが、
行をスキップすることができます。

limitと組み合わせると間をとることも可能です。

C列	D列	E列	F列	G列	H列	I列
name	dept	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1000	2005-03-19	35	TRUE
Dave	Eng	12:00:00	500	2006-04-19	27	FALSE
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE
Dana	Sales	12:00:00	350	2004-09-08	25	FALSE
Mike	Marketing	13:00:00	800	2005-01-10	24	TRUE

=QUERY($C$4:$J$10,"limit 2 offset 2 ",1)
↓2行目から、2行分表示。

name	dept	lunchTime	salary	hireDate	age	isSenior
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE

見出しの変更　label

labelを使って見出しの変更ができます。
グラフなどを作るときには便利な機能となっています。

C列	D列	E列	F列	G列	H列	I列
name	dept	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1000	2005-03-19	35	TRUE
Dave	Eng	12:00:00	500	2006-04-19	27	FALSE
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE
Dana	Sales	12:00:00	350	2004-09-08	25	FALSE
Mike	Marketing	13:00:00	800	2005-01-10	24	TRUE

=QUERY($C$4:$J$10,"label D 'Department', C 'Employee Name'",1)
D列のラベルの'Department'、C列のラベルを'Employee Name'に変更

Employee Name	Department	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1000	2005-03-19	35	TRUE
Dave	Eng	12:00:00	500	2006-04-19	27	FALSE
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE
Dana	Sales	12:00:00	350	2004-09-08	25	FALSE
Mike	Marketing	13:00:00	800	2005-01-10	24	TRUE

フォーマットを変更

データベースに登録できるフォーマットの形は決まっていますので、
出力結果を変更したい場合は、フォーマット関数を使用する必要があります。

C列	D列	E列	F列	G列	H列	I列
name	dept	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1000	2005-03-19	35	TRUE
Dave	Eng	12:00:00	500	2006-04-19	27	FALSE
Sally	Eng	13:00:00	600	2005-10-10	30	FALSE
Ben	Sales	12:00:00	400	2002-10-10	32	TRUE
Dana	Sales	12:00:00	350	2004-09-08	25	FALSE
Mike	Marketing	13:00:00	800	2005-01-10	24	TRUE

=QUERY($C$4:$J$10,"format F '#,##0.00', G 'dd-MMM-yyyy', I 'Yes!:Not yet'",1)

name	dept	lunchTime	salary	hireDate	age	isSenior
John	Eng	12:00:00	1,000.00	19-3月-2005	35	TRUE
Dave	Eng	12:00:00	500.00	19-4月-2006	27	FALSE
Sally	Eng	13:00:00	600.00	10-10月-2005	30	FALSE
Ben	Sales	12:00:00	400.00	10-10月-2002	32	TRUE
Dana	Sales	12:00:00	350.00	08-9月-2004	25	FALSE
Mike	Marketing	13:00:00	800.00	10-1月-2005	24	TRUE

その他使える関数

その他使る関数を以下に示します。

名前
year()	日付または日時値から年の値を返します。たとえば、 year(date "2009-02-05")では2009を返します。
month()	日付または日時の値からゼロから始まる月の値を返します。例： month(date "2009-02-05")では「1」を返します。注：月は0から始まるため、この関数は1月の場合は0、2月の場合は1を返します。
day()	日付または日時値から月の日を返します。例: day(date "2009-02-05")では「5」を返します。
hour()	日時または時刻の値から時間の値を返します。例： hour(timeofday "12:03:17")では「12」を返します。
minute()	日時または時刻の値から分の値を返します。例： minute(timeofday "12:03:17")では「3」を返します。
second()	日時値または時刻値から秒の値を返します。例： second(timeofday "12:03:17")では「17」を返します。
millisecond()	日時または時刻の値のミリ秒の部分を返します。例： millisecond(timeofday "12:03:17.123")では123を返します。
quarter()	日付または日時値から四半期を返します。例： quarter(date "2009-02-05")1を返します。四半期は1から始まるため、この関数は最初の四半期には1を、 2番目の四半期には2を返します。
dayOfWeek()	日付または日時値から曜日を返します。例： dayOfWeek(date "2009-02-26")5を返します。日は1から始まるため、関数は日曜日の場合は1、月曜日の場合は2を返します。
now()	GMTタイムゾーンの現在の日時を表す日時値を返します。
dateDiff()	2つの日付値または日時値の間の日数の差を返します。注：値の【日付部分のみ】が計算に使用されるため、関数は常に整数値を返します。例： dateDiff(date "2008-03-13" date "2008-02-12")29を返します。　　dateDiff(date "2009-02-13" date "2009-03-13")-29を返します。　　時間の値は比較前に切り捨てられます。
toDate()	指定された値を日付値に変換します。日付を指定すると、同じ値を返します。日時を指定すると、日付部分を返します。例： toDate(dateTime "2009-01-01 12:00:00") 「2009-01-01」を返します。
upper()	指定された文字列を大文字で返します。たとえば、 upper("foo")では「FOO」を返します。
lower()	指定された文字列を小文字で返します。たとえば、 lower("Bar")では「bar」を返します。