Dynamically change Google Chart Gauge data and colors based on cell value

Question

Dynamically change Google Chart Gauge data and colors based on cell value

2.6k Views Asked by Pabz At 10 April 2025 at 19:46

I'm making a dashboard in Google Spreadsheet and would like to use cell data. Now you need to hard code the data and the values for redFrom, redTo etc etc but I want them to be dynamic and dependant on a certain value in a cell. Same applies to the values shown by the gauge.

The dashboard will to track monthly income and the gauge will indicate if someone if in the green for that month based on the percentage relative to their target.

This is the standard example from google:

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

      function drawChart() {

        var data = google.visualization.arrayToDataTable([
          ['Label', 'Value'],
          ['Memory', 80],
          ['CPU', 55],
          ['Network', 68]
        ]);

        var options = {
          width: 400, height: 120,
          redFrom: 90, redTo: 100,
          yellowFrom:75, yellowTo: 90,
          minorTicks: 5
        };

        var chart = new google.visualization.Gauge(document.getElementById('chart_div'));

        chart.draw(data, options);

        setInterval(function() {
          data.setValue(0, 1, 40 + Math.round(60 * Math.random()));
          chart.draw(data, options);
        }, 13000);
        setInterval(function() {
          data.setValue(1, 1, 40 + Math.round(60 * Math.random()));
          chart.draw(data, options);
        }, 5000);
        setInterval(function() {
          data.setValue(2, 1, 60 + Math.round(20 * Math.random()));
          chart.draw(data, options);
        }, 26000);
      }
    </script>
  </head>
  <body>
    <div id="chart_div" style="width: 400px; height: 120px;"></div>
  </body>
</html>

So I want it to be something like this:

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

      function drawChart() {

        var data = google.visualization.arrayToDataTable([
          ['Label', 'Value'],
          ['A1', A2],
          ['B1', B2],
          ['C1', C2]
        ]);

        var options = {
          width: 400, height: 120,
          redFrom: D1, redTo: D2,
          yellowFrom:D3, yellowTo: D4,
          minorTicks: 5
        };

Is this possible? Thanks!

Original Q&A

There are 1 best solutions below

**WhiteHat** · Answer 1

if the charts will be running in an html page,
you can query the sheet using --> google.visualization.Query

following is an example, using a sample spreadsheet --> gauge data

columns A & B are used for the gauge chart data

columns D-I are for the from / to color values

the setQuery method is used to separate the two ranges into data tables

first, get the chart data, the options data, then draw the chart...

google.charts.load('current', {
  packages: ['gauge']
}).then(function () {

  // get chart data
  var queryChart = new google.visualization.Query('https://docs.google.com/spreadsheets/d/16IeSOMvl4-B3qFr386kjZA-vSRIHTNvSIigqyracGj0/edit#gid=0');
  queryChart.setQuery('select A,B');
  queryChart.send(function (responseChart) {

    var dataChart = responseChart.getDataTable();

    // get options data
    var queryOptions = new google.visualization.Query('https://docs.google.com/spreadsheets/d/16IeSOMvl4-B3qFr386kjZA-vSRIHTNvSIigqyracGj0/edit#gid=0');
    queryOptions.setQuery('select D,E,F,G,H,I');
    queryOptions.send(function (responseOptions) {

      var dataOptions = responseOptions.getDataTable();

      var options = {
        minorTicks: 5
      };

      options.redFrom = dataOptions.getValue(0, 0);
      options.redTo = dataOptions.getValue(0, 1);
      options.yellowFrom = dataOptions.getValue(0, 2);
      options.yellowTo = dataOptions.getValue(0, 3);
      options.greenFrom = dataOptions.getValue(0, 4);
      options.greenTo = dataOptions.getValue(0, 5);

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

  });
});

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

Dynamically change Google Chart Gauge data and colors based on cell value

There are 1 best solutions below

Related Questions in JAVASCRIPT

Related Questions in CHARTS

Related Questions in GOOGLE-SHEETS

Related Questions in GOOGLE-VISUALIZATION

Related Questions in GOOGLE-GAUGES

Trending Questions

Popular # Hahtags

Popular Questions