Pivoted DataTable other charts

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='annotationchart_div' style='width: 900px; height: 500px;'></div>
<br />
<br />
<script type="text/javascript" src="https://www.google.com/jsapi?autoload={'modules':[{'name':'visualization','version':'1.1','packages':['corechart', 'controls']}]}"></script>
<script type="text/javascript" src="https://www.google.com/jsapi?autoload={'modules':[{'name':'visualization','version':'1.1','packages':['annotationchart']}]}"></script>
<div id="dashboard_div" style="border: 1px solid #ccc">
    <br />
    <div id="chart_div">
        <!-- Line chart renders here -->
    </div>
    <div id="ChartRangecontrol_div">
        <!-- Controls renders here -->
    </div>
    <div id="linechart_div">
        <!-- Line chart renders here -->
    </div>
    <div id="namePicker_div">
        <!-- Table renders here -->
    </div>
    <div id="tablechart_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/1XGqGIs36krgKbRjufKJJNRdu1NUBZlgIJek2ojnOo2Y/edit#gid=967698286');

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