How to modify google sheet chart color based on cell value using app script

user_3354335 0
Viewed 4807

I want to create a dynamic chart that changes the color based on a cell value. I used the example in this answer but it only uses the first color I declared in the legend as seen in this image:

enter image description here

function modifyChart(sheet, newCssColor) {
  // Assume there is only one chart on this sheet.
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  const charts = sheet.getCharts();
  var array = [];

  var colorValues = sheet.getRange("G4:G6").getValues();
  for(var i = 0; i < colorValues.length; i++){
    array.push(colorValues[i][0]);
  }

Logger.log(colorValues);
  const barBuilder = charts[0].modify().asColumnChart().setColors(array);
  sheet.updateChart(barBuilder.build());
}

But here's exactly want to do:

If score <= 49 set bar color to red
Else if score >= 50 and score <= 89 set bar color to orange
else set bar color to green

Just like how the cell background changes because I set rules to it using Conditional Formatting.

Edit: Change the the cell range to match the sample

google-apps-script google-sheets google-visualization charts javascript
1 Answers

Each of your categories is a series in your embedded chart. You want to set the style options of each series individually. Use setOption() and set the color options for each series.

For example:

EmbeddedChartBuilder.setOption('series.0.color', 'red').setOption('series.1.color', 'orange').setOption('series.2.color', 'green').build()

user_42439270
Related