Google スプレッドシートのデータをPivotTable.jsで表示するウェブアプリケーションをGoogle Apps Scriptで作ってみました。

概要

列がたくさんあるようなデータを分析する際にはExcelなどでピボットテーブル・ピボットグラフを使うと対話的に集計したり、並べ替えたりできるので便利です。

しかし、頻繁に更新されるデータでピボットテーブルを使う場合、Excelではデータの共有・更新を面倒に感じていました。一方でGoogle スプレッドシートは共有・更新は楽ですが、今のところピボットテーブルにはExcelほどの機能がないようです。

そこで今回は、共有・更新が楽なGoogle スプレッドシートからデータを読み込み、PivotTable.jsでピボットテーブルを作成するというウェブアプリケーションを作ってみます。

PivotTable.jsはピボットテーブルを作成するためのライブラリで、様々な集計関数やグラフを描画といった機能をサポートしています。(Examples)

また、今回はGoogle Apps Scriptでウェブアプリケーションを作成します。これによりサーバーを用意すること無くウェブアプリケーションを作成することができます。Googleアカウントを利用した公開範囲の制限も可能です。

ウェブアプリケーションの作成

スクリプトエディタ(https://script.google.com) を開き「空のプロジェクト」を選択

コード.gs の中身を全て削除し、以下のコードを貼付けて保存
（プロジェクト名は分かりやすい名前を適当に付けておきます。）

コード.gs

function doGet(e) {
    return HtmlService.createHtmlOutputFromFile('index')
        .setTitle('PivotTable')
        .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

function getSpreadSheetFiles(folder, path) {
    if (folder == null && path == null) {
        return getSpreadSheetFiles(DriveApp.getRootFolder(), "");
    }

    var files = [];
    path = path + "/" + folder.getName();

    var fileIt = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
    while(fileIt.hasNext()) {
        var f = fileIt.next();
        files.push({id: f.getId(), path: path + "/" + f.getName()});
    }

    var folderIt = folder.getFolders();
    while(folderIt.hasNext()) {
        fs = getSpreadSheetFiles(folderIt.next(), path);
        for (var i = 0; i < fs.length; i++) {
            files.push(fs[i]);
        }
    }
    return files;
}

function getSheets(fileId) {
    var sheets = [];
    var spreadsheet = SpreadsheetApp.openById(fileId);
    var sheetsTmp = spreadsheet.getSheets();
    for (var i = 0; i < sheetsTmp.length; i++) {
        var sheet = sheetsTmp[i];
        sheets.push(sheet.getSheetName());
    }
    return sheets;
}

function getData(fileId, sheetName) {
    return SpreadsheetApp.openById(fileId)
        .getSheetByName(sheetName)
        .getDataRange()
        .getValues();
}

メニューから「実行 > doGet」を選択。Drive, SpreadSheetへの接続の承認が求められるので承認する
（htmlファイルが無いという警告がでますが、ここでは無視します）
メニューから「ファイル > 新規作成 > HTMLファイル」を選択し、ファイル名をindex としてHTMLファイルを作成します。

index.html の中身を一旦全て削除し以下を貼付けて保存します。

index.html

<!-- StyleSheet -->
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/pivottable/1.4.0/pivot.min.css">

<style>
    select {
        height: 38px;
    }
    select#file-select {
        width: 300px;
    }
    select#sheet-select {
        width: 100px;
    }
    #pivottable {
        margin: 30px;
        padding: 10px;
        border: solid thin black;
    }
</style>

<!-- Body -->
<div class="inline form-group">
    <label for="File">File</label>
    <select disabled id="file-select" onchange="updateSheets()">
        <option disabled selected>Loading...</option>
    </select>
</div>
<div class="inline form-group">
    <label for="Sheet">Sheet</label>
    <select disabled id="sheet-select" onchange="updateData()">
        <option disabled selected>Loading...</option>
    </select>
</div>

<div class="block" id="pivottable"></div>

