LoginSignup
182
184

More than 5 years have passed since last update.

@atsaki

Google スプレッドシートからデータをSQLライクに取得してグラフを描く

Last updated at Posted at 2015-05-17

スプレッドシートのQUERY関数やVisualization APIではSQLライクなクエリ言語を使ってデータの集計をすることができます。今回はクエリ言語を使ってデータを集計し、結果を利用してグラフを描くアプリケーションを作成してみます。

スクリーンショット 2015-05-17 20.46.57.png

前回作成したPivotTable.jsでは集計関数を一つしか使えないため、ある値の最小値、最大値、平均を並べるといったことはできませんでした。今回の方法では同時に集計関数を複数使う事も可能になっています。

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

TL;DR

  • スプレッドシートのQUERY関数、Visualization APIではSQLライクなクエリ言語を使ってスプレッドシートからのデータ取得・集計ができる
  • クエリ言語のGROUP BY句やPIVOT句はピボットテーブルと対応づけると覚えやすい
  • クエリした結果からGoogle Chartsでグラフを作成するアプリケーションを作ってみた

スプレッドシートでのクエリ実行

クエリ言語はスプレッドシート、Visualization APIから使用可能です。
まずは手軽なスプレッドシートのQUERY関数でクエリを実行して動作を調べてみます。

QUERY - ドキュメント エディタ ヘルプ

データの用意

前回の例で使用したCanadian Parliament 2012 dataset を使っていきます。

適当に空のシートを開き、セルA1 に下記を入力して実行するとデータが読み込まれます。

=IMPORTDATA("http://nicolas.kruchten.com/pivottable/examples/mps.csv")

IMPORTDATA_01.png

QUERY関数

クエリの結果を表示したいセルで次のようにQUERY関数を実行すると結果が取得できます。

=QUERY(<クエリの対象のデータ範囲>, <クエリ>)

クエリ言語はSQLライクな言語となっており、以下の句を使用することができます。
句は以下の順で使用する必要があります。

  • SELECT
  • WHERE
  • GROUP BY
  • PIVOT
  • ORDER BY
  • LIMIT
  • OFFSET
  • LABEL
  • FORMAT
  • OPTIONS

以下でよく使いそうな句を紹介していきます。

SELECT句

SELECT句は出力する列を指定するために使います。

読み込んだデータからPartyとAge列のみを表示させてみます。
以下のクエリをセルG1に入力して実行すると、Party、Age列が出力されます。
（列はスプレッドシートの列のIDで指定します。）

=QUERY(A:E, "SELECT B, D")

SELECT_01.png

SELECT * （またはSELECT句を省略）で全ての列を出力します。

=QUERY(A:E, "SELECT *")
または
=QUERY(A:E, "")

SELECT_02.png

WHERE句

WHERE句は出力する行を選択するために用いられます。

例えばParty=NDPの行を得るためのクエリは以下のようになります。

=QUERY(A:E, "SELECT * WHERE B = 'NDP'")
または
=QUERY(A:E, "WHERE B = 'NDP'")

WHERE_01.png

複数の条件をANDやORで組み合わせて使う事もできます。
Party=NDPかつProvince=Quebecである行は次のようにして得ることができます。
（以降、SELECT * を省略しないで書きます。）

=QUERY(A:E, "SELECT * WHERE B = 'NDP'　AND C = 'Quebec'")

WHERE_02.png

文字列の比較にLIKE等の演算子を使うこともできます。サポートされている演算子についての詳細はドキュメントを参照してください。

名前がAllenから始まる人の行は以下のクエリで得ることができます。

=QUERY(A:E, "SELECT * WHERE A LIKE 'Allen%'")

WHERE_03.png

GROUP BY句

GROUP BY句は指定した属性の値でデータをグループ化・集計するために用いられます。

以下のクエリで、各政党に属している人数を得る事ができます。

