Google Visualization - Specify Type from Query

Question

I am creating a dashboard that queries a published google spreadsheet as the dataTable. The charts (column, table, and candlestick) show up just fine. However, I have added a listener event for 'sort' on the table and would like this to sort the corresponding data in the column and candlestick charts. However, when I sort the table by clicking on the header, the charts show the following error in red:

"All data columns of the same series must be of the same data type".

My spreadsheet contains a string for the first column and numbers for the other columns. However, I assume that I am getting this error message because the query is not returning the data as 'string' in the first column and 'number' in the other columns. Can I specify this? If so, how? Thanks.

EDIT: Here is my code...the addListener event at the bottom is causing the issue:

var query = new google.visualization.Query('https://docs.google.com/spreadsheet/pub?key=0AukymWvA6LlzdHpwblVtSmU3ZXJOMGhUVFZiV3NnSkE&single=true&gid=0&output=html');

query.setQuery('SELECT A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T');

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

    // CategoryFilter for Grade
    var gradeFilter = new google.visualization.ControlWrapper({
        controlType: 'CategoryFilter',
        containerId: 'gradeFilter_div',
        options: {
            filterColumnIndex: 3
        }
    });

    // CategoryFilter for School
    var schoolFilter = new google.visualization.ControlWrapper({
        controlType: 'CategoryFilter',
        containerId: 'schoolFilter_div',
        options: {
            filterColumnIndex: 16
        }
    });

    // CategoryFilter for Teacher
    var teacherFilter = new google.visualization.ControlWrapper({
        controlType: 'CategoryFilter',
        containerId: 'teacherFilter_div',
        options: {
            filterColumnIndex: 17
        }
    });

    // CategoryFilter for Entity
    var entityFilter = new google.visualization.ControlWrapper({
        controlType: 'CategoryFilter',
        containerId: 'entityFilter_div',
        options: {
            filterColumnIndex: 18
        }
    });

    var numberSlider = new google.visualization.ControlWrapper({
        controlType: 'NumberRangeFilter',
        containerId: 'rNumberRangeFilter_div',
        options: {
            filterColumnIndex: 19
        }
    });

    // create a Table visualization
    var rBubbleChart = new google.visualization.ChartWrapper({
        chartType: 'BubbleChart',
        containerId: 'rBubble_div',
        options: {
            title: 'Comparison of Percentiles',
            height: 500,
            width: 500,
            chartArea:{left:"10%",top:"10%",width:"80%",height:"80%"},
            backgroundColor: 'transparent',
            bubble: {opacity: 0.6, stroke: 'transparent', textStyle: {fontSize: 8, color: 'transparent'}},
            hAxis: {minValue: 0, maxValue: 100, gridlines: {count: 11, color: '#EEEEEE'}, title: '2013 Percentile', titleTextStyle: {fontSize:10}, textStyle: {fontSize:10}},
            vAxis: {minValue: 0, maxValue: 100, gridlines: {count: 11, color: '#EEEEEE'}, title: '2014 Percentile', titleTextStyle: {fontSize:10}, textStyle: {fontSize:10}},
            colors: ['#a4c2f4','#89B0F0','#6d9eeb','#558BE2','#3c78d8','#2767D2','#1155cc'],
            legend: {position: 'in', alignment: 'center', textStyle: {fontSize:10}},
            animation: {duration:1500, easing:'out'},
            sizeAxis: {minSize: 2, minValue: 5, maxSize: 30, maxValue: 500}
        },
        view: {columns: [0, 1, 2, 3, 4]}
    });

    //Draw chart with y=x line
    var hackChart = new google.visualization.ChartWrapper({
        chartType: 'LineChart',
        containerId: 'hack_chart_div',
        dataTable: [['x', 'y'],[0, 0], [100, 100]],
        options: {
            height: 500,
            width: 500,
            chartArea:{left:"10%",top:"10%",width:"80%",height:"80%"},
            hAxis: {minValue: 0, maxValue: 100, textPosition: 'none', gridlines: {count: 0}, baselineColor: 'none'},
            vAxis: {minValue: 0, maxValue: 100, textPosition: 'none', gridlines: {count: 0}, baselineColor: 'none'},
            colors: ['black'],
            pointSize: 0,
            lineWidth: 1,
            enableInteractivity: false,
            legend: {position: 'none'},
        }
    });
    hackChart.draw();

    var rCandlestickChart = new google.visualization.ChartWrapper({
        chartType: 'CandlestickChart',
        containerId: 'rCandle_div',
        options: {
            title: 'Distribution of Performance (Interquartile Range)',
            height: 250,
            width: 500,
            chartArea:{left:"10%",top:"10%",width:"80%",height:"60%"},
            hAxis: {textStyle: {fontSize:10}},
            vAxis: {minValue: 0, maxValue: 100, title: 'Percentile', titleTextStyle: {fontSize:10}, textStyle: {fontSize:10}},
            legend: {position: 'in'},
            animation: {duration:1500, easing:'out'},
            colors: ['#a4c2f4','#3c78d8']
        },
        view: {columns:[5, 6, 7, 8, 9, 10, 11, 12, 13]}
    });

    var rColumnChart = new google.visualization.ChartWrapper({
        chartType: 'ColumnChart',
        containerId: 'rColumn_div',
        options: {
            title: 'Percent Satisfactory',
            height: 250,
            width: 500,
            chartArea:{left:"10%",top:"10%",width:"80%",height:"60%"},
            hAxis: {textStyle: {fontSize:10}},
            vAxis: {minValue: 0, maxValue: 100, title: '% Satisfactory', titleTextStyle: {fontSize:10}, textStyle: {fontSize:10}},
            legend: {position: 'in'},
            animation: {duration:1500, easing:'out'},
            colors: ['#a4c2f4','#3c78d8']
        },
        view: {columns:[5, 14, 15]}
    });

    var rTableChart = new google.visualization.ChartWrapper({
        chartType: 'Table',
        containerId: 'rTable_div',
        options: {
            width: '300px',
            height: '500px',
            allowHtml: true,
            cssClassNames: {tableCell: 'styleRows', headerRow: 'styleHeader'}
        },
        view: {columns: [16, 5, 19]}
    });

    var formatter = new google.visualization.BarFormat({width: 100, drawZeroLine: true, min: -20, max: 20});
    formatter.format(data, 19);

    // Create the dashboard.
    var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard')).
        bind(entityFilter, [rBubbleChart, rCandlestickChart, rColumnChart, rTableChart]).
        bind(schoolFilter, gradeFilter).bind(gradeFilter, teacherFilter).
        bind(teacherFilter, [rBubbleChart, rCandlestickChart, rColumnChart, rTableChart]).
        bind(numberSlider, [rBubbleChart, rCandlestickChart, rColumnChart, rTableChart]).
        draw(data);


 google.visualization.events.addListener(rTableChart, 'ready', function() {
        google.visualization.events.addListener(rTableChart.getChart(), 'sort', function(event) {

            data.sort([{column: event.column, desc: !event.ascending}]);
            boxchartObject = rCandlestickChart.getChart();
            boxchartObject.draw(data);

            columnChartObject = rColumnChart.getChart();
            columnChartObject.draw(data);
          });
    });

