2021年2月22日 / 最終更新日時 : 2022年7月12日 officeの杜 Google

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

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

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

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

1 使用するクラスやメソッド・材料
2 実行結果
3 Pivottable.jsを使った事例
4 Visualization APIを使った事例
5 Vue-Crosstabを使った事例
- 5.1 GAS側コード
- 5.2 HTML側コード
6 関連リンク

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

実行結果

スプレッドシートを開き、上部にある「▶チャート表示」メニューの中の【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;
}

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>

<head>

<style>

* {font-family: Verdana;}

</style>

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>

</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ではなくこの表記に変更
		}
	);
});

$(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 '商品名'

1	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');
}

//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>

<!DOCTYPE html>

<html>

<head>

//スプレッドシート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>

</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

1	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

1	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

1	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;
  }));
}

//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>

<!DOCTYPE html>

<html>

<head>

<style>

html, body {

font-family: 'Lato', 'Avenir', sans-serif;

height: 100%;

margin: 0;

padding: 0;

}

#app {

height: 98%;

}

</style>

//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>

<crosstab

:data="data"

:rows="rows"

:cols="cols"

:transform="transform"

:theme="theme"

:bars="true"

:width="widthman"

:font-size="10"

:cell-width="150"

</crosstab>

</div>

//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を再初期化する。

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

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

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

実行結果

Pivottable.jsを使った事例

GAS側コード

HTML側コード

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

Visualization APIを使った事例

URLのみで構築する方法

APIを使ってデータを取得

GAS側コード

HTML側コード

コンストラクタの引数

gidの指定

JSONで取得

Rangeの指定

Vue-Crosstabを使った事例

GAS側コード

HTML側コード

関連リンク

コメントを残すコメントをキャンセル

Google Apps Scriptで2つのスプレッドシートの差分を取り出してみる【GAS】

Microsoft Power Automate DesktopでRPAを実現してみる

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

実行結果

Pivottable.jsを使った事例

GAS側コード

HTML側コード

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

Visualization APIを使った事例

URLのみで構築する方法

APIを使ってデータを取得

GAS側コード

HTML側コード

コンストラクタの引数

gidの指定

JSONで取得

Rangeの指定

Vue-Crosstabを使った事例

GAS側コード

HTML側コード

関連リンク​

コメントを残す コメントをキャンセル

Google Apps Scriptで2つのスプレッドシートの差分を取り出してみる【GAS】

Microsoft Power Automate DesktopでRPAを実現してみる

関連リンク

コメントを残すコメントをキャンセル