=QUERY(A:E, "SELECT B, COUNT(A) GROUP BY B")

GROUP_BY_01.png

Partyが空の人が691人いることになっていますが、データを確認すると結果にデータの無い行が含まれていることがわかります。

データの無い行を取り除くためにWHERE句を使うことができます。

=QUERY(A:E, "SELECT B, COUNT(A) WHERE A != '' GROUP BY B")

GROUP_BY_02.png

データ範囲で行まで指定しQUERY関数の引数の範囲からデータの無い行を取り除くことによって、データのない行が集計されてしまうことを防ぐことも可能です。
こちらの方法ではWHERE句は不要でクエリが短くなります。以降ではクエリを短くするため、データの範囲を修正しデータが無い行を集計から除外します。

=QUERY(A1:E309, "SELECT B, COUNT(A) GROUP BY B")

GROUP_BY_03.png

クエリで得られる結果と同様の結果をピボットテーブルでも得ることができます。
今回のクエリはピボットテーブルで行にPartyを指定し、COUNTAで名前を数えることに相当しています。

GROUP_BY_04.png

集計関数にはCOUNT以外にもMIN, MAX, AVGなどがサポートされています。

以下のクエリで各政党の平均年齢が得られます。

=QUERY(A1:E309, "SELECT B, AVG(D) GROUP BY B")

GROUP_BY_05.png

このクエリは、ピボットテーブルで行にPartyを指定しAVERAGEでAgeの平均を計算することに相当します。

GROUP_BY_06.png

各政党の平均年齢を計算する事ができたのでこれを棒グラフにしてみます。
（年齢を棒グラフで表すのに違和感がありますが、ここではあまり気にしないでいただけるとありがたいです。）

クエリ関数により生成されたテーブル内の任意のセルを選択した状態でメニューから「挿入 > グラフ」でグラフを作成すると、自動的にデータの範囲を選択して以下のようなグラフを作成することができます。

GROUP_BY_07.png

同様にピボットテーブル内のセルを選択した状態でグラフを作成すると以下のようなグラフを作成することができます。（グラフを合わせるため合計を表示のチェックを外しています。）

GROUP_BY_08.png

GROUP BY句には複数の属性を指定する事も可能です。次のクエリで各政党の男女の平均年齢を計算することができます。

=QUERY(A1:E309, "SELECT B, E, AVG(D) GROUP BY B, E")

GROUP_BY_09.png

GROUP BYに複数の列名を指定する事は、ピボットテーブルでは行に属性を複数追加することに相当します。

GROUP_BY_10.png

GROUP BY句に複数の属性を指定したクエリを実行した結果から下図のようなグラフを描きたくなるかもしれません。しかし、現在の結果の表の形式はこのグラフを作成するための形式となっていません。

GROUP_BY_11.png

上図のようなグラフを描くためには下のように見出し、ラベルが1行、1列の表を作成する必要があります。この結果を得るためには後述するPIVOT句を使用する必要があります。

GROUP_BY_12.png

GROUP BY句を使用している場合、SELECT句で集計関数を複数使用する事ができます。以下のクエリで各政党の党員の年齢の最小値、平均、最大値を得ることができます。この場合、表の形式が見出し、ラベルが1行、1列となっているためグラフを作成することができます。

=QUERY(A1:E309, "SELECT B, MIN(D), AVG(D), MAX(D) GROUP BY B")

GROUP_BY_13.png

SELECT句で集計関数を複数使用することは、ピボットテーブルでは値を複数指定することに相当します。

GROUP_BY_14.png

PIVOT句

PIVOT句は指定した属性の値を使って新しい列を作るために用いられます。

GROUP BY句の例でデータを政党毎にグループ化し各政党の平均年齢を計算しました。
このとき結果は以下のように政党が行、平均年齢が列になりました。

GROUP_BY_05.png