Answer 1

The problem is in the way you redraw the charts. By calling the ChartWrapper#getChart#draw method, you are bypassing all of the ChartWrapper's parameters - including the view parameter which specifies the columns to use. This is why you get the data type error. You need to redraw the Dashboard (to avoid a clash between the sorting and the filters). Use this:

var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard'));
google.visualization.events.addListener(rTableChart, 'ready', function() {
    google.visualization.events.addListener(rTableChart.getChart(), 'sort', function(event) {
        // set the sorting options in the table so they are preserved on redraw
        rTableChart.setOption('sortColumn', event.column);
        rTableChart.setOption('sortAscending', event.ascending);
        // convert the Table view column to a DataTable column
        var col = rTableChart.getView().columns[event.column];
        // sort the DataTable
        data.sort([{column: col, desc: !event.ascending}]);
        // redraw the dashboard
        dashboard.draw(data);
    });
});

dashboard.bind(schoolFilter, gradeFilter)
.bind(gradeFilter, teacherFilter)
.bind([entityFilter, teacherFilter, numberSlider], [rBubbleChart, rCandlestickChart, rColumnChart, rTableChart])
.draw(data);

To ensure that the sort event handler is set up properly, you should set up the Table wrapper's ready event before calling the Dashboard's draw method.

Update working example: http://jsfiddle.net/asgallant/t5rkJ/4/