How can we affect chart color with conditional formatting in Google Sheets?

Question

In this Google Sheets workbook, I have conditional formatting based on checkboxes. I'd like to create a pie chart that will update based on the user-input data (boolean yielded from the checkbox data validation).

Here's a link if you'd like to tinker with it directly.

In the sheet, there are three questions, and the user checks a box to indicate their response. The responses trigger a yellow warning, then orange warning, then red warning color formatted across the row.

I'd like to create a chart in the workbook for a quick glance at the user's "health," with all the unformatted rows (enumerated by the user in the Number column) showing green, and the yellow/orange/red updating respectively.

I've been pulling data from the Number, Q1, Q1, and Q3 columns for this chart and pulling my hair out trying to graph it properly.

In an answer to a similar question, one of the only responses was from a user saying we'd need to script for this function.

Does anyone here know the necessary scripting? Would it be in Sheets API or Google Apps Script?

Thanks!

Answer 1

I sorted it out with a much easier fix!

You'll see in the workbook a chart now, on the "Health" sheet. I made a little data pull counting the boolean results and used that to format.

Dear Brain, we don't always need to complicate things.

Simplify, and simplify again.

Thanks!