<!-- Script -->
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script src="//ajax.googleapis.com/ajax/libs/jqueryui/1.9.1/jquery-ui.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pivottable/1.4.0/pivot.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pivottable/1.4.0/gchart_renderers.min.js"></script>
<script>
    google.load("visualization", "1", {packages:["corechart", "charteditor"]});
    $(function() {
        google.script.run
        .withSuccessHandler(function(files) {
            var fileSelect = $('#file-select');
            fileSelect.children().remove();
            fileSelect.append(
            '<option disabled selected>Choose File</option>'
            );
            for (var i = 0; i < files.length; i++) {
                fileSelect.append(
                '<option value="' + files[i].id + '">' + files[i].path + '</option>');
            }
            fileSelect.prop('disabled', false);
        })
        .getSpreadSheetFiles(null, null);

        $('#pivottable').pivotUI([], {}, true); 
    });

    function updateSheets() {
        var fileId = $('#file-select').val();
        var sheetSelect = $('#sheet-select');
        sheetSelect.prop('disabled', true);
        sheetSelect.children().remove();
        sheetSelect.append('<option disabled selected>Loading...</option>');

        google.script.run
        .withSuccessHandler(function(sheets) {
            var sheetSelect = $('#sheet-select');
            sheetSelect.children().remove();
            sheetSelect.append(
            '<option disabled selected>Choose Sheet</option>'
            );
            for (var i = 0; i < sheets.length; i++) {
                sheetSelect.append('<option>' + sheets[i] + '</option>');
            }
            sheetSelect.prop('disabled', false);
        })
        .getSheets(fileId);
    }

    function updateData() {                                                    
        var fileId = $('#file-select').val();                                  
        var sheetName = $('#sheet-select').val();                              
        var renderers = $.extend($.pivotUtilities.renderers, $.pivotUtilities.gchart_renderers);
        google.script.run                                                      
        .withSuccessHandler(function(data) {                                   
            var rows = data.length > 0 ? data[0] : [];                         
            rows = rows.filter(function(s){ return (s != "") });               
            $('#pivottable').pivotUI(data,                                     
            {                                                                  
                rows: rows,                                                    
                cols: [],                                                      
                renderers: renderers                                           
            }, true);                                                          
        })                                                                     
        .getData(fileId, sheetName);                                           
    }  
</script>

メニューから「公開 > ウェブアプリケーションとして導入」を選択
プロジェクトバージョンに「新規作成」と入力し、「新しいバージョンを保存」をクリック
「導入」をクリック
「現在のウェブアプリケーションの URL」に表示されているURLをブラウザで開く
成功していれば以下のような画面が表示されます。

使い方

セレクトボックスからGoogle Drive内のスプレッドシートとスプレッド内のシートを選択するとデータがロードされます。

デモとしてPivotTable.jsの例で使われているCanadian Parliament 2012 dataset をスプレッドシートに読み込んで使用してみます。

Google Driveで空のスプレッドシートを作成し、セルA1に=IMPORTDATA("http://nicolas.kruchten.com/pivottable/examples/mps.csv") を貼付けデータを読み込ませ適当な名前を付けて保存します。

ウェブアプリケーションをリロードすると作成したファイルが選択できるようになっているので、ファイルとシートを選択します。初期状態ではもとのテーブルに近い状態でデータを表示するため全ての属性を行に移動しています（例外として名前がついていない属性は行に移動されません。）。

列名をドラッグ・ドロップし、集計関数や表示形式を変更する事で色々な角度からデータを見る事ができます。詳しい使い方はPivotTable.jsのドキュメントを参照してください。

手持ちのデータを読み込んでみたり、Rdatasets などから色々なデータをインポートして試してみると面白いと思います。（リンク先のHTML Index から見たいCSVのリンクをIMPORTDATA に渡して読み込めます。）

最後に

PivotTable.jsは集計関数が一度にひとつしか使えないといった制約もありますが（ExcelやGoogle スプレッドシートは複数同時にしようできる）、データをいろいろ眺めて

今回は任意のスプレッドシートを読み込ませるという使い方をしたので、PivotTable.jsの表示の設定は特に行いませんでしたが、APIなどで特定のスプレッドシートをアップデートしていて読み込むデータが決まっている場合には、読み込むデータを選択できるようにする代わりに初期状態での表示の設定などをしておくと良いかもしれません。

参考資料

Google Apps Scriptで外部ライブラリを使ったピボットテーブル作成 | eye4brain