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.
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.
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;
}
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.
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?
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.
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);
});
});
...
}
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().
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.