Googleスプレッドシート連携のピボットテーブル作成【GAS】

Googleスプレッドシートのデータからピボットテーブル、すなわちクロス集計結果を得る手段はいくつか存在します。Query関数は代表的な方法ですが、それ以外にもライブラリを使ってデータを整形する手段があります。今回はPivottable.jsやVisualization APIを使って取得して表示してみたいと思います。

他、GUIを構築するためにjQueryやCSS類をロードさせています。今回の外部ライブラリはPivotTable.jsと呼ばれるjQueryを利用したライブラリで、datatableや配列の他、JSONデータなども扱えるのですが、古い形式のJSON形式しか読み込んでくれないので、Googleスプレッドシートの出力するJSONでは読み込めませんでした。ですので、今回は配列データのまま取得してそのまま読み込ませています。

図:表示だけでなく操作も可能

使用するクラスやメソッド・材料

  1. HTML ServiceのcreateHtmlOutputFromFileメソッド他
  2. jQuery 1.8.3 ライブラリ
  3. jQuery UI 1.9.2 ライブラリ
  4. PivotTable JS ライブラリ
  5. Google Visualization API
  6. 今回使用するスプレッドシート

実行結果

スプレッドシートを開き、上部にある「▶チャート表示」メニューの中の【Pivottable.js】を開くとスプレッドシート上のデータをピラミッドテーブルで表示します。

Pivottable.jsを使った事例

GAS側コード

  • GAS側は単純で、HTML Serviceの生成とスプレッドシートデータを取得して返すだけの作業です。

HTML側コード

  • コード後半のjQueryでのコードの部分が本体。初期表示状態をここで指定して、idがoutputのelementに表示するように指定しています。
  • 配列をGAS側からそのまま受け取って無加工で渡せるのでこれが最も手軽です。
  • デフォルトでヒートマップやピボットグラフ表示機能を持っています。
  • Ajaxなライブラリなので、動的にピボットテーブルの中身を編集することが可能になっています。

カウントしかしてくれない問題

一部の環境で、同じライブラリを使っているにも関わらず、また、合計等を指定しているにも関わらず、Pivottable.jsの表示結果が「カウント」しか返さないケースに遭遇する事があります。自分も、Google Apps Scriptでは正しく表示出来ていたのが、Electronではなぜかカウント表記になり困っていました。

StackOverFlowにこれに対する解決策が提示されており、装備してみたところきちんと合計表記になったので、通常通り装備してもカウントしか返さない現象に遭遇したら下記のようにコードを追加すると良いでしょう。

  • 通常は、aggregatorでintSumなどを指定すれば合計表記になるはずがならないのが本ケース
  • 追加のコードでintSumになるようにintFormatを定義する
  • データの配列に於いて、valueが数字の入ってる項目。これに対してsum(intFormat)(["value"])を指定してあげる

Visualization APIを使った事例

Googleスプレッドシートからの手段限定では、Google Visualization APIを使ったクロス集計結果を得る方法があります。通常はGoogleスプレッドシート上でQuery関数を使って、クロス集計結果を生成するのが定石ですが、プログラムのコードとしてもVisualization APIを用いて生成が可能です。

URLのみで構築する方法

実はあまりしられていないのですが、URLを構築して実行するだけでクロス集計結果を得ることも可能です。以下のような形でURLを構築します。このURLの実行結果はこちらになります。コンストラクタの引数に置いて、QUERY文を作って投げることでSQL文のような取り方が可能になっています。但し、ちょっとややこしいので、ノートパッドなどで文をつくってから、何度か貼り付けて抽出できるようにしてみると良いと思います。作り方は、tq=に続けてSQL文っぽいのを入れてあげるわけです。

QUERY文の作り方は、Query Language Referenceを参照してください。

また、今回は公開スプレッドシートであるため、ログインせずとも上記のURLで表示が出来ますが、非公開やアクセス権限が必要なスプレッドシートの場合は、OAuth2.0認証でAccess Tokenを取得後に、上記URLに&access_token=ここにアクセストークンをつなげて実行すると、同様に取得できるようになります(Visualization APIでgoogle.visualization.QueryにてURLを叩く場合も同様に可能です)

Access TokenをつなげたURLで一般公開はNGですので要注意。

図:こんな感じの図がURL叩くだけで表示出来ます

APIを使ってデータを取得

今回はVisualization APIのTable Chartを使って、スプレッドシートのデータをクロス集計して表示してみようと思います。

GAS側コード

GAS側はHTML表示するだけ。ほとんどの作業はHTML側のコードで行います。

HTML側コード

  • 今回はpackageはcorechartの他にtableを指定する必要があります
  • query.visualization.QueryにてURLを指定する。URLはコンストラクタの引数を使って様々な指定が可能です。
  • query.setQueryにてクエリ文を指定。ここでクロス集計文を構築して、sendします。
  • 返ってきたデータはgetDataTableにてデータテーブル形式で取得。そのままチャートにdrawのデータとして渡すだけ。加工は必要ないです。

図:きれいに整形されて表示される

コンストラクタの引数

前述のURLを指定する場合のコンストラクタの引数ですが、URLにシートを特定する情報がない場合は1枚目のシートが自動で引っ張られます。シートやそのレンジを指定するには、以下のような指定が必要です。

gidの指定

gidは1枚目は必ず0になります。それ以外はランダムな値がシートに紐付けされており、Google Apps Scriptでもgetsheetid()を実行すると値を取得することが可能です。

gidが1233042226は今回のサンプルの2枚目のシートのIDになります。

JSONで取得

URLを叩いてクロス集計を表示するだけでなく、JSONで値をダウンロードも可能になっています。その場合はoutputの指定をせずに以下のようなURLを構築して叩くだけです。URLを実行するとJSON形式でスプレッドシートの値が取得されます。

ただ、出力されるJSONがちょっと変な形なので使いにくいです。

Rangeの指定

range指定で特定のエリアだけを出力するといったことも可能になっています。range=A1:D5といったようなURLを加えるだけでOKです。実行すると、指定の範囲だけをHTMLで出力します。

図:限定されたエリアだけを整形して表示

Vue-Crosstabを使った事例

Vue.jsとそのプラグインであるVue-Crosstabを使って、GASのデータをクロス集計して表示することが可能です。ちょっと癖があって、先にVueで初期化後にdataを入れ替えると値だけが表示されないという問題があるため、先にデータを取得してからVueを初期化する事で実現出来ました。

GAS側コード

  • HTML側にはスプレッドシートデータをJSON形式で加工して返す必要があります。

HTML側コード

  • Vue.jsのComponentにはCrosstabを登録する必要があります。
  • crosstabでbindするdataはrecmanの値を参照させています。
  • recmanにはGAS側からのデータをJSON.parseして格納します。
  • Vue.jsの初期化はrecmanに値を格納後に行わないと値が表示されませんので要注意。
  • 値入れ替えしたい場合は、同様の手順でrecmanに値を入れて、Vue.jsを再初期化する。

図:スクロール対応でヘッダと行タイトルは固定化されています

関連リンク

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)