Google sheets: How to dynamically change the color of bar chart based on cell value with google script?

0
votes

I made a gantt chart on excel (see screenshot), and I'm trying to figure out how to dynamically change the color of a bar based on assignee (team member). For reference, this is nothing but a stacked bar chart, where the first part of the column is just made to be transparent. There are 2 series: "Start on day" - transparent, and "Duration" - light blue.

enter image description here

As you can see right now, all bars are the same color, while I want something like this:

enter image description here

So I found this piece of code, I edited it a little bit, and managed to make it work so that when I change a name in the Team member column, it colors the chart differently (essentially it receives a CSS color code from a hidden column). Problem is, it colors all the bars (because it's targeting a series) and not just one. I couldn't find a way to make it work with one bar only.

Suggestions? Any help is much appreciated. (code below)

function modifyChart_(sheet, newCssColor) {
  // Assume there is only one chart on this sheet.
  const charts = sheet.getCharts();
  const barBuilder = charts[0].modify().asBarChart();
  const option = {};

  option[0] = {"color": "rgba(255,255,255, 0"};
  option[1] = {};
  option[2] = {"color": newCssColor};
  barBuilder.setOption("series", option);

  // Update the chart on the sheet.
  sheet.updateChart(barBuilder.build());
}
1
google-apps-scriptgoogle-sheetsgoogle-visualization

1 Answers

1
votes

How to set the colors of a bar chart statically

Sample:

function modifyChart(sheet, newCssColor) {
  // Assume there is only one chart on this sheet.
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  const charts = sheet.getCharts();
  const barBuilder = charts[0].modify().asBarChart().setColors(["red", "green", "grey"]);
   sheet.updateChart(barBuilder.build());
}

Note:

  • Apps Script does not support the underscore _ at the end of a function name
  • The most convenient method for changing bar colors is setColors

How to set the color of a bar chart dynamically from a cell value

Sample:

function modifyChart(sheet, newCssColor) {
  // Assume there is only one chart on this sheet.
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  const charts = sheet.getCharts();
  var array = [];
  //assuming the color values are in cells A10, A11 and A12
  var colorValues = sheet.getRange("A10:A12").getValues();
  for(var i = 0; i < colorValues.length; i++){
    array.push(colorValues[i][0]);
  }
  const barBuilder = charts[0].modify().asBarChart().setColors(array);
  sheet.updateChart(barBuilder.build());
}

References: