Query関数を使ってデータを集計しよう - 其の弐
Googleスプレッドシートには、Excelにはない特別な関数がいくつかあります。それらはスクリプト無しでまた、特別な機能を使わずにデータの塊の中から、色々な条件でデータを塊で取り出すことが出来ます。Excelもいよいよ2019よりこの配列関数をスピルという機能としてサポートするようで、Filter関数、Unique関数、Sort関数などが導入されることになりました。
前回の記事で、Query関数の最もよく使う事例についてまとめました。今回はそこでは扱いきれなかった部分についてまとめてみたいと思います。利用頻度は高くないと思いますが、スプレッドシート上でデータベース的な扱いのできる機能ですので、ケースによっては非常に便利じゃないかなと思います。
※正直これだけ多彩なことができる関数は多分他にはないと思います。
今回使用するスプレッドシートF
※内部でUrlfetchAppを使っている関数があるため、一度スクリプトエディタで実行させて承認をしてください。
※その１は以下のエントリーになります。
データの結合をさせる
Accessなどのデータベースがとても便利といえるのは、テーブル同士を結合してクエリを作り、新たなテーブルとして利用することが出来る点にあります。最近はExcelでもPowerQueryなどのツール（2016から標準搭載）があり、また範囲をテーブル化出来るようになったこともあって、特にクエリ機能については習得すべき大きな機能になっています。
２つのシートを単純に結合する
縦に結合する
単純に同じ形式のデータの塊を１つのデータの塊に結合する方法です。クエリの最も基本的なものですね。しかし、数式は少し特殊です。Accessで言うところのユニオンクエリ的なものです。
- なお、範囲の指定は{}を使ってくくり、範囲同士はセミコロン（ ; ）で繋げるというルールがあります。
- また範囲の結合時にはカラムの指定は、1個目のカラムならば、Col1、2個目ならばCol2といった表記を使います。
- そこで、条件で例えば空は除く場合には、Where Col1 is not nullと指定します。
- ただし、where条件で指定のColが文字列の場合には、Where Col1 != ""でも抜き出せます。
- この単純な結合は、Query関数を使わなくても可能です。
- select文を省略していますが、使用することも可能です
|
1 2 3 4 5 |
//QUERY関数の単純な範囲の結合 =query({A2:D10;F2:I10},"where Col1 is not null") //QUERY関数を使わなくてもできる ={A2:D10;F2:I10} |
図：２つの範囲を結合してみた
横に連結する
前述のケースは非常によく使うタイプのケースです。対してこちらは表1のデータに表2を横連結させて1枚の表にするもの。後述するAccessで言うところの外部結合というのとは違い、単純に本当に列で連結するだけなので、表の間の関連性が無い。よって、表1と表2は同じ行数でそれぞれの行に関連する位置に表2側はデータが無いと意味不明な連結になってしまうので注意。
- なお、範囲の指定は{}を使ってくくり、範囲同士はカンマ（ , ）で繋げるというルールがあります。
- また範囲の結合時にはカラムの指定は、1個目のカラムならば、Col1、2個目ならばCol2といった表記を使います。
- この単純な結合は、Query関数を使わなくても可能です。
- select文を省略していますが、使用することも可能です
- 双方の表の行数は揃える必要があります。
双方の表で結合する基準となる列をもって連結する場合は後述の疑似外部結合を参照してください。
図：本当に列をガッチャンコするだけ
他のスプレッドシートの範囲を結合する
同じファイル内であれば、前述の数式で結合が可能です。しかし、他のシートとなると単純に結合はできません。しかし、Googleスプレッドシートにはimportrangeという優れた関数で呼び出す事が可能です。importrange関数自体は、=importrange("シートのID","範囲指定")で可能です。
このimportrangeをQuery関数内で使用して結合をします。以下のような構文になります。
|
1 2 |
//importrangeとの合わせ技 =query({importrange("1pmdZK-SSE5xWQT_V3Ue4RpihfrtTttImmClEbt_mCp4","table1!A2:D");importrange("1pmdZK-SSE5xWQT_V3Ue4RpihfrtTttImmClEbt_mCp4","table2!A2:D")},"Where Col4 >= 500") |
今回は、Where条件としてCol4が500以上、つまり金額が500以上のレコードを抽出して結合しています。
また、importrangeではなく、Query文同士で結合も可能になっています。手法的にはQuery文の中でQuery文を使う構文になります。
図：複雑だけれど、すごく便利です
疑似外部結合を実現する
Query関数には残念ながら、INNER JOINやLEFT JOINのような結合に関するオプション項目がありません。Visualization APIにはJOINメソッドがあり、キー同士で結合の出来るメソッドがあるのですが・・・必ずしも、同じものではないようです。しかし、データベースではこの結合方法はよく使うテクニックで、これが出来ると利用幅が広がるのに。Google SpreadsheetにもPower Queryのようなツールが欲しいところですね。
さて、これを実現させる方法として、VLOOKUPとARRAYFORMULA関数を組み合わせて実現する方法を使ってみました。
|
1 2 |
//擬似的に２つのテーブルをリンクする形で結合 =query({D3:E12,iferror(arrayformula(vlookup(F3:F12,A3:B6,2,false)))}) |
Accessではよく使うテクニックですが、こうする事で２つのテーブルの「キー」を元にリンクして１つのテーブルを作る事が可能です。解説すると・・・
- メインのマスタからは外部キー以外の部分を選択しておく（D3:E12の部分）
- iferrorでエラーがでたら何も表示しないようにしておく（外部キーに存在しないキーがある場合の対処）
- arrayformulaにてvlookup関数の結果を配列で返すようにする
- vlookup関数では１つの値ではなく、範囲で値を指定（外部キー）。ターゲットの範囲はその外部キーを持つ別のマスタを参照。
- 絶対値で参照させている
複雑な構文なので多用できないのと、Arrayformula関数自体が難しい概念の関数なので、利用する時は注意書きを添えておくと良いでしょう。
また、arrayformulaを使っている為、レコードの複数列を一度に取得して返すことも可能です。通常vlookupは1列分しか返せませんが、上記の式であれば、以下のように書き直す事で、2列目と3列目を返せます
|
1 2 |
//擬似的に２つのテーブルをリンクする形で結合 =query({D3:E12,iferror(arrayformula(vlookup(F3:F12,A3:B6,{2,3},false)))}) |
列指定の部分を、{2,3}と指定する事で、2列目と3列目を一度に取得する事が可能です。これで、疑似外部結合ができました。
図：２つの異なるテーブルをキーで結合して１つの表に
XLOOKUPで都合良く結合させる
前述の疑似外部結合の方法は１つ弱点があり、vlookupを使ってるが為に検索するタイプIDの列はタイプマスタの1列目に無いと駄目という制約があります。となると、タイプIDより左側は引っ張ってこれません。しかし、単純にxlookupに置き換えてもarrayformulaではxlookupは使えません（どちらも配列関数であるため）。
マスタ1の3列目の値とマスタ2の3列目を連結して、マスタ1の全部の列とマスタ2の都合の良い列を引っ張る為には、xlookupだけじゃなくmap関数とLAMBDA関数を使うことで実現可能です。ベースにしたのはこちらの記事。
|
1 2 |
//アドレス列を基準に2つの範囲を結合 =query({A2:E6,map(C2:C6,LAMBDA(xx, XLOOKUP(xx,C10:C14,{B10:B14,D10:D14})))}) |
- A2:E6は1つ目のマスタ全部の領域を指定（これが結合のベースになる）
- map関数を使って1つ目マスタのC列の値を元にしつつ、xlookup関数で2つ目マスタのC列を検索する
- xlookupはLAMBDA関数のパラメータxxを基準に2.の結果として2つ目マスタのB列とD列を返すように構築
- LAMBDA関数のxx部分は、mapのc2:c6の値を一個ずつ取り出して入ってきます。それがxlookup側のxxにも入ってくる仕組みです）
- query関数で1.と3.を単純結合する
といった仕組みです。mapとlambdaという２つが一番の理解するのが難しい関数です。mapの部分だけを取り出すと2つ目マスタのB,D列が見事に抽出出来てるので、これとA2:E6を単純結合させてるわけです。xlookupを使ってるので、検索列の左側も持ってこれるのが特徴。
2つ目マスタのレコード順番を動かしても結果はきちんと出てきます。主な使い所は、同じキーとなる列を持つ全く別種のマスターシート同士（社員マスタとPC管理マスタ、双方メアドを持ってる）といったような場合で、メアド列が先頭に無いようなデータをがっちり連結して１つにしたいといったようなケースがこれに該当します。
図：マスタ1全部とマスタ2のB,D列とをアドレスを基準に結合
完全外部結合風の表を作る
2つの類似の表でそれぞれに結合するための基準となる列（例えばID）があった場合、SQLで言うところのFULL OUTER JOINな結合をした表を作りたい場合があります。この場合2つの表を結合する場合、それぞれの表のIDは数や表1、表2それぞれにあるIDの値が一致しない場合があります。そうであっても双方のIDのユニークな値を表示しつつ、そのIDに連なる表１と表２のそれぞれの値を連結させて表示したいのが今回のテクニック。
よって、2列目以降は空白のものが出てきますが双方にあるものが完全に表示されるようになります。ただし今回のテクはちょっとトリッキーなので、２つの数式を利用します。
|
1 2 3 4 5 |
//1列目の数式 =sort(unique({A2:A;D2:D})) //2列目の数式 =query({iferror(arrayformula(vlookup(G2:G,A2:B,2,false))),iferror(arrayformula(vlookup(G2:G,D2:E,2,false)))}) |
- 1列目では、2つの範囲のID列を取ってUnique関数でユニークな値を取り出し、sort関数で降順ソートしておきます。2つの範囲は{ }で括った状態で、セミコロンで結合すると取ることが可能です。
- 2列目は1.の結果を利用してquery関数で擬似外部結合と同じようなテクで2列目（表1の2列目を表示）のルックアップ、3列目（表2の2列目を表示）を{ }で横で結合させています。
- すると、ユニークなIDをもとにそれぞれの表の該当する値を表示し、1枚の表として形成することが可能です。
- それぞれの範囲はA列全部やD列全部といったように列の値すべてを範囲として利用するように数式を作ります。
こうすることで、表1と表2のレコードが増えても2つの数式でそれぞれ計算しQuery関数で結合するので、メンテナンスフリーで完全外部結合な表が作成することができます。
図：2枚の表を結合して全部の値を表示できた
特殊な抽出オプション
NULL値を基準にデータを抽出
データが空のもの、そうでないものを抽出する機会が以外と多いです。Accessのクエリではこれを用いて不一致や重複などのクエリを作ったりもしますね。データが空とはつまり「NULL」なので、IS NULLで空のデータ、IS NOT NULLで空ではないデータを抽出することが可能です。Where条件に加えて使用します。
今回は3列目が空のデータを操作してみました。
|
1 2 3 4 5 |
//空ではないデータを抽出 =QUERY(A2:C12,"WHERE C IS NOT NULL", -1) //空のデータを抽出 =QUERY(A2:C12,"WHERE C IS NULL", -1) |
図：空白がつまりNULLです。
正規表現を用いてデータを抽出
Googleの関数系やメソッドは「正規表現」が使えるものが多い印象。その正規表現を用いて合致するデータを抽出するオプションが、Query関数にもあります。その際にWhere条件に使用するのがMATCHESです。正規表現が使えると、ほとんどのデータをこれ一つで処理ができますが、正規表現自体がとっても難しいものなので、無理して使わなくても良いと思います。
よくあるのは、メールアドレスの形式ルールに従ってるかどうかチェックなどなど。。。今回は「ベニ」を含むキノコ名を抽出してみました。
|
1 2 |
//ベニを含むキノコを抽出 =QUERY(A2:C11,"WHERE B matches '^(?=.*ベニ).*$'") |
図：便利だけれど難しくてどうにも苦手・・・
指定文字列を含むデータを抽出
指定文字列を含んでいるものだけを抽出はよく利用します。どの位置であっても良いので、とにかくその文字を含んでるものは全て抽出したいというわりと大雑把な抽出だからこそ、利用シーンは多いですね。
この時に使うのがWhere条件にCONTAINSです。Drive APIのsearchメソッドなどでも見かけましたね。また、逆に「含まない」というケースもありますね。その場合は、NOTで条件をくくればOKです。
|
1 2 3 4 5 |
//ベニを含むデータだけ抽出 =QUERY(A2:C11,"WHERE B CONTAINS 'ベニ'", -1) //ベニを含まないデータだけを抽出 =QUERY(A2:C11,"WHERE NOT(B contains 'ベニ')", -1) |
図：ベニを含むキノコだけを抽出してみた
ワイルドカードを使って抽出その１
前方一致、後方一致、部分一致といった類の抽出方法で、Where条件にLIKEを使い、通常はアスタリスクを使うのですが、Query関数では「%」を使って表現します。キノコの名前のうち「タケ」で終わるものだけを抽出するようにしてみました。
例えば、「ベニ%」とすれば、ベニで始まるキノコとなり、「エ%ギ」とすると、エから始まりギで終わるキノコといった具合に柔軟に抽出が可能となります。
|
1 2 |
//タケで終わるキノコだけ抽出（後方一致） =QUERY(A2:C11, "WHERE B LIKE '%タケ'", -1) |
図：後方一致で見事に抽出できた。
ワイルドカードを使って抽出その２
もうひとつのワイルドカードとしてアンダーバー（_）を使った抽出法があります。これは、アンダーバーの数 = 文字数に合致するものを抽出するというもので、4つ使えば4文字のデータということになります。こちらもその１同様に「エ__ギ」といった指定をすれば、エからはじまり、ギで終わる4文字のキノコという事で抽出が可能です。
同じく、WhereにてLIKEを使います。
|
1 2 |
//4文字のキノコ名を抽出 =QUERY(A2:C11, "WHERE B LIKE '____'", -1) |
図：4文字のキノコを抽出してみた
頭文字が一致するレコードを抽出
ワイルドカードが使えれば、使う機会はないのですが、手っ取り早く、前方一致（頭の文字で一致するもの）を抽出するためのオプションがstarts with句です。whereに続けて列を指定し、starts with '頭文字'といった具合に入れると、頭の文字が一致するレコードを抽出してくれます。
|
1 2 |
//頭文字がドクを含むデータを抽出 =query(A1:C11,"where B starts with 'ドク'") |
図：ドクから始まるきのこを抽出
後ろ文字が一致するレコードを抽出
こちらもあまり使う機会は無いと思いますが、後方一致（後ろの文字が指定の文字で終わるデータ）を抽出するためのオプションがends with句です。where条件に列を指定し、ends withに続けて文字列を入れると、抽出が可能です。ワイルドカードでももちろん代用可能。
|
1 2 |
//後方一致でタケで終わるデータを抽出 =query(A1:C11,"where B ends with 'タケ'") |
図：タケで終わるきのこを抽出してます。
グループ化する
Accessではおなじみのグループ化。Group By句を使って同じ名前のものはまとめて、さらにSUMを使うことで総合計を集計できる。リストのデータの対象の列の項目ごとにグループ化を行うので、複数のグループ化も行うことが可能。Pivotでも非常によく利用する。グループ化する場合は、必ずSUMやCOUNTなどでの集計列が必要になるので、使用時は注意。
また、集計関数を使うので、LABEL句を併用する必要もあります。
|
1 2 |
//支店名でグループ化して売上の総合計を取る =query(A2:F19,"select A,sum(F) group by A LABEL A '支店名', sum(F) '売上合計'") |
図：こんな集計表が関数一発でできる魅力
表示形式を編集する
元のオリジナルの表は、例えば数値に対して「個」であったり、「円」といった単位のついた文字列がない場合、表示形式から装飾が可能ですが、オリジナルデータに対して手を加えたくない場合もあります。そんな時、Query関数で特定の列だけ、一時的に装飾をすることが可能です。Format句を使って表現します。
|
1 2 |
//C列を個、D列を円で表示 =query(A1:D19,"format C '0個', D '0円'") |
ちなみに、この結果作成された表の値をSUM関数などで集計を掛けてやると、集計されるだけでなくきちんと付けた装飾を継承してくれるので、個とか円の表示がなされます。とても便利。他にも、”format C ‘yyyy年mm月dd日'”といった表記をすれば、元の数値から日付形式を生成も可能です。
図：元の表を弄らなくて済むので利点は大きい
トップ10表示をする
Accessでもよくやるトップ10集計クエリ。全部のデータは必要ないけれど、上位10件だけ欲しいといった表記の集計表が必要なケースは多々あります。この表記もQuery関数で一発で可能。この際に利用するのは、limit句となります。ほぼ必ず、ORDER BY句も併用してソートするのが定石です。ORDER BY はDESCで降順、ASCで昇順になります。limitの後の数値を変えればトップ○○は変更可能
|
1 2 |
//売上金額TOP10 =query(A1:C19,"order by C desc limit 10") |
図：TOP10も簡単に作れてしまう。恐ろしい
下位ランキング
前述のlimit句とは異なり、逆に下位○○のランキングも必要なケースはあるかもしれません。ブービーは誰なのか？といったゲームなどでは使う機会がありそうですね。この表記もQuery関数なら一発です。offset句を利用します。limit句同様にORDER BY句を併用するのが定石。但し、limitと異なり、これは上位○○分のレコードを省くという意味なので、指定する数値は、今回の事例だと8を指定すると、10レコード出すことができました（全部で18レコードあるので、8を省くと下位10レコードになるため）。ここが厄介なポイントですね。
|
1 2 |
//下位10位を洗い出す =query(A1:C19,"order by C desc offset 10") |
図：ブービーは新宿支店のモウセンゴケ800円
2列を比較して不一致の値だけ抽出
完全なマスターである列と比較して、もう１列の値を比較し、一致しないデータだけを抽出といった事例に於いてもQuery関数は力を発揮します。今回はA列に一部のデータだけを記載し、B列に完全なデータの列を用意。比較してB列を基準として、B列からA列にいる人を除外した値を抽出します。
|
1 2 |
//B列基準でA,B両方にいるデータは除外する =query(B2:B,"Where not B matches '(" & join("|",A2:A) & ")'") |
下のスクショをみると、
- karon@tomato.comについてはA,B両方にいるので結果から除外されます。
- B列にだけいる人は結果に表示されます。
- A列にだけいる人は結果から除外されます（B列基準である為）
A列のkaron@tomato.comを消してB列に追加すると、結果にkaron@tomato.comが出てくるという仕掛けです。よって、上記の数式も結果はB2:Bで出力しつつ、Where条件ではnot BなのでB列に居ない者という意味になります。
matchesで結合したA列と比較をした結果、上記のような複雑な条件にマッチしたものだけを抽出する事が出来ます。
図：きちんと理解する必要のある比較です
ちょっと変わった高等テクニック
総合計を付け加える
Query関数はこのように大変便利なのですが、その集計結果に手動で毎回SUMなどの数式を入れてる人も多いと思います。しかし、この場合、Query関数の計算結果、配列が広がった場合、このSUMの数式の場所が邪魔して、エラーになってしまいます（上書きされないため）。
そこで、配列数式を利用して、Queryの計算結果に対していわゆる総合計を加える手法があります。主に、QueryでGroup Byでグループ化合計を取ったものに対して、総合計をつけるような事例で利用します。このケースの場合、数式が広がっても、自動的に総合計がついて来てくれます。
|
1 2 |
//Query式に総合計を付けてみる（グループ化合計した表に対して） ={query(A2:F19,"select A,sum(F) group by A LABEL A '支店名', sum(F) '売上合計'");{"総合計",sum(F2:F)}} |
数式に{}がついてるのが特徴。だからといって、arrayformulaでやってみるとエラーになります。また、この時使用する2つ目の式のSUMでQueryの小計を参照して合計を取ろうとすると、循環参照となりエラーになります。なので、参照すべきは元の表です。試しに１つ新しい支店の情報を加えてみると、手動でSUMで合計取った時との大きな違いがわかります。
※WHERE条件などを加えてフィルタしてしまってる場合にはSUMではなくSUMIFSなどで同じようなフィルタ合計を取る数式を加えると良いです。
図：自動総合計を付け加えられる
Pivotの結果の行列入れ替え
あまり使う機会はないと思いますが、pivotを使ったクロス集計結果の行と列を入れ替えたい場合、数式を変えるのではなくTranspose関数を使うことで行列転換が可能です。
|
1 2 |
//クロス集計結果を反転 =transpose(QUERY(A2:F19,"SELECT C,SUM(F) GROUP BY C PIVOT A")) |
図：縦横転換はQuery関数の結果に対しても有効だった
Pivotの縦の結果に総合計を加える
上記の2項目をさらに組み合わせて、pivotの結果に対して総合計を加えるというテクニックを使うことも可能です。かなり複雑なので理解するのに時間が掛かると思いますが、総合計を自動で加えることが可能になるので、非常に便利です。Transpose関数ももちろん利用しますし、Group Byも利用します。。
|
1 2 |
//pivotの結果に対して各列の総合計を取る ={QUERY(A2:F19,"SELECT A,SUM(F) GROUP BY A PIVOT C LABEL A '支店名'");{TRANSPOSE(QUERY(A2:F19,"SELECT SUM(F) GROUP BY C LABEL SUM(F) '総合計'"))}} |
理屈としては、Pivotで取ったQuery関数の配列結果と、別に作った特定項目のGroup Byで集計したQuery結果をTransposeで反転、これを配列数式で結合させたものになります。なので、総合計の行は別の数式で作ってるわけです。ですので、この結果を更にTransposeで反転させれば、行の合計に早変わりになるわけです。
図：クロス集計の各列の総合計を取ることも可能
図：反転させれば行の合計となる
Pivotの縦横の結果に総合計を加える
列の合計までは前項までに出しました。しかし、さらに行の合計も同時に欲しい場合があります。というか集計表を名乗るなら、縦横にそれぞれ総合計を設けるのが定石でしょう。それを行うには、かなり複雑な数式になるので、何段階かに分けてqueryの結果を容易し、Transposeで反転させては、またそれを結合させたりするという作業を行います。
ただしこの作業を行う場合には、一番最初のQueryでのPivotは自分が欲しい行列の組み合わせとは逆のものを用意しておく事に注意してください。1回必ずTransposeで反転が必要ですから。また、それぞれの総合計列も1回transposeするので、LABELの付け方に注意が必要です。
|
1 2 |
//縦横にそれぞれGrand Totalを用意する ={TRANSPOSE({QUERY(A2:F19,"SELECT C,SUM(F) GROUP BY C PIVOT A LABEL C '支店名'");TRANSPOSE(QUERY(A2:F19,"SELECT SUM(F) GROUP BY A LABEL SUM(F) '総合計'"))});TRANSPOSE({query(A2:F19,"select sum(F) group by C LABEL SUM(F) '総合計'");{SUM(F2:F19)}})} |
手順を簡単に説明すると
- まずは目的のクロス集計とは縦横逆のものをQuery関数で作っておく
- 次にA列を基準にGroup Byで集計したQuery関数の結果をTransposeで反転させたものを作る（これが3.にて、行の総合計になる）
- 1.と2.を配列数式で結合させたものを作り、Transposeで反転させる（ここで目的のクロス集計の形になる）。
- 次にC列を基準にGroup Byで集計したQuery関数の結果に、総合計を加えて、配列数式で結合させたものを作り、Transposeで反転させてやる（これが列の総合計になる）
- 3.と4.の数式を配列数式で結合させる。
こんな感じ。Query関数はトータル3回使います。Transpose関数も同じく3回使います。これで、要素が追加されても、自動的に縦横それぞれに総合計が加わるので、以降は結果に手動でsumで数式を作る必要はありません。
図：作るのは大変だけれど、1度作ればメンテフリー
番外編
マトリックス図をリストに変換
Microsoft PowerQueryには「ピボット解除」という機能が装備されています。これは、よくあるクロス集計表のような形で集計されているものを、集計前のリスト表に変換してくれる非常にありがたい機能なのですが、GoogleスプレッドシートおよびQuery関数にはそのような関数は装備されていません。これは、Transpose関数とは意味が異なり、実際にデータの二次利用をする上では、リストでないと困るシーンがたくさんあります。
これを実現するためにはユーザ定義関数として、Google Apps Scriptで作成する必要があります。Query関数に装備してほしい機能ですね。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
//マトリックスをリストに変換する function mtx2list(range) { //リスト図を返す為の配列 var array = []; //二重ループで一つ一つデータを生成してpushする for(var i=1;i<range.length;i++){ for(var j=1;j<range[0].length;j++){ //値が空の場合にはスルーする if(range[i][0] != ""){ //列項目、行項目、値にして、配列に追加する array.push([range[i][0],range[0][j], range[i][j]]); } } } //変換結果を返す return array; } |
図：リストに分解すると二次利用がしやすくなる
GASでVisualization APIを使う
本来、Visualization APIとは、JavaScriptなどから使う為のライブラリであるので、Google Apps Scriptから使うのは正攻法ではないのですが、UrlfetchAppを利用することで、アクセスする事が可能です。これをスプレッドシートの関数として使ってみました。結論としては、使えました。ただ、参照させたシートが「他のシートを結合」であったので、ちょっと遅かったです。単体のシートならば実用できるかも。
この手法であればQuery関数にはない、Visualization APIの機能を使ってアクセスさせる事も可能です。ただし、UrlfetchAppを使っている関係で、一度スクリプトエディタ内で実行させて承認をさせないと利用ができません。また、1日に利用できる回数も限られているので、大量に使う事はできないので、使い所を見極める必要があります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
//GASでVisualization APIを叩く function gasvisapi(){ //スプレッドシートを取得 var ss = SpreadsheetApp.getActiveSpreadsheet(); var ssid = ss.getId(); var sheetid = ss.getSheetByName("他のシートを結合").getSheetId(); //URLを構築 var baseurl = "https://docs.google.com/spreadsheets/d/"; var sUrl = baseurl + ssid + "/gviz/tq?gid=" + sheetid + "&tqx=out:json&tq="; //access_tokenを取得 var access_token = ScriptApp.getOAuthToken(); //ヘッダーを構築 var headers = { "Authorization": "Bearer " + access_token } //オプションを構築 var options = { "contentType": "application/json", "headers": headers, "muteHttpExceptions": true } //visualization apiにアクセスする var query = encodeURIComponent("SELECT A, B, C, D"); var ret = UrlFetchApp.fetch(sUrl + query, options); //responseデータをJSONで取得 var data = ret.getContentText(); data = data.split("google.visualization.Query.setResponse(")[1]; data = data.substr(0, data.length - 2); var json = JSON.parse(data); //JSONデータから配列データを組み立てる var rows = json["table"]["rows"].length; var cols = json["table"]["cols"].length; var array = []; for (var i = 0; i < rows; i++) { var values = []; for (var j = 0; j < cols; j++) { values.push(json["table"]["rows"][i]["c"][j]["v"]); } array.push(values); } //配列データを返す return array; } |
関連リンク
- googleスプレッドシートのQUERY関数で指定文字以外を抽出する方法について
- 【GAS】QUERY関数を使って複数の情報を横に結合する
- sql – Googleスプレッドシート“ = QUERY” join（）と同等の機能？
- Googleスプレッドシート QUERY関数の条件(WHERE)に日付を使いたい。
- 【スプレッドシート】QUERY関数で複数範囲・複数シートを結合する
- How to add a total row to a Query Function table in Google Sheets
- How to Use QUERY Function Similar to Pivot Table in Google Sheets
- Google Sheets Query: How to Join Two Tables
- Mastering Join-formulas in Google Sheets
