I have a spreadsheet that generically looks like this:
Date Families
1/2/2016 3
1/3/2016 4
1/4/2016 5
What I'd like to do is create a table from it that's populated with queries for the following values:
My preferred way of doing this would be to set a variable for each query and then manually create a table using those variables.
For example:
var sum1 = value from Query1
var sum2 = value from Query2
var sum3 = value from Query3
var sum4 = value from Query4
var data = new.google.visualization.DataTable({
data.AddColumn('string', 'This Month');
data.AddColumn('string', 'Last Month');
data.AddColumn('string', 'Year to Date');
data.AddColumn('string', 'All Time');
data.AddRow([sum1, sum2, sum3, sum4])
})
var chart = new.google.visualization.Table();
chart.draw(data);
To give the following table:
'This Month' 'Last Month' 'Year to Date' 'All Time'
sum1 sum2 sum3 sum4
However, the big problem is that it seems you can only set one callback for each query, so getting something like this seems like it might require nested callbacks for each query, with each one getting turned into a datatable and then finally joined at the end.
For example:
var q1 = google.visualization.Query(URL);
q1.setQuery(QueryStatement1);
q1.send(function (r1) {
var data1 = r1.getDataTable();
var q2 = google.visualization.Query(URL);
q2.setQuery(QueryStatement2);
q2.send(function(r2) {
var data2 = r2.getDataTable();
var joinedData = google.visualization.data.join(
data1,
data2,
'full',
[0,0],
[[0,0], [0,0]]
);
}
}
The latter way seems a lot more complicated and would probably lend itself to difficulties with formatting. It also seems redundant to repeatedly call the same URL over and over.
The page this is going to go on will do similar things to 3 different columns, so that'd be getting a query 12 times from the same spreadsheet.
So, StackOverflow, I'd like to wrap up this question in two parts.
PART 1: Is it possible to avoid the latter method to create this table?
PART 2: If no, what's the best way to construct google.visualization.data.join() to achieve the desired result?
Thank you.
use a DataView with calculated columns for each category...
var view = new google.visualization.DataView(response.getDataTable());
view.setColumns([0,
{
calc: function (dt, r) {
if ((dt.getValue(r, 0).getMonth() === (new Date()).getMonth()) &&
(dt.getValue(r, 0).getFullYear() === (new Date()).getFullYear())) {
return dt.getValue(r, 1);
}
return null;
},
type: 'number',
label: 'This Month'
},
...
then use the group() method to aggregate the results...
var aggData = google.visualization.data.group(
view,
[{
column: 0,
label: 'Total',
modifier: function () {
return 'Total';
},
type: 'string'
}],
[
{
column: 1,
label: view.getColumnLabel(1),
aggregation: google.visualization.data.sum,
type: 'number'
},
...
see following working snippet...
google.charts.load('current', {
callback: drawChart,
packages: ['table']
});
function drawChart() {
var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1-UbQQMofRecLpO9zUYoscQxo4Wi50N8YnmQmbW1lNJ0/edit?usp=sharing');
query.setQuery('select A, B');
query.send(function (response) {
var view = new google.visualization.DataView(response.getDataTable());
view.setColumns([0,
{
calc: function (dt, r) {
if ((dt.getValue(r, 0).getMonth() === (new Date()).getMonth()) &&
(dt.getValue(r, 0).getFullYear() === (new Date()).getFullYear())) {
return dt.getValue(r, 1);
}
return null;
},
type: 'number',
label: 'This Month'
},
{
calc: function (dt, r) {
var curDate = new Date();
var rowDate = dt.getValue(r, 0);
var thisMonth = new Date(curDate.getFullYear(), curDate.getMonth(), 1);
var lastMonth = new Date(curDate.getFullYear(), curDate.getMonth(), 0);
lastMonth = new Date(lastMonth.getFullYear(), lastMonth.getMonth(), 1);
if ((rowDate.getTime() >= lastMonth.getTime()) &&
(rowDate.getTime() < thisMonth.getTime())) {
return dt.getValue(r, 1);
}
return null;
},
type: 'number',
label: 'Last Month'
},
{
calc: function (dt, r) {
if (dt.getValue(r, 0).getYear() === (new Date()).getYear()) {
return dt.getValue(r, 1);
}
return null;
},
type: 'number',
label: 'Year to Date'
},
{
calc: function (dt, r) {
return dt.getValue(r, 1);
},
type: 'number',
label: 'All Time'
}
]);
var aggData = google.visualization.data.group(
view,
[{
column: 0,
label: 'Total',
modifier: function () {
return 'Total';
},
type: 'string'
}],
[
{
column: 1,
label: view.getColumnLabel(1),
aggregation: google.visualization.data.sum,
type: 'number'
},
{
column: 2,
label: view.getColumnLabel(2),
aggregation: google.visualization.data.sum,
type: 'number'
},
{
column: 3,
label: view.getColumnLabel(3),
aggregation: google.visualization.data.sum,
type: 'number'
},
{
column: 4,
label: view.getColumnLabel(4),
aggregation: google.visualization.data.sum,
type: 'number'
}
]
);
var chartDiv = document.getElementById('chart_div');
var chart = new google.visualization.Table(chartDiv);
chart.draw(aggData);
});
}<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>