How can I bulk edit Conditional Formatting rules in Google Sheets?

Question

My Google Sheet Conditional Formatting rules are full of Strings, like =INDIRECT("Settings!D18").

If I rearrange things on my Settings sheet and D18 becomes X45, is there a way for me to do a global Search/ Replace to reflect that?

You could do this with Google Apps Script - see ConditionalFormatRuleBuilder. — ziganotschka
– ziganotschka, Commented May 31, 2021 at 7:59
@ziganotschka please answer the question and I will Accept it — James Raitsev
– James Raitsev, Commented Jun 1, 2021 at 3:51

ziganotschka · Accepted Answer · 2021-06-02 20:34:48Z

You could do this with Google Apps Script to bulk edit the conditional formatting rules programmatically

For this, use the ConditionalFormatRuleBuilder and the withCriteria() builder.

Sample:

function bulkUpdateConditionalFormatting(){ var sheet = SpreadsheetApp.getActiveSheet(); var rules = sheet.getConditionalFormatRules(); var newRules = []; var newCriteriaValues rules.forEach(function(rule){ var booleanCondition = rule.getBooleanCondition(); Logger.log(booleanCondition.getCriteriaValues()); if (booleanCondition != null && booleanCondition.getCriteriaType() == 'CUSTOM_FORMULA') { var values = booleanCondition.getCriteriaValues() values.forEach(function(value,i){values[i] = value.replace("D18", "D45")}); var newRule = SpreadsheetApp.newConditionalFormatRule() .withCriteria(booleanCondition.getCriteriaType(), values) .setBackground(booleanCondition.getBackground()) .setRanges(rule.getRanges()) .build(); newRules.push(newRule); } }) sheet.setConditionalFormatRules(newRules); }

Collectives™ on Stack Overflow

How can I bulk edit Conditional Formatting rules in Google Sheets?

1 Answer 1

You could do this with Google Apps Script to bulk edit the conditional formatting rules programmatically

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

You could do this with Google Apps Script to bulk edit the conditional formatting rules programmatically

Comments

Related