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>
See Question&Answers more detail:
os 
