How to get an Excel cell fill color caused by conditional formatting using office-js

Question

I'm setting conditional formatting using the Excel Javascript API that does different types of error checking. For example, if a user enters anything other than "foo" or "bar" in range A1:A10, then the conditional formatting will highlight the cell in red.

I am trying to write a function that returns a list of all the cells in A1:A10 that are highlighted in red without looping through the data again and checking if a value is "foo" or "bar". I just want to get the list of cells that conditional formatting has made red.

When I load the format/fill/color property using range.getCellProperties(), it returns white (#FFFFFF) even when the conditional formatting is showing the cell fill as red. How can I get the fill color of the cell after conditional formatting has evaluated?

Click here for code that reproduces this issue. Easy place to run this is in Script Lab. Also posting code below:

await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        // setup
        var rangeWithValues = sheet.getRange("A1:A5");
        rangeWithValues.values = [["foo"], ["bar"], ["foo"], ["bar"], ["foo"]];
        var conditionalFormatRange = sheet.getRange("A1:A10");
        conditionalFormatRange.conditionalFormats.clearAll();
        var conditionalFormat = conditionalFormatRange.conditionalFormats.add(Excel.ConditionalFormatType.custom);
        conditionalFormat.custom.rule.formula = `=NOT(OR(A1="foo",A1="bar"))`;
        conditionalFormat.custom.format.fill.color = "red";
        await context.sync();
        // get cell props
        var rangeProps = conditionalFormatRange.getCellProperties({
          address: true,
          format: {
            fill: {
              color: true
            }
          }
        });
        await context.sync();
        // output vals
        rangeProps.value.forEach(rowProps => {
          rowProps.forEach(cellProp => {
            console.log("address: " + cellProp.address + "; fill color: " + cellProp.format.fill.color);
          })
        })
      }); 

Answer 1

RangeFormat API can get the range Format, but it cannot get conditionalFormat as conditionalFormat didnt change the real range format, you could try to change background color in UI for A8, no matter what color you change, it displays as Red (the conditional format color).

Therefore, this would be a new request to detect the display format of the range, so I would suggest that you could submit your request in uservoice and upvote for this feature at https://officespdev.uservoice.com/forums/224641-feature-requests-and-feedback?category_id=163563. and upvote for this feature. thanks for your support