google-sheetsgs-conditional-formatting

Change ranges for ALL conditional formatting rules?


I have a few sheets with easily 20+ conditional formatting rules.

Apparently, Sheets doesn't like moving cells around, because it just completely messes up the ranges defined in all of the conditional formatting rules. Something... "normal" turns into A1:E355,F1:F16,G1:G19,H1:P355,F19,F21:G24,F33:G355.

Is there a way to change all the CF ranges back to, for example, A:AA without having to go through each and every one "by hand" and pasting the normal range definition in there?


Solution

  • I found a super simple answer (I didn't know Apps Script that well when I asked this question)...

    function changeRanges()
    {
      var sheet = SpreadsheetApp.getActiveSheet();
      var rules = SpreadsheetApp.getActiveSheet().getConditionalFormatRules();
    
      var entireSheet = [sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns())];
    
      for (var i = 0; i < rules.length; i++)
      {
        rules[i] = rules[i].copy().setRanges(entireSheet).build();
      }
      sheet.setConditionalFormatRules(rules);
    }
    

    Works universally, but it's obviously dangerous as it'll thoroughly mess up color scales or similar.