AppScript function to add Conditional Formatting doesn't work until Refresh in Google Sheets-CodePudding

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();
}

CodePudding user response：

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);
  
}