GROUP BY句の代わりにPIVOT句を使うと各政党を列にすることができます。
PIVOT句もGROUP BY句と同様にデータの集計を行います。データの集計にはSELECT句で指定した集計関数が使用されます。
グラフをみるとGROUP BY句で作成したものと形は同じですがデータの系列が政党で分かれています。

=QUERY(A1:E309, "SELECT AVG(D) PIVOT B")

PIVOT_01.png

GROUP BY句はピボットテーブルの行に属性を指定する事に対応していました。同様にPIVOT句はピボットテーブルの列に属性を指定する事に対応しています。

PIVOT_02.png

GROUP BY句の説明の中で以下のクエリで政党の男女別の平均年齢を得た場合、表の形式が見出し、ラベルが1行、1列とならずグラフが描けないことに触れました。

=QUERY(A1:E309, "SELECT B, E, AVG(D) GROUP BY B, E")

グラフを描ける結果を得るにはPIVOT句を用いて以下のようにします。

=QUERY(A1:E309, "SELECT B, AVG(D) GROUP BY B PIVOT E")

PIVOT_03.png

または、BとEを入れ替えて次のようにすると行と列が入れ替わります。

=QUERY(A1:E309, "SELECT E, AVG(D) GROUP BY E PIVOT B")

PIVOT_04.png

これらのGROUP BYとPIVOTを組み合わせたクエリはピボットテーブルでは行と列に属性を指定する事に相当します。

PIVOT_05.png

GROUP BYに複数の属性を指定できたように、PIVOTにも複数の属性を指定することが可能です。
新しい列名は属性の値をカンマ区切りで結合したものとなります。

=QUERY(A1:E309, "SELECT AVG(D) PIVOT B, E")

PIVOT_06.png

ピボットテーブルでは以下のようになります。

PIVOT_07.png

ORDER BY句

ORDER BY句は行をソートするために用いられます。
（クエリで列はソートできません。ピボットテーブルではできます。）

=QUERY(A1:E309, "SELECT B, AVG(D) GROUP BY B ORDER BY AVG(D)")

ORDER_BY_01.png

LABEL句

LABEL句をは属性にラベルをつけるために用いられます。

=QUERY(A1:E309, "SELECT B, AVG(D) GROUP BY B PIVOT E LABEL B '政党'")

LABEL_01.png

Google Visualization APIを使ったアプリケーション

Google Visualization APIを使用するとウェブアプリケーションからスプレッドシートに対してクエリを実行する事ができます。
次のようにスプレッドシートのURLを渡してQueryクラスを初期化し、クエリをセットしてからsendメソッドを実行するとハンドラに結果が渡されます。

var query = new google.visualization.Query(URL);
query.setQuery(QUERY);
query.send(handleQueryResponse);

詳細はドキュメントを参照してください。

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

Google Visualization APIを使ってスプレッドシートからクエリでデータを取得・集計し、結果からGoogle Chartsでグラフを作成するアプリケーションを作ってみました。

以下の手順で作成する事ができます。

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

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

    コード.gs
    function doGet(e) {                                                            
    return HtmlService.createHtmlOutputFromFile('index')                       
        .setTitle('QueryExample')                                              
        .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 getUrl(fileId) {                                                      
    return SpreadsheetApp.openById(fileId).getUrl();                           
}

  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>
    form {
        margin: 8px;
    }
    select {
        height: 38px;
    }
    select#file-select {
        width: 300px;
    }
    select#sheet-select {
        width: 100px;
    }
    input#query {
        width: 500px;
    }
    td, th {
        padding: 5px 10px;
    }
    .slide_toggle h3 {
        cursor: pointer;
        border-bottom: 1px solid black ;
    }
    .slide_toggle h3:before {
        content: '';
        position: relative;
        float: left;
        border-top: 10px solid black;
        border-left: 5px solid transparent;
        border-right: 5px solid transparent;
        width: 0;
        height: 0;
        top: 5px;
        right: 5px;
    }
    .slide_toggle h3.open::before {
        content: '';
        position: relative;
        float: left;
        border-left: 10px solid black;
        border-top: 5px solid transparent;
        border-bottom: 5px solid transparent;;
        width: 0;
        height: 0;
        top: 5px;
        right: 5px;
    }
