问题

I'm using the below code by @BryanMonterrosa. The code pulls the conditional format values of any marked in green but for some reason will not pull any other colors and shows error

> Exception: The number of columns in the data does not match the number of columns in the range. The data has 0 but the range has 1

I have now made sure that any conditional formatting on the target sheet, in this example,'Sheet 1', is within column A, I've tried adjusting some of the values in the code to try and get it to work but nothing has succeeded so far.

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);
    }
  }
}

** Edit **
I also seem to be getting error messages in some instances of 'TypeError: Cannot read properties of null (reading 'getCriteriaType')

I have now also attached a screenshot with sample data. If easier please direct me to where I can create a sample spreadsheet to upload


<details>
<summary>英文:</summary>

I&#39;m using the below code by @BryanMonterrosa. The code pulls the conditional format values of any marked in green but for some reason will not pull any other colours and shows error

&gt; Exception: The number of columns in the data does not match the number of columns in the range. The data has 0 but the range has 1

I have now made sure that any conditional formatting on the target sheet, in this example,&#39;Sheet 1&#39;, is within column A, I&#39;ve tried adjusting some of the values in the code to try and get it to work but nothing has succeeded so far.

    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(&quot;Sheet1&quot;);
    const resultSheet = ss.getSheetByName(&quot;Result&quot;);
    
    function onOpen() {
      var ui = SpreadsheetApp.getUi();
      ui.createMenu(&#39;Solution&#39;)
        .addItem(&#39;ExtractValues&#39;, &#39;extractValues&#39;)
        .addToUi();
    }
    
    function extractValues() {
      const conditionalFormatRules = sheet.getConditionalFormatRules();
      if (conditionalFormatRules.length &gt; 0) {
        const colors = {};
        conditionalFormatRules.forEach(rule =&gt; {
          var color = rule.getBooleanCondition().getBackground();
          if (!(color in colors)) {
            colors[color] = [];
          }
          colors[color].push(rule.getBooleanCondition().getCriteriaValues());
        })
        for (var i = 0; i &lt; 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);
        }
      }
    }

** Edit **
I also seem to be getting error messages in some instances of &#39;TypeError: Cannot read properties of null (reading &#39;getCriteriaType&#39;)

I have now also attached a screenshot with sample data. If easier please direct me to where I can create a sample spreadsheet to upload

[![enter image description here][1]][1]


  [1]: https://i.stack.imgur.com/xLUbx.png

</details>


# 答案1
**得分**: 1

The error appears whenever the data added to ```Range.setValue()``` is not the same size. The code is designed to work strictly with conditions of type ```Text is exactly```. 

It breaks whenever another type of validation is used because there are types that contain multiple values. i.e. ```Value is between 1 and 2``` will yield 2 values instead of just 1.

### Solution:

* You can use this modification that will only run the code for the appropriate type of conditional formatting rule:

```function extractValues() {
  const conditionalFormatRules = sheet.getConditionalFormatRules();
  if (conditionalFormatRules.length > 0) {
    const colors = {};
    conditionalFormatRules.forEach(rule => {
      if (rule.getBooleanCondition().getCriteriaType() == "TEXT_EQUAL_TO") { // < -- Create columns 
                                                                            // only for the correct type of 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);
    }
  }
}```

### Example:

[![Example][1]][1]

<details>
<summary>英文:</summary>

The error appears whenever the data added to ```Range.setValue()``` is not the same size. The code is designed to work strictly with conditions of type ```Text is exactly```. 

It breaks whenever another type of validation is used because there are types that contain multiple values. i.e. ```Value is between 1 and 2``` will yield 2 values instead of just 1.

### Solution:

* You can use this modification that will only run the code for the appropriate type of conditional formatting rule:

function extractValues() {
const conditionalFormatRules = sheet.getConditionalFormatRules();
if (conditionalFormatRules.length > 0) {
const colors = {};
conditionalFormatRules.forEach(rule => {
if (rule.getBooleanCondition().getCriteriaType() == "TEXT_EQUAL_TO") { // < -- Create columns
// only for the correct type of 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);
}

}
}

### Example:
[![Example][1]][1]
[1]: https://i.stack.imgur.com/vT6rg.gif
---
Note that this solution is focused on solving the reported error which I was able to reproduce after using different conditional formatting rule types, you could share a screenshot(s) of your conditional formatting rules so that I could try to replicate and get a proper solution if this one doesn't work.
</details>