Go to Qiita Advent Calendar 2024 Top
LoginSignup
35
34

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@atsaki

Google スプレッドシートのデータをPivotTable.jsで表示する

Last updated at Posted at 2015-05-03

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

スクリーンショット3.png

概要

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

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

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

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

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

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

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

  2. コード.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();
}

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

  4. メニューから「ファイル > 新規作成 > HTMLファイル」を選択し、ファイル名をindex としてHTMLファイルを作成します。

  5. 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>

  6. メニューから「公開 > ウェブアプリケーションとして導入」を選択

  7. プロジェクトバージョンに「新規作成」と入力し、「新しいバージョンを保存」をクリック

  8. 「導入」をクリック

  9. 「現在のウェブ アプリケーションの URL」に表示されているURLをブラウザで開く
    成功していれば以下のような画面が表示されます。

    スクリーンショット1.png

使い方

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

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

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

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

スクリーンショット2.png

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

スクリーンショット3.png

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

最後に

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

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

参考資料

35
34
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
35
34

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?