How do I conditionally format Google visualization

Question

I am new to the world of the Google Visualization Api and was hoping someone can help me conditionally format the color of cells in my google visualization table. I have been able to change the number format that different columns display, but am not having such luck with color formatting. I am using the arrayToDataTable and chartwrapper functions to display some data I have queried from a spreadsheet.

Is it something I need to change with the colorFormat variable or the chartwrapper function that in not accepting the formatting? Thank you in advance!

function drawDashboard(response) {
  $('#main-heading').addClass("hidden");
  if (response == null) {
    alert('Error: Invalid source data.')
    return;
  } else {

    // Transmogrify spreadsheet contents (array) to a DataTable object
    var responseObjects = JSON.parse(response);
    console.log(responseObjects);
    var testData = [];
    for (var i = 1; i < responseObjects.length; i++) {
      responseObjects[i][0] = new Date(responseObjects[i][0]);
    }
    var data = google.visualization.arrayToDataTable(responseObjects, false);
    console.log(data);
    var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard-div'));

    var percentFormatter = new google.visualization.NumberFormat({
      pattern: '#,###.##%'
    });
    percentFormatter.format(data, 1);
    percentFormatter.format(data, 3);

    var numberFormatter = new google.visualization.NumberFormat({
      pattern: '#.##'
    });
    numberFormatter.format(data, 7);
    numberFormatter.format(data, 8);

    var colorFormatter = new google.visualization.ColorFormat();
    colorFormatter.addRange(0, 5, 'white', 'orange');
    colorFormatter.addRange(20000, 6, 'red', '#33ff33');
    colorFormatter.format(data, 8);
    colorFormatter.format(data, 9);
    colorFormatter.format(data, 10);
    colorFormatter.format(data, 11);

    var table = new google.visualization.ChartWrapper({
      'chartType': 'Table',
      'containerId': 'table-div',
      'view': {
        'columns': [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]
      },
    });

    var donutSlider = new google.visualization.ControlWrapper({
      'controlType': 'DateRangeFilter',
      'containerId': 'slider-div',
      'options': {
        'filterColumnLabel': 'Date'
      }
    });

    // Set up dependencies between controls and charts
    dashboard.bind(donutSlider, [table]);
    // Draw all visualization components of the dashboard
    dashboard.draw(data);
  }
}

Answer 1

looks like you're using the using the formatter correctly
but the parameters are a little off

also need allowHtml: true in the Table options

see following example using the linked spreadsheet...

google.charts.load('current', {
  callback: function () {
    var query = new google.visualization.Query(
      'https://docs.google.com/spreadsheets/d/1TBTX_OmNUiq_J0uXEstkxeD6mtImi7BAPWKDBAQIiFA/edit#gid=0'
    );
    query.setQuery("select *");
    query.send(drawDashboard);
  },
  packages: ['controls', 'table']
});

function drawDashboard(response) {
  if (response.isError()) {
    console.log('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
    return;
  }

  var data = response.getDataTable();

  var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard-div'));

  var percentFormatter = new google.visualization.NumberFormat({
    pattern: '#,###.##%'
  });
  percentFormatter.format(data, 1);
  percentFormatter.format(data, 3);

  var numberFormatter = new google.visualization.NumberFormat({
    pattern: '#.##'
  });
  numberFormatter.format(data, 7);
  numberFormatter.format(data, 8);

  var colorFormatter = new google.visualization.ColorFormat();
  colorFormatter.addRange(-20000, 0, 'white', 'orange');
  colorFormatter.addRange(20000, null, 'red', '#33ff33');
  colorFormatter.format(data, 8);
  colorFormatter.format(data, 9);
  colorFormatter.format(data, 10);
  colorFormatter.format(data, 11);

  var table = new google.visualization.ChartWrapper({
    chartType: 'Table',
    containerId: 'table-div',
    options: {
      allowHtml: true
    }
  });

  var donutSlider = new google.visualization.ControlWrapper({
    controlType: 'DateRangeFilter',
    containerId: 'slider-div',
    options: {
      filterColumnLabel: 'Date'
    }
  });

  dashboard.bind(donutSlider, [table]);
  dashboard.draw(data);
}

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="dashboard-div">
  <div id="slider-div"></div>
  <div id="table-div"></div>
</div>