Using Google Script to extract conditional formatting from a google sheet so I can reuse it

Question

I am familiar with writing conditional formatting rules in Google script.

I have a Google sheet that I have inherited that has been developed over a long period and conditional formatting rules have been manually inserted.

I am looking to copy all of the conditional formatting out of that google sheet using google apps script. The sheet has around 50 columns with many drop downs, and each dropdown, or combination of dropdowns, applies formatting. I think there are over 100 rules.

It would be VERY useful if I could get the conditional formatting out in a format that I could modify as needed and apply other (similar) sheets.

Any suggestions?

Mark

Answer 1

SUGGESTION

Note: We normally do not code for you, but in this case I have a sample script that I can share with you that was derived from the samples of these Spreadsheet App classes listed below:

You can try using these Spreadsheet App classes in Apps Script:

• getBooleanCondition()
• withCriteria(criteria, args)
• ConditionalFormatRuleBuilder
• Class Ui

Sample Script

var ui = SpreadsheetApp.getUi();

function onOpen() { //Sets the custom menu
  ui.createMenu('Extract Existing Conditional Formatting')
    .addItem('Copy Conditonal Formatting', 'checkRangeSelection')
    .addToUi();
}

function checkRangeSelection() { //checks the selected range & asks user if he/she wants to copy the range's conditonal formatting values to another sheet range
  var rule = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getConditionalFormatRules()[0];
  var ranges = rule.getRanges();
  var selectedRange = SpreadsheetApp.getActiveSheet().getSelection().getActiveRange().getA1Notation();
  var newCriteriaValue;

  for (var i = 0; i < ranges.length; i++) {

    if (selectedRange == ranges[i].getA1Notation()) {
      var response = ui.alert("Selected Range: " + selectedRange +
        "\n\nThis range has these criteria:\n" +
        "\nTYPE: " + rule.getBooleanCondition().getCriteriaType() +
        "\nVALUE: " + rule.getBooleanCondition().getCriteriaValues() + '\n\nDo you want to update it and apply it to a new sheet range?',
        ui.ButtonSet.YES_NO);

      if (response == ui.Button.YES) {

        var valueNew = ui.prompt("Current Criteria Value is: " + rule.getBooleanCondition().getCriteriaValues() + "\n\n Type here to change it:\n");
        valueNew.getResponseText() == '' ? newCriteriaValue = rule.getBooleanCondition().getCriteriaValues() : newCriteriaValue = valueNew.getResponseText();
        var destSheet = ui.prompt("Type the \"Sheet Name\" where you would like to apply the conditional formatting:\n\n");
        var newRange = ui.prompt("Type the \"Range\" (e.g. A1:A100) where you would like to apply the conditional formatting:\n\n");

        try {

          var destinationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(destSheet.getResponseText());
          var range = destinationSheet.getRange(newRange.getResponseText());

          var copiedrule = rule.copy().withCriteria(rule.getBooleanCondition().getCriteriaType(), [newCriteriaValue.toString()]).setRanges([range]).build();
          var rules = destinationSheet.getConditionalFormatRules();

          rules.push(copiedrule);
          destinationSheet.setConditionalFormatRules(rules);

        } catch  {
          ui.alert('Double check Sheet Name and the Range you have used & try again.');
        }

      } else {
        ui.alert('Cancelled');
      }

    } else {
      ui.alert('Selected range \"' + selectedRange + '\" doesn\'t contain any criteria.');
      return;
    }
  }
}

Demonstration

Note: On this demonstration, I have two sample sheets named the CURRENT (the main sheet that contains the conditional formatting rules) & DESTINATION

• Save the script as a bound script in your spreadsheet file
• Then, run the onOpen function once on the Apps Script editor to load the custom menu.
• After that, you will see a custom menu named "Extract Existing Conditional Formatting" on your spreadsheet as seen below:

• You can highlight any range that contains the Conditional Formatting that you'd like to copy to another sheet tab, then press the custom menu. It will show you the criteria type used and its value, as seen here (this sample was done on the CURRENT sheet) :

• If you press the Yes button, you will be prompted to change these details below:

1. Update the Criteria Value Or you could use the default value by just pressing Ok to go to the next step:
2. Type the Destination Sheet name
3. Type the Range (in A1 Notation format) where you want to apply the copied Conditional Formatting

• After that, on the DESTINATION sample sheet, the Conditional Formatting has been applied to the range.