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