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:

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

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:

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

• javascript
• google-apps-script
• google-sheets
• charts
• google-visualization

Share Improve this question Follow edited Sep 17, 2020 at 4:31 jrwebapps asked Sep 16, 2020 at 9:31 jrwebappsjrwebapps 10211 gold badge11 silver badge99 bronze badges 4

• 1 Do you mean in a similar way to the UI's Customise > Series, Add under 'Format data point`? Maybe recording a macro would help see how the individual lines can have their colour changed? – Rafa Guillermo Commented Sep 16, 2020 at 9:51
• @RafaGuillermo, yes that's the same in context. Although when you use format data point, you can only set static colour. I want it to be dynamically assigned based on cell value. – jrwebapps Commented Sep 16, 2020 at 10:47
• 1 in the code posted, the colors are retrieved from column I -- I3:I6 -- but in the picture, it shows the color legend values are in column G -- G4:G6 -- what is in column I? – WhiteHat Commented Sep 16, 2020 at 12:06
• @WhiteHat, I copied the result in another sheet that is why the column does not match on what's in the code. I cannot show the real data here. – jrwebapps Commented Sep 16, 2020 at 12:12

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 2

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()