Google GeoChart Tooltips from Google Sheet Data-CodePudding

I'm trying to build a custom GeoChart for my company's website. The GeoChart works with the US region and needs to color states according to which team member helps that state's customers. I need the chart to be updated from a Google Sheet so my co-workers can adjust state information easily without requiring me to manually edit the datatable. I set up the sheet so that team members choose from a list of colors. Each color has a number associated with it, and that number is then passed to the GeoChart data as the state's value. Each of the possible values (currently 0-16) has a unique color associated with it.

I have successfully created the chart as a webApp. The different states are colored properly based on the team member (and, by association, color code) assigned to each state. HOWEVER, the tooltip is simply displaying the state name and the color code. I need the tooltip to display state-specific information, and that's where I'm stuck.

Currently, the state names are coming from column A of my spreadsheet. The color codes are coming from column B. The tooltip data should be coming from column C. However, the only way I have found to create a datatable from spreadsheet data has been to query the spreadsheet, and doing so will not let me include more than two columns (because, of course, GeoCharts can only accept two columns). I have seen people manually create a third tooltip column, but, like I said, I want this information to get sent to the GeoChart automatically.

I suspect the solution involves creating the datatable, adding three columns, and then adding the rows from the spreadsheet data, but I can't find anything on how to do that. I'm new to all this, so I'd really appreciate some advice!

Here's what I have so far:

<!DOCTYPE html>
<html>
  <head>
  <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
  <script type="text/javascript">
   google.charts.load('current', {
      'packages': ['geochart'],
    });

    google.charts.setOnLoadCallback(drawRegionsMap);

    function drawRegionsMap() {
      var query = new google.visualization.Query(
        'https://docs.google.com/spreadsheets/d/19-xZpeNUKjYiHIFyd0R0i5FU6edDZMtFNYu9WAcrux0/edit?gid=1645698275&headers=1&range=A1:B51');

      query.send(handleQueryResponse);
    }

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

      var options = {
        region: 'US',
        displayMode: 'regions',
        resolution: 'provinces',
        colorAxis: {minValue: 0,maxValue: 16,colors: ['#ffffff', '#ea9999', '#ea4335', '#ff9900', '#ff6d01', '#ffd966', '#fbbc04', '#8ee909', '#b6d7a8', '#34a853', '#46bdc6', '#00ffff', '#4285f4', '#b4a7d6', '#9148ac', '#ff84c2', '#ff00ff']},
        backgroundColor: {fill: '#233271',stroke: '#233271',strokeWidth: 0},
        datalessRegionColor: '#f5f5f5',
        legend: 'none',
        enableRegionInteractivity: 'true',
        tooltip: {textStyle: {color: '#233271'},trigger: 'hover'}};

      var data = response.getDataTable();

      var chart = new google.visualization.GeoChart(document.getElementById('regions_div'));

      chart.draw(data, options);
    }
    </script>
  </head>
  <body>
    <div id="regions_div" style="width: 900px; height: 500px;"></div>
  </body>
</html>

CodePudding user response：

first, I changed the range in the query to...

...&range=A1:C51

create a new data table with the correct columns / types...

var dataChart = new google.visualization.DataTable({
  cols: [
    {label: 'State', type: 'string'},
    {label: 'Value', type: 'number'},
    {role: 'tooltip', type: 'string'}
  ]
});

then add the values from the query data to the new data table...

for (var i = 0; i < data.getNumberOfRows(); i  ) {
  dataChart.addRow([
    data.getValue(i, 0),
    data.getValue(i, 1),
    data.getValue(i, 2)
  ]);
}

and use the new data table to draw the chart...

chart.draw(dataChart, options);

see following snippet...
(for some reason, it doesn't run in the snippet below, but it worked fine on my local test page)

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

google.charts.setOnLoadCallback(drawRegionsMap);

function drawRegionsMap() {
  var query = new google.visualization.Query(
    'https://docs.google.com/spreadsheets/d/19-xZpeNUKjYiHIFyd0R0i5FU6edDZMtFNYu9WAcrux0/edit?gid=1645698275&headers=1&range=A1:C51');

  query.send(handleQueryResponse);
}

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

  var options = {
    region: 'US',
    displayMode: 'regions',
    resolution: 'provinces',
    colorAxis: {minValue: 0,maxValue: 16,colors: ['#ffffff', '#ea9999', '#ea4335', '#ff9900', '#ff6d01', '#ffd966', '#fbbc04', '#8ee909', '#b6d7a8', '#34a853', '#46bdc6', '#00ffff', '#4285f4', '#b4a7d6', '#9148ac', '#ff84c2', '#ff00ff']},
    backgroundColor: {fill: '#233271',stroke: '#233271',strokeWidth: 0},
    datalessRegionColor: '#f5f5f5',
    legend: 'none',
    enableRegionInteractivity: 'true',
    tooltip: {textStyle: {color: '#233271'},trigger: 'hover'}
  };

  var data = response.getDataTable();

  var dataChart = new google.visualization.DataTable({
    cols: [
      {label: 'State', type: 'string'},
      {label: 'Value', type: 'number'},
      {role: 'tooltip', type: 'string'}
    ]
  });

  for (var i = 0; i < data.getNumberOfRows(); i  ) {
    dataChart.addRow([
      data.getValue(i, 0),
      data.getValue(i, 1),
      data.getValue(i, 2)
    ]);
  }

  var chart = new google.visualization.GeoChart(document.getElementById('regions_div'));

  chart.draw(dataChart, options);
}

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="regions_div"></div>