导出到 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()code>，它看起来是正确的.同样在 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 脚本 Logger.log() 声明——就在它返回之前——数据正确地是一个数组?
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>
推荐答案
您可能正在发送不兼容的数据类型:根据 客户端-服务器通信 文档，带有 Date 的请求将失败.
You probably are sending incompatible datatypes: per the client-server communication documentation, requests with Dates will fail.
我首选的 google.script.run 通信方法是 1) 通过 Date().getTime()<以毫秒为单位发送所有 Date/code>，因为这也将避免时区问题和浏览器相关的日期字符串解析差异.然后在客户端，您可以通过调用 new Date(milliseconds_value) 将输入数据重新映射回 Date 对象，即
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().
您还有其他一些事情要做(例如不明智和不可移植的for...in Arrays 的迭代，使用 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屋！
