如何从电子表格创建包括饼图和范围选择过滤器的Google仪表板？ [英] How to create google dashboard including piechart and range select filter from a spreadsheet?
问题描述
有谁知道如何将信息中心绑定到Google电子表格的范围内？
在我当前的代码中，我假设仪表板可以像Google一样绑定到Google Spreadsheet的一个范围，但setDataTable函数调用给我一个很难的时间。
从表格名称和时间中只选择两列。
< html>
< head>
<！ - 加载AJAX API - >
< script type =text / javascriptsrc =https://www.google.com/jsapi>< / script>
< script type =text / javascript>
//加载可视化API和控件包。
google.load（'visualization'，'1.0'，{'packages'：['controls']}）;
//设置回调以在加载Google Visualization API时运行。
google.setOnLoadCallback（drawDashboard）;
//创建并填充数据表的回调，
//实例化仪表板，范围滑块和饼图，
//传入数据并绘制它。
函数drawDashboard（）{
//创建我们的数据表。
var data = google.visualization.arrayToDataTable（[
['Name'，'Donuts eaten']，
['Michael'，5]，
['Elisa'， 7]，
['Robert'，3]，
['John'，2]，
['Jessica'，6]，
['Aaron'，1] ，
['Margareth'，8]
]）;
//创建仪表板。
var dashboard = new google.visualization.Dashboard（
document.getElementById（'dashboard_div'））;
//创建一个范围滑块，传递一些选项
var donutRangeSlider = new google.visualization.ControlWrapper（{$ b $'controlType'：'NumberRangeFilter'，
' containerId'：'filter_div'，
'options'：{$ b $'filterColumnLabel'：'Donuts eaten'
}
}）;
//创建一个饼图，传递一些选项
var pieChart = new google.visualization.ChartWrapper（{$ b $'chartType'：'PieChart'，
' containerId'：'chart_div'，
'options'：{`在此处输入代码'
'width'：300，
'height'：300，$ b $'pieSliceText'：'值'，
'legend'：'right'
}
}）;
//建立依赖关系，声明'过滤'驱动'pieChart'，
//这样饼图将只显示通过
获得的条目//给定选择滑块范围。
dashboard.bind（donutRangeSlider，pieChart）;
//绘制仪表板。
dashboard.draw（data）;
}
< / script>
< / head>
< body>
<！ - 将保存仪表板的Div - >
< div id =dashboard_div>
<！ - 将容纳每个控件和图表的div - >
< div id =filter_div>< / div>
< div id =chart_div>< / div>
< / div>
< / body>
< / html>
所以，你只需要将Name /时间到数据表？
您必须在google服务器上转换数组，然后返回到HTML页面，然后转换为dataTable，如下所示：
in code.gs
function getTimes（）{
var playTimes = SpreadsheetApp.openById（1csv3OQ0IrVNyNIALcpqoLewccgYEEwErsS-Tp43IZfQ）。getSheetByName（players ）.getRange（ B1：E）的GetValues（）;
（playTimes）{
playTimes [i] = [playTimes [i] .splice（0,1）[0]，playTimes [i] .splice（2,1）[0] ]。
}
返回playTimes;
}
- >更改 google。 setOnLoadCallback（drawDashboard）; 到 google.setOnLoadCallback（loadPlayTimes）; ，也改变函数drawDashboard（） to 函数drawDashboard（playTimes）接受一个参数：
function loadPlayTimes（）{
google.script.run.withSuccessHandler（drawDashboard）.getTimes（）;
}
然后创建dataTable：
var data = google.visualization.arrayToDataTable（playTimes）;
Does anyone know how to bind the dashboard to a range from Google Spreadsheet?
In my current code, I am assuming that the dashboard can be bound to a range from Google Spreadsheet like so, but the setDataTable function call is giving me a hard time.
Select only two columns from the sheet name and time.
<html>
<head>
<!--Load the AJAX API-->
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
// Load the Visualization API and the controls package.
google.load('visualization', '1.0', {'packages':['controls']});
// Set a callback to run when the Google Visualization API is loaded.
google.setOnLoadCallback(drawDashboard);
// Callback that creates and populates a data table,
// instantiates a dashboard, a range slider and a pie chart,
// passes in the data and draws it.
function drawDashboard() {
// Create our data table.
var data = google.visualization.arrayToDataTable([
['Name', 'Donuts eaten'],
['Michael' , 5],
['Elisa', 7],
['Robert', 3],
['John', 2],
['Jessica', 6],
['Aaron', 1],
['Margareth', 8]
]);
// Create a dashboard.
var dashboard = new google.visualization.Dashboard(
document.getElementById('dashboard_div'));
// Create a range slider, passing some options
var donutRangeSlider = new google.visualization.ControlWrapper({
'controlType': 'NumberRangeFilter',
'containerId': 'filter_div',
'options': {
'filterColumnLabel': 'Donuts eaten'
}
});
// Create a pie chart, passing some options
var pieChart = new google.visualization.ChartWrapper({
'chartType': 'PieChart',
'containerId': 'chart_div',
'options': {`enter code here`
'width': 300,
'height': 300,
'pieSliceText': 'value',
'legend': 'right'
}
});
// Establish dependencies, declaring that 'filter' drives 'pieChart',
// so that the pie chart will only display entries that are let through
// given the chosen slider range.
dashboard.bind(donutRangeSlider, pieChart);
// Draw the dashboard.
dashboard.draw(data);
}
</script>
</head>
<body>
<!--Div that will hold the dashboard-->
<div id="dashboard_div">
<!--Divs that will hold each control and chart-->
<div id="filter_div"></div>
<div id="chart_div"></div>
</div>
</body>
</html>
So, all you want is to transform the Name/Time to a dataTable? You'll have to transform the array in the google server, return to the HTML page then transform to dataTable, as such:
in code.gs
function getTimes(){
var playTimes = SpreadsheetApp.openById("1csv3OQ0IrVNyNIALcpqoLewccgYEEwErsS-Tp43IZfQ").getSheetByName("players").getRange("B1:E").getValues();
for( i in playTimes ){
playTimes[i] = [ playTimes[i].splice(0, 1)[0], playTimes[i].splice(2, 1)[0] ];
}
return playTimes;
}
in the HTML page -> change the google.setOnLoadCallback(drawDashboard); to google.setOnLoadCallback(loadPlayTimes);, also change function drawDashboard() to function drawDashboard( playTimes ) to accept a parameter:
function loadPlayTimes(){
google.script.run.withSuccessHandler(drawDashboard).getTimes();
}
And create the dataTable as such:
var data = google.visualization.arrayToDataTable( playTimes );
这篇关于如何从电子表格创建包括饼图和范围选择过滤器的Google仪表板？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
