Google visualization Script Issues

0
votes

I am trying to create a dashboard to represent some data I have in a google spreadsheet and I have followed the Google charts guides as best as I could (I am not a programmer). I have come up with the script below which is not working and I would like some help in getting it to work.

Here's the link to my spreadsheet: https://docs.google.com/spreadsheets/d/1L2wjSj0IYSrA-dwYYly6rjmtw_zMCB6l7FB5B-rcvZc/edit#gid=1303777890

The data I am trying to show as a column chart is in Sheet2 (Data2) columns D and E (highlighted), and I added that range to the url query "range=D:E"

The code that I have come up with is below. I just want to get it to show the chart. Any help is appreciated.

enter code here<html>


  // Load the Visualization API and the corechart package.
  google.charts.load('current', {'packages':['corechart']});

  // Set a callback to run when the Google Visualization API is loaded.
  google.charts.setOnLoadCallback(drawChart);

  // Callback that creates and populates a data table,
  // instantiates the pie chart, passes in the data and
  // draws it.

function drawSheetName() {
  var queryString = encodeURIComponent('SELECT D, E');

  var query = new google.visualization.Query(
      'https://docs.google.com/spreadsheets/d/1L2wjSj0IYSrA-dwYYly6rjmtw_zMCB6l7FB5B-rcvZc/edit#gid=1303777890range=D:E');
  query.send(handleSampleDataQueryResponse);
}

function handleSampleDataQueryResponse(response) {
  if (response.isError()) {
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }

  var data = response.getDataTable();
  var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
  chart.draw(data, { height: 400 });
}
}
1
google-apps-scriptgoogle-sheets

1 Answers

0
votes

I think I've found the problem. 

// Set a callback to run when the Google Visualization API is loaded.
  google.charts.setOnLoadCallback(drawChart);

  // Callback that creates and populates a data table,
  // instantiates the pie chart, passes in the data and
  // draws it.

function drawSheetName() {
  var queryString = encodeURIComponent('SELECT D, E');

  var query = new google.visualization.Query(
      'https://docs.google.com/spreadsheets/d/1L2wjSj0IYSrA-dwYYly6rjmtw_zMCB6l7FB5B-rcvZc/edit#gid=1303777890range=D:E');
  query.send(handleSampleDataQueryResponse);
}

You used the drawSheetName() but didn't call it in the google.charts.setOnLoadCallback().

Code Revision

google.charts.setOnLoadCallback(drawSheetName)

Then for the 

var query = new google.visualization.Query(
      'https://docs.google.com/spreadsheets/d/1L2wjSj0IYSrA-dwYYly6rjmtw_zMCB6l7FB5B-rcvZc/edit#gid=1303777890range=D:E');

Change the format to 

var query = new google.visualization.Query(
            'https://docs.google.com/spreadsheets/d/ID/gviz/tq?tq='+queryString);

Query Language Reference

The Google Visualization API Query Language lets you perform various data manipulations with the query to the data source.

Setting the Query in the Data Source URL

The query string can be added to the data source URL using the tq parameter. Setting the query in the URL parameter instead of in JavaScript allows you to easily use visualizations written by other developers, and still be able to customize the query.

The query string must be properly encoded as a URL parameter. You can encode a URL using the JavaScript encodeURIComponent function, or you can encode it by hand, using the encoding tool at the end of this section.

For additional information and sample: