导出到Apps Script Webapp的图表数据为空 [英] Chart data exported to an Apps Script webapp is null
问题描述
我有一个Google表格，该数据库正在用于我的团队正在处理的项目.我要做的是使用这些数据在Google网站上建立不同的屏幕.例如，我已经能够添加一个显示所有活动项目的下拉列表.选择项目后，将返回并显示HTML表.
I have a Google Sheet that I’m using a database for projects my team is working on. What I want to do is use that data to build out different screens on a Google Site. For example, I have been able to add a drop-down list that shows all of the active projects. When a project is selected, an HTML table is returned and displayed.
现在我要添加的是将甘特图添加到客户端HTML，并从同一Google表格中提取数据.我从展示团队的能力中推断出很多东西，并使用 gviz文档作为图表信息的资源.我的大多数代码都能正常工作，但是我无法将图表数据从表格"端发送到客户端HTML.
Now what I’m trying to add is a Gantt chart to the client HTML, with data pulled from the same Google Sheet. I've extrapolated a lot from what I was able to do to display the teams and used the gviz documentation as a resource for the chart info. Most of my code works well, but I'm having trouble sending the chart data from the Sheets side to the client HTML.
在我的图表构建代码中，我有一个Logger.log语句，该语句表明chartData是Array，并且它具有我期望的数据:
Within my chart building code, I have a Logger.log statement, that shows that chartData is an Array and that it has the data I am expecting:
function buildChart(project) {
var detailsSheet = ss.getSheetByName("Details");
var details = detailsSheet.getRange(2, 1, detailsSheet.getLastRow(), detailsSheet.getLastColumn()).getValues();
// get the list of teams working on the selected project
var teams = getTeamsFromProjects(project, details); // Works, not shown.
// get the list of teams, without the category
var sendTeams = new Array();
for (l in teams) {
var lRow = teams[l];
sendTeams.push(lRow[0]);
}
// get the projects that the teams are working on
var projectList = getProjectsFromTeams(sendTeams, details); // Works, not shown.
var chartData = getChartDataFromProjects(projectList, details); // Works, not shown.
Logger.log(chartData); // this shows that my data is there, in an array
return chartData;
}
在webapp的HTML中，我有一个console.log("HTML: " + HTML)可以正确显示返回的HTML(通过.gs函数displayTeams()，它看起来是正确的.同样在HTML中，我有一个console.log("chart data: " + chartData)应该显示返回的数组.但是，控制台说chartData为NULL.
Within the webapp's HTML, I have a console.log("HTML: " + HTML) that correctly shows the returned HTML (from a .gs function, displayTeams(), and it looks correct. Also within the HTML I have a console.log("chart data: " + chartData) that should show the returned array. However, the console says that that chartData is NULL.
我的问题是，为什么我可以从Apps Script Logger.log()语句(即在返回之前)看到chartData作为NULL返回(或至少写入到Webapp的控制台日志中)?数据是正确的数组吗?
My question is, why is chartData being returned (or at least written to the webapp's console log) as NULL, when I can see from the Apps Script Logger.log() statement -- just before it’s returned -- that the data is correctly an array?
一些相关的HTML文件:
Some of my relevant HTML file:
<!DOCTYPE html>
<html>
<style>
table, th, td {
border: 5px solid White;
}
th {
font-weight: bold;
}
</style>
<head>
<base target="_top">
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js">
</script>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js">
</script>
<script>
// get the list of Open Projects for the Drop Down
$(function() {
google.script.run.withSuccessHandler(buildProjectList)
.getProjects();
});
function buildProjectList(options) {
var list = $('#projectList');
list.empty();
for (var i = 0; i < options.length; i++) {
list.append('<option value="' + options[i] + '">' + options[i] + '</option>');
}
}
// function called when a Project is selected from the Drop Down
function showResults() {
var selectedProject = $('#projectList').val();
google.script.run.withSuccessHandler(displayTeams)
.buildTeams(selectedProject);
google.script.run.withSuccessHandler(drawChart)
.buildChart(selectedProject); //THIS IS MY PROBLEM STATEMENT
}
// add the teams to the div
function displayTeams(html) {
console.log("html: " + html);
$('div.results_div').html(html);
}
// add the chart to the div (I HOPE)
google.charts.load('current', {'packages':['gantt']});
function drawChart(chartData) {
console.log("chart data: " + chartData); // chartData is missing here
var data = new google.visualization.DataTable();
data.addColumn('string', 'Task ID');
data.addColumn('string', 'Task Name');
data.addColumn('string', 'Resource');
data.addColumn('date', 'Start Date');
data.addColumn('date', 'End Date');
data.addColumn('number', 'Duration');
data.addColumn('number', 'Percent Complete');
data.addColumn('string', 'Dependencies');
data.addRows(chartData);
var options = {
height: 400,
gantt: {
trackHeight: 30
}
}
var chart = new google.visualization.Gantt(document.getElementByClassName("chart_div"));
chart.draw(data, options);
}
</script>
</head>
<body>
Select a Project from this list<br><br>
Projects:<br>
<select onchange='showResults();' id="projectList" name="project"></select>
<br>
<div class="results_div"></div>
<br>
<div class="chart_div"></div>
</body>
</html>
推荐答案
You probably are sending incompatible datatypes: per the client-server communication documentation, requests with Dates will fail.
我对google.script.run进行通信的首选方法是:1)通过Date().getTime()以毫秒为单位发送所有Date，因为这也可以避免时区问题和与浏览器相关的日期字符串解析差异.然后在客户端中，您可以通过调用new Date(milliseconds_value)，即
My preferred method for google.script.run communication is to 1) send all Dates as milliseconds, via Date().getTime(), as this will also avoid timezone issues and browser-dependent datestring parsing differences. In the client then, you can remap your input data back to a Date object by calling new Date(milliseconds_value), i.e.
function successHandler(rectangularArrayData) {
// Array indices of column values that need to be converted back to Date objects.
const dateIndices = [0, 3, 8 /**, etc... */];
// In general, pass your received data into a transformation function. This particular
// example assumes you only need to remap milliseconds to Date objects.
const chartData = rectangularArrayData.map(function (row) {
return row.map(function (col, index) {
// If this column index should be a date, make it a Date from the milliseconds input.
// Otherwise, do nothing to it.
return (dateIndices.indexOf(index) === -1) ? col : new Date(col);
});
});
...
}
和2)在发送前将其序列化为JSON字符串.有时，您只是发送了过于复杂的对象，并且在return之前通过(JSON.stringify)将其转换为字符串有助于确保该对象不会在连线上混乱.在客户端中，您的成功处理程序只需通过JSON.parse()进行实例化.
and 2) serialize it to a JSON string before sending it. Sometimes you're just sending too complex of an object, and converting it to a string via (JSON.stringify) before return helps ensure it doesn't get mangled on the wire. In the client, your success handler just needs to reinstantiate it via JSON.parse().
您还有其他几件事(例如不明智且不可携带的使用new Array()而不是[] ，或者执行多次比较以检查多个文本值之一的特定变量，而不是使用Array#indexOf方法). ，但它们不在您的问题范围内，而不是您的问题的根源.
There are a couple other things you do (like ill-advised and non-portable for...in iteration of Arrays, using new Array() instead of [], or performing multiple comparisons to check a specific variable for one of several text values instead of using the Array#indexOf method) that can be improved, but they're outside the scope of your question and not the source of your issue.
这篇关于导出到Apps Script Webapp的图表数据为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
