LINK GOOGLE SHEET DATA TO LINE CHART -

200 views

Skip to first unread message

sairam s

unread,

Nov 28, 2018, 4:09:09 AM11/28/18

to Google Visualization API

Hi All,

All the Line chart examples that i see online rely on building the data table manually in the app script and then constructing the line graph. Is there a means to Link the google sheet data to create line graph.

I have my sample code, but not sure where i am going wrong in developing a webscript to draw line chart using google sheet data. Below is my code.

// Code.gs
function doGet(e) {
  
  return HtmlService
  .createTemplateFromFile("index")
  .evaluate()
  .setTitle("Google Spreadsheet Chart")
  .setSandboxMode(HtmlService.SandboxMode.IFRAME);
  
}

function getSpreadsheetData() {
  
  var ssID   = "PUT_YOUR_SPREADSHEET_ID",
      sheet  = SpreadsheetApp.openById(ssID).getSheets()[0],
      data   = sheet.getDataRange().getValues();
  
  return data;
    
}

Below is the html file inside the script editor saved as index.html

<!DOCTYPE html>
<html>

<head>
  <script src="https://www.google.com/jsapi"></script>
</head>

<body>

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

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

    google.setOnLoadCallback(getSpreadsheetData);

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

    function drawChart(rows) {

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

      var data = google.visualization.arrayToDataTable(rows, false),
        chart = new google.visualization.LineChart(document.getElementById("curve_chart"));

      chart.draw(data, options);

    }
  </script>
</body>

</html>

your support and help is much appreciated.

Thanks

Regards

Ray Thomas

unread,

Nov 29, 2018, 9:58:12 PM11/29/18

to Google Visualization API

As most of what you need is in your index.html file anyway it may be an idea to remove the GS code altogether, or at least the getSpreadsheetData() function and use the API's query to connect to and get the data from the spreadsheet. There's a complete example at https://developers.google.com/chart/interactive/docs/spreadsheets

All you need is your Sheet ID, GID or sheet name and the columns you want to import and change the chart type from ColumnChart to LineChart. The API is good at importing the data it can find so you may not even need the querystring part. In the example use 'https://docs.google.com/spreadsheets/d/1XWJLkAwch5GXAt_7zOFDcg8Wm8Xv29_8PWuuW15qmAE/gviz/tq?gid=0&headers=1'; instead of 'https://docs.google.com/spreadsheets/d/1XWJLkAwch5GXAt_7zOFDcg8Wm8Xv29_8PWuuW15qmAE/gviz/tq?gid=0&headers=1&tq=' + queryString);

This won't work in the example, but could for your sheet. You'll see why if you look at the example's sheet at https://docs.google.com/spreadsheets/d/1XWJLkAwch5GXAt_7zOFDcg8Wm8Xv29_8PWuuW15qmAE/edit#gid=0

Also in your code you have <div id="main"></div> The empty div is to hold the chart, so you need to change it to <div id="curve_chart"></div> which is what you've used later on in your code.

I hope all that makes sense, standard charts are fairly easy to produce once you get the hang of what's happening.

Reply all

Reply to author

Forward

0 new messages