As the title states, I have some conditional formatting I'm setting via App Script. My issue is that the highligting doesn't work until I go into the UI and refresh it manually (e.g., changing the color, or deleting and re-adding back a character in the rule definition). I also tried adding a blanket rule to turn everything black, and... everything turns black, where the values that match my rule don't highlight until I mess around like I said above in the UI.
Here is my code:
function highlightExistingFarms(sheet){
var rules = [];
var range = sheet.getRange("G:G")
strRule = "=VLOOKUP(G:G,INDIRECT(\"'1862'!B:B\"),1,FALSE)";
sheet.clearConditionalFormatRules();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenTextContains(strRule)
.setBackground("#b3cfb0")
.setRanges([range])
.build();
var ruleForceRefresh = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setBackground("#000000")
.setRanges([range])
.build();
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
//rules.push(ruleForceRefresh);
sheet.setConditionalFormatRules(rules);
rules = sheet.getConditionalFormatRules();
sheet.setConditionalFormatRules(rules);
SpreadsheetApp.flush();
}
The conditional formatting rules will be applied until a spreadsheet recalculation of the conditional formatting range is done, by the other hand, when using a custom formula for the conditional formatting rule, instead of whenTextContains use whenFormulaSatisfied .
Try this:
function highlightExistingFarms(sheet){
var rules = [];
var range = sheet.getRange("G:G")
strRule = "=VLOOKUP(G:G,INDIRECT(\"'1862'!B:B\"),1,FALSE)";
sheet.clearConditionalFormatRules();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied(strRule)
.setBackground("#b3cfb0")
.setRanges([range])
.build();
rules.push(rule);
sheet.setConditionalFormatRules(rules);
/** Force a recalculation */
const g1 = sheet.getRange('G1');
const g1Value = g1.getValue();
g1.clearContent();
SpreadsheetApp.flush();
g1.setValue(g1Value);
}