Extract values of conditional formating

Question

I have a spreadsheet that has literally hundreds of conditional formatting rules within it. All the rules are 'text is exactly' rules and includes postcodes such as BH17, CA24 etc.

I have recently amended the spreadsheet as per the following as the sheet was becoming slow due to the volume of rules: conditional format based on text in cells

Is there any way to extract the text from the rules based on the color of conditional formatting i.e pull all green rules and place the postcodes extracted into a column so that I can enter them into the correct columns or am I looking at the tedious task of starting from scratch as I am highly expecting?

Edit To confirm, I am looking to see if I could extract the text from conditional rules so an example using the image below would be: if I wanted to extract the postcodes for all rules that have an orange color attached it would put these values into a column somewhere, AB10,AB11,AB12,AB13 etc

Hope that makes sense but if not feel free to ask.

Answer 1

You can use this script to extract the values and colors from all conditional formatting rules applied to a given Sheet1 sheet and paste it to a sheet named Result:

The code:

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("Sheet1");
const resultSheet = ss.getSheetByName("Result");

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Solution')
    .addItem('ExtractValues', 'extractValues')
    .addToUi();
}

function extractValues() {
  const conditionalFormatRules = sheet.getConditionalFormatRules();
  if (conditionalFormatRules.length > 0) {
    const colors = {};
    conditionalFormatRules.forEach(rule => {
      var color = rule.getBooleanCondition().getBackground();
      if (!(color in colors)) {
        colors[color] = [];
      }
      colors[color].push(rule.getBooleanCondition().getCriteriaValues());
    })
    for (var i = 0; i < Object.keys(colors).length; i++) {
      color = Object.keys(colors)[i];
      resultSheet.getRange(1, i + 1).setValue(color).setBackground(color);
      resultSheet.getRange(2, i + 1, colors[color].length).setValues(colors[color]).setBackground(color);
    }
  }
}

Working example:

• It will group the results under its respective colors.