CODEWITHSUNDEEP
google-sheetsgoogle-visualization
Zeke
Zeke

Reputation: 25

How to query google spreadsheet to create a datatable for google charts service

I'm trying to query a google spreadsheet to build a datatable for google chart services. The ultimate goal is to put graphs from multiple spreadsheets into a page with the HTML service.

function initialize() {
        var opts = {sendMethod: 'auto'};
        var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1LFhcZ5_vdghyWhuDNpnp__x2FcA_aUEIvFWOasRvEaA/edit#gid=0', opts);
        query.setQuery('select A, select B');
        query.send(handleQueryResponse);
        }

        function handleQueryResponse(response) {
          if (response.isError()) {
            alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
            return;
            }
        var data = response.getDataTable();

        var options = {'title':'How Much Pizza I Ate Last Night',
                       'width':400,
                       'height':300};
        var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
        chart.draw(data, options);
        }

When I build the data table manually with .addRows and .addColumn it works fine. I'm guessing this means that somehow I'm not getting a datatable back from my query. I have no idea why. Any suggestions?

Upvotes: 1

Views: 1012

Answers (1)

WhiteHat
WhiteHat

Reputation: 61222

the "select statement" should be...

select A, B

vs.

select A, select B

see following, working snippet...

google.charts.load('current', {
  'callback': initialize,
  'packages': ['corechart']
});

function initialize() {
  var opts = {sendMethod: 'auto'};
  var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1LFhcZ5_vdghyWhuDNpnp__x2FcA_aUEIvFWOasRvEaA/edit#gid=0', opts);
  query.setQuery('select A, B');
  query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
  if (response.isError()) {
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }
  var data = response.getDataTable();

  var options = {
    'title': 'How Much Pizza I Ate Last Night',
    'width': 400,
    'height': 300
  };
  var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
  chart.draw(data, options);
}
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>

Upvotes: 1

Related Questions