Change Bar color based on cell value

Question

I am trying to find a solution to automatically adjust the color of a stacked bar in a chart based on the value or color of a cell.

Specifically, when I change the value of F2 I want the bar to change color as well, based off the color of the cell or the value/string of the cell.

Here is my sheet: https://docs.google.com/spreadsheets/d/1QwAVPEtQ3l7iIZhLDYMT1I9eGc7HoaFsTyg1p-hi3G8/edit?usp=sharing

I have setup my test data and researched solutions but haven't found a method to exactly do what I am looking for.

I'm hoping to find a solution, and I am leaning towards an Apps Script method as I don't see any options in the "Conditional Format" menu that will control the chart series formatting, and I don't see any options in the bar chart menu to target a specific cell.

Any advice or guidance is greatly appreciated.

gs-conditional-formatting is about cell formatting, not charts. — Rubén

tehhowch tehhowch · Accepted Answer · 2018-06-26T01:51:22

You can modify the chart based on edits to the sheet.

Using the simple trigger function onEdit with its event object, you can determine if sheet edits were made that altered your reference cell. If the edit did, then you can access the sheet's embedded charts, and modify them as you desire:

function onEdit(e) {
  if (!e) throw new Error("You ran this manually");
  const sheet = e.range.getSheet();

  // Sanity check: only care about edits on the desired sheet.
  if (sheet.getName() !== "some sheet name with your reference cell on it")
    return;
  // Sanity check: only care about edits to the reference cell:
  if (e.range.getA1Notation() !== "reference cell A1 notation here")
    return;
  // Sanity check: only care if there is a new value.
  if (e.value === undefined)
    return;
  // Example change: alter the color of the 3rd series. Assumption: e.value is a
  // valid CSS color or CSS color code. If it is numeric, write code to transform it.
  modifyChart_(sheet, 2, e.value)
}

function modifyChart_(sheet, seriesIndex, newCssColor) {
  // Assume there is only one chart on this sheet.
  const charts = sheet.getCharts();
  if (charts.length > 1)
    console.warn("Assumption invalid: more than 1 chart on the given sheet");
  const barBuilder = charts[0].modify().asBarChart();

  const option = {};
  option[seriesIndex] = {"color": newCssColor};
  barBuilder.setOption("series", option);

  // Update the chart on the sheet.
  sheet.updateChart(barBuilder.build());
}

Reference documentation (aka REQUIRED reading):

Change Bar color based on cell value

1 Answers