Line Chart creation using google script - Google sheet as Data source

Question

Hi Have a google sheet data whose data looks like this

DATE              LSL LCL DATA UCL USL
16 - Nov - 2018     1  3   2.3   7  9
17 - Nov - 2018     1  3   3.1   7  9
18 - Nov - 2018     1  3   2.7   7  9
19 - Nov - 2018     1  3   4.9   7  9
20 - Nov - 2018     1  3   5     7  9
21 - Nov - 2018     1  3   3     7  9
22 - Nov - 2018     1  3   10    7  9
23 - Nov - 2018     1  3   7.8   7  9
24 - Nov - 2018     1  3   4.5   7  9
25 - Nov - 2018     1  3   5.4   7  9
26 - Nov - 2018     1  3   2.2   7  9
27 - Nov - 2018     1  3   4.9   7  9
28 - Nov - 2018     1  3   5.8   7  9
29 - Nov - 2018     1  3   4.9   7  9

I wish to develop a web script/google script to draw a line chart making use of the data from a google sheet. I dont wish to construct a data table in the app script and build the chart but rather build the chart directly by sourcing the data from the google sheet.

This is the code i developed.

1st code - which is a .gs file - FILE NAME : Code.gs

function doGet(e) {

  return HtmlService
    .createTemplateFromFile("index")
    .evaluate()
    .setTitle("Google Spreadsheet Chart")
    .setSandboxMode(HtmlService.SandboxMode.IFRAME);

}

function getSpreadsheetData() {

  var ssID = "1WFV48PNNGw9Pvrj9dQ1vYD-kF1zvxMo_02VIbKBYicQ",
    sheet = SpreadsheetApp.openById(ssID).getSheets()[0],
    data = sheet.getDataRange().getValues();

  return data;

}

2nd FIle - HTML. File Name: index.html

Code as below.

<!DOCTYPE html>
<html>

<head>

<script src="https://www.gstatic.com/charts/loader.js"></script>
</head>

<body>

  <div id="main"></div>

  <script>
    google.charts.load('current', {
    packages: ['corechart', 'line']
});

    google.charts.setOnLoadCallback(getSpreadsheetData);

    function getSpreadsheetData() {
    google.script.run.withSuccessHandler(drawChart).getSpreadsheetData();
}

    function drawChart(rows) {

    var options = {
    title: 'Line Chart',
    legend: 'none',
    chartArea: {
    width: '60%'
},
        
    vAxis: {
    textStyle: {
    fontFamily: 'Arial',
    fontSize: 12
    }
   }
 };

    var data = google.visualization.arrayToDataTable(rows, false),
    chart = new 
    google.visualization.LineChart(document.getElementById("main"));
    chart.draw(data, options);
    }
</script>
</body>

</html>

Not sure where i am getting it wrong. When i try to publish, the dashboard is empty. Any sort of help is much appreciated.

Expected outcome is

Expected Result

WhiteHat WhiteHat · Accepted Answer · 2018-11-28T12:33:21

in the html, you have a div with id = "main"

<div id="main"></div>

however, in the javascript, you're trying to draw the chart in a container with id = "curve_chart"

chart = new google.visualization.LineChart(document.getElementById("curve_chart"));

the ids need to match

also, recommend cleaning up the white space in the html,
i've seen this cause problems as well

from...

<
div id = "main" > < /div>

to...

<div id="main"></div>

note: recommend using loader.js to load the library, vs. jsapi

according to the release notes...

The version of Google Charts that remains available via the jsapi loader is no longer being updated consistently. Please use the new gstatic loader.js from now on.

<script src="https://www.gstatic.com/charts/loader.js"></script>

this will only change the load statement...

google.charts.load('current', {
  packages: ['corechart', 'line']
});
google.charts.setOnLoadCallback(getSpreadsheetData);

Line Chart creation using google script - Google sheet as Data source

1 Answers