Timo Reichert Timo Reichert - 3 months ago 18
Javascript Question

Google Charts - query all rows, but only draw with 2 of them. How?

I'm trying to use data from a google spreadsheet to draw a pie chart (which is working). And when the user is clicking on a slice, it should show the values in different divs. This doesn't work because I'm only querying row A and D as you can see, because these are the ones that are used to create the Pie Chart. Now what I want to achieve is:

Only use 1 query and draw the pie with row A and D and use the same datatable to fill the variables. Can you help me? :)

google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawGID);
function drawGID() {
var populationPieQuery = encodeURIComponent('SELECT A, D LIMIT 10');
/*var serverQuery = encodeURIComponent('SELECT A, C, E, G, H, I, J, K, L, M, N, O LIMIT 20');*/

var queryPopulation = new google.visualization.Query(
'https://docs.google.com/spreadsheets/d/11zIoeZsSi8jPD993uzYYNyY1PaVd4rxIkP4IJ0mUH7U/gviz/tq?sheet=Sheet1&headers=1&tq=' + populationPieQuery);
/*var queryServer = new google.visualization.Query(
'https://docs.google.com/spreadsheets/d/11zIoeZsSi8jPD993uzYYNyY1PaVd4rxIkP4IJ0mUH7U/gviz/tq?sheet=Sheet1&headers=1&tq=' + serverQuery);*/
queryPopulation.send(populationPieQueryResponse);
/*queryServer.send(serverQueryResponse);*/
}

function populationPieQueryResponse(populationResponse) {
if (populationResponse.isError()) {
alert('Error in query: ' + populationResponse.getMessage() + ' ' + populationResponse.getDetailedMessage());
return;
}

// Listen for the 'select' event, and call my function selectHandler() when
// the user selects something on the chart.


var options = {
width: 700, height: 500,
legend: { position: 'right', alignment: 'center' },
chartArea: {width: '70%', height: '70%'},
backgroundColor: 'transparent',
pieStartAngle: 180,
};

var populationData = populationResponse.getDataTable().getValue();
/*var serverData = serverResponse.getDataTable();*/
var chart = new google.visualization.PieChart(document.getElementById('population_chart'));
google.visualization.events.addListener(chart, 'select', selectHandler);
chart.draw(populationData, options);
// The select handler. Call the chart's getSelection() method
function selectHandler() {
var selectedItem = chart.getSelection()[0];
if (selectedItem) {
console.log(serverData.getNumberOfColumns());
var server = serverData.getValue(selectedItem.row, 0);
var ranking = serverData.getValue(selectedItem.row, 1);
var population = serverData.getValue(selectedItem.row, 2);
var scripting = serverData.getValue(selectedItem.row, 3);
var latency_eu = serverData.getValue(selectedItem.row, 4);
var latency_us = serverData.getValue(selectedItem.row, 5);
var staff = serverData.getValue(selectedItem.row, 6);
var community = serverData.getValue(selectedItem.row, 7);
var type = serverData.getValue(selectedItem.row, 8);
var rates = serverData.getValue(selectedItem.row, 9);
var style = serverData.getValue(selectedItem.row, 10);
var language = serverData.getValue(selectedItem.row, 11);

document.getElementById("server").innerHTML=server;
document.getElementById("ranking").innerHTML=ranking;
document.getElementById("population").innerHTML=population;
document.getElementById("scripting").innerHTML=scripting;
document.getElementById("latency_eu").innerHTML=latency_eu;
document.getElementById("latency_us").innerHTML=latency_us;
document.getElementById("staff").innerHTML=staff;
document.getElementById("community").innerHTML=community;
document.getElementById("type").innerHTML=type;
document.getElementById("rates").innerHTML=rates;
document.getElementById("style").innerHTML=style;
document.getElementById("language").innerHTML=language;
}

}
}


Update

google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawGID);

function drawGID() {
var queryString = encodeURIComponent('SELECT A, D, B, E, F, G, H, I, J, K, L, M, N, O');
var query = new google.visualization.Query(
'https://docs.google.com/spreadsheets/d/11zIoeZsSi8jPD993uzYYNyY1PaVd4rxIkP4IJ0mUH7U/gviz/tq?sheet=Sheet1&headers=1&tq=' + queryString);
query.send(handleQueryResponse);
}

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

var options = {
width: 700, height: 500,
legend: { position: 'right', alignment: 'center' },
chartArea: {width: '70%', height: '70%'},
backgroundColor: 'transparent',
pieStartAngle: 180,
};

var data = response.getDataTable().getValue();
var populationView = new google.visualization.DataView(data);
populationView.setColumns([0,1]);


var chart = new google.visualization.PieChart(document.getElementById('population_chart'));
google.visualization.events.addListener(chart, 'select', selectHandler);

// The select handler. Call the chart's getSelection() method
function selectHandler() {
var selectedItem = chart.getSelection()[0];
if (selectedItem) {
var server = data.getValue(selectedItem.row, 0);
var ranking = data.getValue(selectedItem.row, 1);
var population = data.getValue(selectedItem.row, 1);
/*var scripting = data.getValue(selectedItem.row, 4);
var latency_eu = data.getValue(selectedItem.row, 5);
var latency_us = data.getValue(selectedItem.row, 6);
var staff = data.getValue(selectedItem.row, 7);
var community = data.getValue(selectedItem.row, 8);
var type = data.getValue(selectedItem.row, 9);
var rates = data.getValue(selectedItem.row, 10);
var style = data.getValue(selectedItem.row, 11);
var language = data.getValue(selectedItem.row, 12);*/

document.getElementById("server").innerHTML=server;
document.getElementById("ranking").innerHTML=ranking;
document.getElementById("population").innerHTML=population;
/*document.getElementById("scripting").innerHTML=scripting;
document.getElementById("latency_eu").innerHTML=latency_eu;
document.getElementById("latency_us").innerHTML=latency_us;
document.getElementById("staff").innerHTML=staff;
document.getElementById("community").innerHTML=community;
document.getElementById("type").innerHTML=type;
document.getElementById("rates").innerHTML=rates;
document.getElementById("style").innerHTML=style;
document.getElementById("language").innerHTML=language; */
}

}
chart.draw(populationView, options);
}

Answer

use a DataView to draw the PieChart

this will allow you to return all the data you need and still only pass two columns to the PieChart

assumes columns A and D are first in 'SELECT A, D, ...'

var populationData = populationResponse.getDataTable();

var populationView = new google.visualization.DataView(populationData);
populationView.setColumns([0,1]);


var chart = new google.visualization.PieChart(document.getElementById('population_chart'));
google.visualization.events.addListener(chart, 'select', selectHandler);
chart.draw(populationView, options);