Menu
I'm trying to use various columns on a spreadsheet to chart running durations and try different styles by using Google Charts. I have problems when I try to query more than 2 columns. I want to use one column (B, for example) for attribution text, or labels, above the points on the graph. But when I use 'SELECT A, B, D', I get an error of 'All series on a given axis must be of the same data type'. How can I query/load multiple columns but only use the ones I choose as series in the chart? It looks like it automatically uses all for a series, so I think it's trying to plot a string and a date on the x axis. Successful chart (querying just column A and D), spreadsheet, and code are below. Thanks for any help.
I did try to implement the suggestion from this post but without success. I wasn't sure how to implement it with 3 columns vs. 2. Any help on that end would be much appreciated.
<html>
<head>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var queryString = encodeURIComponent('SELECT A, D');
var query = new google.visualization.Query(
'https://docs.google.com/spreadsheets/d/1xRV24c1itTkK1OWLo3KdpVhkjXpuMzi8lXi4UFHanso/gviz/tq?sheet=Sheet1&headers=1&tq=' + queryString);
query.send(handleSampleDataQueryResponse);
}
function handleSampleDataQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
var data = response.getDataTable();
var options = {
title: 'L\'s 2024 Cross Country Run Times ',
width: 900,
height: 500,
trendlines: {
0: {
color: 'blue',
}
},
vAxis: {
format: 'mm:ss'
}
};
var chart = new google.visualization.ScatterChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
</script>
</head>
<body>
<div id="chart_div" style="width: 100%; height: 500px;"></div>
</body>
</html>
679
as you suspected, when you draw the chart directly from the query response, it uses all columns.
the first column is used on the x-axis, and the remaining columns on the y-axis.
but in order to draw annotations, you must include an annotation column role.
this is where the data view comes into play.
with a data view, you can specify a specific role for a column or columns.
here, we select the annotation data (column B) as the last column in the select statement.
var queryString = encodeURIComponent('SELECT A,D,B');
then we create the data view, from the data table response, using the last column as the annotation.
// need to create dataview to use column as annotation
var view = new google.visualization.DataView(data);
view.setColumns([0, 1, { // <-- columns A (x), D (y)
calc: 'stringify',
sourceColumn: 2, // <-- column B as annotation
type: 'string',
role: 'annotation'
}]);
see following snippet.
I don't think it will actually run from a stack overflow snippet,
so here is working example of JSFiddle...
https://jsfiddle.net/WhiteHat/xcdb7o2k/2/
// load google charts
google.charts.load('current', {
packages: ['corechart']
}).then(drawChart);
function drawChart() {
// initialize query statement
var queryUri = 'https://docs.google.com/spreadsheets/d/1xRV24c1itTkK1OWLo3KdpVhkjXpuMzi8lXi4UFHanso/gviz/tq?sheet=Sheet1&headers=1&tq=';
// add select statement, set B as last column
var queryString = encodeURIComponent('SELECT A,D,B');
// initialize query
var query = new google.visualization.Query(queryUri + queryString);
// run query
query.send(function (response) {
// determine if error occurred
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}
// extract data from response
var data = response.getDataTable();
// need to create dataview to use column as annotation
var view = new google.visualization.DataView(data);
view.setColumns([0, 1, {
calc: 'stringify',
sourceColumn: 2,
type: 'string',
role: 'annotation'
}]);
// create options
var options = {
title: 'L\'s 2024 Cross Country Run Times',
width: 900,
height: 500,
trendlines: {
0: {
color: 'blue'
}
},
vAxis: {
format: 'mm:ss'
}
};
// draw chart
var chart = new google.visualization.ScatterChart(document.getElementById('chart_div'));
chart.draw(view, options);
});
}<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div" style="width: 100%; height: 500px;"></div>If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
