Manually pivoted DataTable BIN

by forssux

HTML

<script src="https://www.google.com/jsapi?fake=.js"></script>
Before manual pivot:
<div id="table1"></div>After manual pivot:
<div id="table2"></div>
       <div id="control1"></div>
        <div id="control2"></div>
        <div id="table_div"></div>
        <div id="chart1"></div>
        <div id="chart2"></div>
        <div id="chart3"></div>
<script type="text/javascript" src="https://www.google.com/jsapi?autoload={'modules':[{'name':'visualization','version':'1.1','packages':['corechart', 'controls']}]}"></script>
<div id="dashboard_div" style="border: 1px solid #ccc">
    <div id="control_div">
        <!-- Controls renders here -->
    </div>
    <div id="line_div">
        <!-- Line chart renders here -->
    </div>
  <div id="control1"></div>
        <div id="control2"></div>
        <div id="table1_div"></div>
        <div id="table2_div"></div>
        <div id="chart1"></div>
        <div id="chart2"></div>
        <div id="chart3"></div>
        <!-- Table renders here -->
 
    </div>
</div>

CSS

#dashboard_div, #chart_div, #control_div {
    width: 100%;
}

JavaScript

google.load('visualization', '1', {
    packages: ['table']
});
google.setOnLoadCallback(drawChart);


function drawChart() {
    ///// getting the data ////////

    var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1it-zPx4kR2mwh29ZJT6xVpuWes8BqdycHAFi9OJetfg/edit#gid=920631872');

    // Apply query language statement.
    query.setQuery('SELECT A,B,C  ORDER BY C');

    // Send the query with a callback function.
    query.send(handleQueryResponse);


}

///// ----------------------- //////

function handleQueryResponse(response) {
    var dashboard = new google.visualization.Dashboard(
    document.getElementById('dashboard_div'));
    if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
    }

    var data = response.getDataTable();


    var table1 = new google.visualization.Table(document.getElementById('table1'));
    table1.draw(data, {});

    /* pivot the data table
     * set column A as the first column in the view, 
     * then we have to separate out the C values into their own columns
     * according to the value of B, using a DataView with calculated columns
     */

    // get all the values in column B
    // this sorts the values in lexicographic order, so if you need a different order you have to build the array appropriately
    var distinctValues = data.getDistinctValues(1);

    var viewColumns = [0];
    var groupColumns = [];
    // build column arrays for the view and grouping
    for (var i = 0; i < distinctValues.length; i++) {
        viewColumns.push({
            type: 'number',
            label: distinctValues[i],
            calc: (function (x) {
                return function (dt, row) {
                    // return values of C only for the rows where B = distinctValues[i] (passed into the closure via x)
                    return (dt.getValue(row, 1) == x) ? dt.getValue(row, 2) : null;
                }
  ...