CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsconditional-formatting
mdubes13
mdubes13

Reputation: 11

Failing to create 3 separate Colour scale ranges in Google Sheets conditional formatting

So I am trying to create 3 separate colour scales, such that numbers in the range 0-1 change from red through white to (say) green, 2-5 change from red through white to cyan, and 6-90 change from red through white to purple. The intention is to indicate that values close to 0, 2, and 6 are all 'bad' within their respective ranges, and values closer to the top of each respective range are 'good'.

I started off by simply going to:

Conditional formatting-> Colour scale -> Select Minpoint, Midpoint, and Maxpoint Number, and colour. -> Scroll down -> Add another rule

... until I had my 3 rules. It all looked like it would work, since the ranges don't overlap.

Unfortunately though, all numbers entered into the defined range are only formatted according to one of the rules. So eg. the first time I tried, numbers between 0-70 are ALL red, and numbers 70-90 gradually become purple as intended.

I tried to build it all again in script (which I'm not at all familiar with) just based on the example given here: https://developers.google.com/apps-script/reference/spreadsheet/condit... and it seems to have had the exact same result as my first try, so now all numbers are being formatted according to the rule for values 0-1. So even eg. 50, which should be white within its range, is fully green.

Here's my scripting attempt, I'd be very grateful if someone can help me get it working.

function myFunction() {

var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("E3:BR500");
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenNumberBetween (6,90)
    .setGradientMaxpointWithValue("#d600ff", SpreadsheetApp.InterpolationType.NUMBER, "90")
    .setGradientMidpointWithValue("#ffffff", SpreadsheetApp.InterpolationType.NUMBER, "50")
    .setGradientMinpointWithValue("#ff0000", SpreadsheetApp.InterpolationType.NUMBER, "6")
    .setRanges([range])
    .build();

var rule = SpreadsheetApp.newConditionalFormatRule()
.whenNumberBetween(2,5)
    .setGradientMaxpointWithValue("#00b8ff", SpreadsheetApp.InterpolationType.NUMBER, "5")
    .setGradientMidpointWithValue("#ffffff", SpreadsheetApp.InterpolationType.NUMBER, "3.5")
    .setGradientMinpointWithValue("#ff0000", SpreadsheetApp.InterpolationType.NUMBER, "2")
    .setRanges([range])
    .build();

var rule = SpreadsheetApp.newConditionalFormatRule()
.whenNumberBetween(0,1)
    .setGradientMaxpointWithValue("#00ff9f", SpreadsheetApp.InterpolationType.NUMBER, "1")
    .setGradientMidpointWithValue("#ffffff", SpreadsheetApp.InterpolationType.NUMBER, "0.7")
    .setGradientMinpointWithValue("#ff0000", SpreadsheetApp.InterpolationType.NUMBER, "0")
    .setRanges([range])
    .build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);
  
}

Upvotes: 1

Views: 326

Answers (1)

TheMaster
TheMaster

Reputation: 50573

I believe inbuilt conditional formatting is not suitable for what you're attempting to do. A possible solution is to skip inbuilt conditional formatting and calculate color hex for each value using script and use setBackgrounds().

Upvotes: 1

Related Questions