1

I’m trying to make a script using the Google Visualization App Geomap. In the example code on the Google Developers page, they are using a hard coded table. What I want to do, is to make code use data from a Google Spreadsheet connected to a Google Form. More specifically, I want to use the data of the spreadsheet tab «countries» from the following spreadsheet: https://docs.google.com/spreadsheets/d/1l77TXctG6mgva1ggs3iR3eBNx949hzn9SiVjki1v59I/edit?usp=sharing.

Somehow this does not work, and I get the error messages listed below.

How can I collect the data from the spreadsheet in the right format? I have attempted to use this procedure to collect the data from the spreadsheet: https://google-developers.appspot.com/chart/interactive/docs/spreadsheets.

Error message on web page:

"Data table is not defined"

Error message in Safari Console: 

TypeError: undefined is not an object (evaluating 'new google.visualization.Query’)
drawChart help:113
(anonymous function) help:117

My code

<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
  google.load("visualization", "1", {packages:["geomap"]});
  google.setOnLoadCallback(drawMap);

  function drawMap() {
    var opts = {sendMethod: 'auto'};

    // ***My code***
    var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1l77TXctG6mgva1ggs3iR3eBNx949hzn9SiVjki1v59I/edit#gid=957991050', 'sheet=countries', 'headers=1');
    var data = query.send(handleQueryResponse);

    var options = {};
    options['dataMode'] = 'regions';

    var container = document.getElementById('regions_div');
    var geomap = new google.visualization.GeoMap(container);

    geomap.draw(data, options);
  };

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

      return response.getDataTable();
    }

</script>

Hope there is someone out there who can help me with this (hopefully) not so hard task. Thank you very much in advance! :-)

123456654321
  • 11
  • 1

1 Answers1

1

You can get data inside of a callback function handleQueryResponse

function handleQueryResponse(response) {
  if (response.isError()) {
    alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
  } else {
    var container = document.getElementById('regions_div');
    var geomap = new google.visualization.GeoMap(container);
    var data = response.getDataTable();
    geomap.draw(data, options);
  }
}

Google provides example here.

Also check format which you get in response and use method google.visualization.arrayToDataTable to format data as in example if needed cause it format should be correct.

Viktor Kukurba
  • 1,360
  • 9
  • 14
  • I'm afraid this did not help me that much. Are there anyone who knows about this? – 123456654321 Feb 01 '16 at 12:17
  • I couldn't find the related documentation, but when querying a Spreadsheet, the query will select the information in the first Sheet. In your Spreadsheet 'records' is the first sheet and that's the one being queried. Try change the order of sheets so 'countries' is the first one and the 'records'. This is in addition to what Viktor Kukurba mentioned. – Gerardo Feb 09 '16 at 01:40