</style>

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

<form onsubmit="updateData(); return false;">
    <div class="inline form-group">
        <label for="query">Query</label> 
        <input id="query" type="text" disabled >
    </div>
    <div class="inline form-group">
        <input id="query-submit" type="submit" disabled >
    </div>
</form>
<div class="query-examples">
Query Examples
  <ul>
    <li>SELECT * LIMIT 10</li>
    <li>SELECT B, AVG(D) GROUP BY B PIVOT E</li>
  </ul>
</div>

<div class="slide_toggle">
    <h3>Columns</h3>
    <table id="columns"></table>
</div>

<div class="slide_toggle">
    <h3>Table</h3>
    <div class="block" id="table"></div>    
</div>

<div class="slide_toggle">
    <h3>Chart</h3>
    <div class="inline form-group">
        <label for="chart-type-select">Chart Type</label>
        <select id="chart-type-select" onchange="updateChart()">
            <option selected>ColumnChart</option>
            <option>ColumnChart(stacked)</option>
            <option>ColumnChart(percent)</option>
            <option>BarChart</option>
            <option>Histogram</option>
            <option>LineChart</option>
            <option>AreaChart</option>
            <option>AreaChart(stacked)</option>
            <option>ScatterChart</option>
            <option>BubbleChart</option>
            <option>PieChart</option>
            <option>CandlestickChart</option>
            <option>Sankey</option>
            <option>TreeMap</option>
            <option>Gauge</option>
            <option>Calendar</option>
        </select>
    </div>

    <div id="chart" style="width: 600px; height:400px" ></div>
    <div id="png"></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>
        var data;

        google.load('visualization', '1.1', {packages:['corechart', 'charteditor', 'treemap', 'sankey', 'calendar']});
        $(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);

            $('.slide_toggle h3').on('click', function() {
                $(this).toggleClass('open').next().slideToggle();
            });
        });

        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>');
            $('#query').prop('disabled', true);
            $('#query-submit').prop('disabled', true);

            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 query = $('#query').val();

            $('#query').prop('disabled', true);
            $('#query-submit').prop('disabled', true);

            if (fileId == null || sheetName == null) {
                return
            }
            google.script.run                                                          
                .withSuccessHandler(function(spreadsheet_url) {

                    var url = spreadsheet_url + "?sheet=" + sheetName;
                    var vq = new google.visualization.Query(url);
                    if(query) {
                        vq.setQuery(query);
                    }
                    vq.send(handleQueryResponse);
                    $('#query').prop('disabled', false);
                    $('#query-submit').prop('disabled', false);
                })
            .getUrl(fileId);
        }

        function updateColumnTable() {
            if(!$('#query').val()) {
                var columnsTable = $('#columns');
                columnsTable.children().remove();
                columnsTable
                    .append($('<thead><tr>')
                            .append('<th>Id</th>')
                            .append('<th>Label</th>')
                            .append('<th>Type</th>')
                           );
                var tbody = columnsTable.append('<tbody>');
                for (var ci = 0; ci < data.getNumberOfColumns(); ci++) {
                    tbody
                        .append($('<tr>')
                                .append('<td>' + data.getColumnId(ci) + '</td>')
                                .append('<td>' + data.getColumnLabel(ci) + '</td>')
                                .append('<td>' + data.getColumnType(ci) + '</td>')
                               );
                }
            }
        }

        function updateTable() {   
            var table =  new google.visualization.Table(document.getElementById('table'));
            table.draw(data);
        }

        function updateChart() {
            var chart;
            var options;
            switch($('#chart-type-select').val()){
                case 'ColumnChart' :
                    chart =  new google.visualization.ColumnChart(document.getElementById('chart'));
                    break;
                case 'ColumnChart(stacked)' :
                    chart =  new google.visualization.ColumnChart(document.getElementById('chart'));
                    options = {isStacked: true};
                    break;
                case 'ColumnChart(percent)' :
                    chart =  new google.visualization.ColumnChart(document.getElementById('chart'));
                    options = {isStacked: 'percent'};
                    break;
                case 'BarChart' :
                    chart =  new google.visualization.BarChart(document.getElementById('chart'));
                    break;
                case 'Histogram' :
                    chart =  new google.visualization.Histogram(document.getElementById('chart'));
                    break;
                case 'LineChart' :
                    chart =  new google.visualization.LineChart(document.getElementById('chart'));
                    break;
                case 'AreaChart' :
                    chart =  new google.visualization.AreaChart(document.getElementById('chart'));
                    break;
                case 'AreaChart(stacked)' :
                    chart =  new google.visualization.AreaChart(document.getElementById('chart'));
                    options = {isStacked: true};
                    break;
                case 'ScatterChart' :
                    chart =  new google.visualization.ScatterChart(document.getElementById('chart'));
                    break;
                case 'BubbleChart' :
                    chart =  new google.visualization.BubbleChart(document.getElementById('chart'));
                    break;
                case 'PieChart' :
                    chart =  new google.visualization.PieChart(document.getElementById('chart'));
                    break;
                case 'CandlestickChart' :
                    chart =  new google.visualization.CandlestickChart(document.getElementById('chart'));
                    break;
                case 'Sankey' :
                    chart =  new google.visualization.Sankey(document.getElementById('chart'));
                    break;
                case 'TreeMap' :
                    chart =  new google.visualization.TreeMap(document.getElementById('chart'));
                    break;
                case 'Gauge' :
                    chart =  new google.visualization.Gauge(document.getElementById('chart'));
                    break;
                case 'Calendar' :
                    chart =  new google.visualization.Calendar(document.getElementById('chart'));
                    break;
            }
            chart.draw(data, options);

            $('#png').html('');
            if(chart.getImageURI()) {
                $('#png').html('<a target="_blank" href="' + chart.getImageURI() + '">Download chart image</a>');
            }
        }

        function handleQueryResponse(response) {
            if (response.isError()) {
                alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
                return;
            }
            data = response.getDataTable();

            updateColumnTable();
            updateTable();
            updateChart();
        }
