0

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

Sai Ram
  • 43
  • 1
  • 9

1 Answers1

1

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);
WhiteHat
  • 59,912
  • 7
  • 51
  • 133
  • hope this helps, you can find errors like this in the browser's console, F12 on most... – WhiteHat Nov 28 '18 at 12:33
  • Dear @WhiteHat when i try to publish, its still empty/blank webpage. Not sure where i am getting it wrong. I made all the corrections as per your suggestion – Sai Ram Nov 29 '18 at 01:17
  • I made a change to my data source. Removed my Date column and changed to S.No's in order of 1,2,3,4.. Upon that change it worked. Why dint my code recognize date as my x-axis. – Sai Ram Nov 29 '18 at 02:37
  • Dear @WhiteHat, any comments on this problem with having date in 1st column. Once i changed the 1st column from date to a serial number my code was able to publish. – Sai Ram Dec 03 '18 at 01:46