Script conditional formating not working until manually overwritten

Question

I have written a Google Apps Script that creates a conditional format on a range. After running the script, the conditional format is created perfectly but it doesn't work on its own: I have to manually enter on Format > Conditional format > Enter the validation I created and save it (without changing a thing) for it to work as it should.

 var hojaCamada = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(camada); var range = hojaCamada.getRange("K6:K100"); var rule = SpreadsheetApp.newConditionalFormatRule() .whenFormulaSatisfied('=Y($J6 < $I$3;$K6<1)') .setBackground("#ea9999") .setRanges([range]) .build(); var rules = hojaCamada.getConditionalFormatRules(); rules.push(rule); hojaCamada.setConditionalFormatRules(rules);

Can anyone help me figure out what the problem is?

@Karl_S Yes I do have a trigger, does it afect the conditional? — Marcos Alejandro Pérez
– Marcos Alejandro Pérez, Commented Apr 23, 2019 at 17:01
The trigger is what gets the function to run so if you have an onEdit trigger then with every edit the function will run. If you have a time trigger, it will only run when the time is correct. An onOpen only when the file is open, etc. So make sure the function is triggered when you are expecting it by using the appropriate trigger from the link in my original comment. — Karl_S
– Karl_S, Commented Apr 23, 2019 at 18:55

J. G. · Accepted Answer · 2019-04-23 16:17:42Z

I'm not seeing anything wrong with your code, I will share some things I do differently though,

I start my colors function by clearing the existing rules.

 var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getSheetByName("x"); sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate(); sheet.clearConditionalFormatRules(); var conditionalFormatRules = spreadsheet.getActiveSheet().getConditionalFormatRules();

I also do my ranges manually in each rule

 var rule = SpreadsheetApp.newConditionalFormatRule() .setFontColor('#a2c8ec') .setBackground('black') .whenFormulaSatisfied('=$D1=1') .setRanges([spreadsheet.getRange('A:J')]) .build(); conditionalFormatRules.push(rule); sheet.setConditionalFormatRules(conditionalFormatRules);

In your place I would do those two things, change how you select the ranges and clear all the old formatting each time you run the function.

Collectives™ on Stack Overflow

Script conditional formating not working until manually overwritten

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related