</script>

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

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

  8. 「導入」をクリック

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

App_01.png

使い方の例

まず使用するファイルとシートを選択します。ここではスプレッドシートで例として用いたCanadian Parliament 2012 datasetを引き続き使用します。

Usage_01.png

しばらくするとシート内のデータが読み込まれます。
Columnsにデータの各列の情報、Tableにデータが表示されます。

Usage_02.png

データの形式がグラフと合っていればこの時点でChartにグラフが表示されますが、今回の例では表示できていません。

Usage_03.png

スプレッドシートの例で利用した以下のクエリを使って各政党の男女別の平均年齢を取得します。
次のクエリをテキストボックスに入力し「送信」ボタンを押します。クエリの列名はColumnsで調べる事ができます。

SELECT B, AVG(D) GROUP BY B PIVOT E

クエリを実行してしばらくするとデータが更新されます。クエリの結果が棒グラフを作成できる形式となっているためChartにグラフが表示されています。グラフの種類によっては「Download chart image」をクリックすることによってイメージとしてグラフを取得する事ができます。

Usage_04.png

Chart Typeを変更すると表示されるグラフが変わります。

Usage_05.png

グラフによって使用できるデータの形式が異なります。各グラフのデータの形式についてはGoogle Chartsのドキュメントを参照してください。

参考資料

Google Spreadsheet & Google Visualization API

Google Charts

CSS

182
184
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
182
184