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

GAS
PexelsによるPixabayからの画像
この記事は約18分で読めます。
スポンサーリンク
スポンサーリンク

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

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

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

select

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

C列D列E列F列G列H列I列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

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

deptsalary
Eng1000
Eng500
Eng600
Sales400
Sales350
Marketing800

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

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

C列D列E列F列G列H列I列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

=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列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

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

namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
SallyEng13:00:006002005-10-1030FALSE
MikeMarketing13:00:008002005-01-1024TRUE

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

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

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

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列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

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

namedeptlunchTimesalaryhireDateageisSenior
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE

or検索も可能

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

C列D列E列F列G列H列I列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

=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の場合 を抽出

namedeptlunchTimesalaryhireDateageisSenior
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

group by と pivot

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

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

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

group by の例

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

C列D列E列F列G列H列I列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

=QUERY($C$3:$J$9,”select D, sum(F) group by D”,-1)

・表題としてD列(所属)を表示
・sum(F)で収入の合計を表示
・集計条件は、gropu by Dで所属ごとで設定。

deptsum salary
Eng2100
Marketing800
Sales750

group byの例2

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

C列D列E列F列G列H列I列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

=QUERY($C$4:$J$10,”select I,E, avg(F), count(H) group by I,E”,-1)

・表題として、I,E列(シニア判定、ランチタイムの時間)と表示
・収入の平均と、人数を集計。
・集計条件は、シニア判定と、ランチタイムの時間

isSeniorlunchTimeavg salarycount age
FALSE12:00:004252
FALSE13:00:006001
TRUE12:00:007002
TRUE13:00:008001

PIVOTの使い方

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

C列D列E列F列G列H列I列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

=QUERY($C$3:$J$9,”select sum(F) pivot D”,-1)

・収入の合計を集計
・集計条件は、D列(部署)で列方向に展開

EngMarketingSales
2100800750

group by と pivot の複合技

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

C列D列E列F列G列H列I列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

=QUERY($C$3:$J$9,”select E, sum(F) group by E pivot D”,1)

・表題として、E列(ランチタイム)を設定。
・収入の合計を集計。
・集計条件は、「E列(ランチタイムの時間ごと)」かつ「D列(所属ごと)」

lunchTimeEngMarketingSales
12:00:001500750
13:00:00600800

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列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

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

namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
SallyEng13:00:006002005-10-1030FALSE
DaveEng12:00:005002006-04-1927FALSE
MikeMarketing13:00:008002005-01-1024TRUE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE

order by と group by のコラボ 

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

C列D列E列F列G列H列I列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

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

deptmax salary
Sales400
Marketing800
Eng1000

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

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

C列D列E列F列G列H列I列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

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

namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE

offset で行をスキップする。

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

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

C列D列E列F列G列H列I列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

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

namedeptlunchTimesalaryhireDateageisSenior
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE

見出しの変更 label

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

C列D列E列F列G列H列I列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

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

Employee NameDepartmentlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

フォーマットを変更

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

C列D列E列F列G列H列I列
namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:0010002005-03-1935TRUE
DaveEng12:00:005002006-04-1927FALSE
SallyEng13:00:006002005-10-1030FALSE
BenSales12:00:004002002-10-1032TRUE
DanaSales12:00:003502004-09-0825FALSE
MikeMarketing13:00:008002005-01-1024TRUE

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

namedeptlunchTimesalaryhireDateageisSenior
JohnEng12:00:001,000.0019-3月-200535TRUE
DaveEng12:00:00500.0019-4月-200627FALSE
SallyEng13:00:00600.0010-10月-200530FALSE
BenSales12:00:00400.0010-10月-200232TRUE
DanaSales12:00:00350.0008-9月-200425FALSE
MikeMarketing13:00:00800.0010-1月-200524TRUE

その他使える関数

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

名前
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」を返します。
スポンサーリンク
スポンサーリンク
GAS google spreadsheet
スポンサーリンク
nujonoaをフォローする
nujonoa_blog

コメント欄

タイトルとURLをコピーしました