google-apps-scriptgoogle-sheetsgs-conditional-formattinggoogle-sheets-conditionalformatting

How to protect conditional formatting in google sheet?

I have 2 CFs in a sheet that I would like to keep upon copy/pastes you can see the range and formulas below. 

CF1 Range : B3:H1001
CF1 Formula : =($A3<>"")*(B3="") //This one higlights the empty cells in a given range

CF2 Range : A3:R1001  // This one highlights the row if the cell in R column is No.
CF2 Formula : =$R:$R="No"

Is there a way to protect these CFs or apply them again after copy paste ? Thanks !

Solution

  • When I copy paste from another range I am losing the Conditional formats that i have created. So I want them to stay like I formatted or I would like them to be applied again after the copy/paste.

    From above replying, I could understand that you want to keep the conditional formats, which are =($A3<>"")*(B3="") for B3:H1001 and =$R:$R="No" for A3:R1001, even when the range is copied and pasted to B3:H1001 and A3:R1001.

    If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

    Issue and workaround:

    In the current stage, unfortunately, there are no methods for directly protecting the conditional formats in Google Apps Script. So in this case, as a workaround, I would like to propose to overwrite the conditional formats when the change of conditional formats and cells using OnChange event trigger.

    Flow:

    1. The range is copied and the parameters of conditional formats are changed.
    2. The script is automatically run by the OnChange event trigger.
    3. The existing conditional formats are deleted and new conditional formats are set.

    Usage:

    1. Copy and paste sample script

    Please copy and paste the following sample script.

    Sample script:

    Please set the sheet name.

    function onChange(e) {
  const sheetName = "Sheet1";  // Please set the sheet name.

  if (e.source.getActiveSheet().getSheetName() != sheetName || e.changeType != "OTHER") return;

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var rules = sheet.clearConditionalFormatRules();
  const rule1 = SpreadsheetApp.newConditionalFormatRule()
    .setRanges([sheet.getRange("B3:H")])
    .setBackground("green")
    .whenFormulaSatisfied('=($A3<>"")*(B3="")').build();
  const rule2 = SpreadsheetApp.newConditionalFormatRule()
    .setRanges([sheet.getRange("A3:R")])
    .setBackground("green")
    .whenFormulaSatisfied('=$R:$R="No"').build();
  sheet.setConditionalFormatRules([rule1, rule2]);
  SpreadsheetApp.flush();  // This line might not be required.
}

    2. Install OnChange event trigger

    Please install the OnChange event trigger to the function of onChange.

    3. Test run

    As a test run, please copy and paste the range in sheetName. By this, the script is run by the OnChange event trigger.

    Note:

    References: