我试图使用谷歌应用程序脚本/Javascript使一个仪表板，是基于从谷歌电子表格文件中的几个表拉出的数据。下面是开始的功能代码，可以成功绘制出8月份数据的图表。我想要的是将7月份的数据(表2)附加到图表中作为第二个系列。这可能吗?
如果我能弄清楚如何存储和访问每个月的数据表，我想我应该能够使用.join()方法来合并这些表。https://developers.google.com/chart/interactive/docs/reference?hl=en加入
感谢它提前任何帮助!
<html>
<head>
<title>
Test
</title>
<!--Load the AJAX API-->
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load('visualization', '1', {'packages': ['table', 'controls', 'corechart']});
google.setOnLoadCallback(initialize);
function initialize() {
var urlMonth = 'https://docs.google.com/spreadsheets/d/1_mSbT87MVWOiX2cfKX_x3dgTnToY5ulCWeGGCVn13iQ/gviz/tq?sheet=Sheet1&tq='
var queryStringMonthly = encodeURIComponent("SELECT B, sum(C), sum(D), sum(E), sum(F), sum(G), sum(H) GROUP BY B LABEL B 'Issue' ");
var queryMonthCurrent = new google.visualization.Query(urlMonth+ queryStringMonthly);
queryMonthCurrent.send(megaData);
}
function megaData(monthData) {
var monthData_table = monthData.getDataTable(firstRowIsHeader = true);
var monthData_tablePivot = new google.visualization.DataTable();
monthData_tablePivot.addColumn('string', 'Rep');
monthData_tablePivot.addColumn('number', 'August');
monthData_tablePivot.addColumn({type: 'string', label: 'Issue', role: 'annotation'});
var newRows = []; //
//iterate through each row
for (i = 0; i < monthData_table.getNumberOfRows(); i ++) {
var issue = monthData_table.getValue(i, 0);
//iterate through each column
for (j = 1; j < monthData_table.getNumberOfColumns(); j ++ ){
var newRow = []; // use an array to park [RepName1, tickets#, Issue1]
rep = monthData_table.getColumnLabel(j);
newRow.push(rep);
newRow.push(monthData_table.getValue(i, j));
newRow.push(issue);
newRows.push(newRow); //push each newRow to newRows
}
}
monthData_tablePivot.addRows( newRows);
// Create a dashboard.
var dashboard = new google.visualization.Dashboard(
document.getElementById('dashboard_div4'));
// Create filter
var issueFilter = new google.visualization.ControlWrapper({
'controlType': 'CategoryFilter',
'containerId': 'filter_div4',
'options': {
'filterColumnLabel': 'Issue',
'ui': {
'allowMultiple': false,
'allowNone': false,
}
},
//Set default filter value
'state': {'selectedValues': [monthData_table.getValue(0 , 1)]}
}
);
//create chart
var yearChart = new google.visualization.ChartWrapper({
'chartType': 'ColumnChart',
'containerId': 'current_year',
'options': {
'legend': {'position': 'right'},
//Set the fontsize of labels so they don't show up crazily
'annotations': {'textStyle': {'opacity': 0},
//use 'line' style so to remove the line pointer
'style': 'point',
'stemLength': 0
},
}
});
// bind charts and controls to dashboard
dashboard.bind(issueFilter, yearChart);
// Draw the dashboard.
dashboard.draw(monthData_tablePivot);
}
</script>
</head>
<body>
<!--Div that will hold the dashboard-->
<div id="dashboard_div4">
<div id="filter_div4"> </div>
<div id="current_year" style="align: right; width:1100px; height: 300px;">
</div>
</div>
</html>这是一个可以解决我的问题的方法。如果有人有更好的解决方案，我会很感激，如果你能在这里分享。基本上你可以在megaData(monthData)函数的末尾使用finished();来绕过异步javascript。所以这个结构看起来像下面这样:
function initialize() {
var urlMonth = 'https://docs.google.com/spreadsheets/d/1dTeqpX1If74h5_I3POrYkoktSm7FxV47iSUHshhlmI4/gviz/tq?sheet=Sheet1&tq='
var queryStringMonthly = encodeURIComponent("SELECT B, sum(C), sum(D), sum(E), sum(F), sum(G), sum(H) GROUP BY B LABEL B 'Issue' ");
var queryMonthCurrent = new google.visualization.Query(urlMonth+ queryStringMonthly);
queryMonthCurrent.send(megaData);
var urlOneMonthPrior = 'https://docs.google.com/spreadsheets/d/1dTeqpX1If74h5_I3POrYkoktSm7FxV47iSUHshhlmI4/gviz/tq?sheet=Sheet2&tq='
var queryStringOneMonthPrior = encodeURIComponent("SELECT B, sum(C), sum(D), sum(E) GROUP BY B LABEL B 'Issue' ");
var queryMonthOnePior = new google.visualization.Query(urlMonth+ queryStringOneMonthPrior);
queryMonthOnePior.send(megaData);
}
function megaData(monthData) {
//...
//use codes from the question post
finished();
}
function finished() {
if (dataTables.length != 2) {
//do nothing if data tables are not fully ready
return;
}
//blahblah do something with all the data tables
}