Googleスプレッドシート連携のピボットテーブル作成【GAS】
Googleスプレッドシートのデータからピボットテーブル、すなわちクロス集計結果を得る手段はいくつか存在します。Query関数は代表的な方法ですが、それ以外にもライブラリを使ってデータを整形する手段があります。今回はPivottable.jsやVisualization APIを使って取得して表示してみたいと思います。
他、GUIを構築するためにjQueryやCSS類をロードさせています。今回の外部ライブラリはPivotTable.jsと呼ばれるjQueryを利用したライブラリで、datatableや配列の他、JSONデータなども扱えるのですが、古い形式のJSON形式しか読み込んでくれないので、Googleスプレッドシートの出力するJSONでは読み込めませんでした。ですので、今回は配列データのまま取得してそのまま読み込ませています。
図：表示だけでなく操作も可能
目次
使用するクラスやメソッド・材料
- HTML ServiceのcreateHtmlOutputFromFileメソッド他
- jQuery 1.8.3 ライブラリ
- jQuery UI 1.9.2 ライブラリ
- PivotTable JS ライブラリ
- Google Visualization API
- 今回使用するスプレッドシート
実行結果
スプレッドシートを開き、上部にある「▶チャート表示」メニューの中の【Pivottable.js】を開くとスプレッドシート上のデータをピラミッドテーブルで表示します。
Pivottable.jsを使った事例
GAS側コード
function onOpen(e) {
var ui = SpreadsheetApp.getUi();
ui.createMenu('▶チャート表示')
.addItem('ピボットテーブル表示', 'pivotman')
.addToUi();
}
function pivotman() {
//シートIDを格納する
getMySheetId();
//ダイアログで表示
var html = HtmlService.createHtmlOutputFromFile('index')
.setWidth(720)
.setHeight(420);
SpreadsheetApp.getUi()
.showModalDialog(html, 'PivotTable.js');
}
//スプレッドシートデータを返す
function datamanrev(){
var prop = PropertiesService.getScriptProperties();
var ssid = prop.getProperty("sheetid");
var sheet = SpreadsheetApp.openById(ssid);
var ss = sheet.getSheetByName("pivot");
var dataman = ss.getRange("A1:D").getValues();
return dataman;
}
//自分自身のIDを取得するコード
function getMySheetId(){
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var myid = sheet.getId();
var Properties = PropertiesService.getScriptProperties();
Properties.setProperty("sheetid", myid);
return myid;
}
- GAS側は単純で、HTML Serviceの生成とスプレッドシートデータを取得して返すだけの作業です。
HTML側コード
<head>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.11.4/jquery-ui.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.13.0/pivot.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jqueryui-touch-punch/0.2.3/jquery.ui.touch-punch.min.js"></script>
<style>
* {font-family: Verdana;}
</style>
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.13.0/pivot.min.css">
<script type="text/javascript">
google.script.run.withSuccessHandler(onSuccess).datamanrev();
function onSuccess(data){
var gdata = data;
$(function(){
$("#output").pivotUI(gdata,
{
rows: ["商品名"],
cols: ["仕入元"],
vals: ['数量合計'],
aggregatorName: 'intSum',
rendererName: 'table'
},
true
);
});
}
</script>
</head>
<body>
<div id="output" style="width: 700px; height: 400px;"></div>
</body>
- コード後半のjQueryでのコードの部分が本体。初期表示状態をここで指定して、idがoutputのelementに表示するように指定しています。
- 配列をGAS側からそのまま受け取って無加工で渡せるのでこれが最も手軽です。
- デフォルトでヒートマップやピボットグラフ表示機能を持っています。
- Ajaxなライブラリなので、動的にピボットテーブルの中身を編集することが可能になっています。
カウントしかしてくれない問題
一部の環境で、同じライブラリを使っているにも関わらず、また、合計等を指定しているにも関わらず、Pivottable.jsの表示結果が「カウント」しか返さないケースに遭遇する事があります。自分も、Google Apps Scriptでは正しく表示出来ていたのが、Electronではなぜかカウント表記になり困っていました。
StackOverFlowにこれに対する解決策が提示されており、装備してみたところきちんと合計表記になったので、通常通り装備してもカウントしか返さない現象に遭遇したら下記のようにコードを追加すると良いでしょう。
$(function(){
//カウント表記回避の為の追加コード
var sum = $.pivotUtilities.aggregatorTemplates.sum;
var numberFormat = $.pivotUtilities.numberFormat;
var intFormat = numberFormat({digitsAfterDecimal: 0});
//Pivot表示
$("#output").pivot(
[
{color: "blue", shape: "circle", value: 1},
{color: "red", shape: "triangle", value: 2},
{color: "blue", shape: "circle", value: 3},
{color: "red", shape: "triangle", value: 4}
],
{
rows: ["color"],
cols: ["shape"],
aggregator: sum(intFormat)(["value"]) //通常のintSumではなくこの表記に変更
}
);
});
- 通常は、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文っぽいのを入れてあげるわけです。
https://docs.google.com/spreadsheets/d/スプレッドシートのID/gviz/tq?tqx=out:html&tq=SELECT B,SUM(D) GROUP By B Pivot C LABEL B '商品名'
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側のコードで行います。
//Visualization APIで処理
function pivotvis(){
//ダイアログで表示
var html = HtmlService.createHtmlOutputFromFile('query')
.setWidth(720)
.setHeight(420);
SpreadsheetApp.getUi()
.showModalDialog(html, 'Visualization API');
}
HTML側コード
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script src="https://www.gstatic.com/charts/loader.js"></script>
<script>
//スプレッドシートURL
var file = "https://docs.google.com/spreadsheets/d/1LvuNInIFZ90XCp4QKxJGR6GwOsTdNw3vtAcLGA9XYFE/edit#gid=0";
//Visualization APIの呼び出し
google.charts.load('current', {packages: ['corechart','table'], 'language': 'ja'});
google.charts.setOnLoadCallback(drawChart);
//Visualization APIでスプレッドシートを取得
function drawChart(){
var query = new google.visualization.Query(file);
query.setQuery("SELECT B,SUM(D) GROUP By B Pivot C LABEL B '商品名'");
query.send(handleQueryResponse);
}
//返ってきた値を処理
function handleQueryResponse(response) {
//エラートラップ
if (response.isError()) {
alert('クエリーエラー: ' + response.getMessage() + ' ' +
response.getDetailedMessage());
return;
}
//データテーブルで取得する
var dataman = response.getDataTable();
//table chartで描画
var table = new google.visualization.Table(document.getElementById('table_div'));
table.draw(dataman, {showRowNumber: true, width: '100%', height: '100%'});
}
</script>
</head>
<body>
<div id="table_div"></div>
</body>
</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()を実行すると値を取得することが可能です。
https://docs.google.com/spreadsheets/d/スプレッドシートのID/edit#gid=1233042226
gidが1233042226は今回のサンプルの2枚目のシートのIDになります。
JSONで取得
URLを叩いてクロス集計を表示するだけでなく、JSONで値をダウンロードも可能になっています。その場合はoutputの指定をせずに以下のようなURLを構築して叩くだけです。URLを実行するとJSON形式でスプレッドシートの値が取得されます。
https://docs.google.com/spreadsheets/d/スプレッドシートのID/gviz/tq?gid=1233042226
ただ、出力されるJSONがちょっと変な形なので使いにくいです。
Rangeの指定
range指定で特定のエリアだけを出力するといったことも可能になっています。range=A1:D5といったようなURLを加えるだけでOKです。実行すると、指定の範囲だけをHTMLで出力します。
https://docs.google.com/spreadsheets/d/スプレッドシートのID/gviz/tq?tqx=out:html&range=A1:D5
図：限定されたエリアだけを整形して表示
Vue-Crosstabを使った事例
Vue.jsとそのプラグインであるVue-Crosstabを使って、GASのデータをクロス集計して表示することが可能です。ちょっと癖があって、先にVueで初期化後にdataを入れ替えると値だけが表示されないという問題があるため、先にデータを取得してからVueを初期化する事で実現出来ました。
GAS側コード
//Vue-Crosstabで処理
function pivotvue(){
//ダイアログで表示
var html = HtmlService.createHtmlOutputFromFile('vue')
.setWidth(720)
.setHeight(420);
SpreadsheetApp.getUi()
.showModalDialog(html, 'Vue-Crosstab');
}
//JSONでデータを返す
function backjson() {
//スプレッドシートデータを取得する
var Properties = PropertiesService.getScriptProperties();
var id = Properties.getProperty("sheetid");
var sheet = SpreadsheetApp.openById(id).getSheetByName("test");
var ss = sheet.getDataRange().getValues();
//タイトル行を取得する
var title = ss.splice(0, 1)[0];
//JSONデータを生成する
return JSON.stringify(ss.map(function(row) {
var json = {}
row.map(function(item, index) {
json[title[index]] = item;
});
return json;
}));
}
- HTML側にはスプレッドシートデータをJSON形式で加工して返す必要があります。
HTML側コード
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
html, body {
font-family: 'Lato', 'Avenir', sans-serif;
height: 100%;
margin: 0;
padding: 0;
}
#app {
height: 98%;
}
</style>
<script>
//GAS側からのデータを取得
function onSuccess(data){
//データをJSON.parseする
var recman = JSON.parse(data);
//Vueを初期化
vm = new Vue({
el: '#app',
data () {
return {
data : recman,
widthman:700,
cols: [
{field: 'company', label: '仕入元'},
{field: 'countman', label: '数量合計', aggregate: 'sum'}
],
rows: [
{field: 'item', label: '商品名'},
],
transform: [],
theme: {name: 'Blue'},
}
},
components: {
Crosstab: crossman
},
})
}
</script>
</head>
<body>
<div id="app">
<crosstab
:data="data"
:rows="rows"
:cols="cols"
:transform="transform"
:theme="theme"
:bars="true"
:width="widthman"
:font-size="10"
:cell-width="150"
>
</crosstab>
</div>
<script src="https://cdn.jsdelivr.net/npm/vue@2.x/dist/vue.js"></script>
<script src="https://unpkg.com/vue-crosstab"></script>
<!-- Vuetifyを初期化する -->
<script>
//Crosstabを初期化
var vm;
const crossman = window.VueCrosstab.default;
//GAS側からデータを取得する
google.script.run.withSuccessHandler(onSuccess).backjson();
</script>
</body>
</html>
- Vue.jsのComponentにはCrosstabを登録する必要があります。
- crosstabでbindするdataはrecmanの値を参照させています。
- recmanにはGAS側からのデータをJSON.parseして格納します。
- Vue.jsの初期化はrecmanに値を格納後に行わないと値が表示されませんので要注意。
- 値入れ替えしたい場合は、同様の手順でrecmanに値を入れて、Vue.jsを再初期化する。
図：スクロール対応でヘッダと行タイトルは固定化されています
関連リンク
- PivotTable.jsを使ってjavascriptで集計を簡単に表示するテスト
- Google Apps Script で spreadsheet のデータを JSON として読み込む
- Google スプレッドシートのデータをJsで読み込む
- Google Gadgetで、jQueryの”.getJSON”メソッドを使って別ドメインから(クロスドメインで)、JSONデータが受け取れない際に注意すること。
- jquery .getJSONがiphone safariで動かない!!
- Google スプレッドシートのデータをPivotTable.jsで表示する
- Integration with Google Charts
- Query CSV file with google visualization query
- Javascript Query.setQuery() to Google sheets using the Google visualization API gives 'Missing query for request id: undefined"
- Google Visualization API using Google Spreadsheet as “Authenticated” Data Source via JavaScript Client API Auth
- Vue-Crosstab - a vue.js library
- D3 cross tabulation HTML table
- JavaScriptでクロス集計
- PivotTable.js - pivot() always return count() values
