As is typical in my experience, times and durations are causing me trouble with graphs and Javascript. I'm graphing duration in mm:ss on the vAxis and date on the hAxis and trying to expand the min and max of the graph, so that it goes beyond the data points, above and below. For example, I'd like to have a min of 11:00 and a max of 14:00. However, I'm unable to do this. I read on one suggestion (cannot recall the URL and if it was stackoverflow and can't find right now) that I could convert the mm:ss to just seconds as a whole number, and use that for max and min. When I do that, I get this error: 'a.getTime is not a function'. I'm guessing that my numbers are not being carried over from my google sheet as a time duration...and maybe just a string. Of course when I use a colon in the max/min, I get an error that it's not expecting a ':'.
Here's the code, which has already been assisted by this site once. It works fine if the "viewWindow" portion is removed.
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
// 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',
viewWindow : {min : 660,max : 840}
}
};
// draw chart
var chart = new google.visualization.ScatterChart(document.getElementById('chart_div'));
chart.draw(view, options);
});
}
</script>
</head>
<body>
<div id="chart_div" style="width: 100%; height: 500px;"></div>
</body>
</html>
when the data is returned by the query, the Time column is returned as a datetime column by default.
we need to convert the column type to timeofday
from the docs...
The DataTable timeofday column data type takes an array of either 3 or 4 numbers, representing hours, minutes, seconds, and optionally milliseconds, respectively. Using timeofday is different than using date and datetime in that the values are not specific to a date, whereas date and datetime always specify a date.
to convert the column to timeofday, we can replace the column's index in the data view by another calculation.
var view = new google.visualization.DataView(data);
view.setColumns([0, { // <-- add calculation to convert datetime to timeofday
calc: function (dt, row) {
var rowDate = dt.getValue(row, 1);
var timeofday = [
rowDate.getHours(),
rowDate.getMinutes(),
rowDate.getSeconds()
];
return timeofday;
},
label: data.getColumnLabel(1),
type: 'timeofday'
}, {
calc: 'stringify',
sourceColumn: 2,
type: 'string',
role: 'annotation'
}]);
then we can represent the preferred view window as timeofday values
viewWindow: {
min : [11, 0, 0],
max : [16, 0, 0]
}
however, the time value in the spreadsheet is formatted as follows...
0:13:11
so this comes across as 0 hours, 13 minutes, and 11 seconds.
I would recommend re-formatting the time values as 13:11:00
I've create a JS Fiddle for example purposes using the time values as-is,
using the following view window.
viewWindow: {
min : [0, 11, 0],
max : [0, 16, 0]
}
https://jsfiddle.net/WhiteHat/xcdb7o2k/16/
See following snippet.
// 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, {
calc: function (dt, row) {
var rowDate = dt.getValue(row, 1);
var timeofday = [
rowDate.getHours(),
rowDate.getMinutes(),
rowDate.getSeconds()
];
return timeofday;
},
label: data.getColumnLabel(1),
type: 'timeofday'
}, {
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',
viewWindow: {
min : [11, 0, 0],
max : [16, 0, 0]
}
}
};
// draw chart
var chart = new google.visualization.ScatterChart(document.getElementById('chart_div'));
chart.draw(view, options);
});
}<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div" style="width: 100%; height: 500px;"></div>