Javascript - How to choose which columns are used in Google Visualization chart and avoid 'All series on a given axis must be of the same data type' error

user1610717
October 12, 2024
158 views
0 votes
2 Answers

I’m trying to use various columns on a spreadsheet to chart running durations and try different styles by using Google Charts. I have problems when I try to query more than 2 columns. I want to use one column (B, for example) for attribution text, or labels, above the points on the graph. But when I use ‘SELECT A, B, D’, I get an error of ‘All series on a given axis must be of the same data type’. How can I query/load multiple columns but only use the ones I choose as series in the chart? It looks like it automatically uses all for a series, so I think it’s trying to plot a string and a date on the x axis. Successful chart (querying just column A and D), spreadsheet, and code are below. Thanks for any help.

I did try to implement the suggestion from this post but without success. I wasn’t sure how to implement it with 3 columns vs. 2. Any help on that end would be much appreciated.

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



     function drawChart() {
      var queryString = encodeURIComponent('SELECT A, D');

      var query = new google.visualization.Query(
          'https://docs.google.com/spreadsheets/d/1xRV24c1itTkK1OWLo3KdpVhkjXpuMzi8lXi4UFHanso/gviz/tq?sheet=Sheet1&headers=1&tq=' + queryString);
      query.send(handleSampleDataQueryResponse);
    }

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

     


      var data = response.getDataTable();

     
         var options = {
          title: 'L's 2024 Cross Country Run Times ',
          
      
          width: 900,
          height: 500,
          trendlines: {
      0: {
        color: 'blue',
        
      }
    },
          vAxis: {
            format: 'mm:ss'
            
          }
          
        };





      var chart = new google.visualization.ScatterChart(document.getElementById('chart_div'));
      chart.draw(data, options);
    }

    </script>
  </head>
  <body>
    <div id="chart_div" style="width: 100%; height: 500px;"></div>
  </body>
</html>

Tags: google-visualization javascript

Answers

This happens because you are trying to use a column with non-numeric values (column B, which has text/labels) alongside numeric data (columns A and D) in the chart.

you can split the columns into two parts: one set for plotting (numeric data only) and another set for labels (text) to avoid type mismatch errors.

i have modified the code already

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

      function drawChart() {
        var queryString = encodeURIComponent('SELECT A, D'); // Select only the numeric columns

        var query = new google.visualization.Query(
          'https://docs.google.com/spreadsheets/d/1xRV24c1itTkK1OWLo3KdpVhkjXpuMzi8lXi4UFHanso/gviz/tq?sheet=Sheet1&headers=1&tq=' + queryString
        );
        query.send(handleSampleDataQueryResponse);
      }

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

        var data = response.getDataTable();

        // Create a DataView to add labels from column B (non-numeric data) without using it as an axis
        var view = new google.visualization.DataView(data);
        view.setColumns([0, 1, {
          calc: function (dt, row) {
            return dt.getValue(row, 1); // Use D column's values for labels
          },
          type: 'string',
          role: 'annotation'
        }]);

        var options = {
          title: 'L's 2024 Cross Country Run Times',
          width: 900,
          height: 500,
          trendlines: {
            0: { color: 'blue' }
          },
          vAxis: {
            format: 'mm:ss'
          }
        };

        var chart = new google.visualization.ScatterChart(document.getElementById('chart_div'));
        chart.draw(view, options);
      }
    </script>
  </head>
  <body>
    <div id="chart_div" style="width: 100%; height: 500px;"></div>
  </body>
</html>

as you suspected, when you draw the chart directly from the query response, it uses all columns.
the first column is used on the x-axis, and the remaining columns on the y-axis.

but in order to draw annotations, you must include an annotation column role.
this is where the data view comes into play.
with a data view, you can specify a specific role for a column or columns.

here, we select the annotation data (column B) as the last column in the select statement.

var queryString = encodeURIComponent('SELECT A,D,B');

then we create the data view, from the data table response, using the last column as the annotation.

// need to create dataview to use column as annotation
var view = new google.visualization.DataView(data);
view.setColumns([0, 1, {  // <-- columns A (x), D (y)
  calc: 'stringify',
  sourceColumn: 2,        // <-- column B as annotation
  type: 'string',
  role: 'annotation'
}]);

see following snippet.
I don’t think it will actually run from a stack overflow snippet,
so here is working example of JSFiddle…

https://jsfiddle.net/WhiteHat/xcdb7o2k/2/

// load google charts
google.charts.load('current', {
  packages: ['corechart']
}).then(drawChart);

function drawChart() {
  // initialize query statement
  var queryUri = 'https://docs.google.com/spreadsheets/d/1xRV24c1itTkK1OWLo3KdpVhkjXpuMzi8lXi4UFHanso/gviz/tq?sheet=Sheet1&headers=1&tq=';

  // add select statement, set B as last column
  var queryString = encodeURIComponent('SELECT A,D,B');

  // initialize query
  var query = new google.visualization.Query(queryUri + queryString);

  // run query
  query.send(function (response) {
    // determine if error occurred
    if (response.isError()) {
      alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
      return;
    }

    // extract data from response
    var data = response.getDataTable();

    // need to create dataview to use column as annotation
    var view = new google.visualization.DataView(data);
    view.setColumns([0, 1, {
      calc: 'stringify',
      sourceColumn: 2,
      type: 'string',
      role: 'annotation'
    }]);

    // create options
    var options = {
      title: 'L's 2024 Cross Country Run Times',
      width: 900,
      height: 500,
      trendlines: {
        0: {
          color: 'blue'
        }
      },
      vAxis: {
        format: 'mm:ss'
      }
    };

    // draw chart
    var chart = new google.visualization.ScatterChart(document.getElementById('chart_div'));
    chart.draw(view, options);
  });
}

<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div" style="width: 100%; height: 500px;"></div>

Please signup or login to give your own answer.

Click here to cancel reply.

Javascript – How to choose which columns are used in Google Visualization chart and avoid 'All series on a given axis must be of the same data type' error

Answers