OfficeScript: how to get the cell color applied by conditional formatting

Question

My sheet uses conditional formatting rules (CFR) to set the cell background color based on the value.

cell.getFormat().getFill().getColor() does not return the real used color applied by the CFR.
Is there any method to do this?

My second approach would have been to get all CFRs for this cell and check which is applied but neither does such method exist.

Any ideas how to accomplish this?

Answer 1

I finally figured it out for my Excel cell using custom conditional format using the code below. The key is to use getConditionalFormat("ID") with the ID being ID of the conditional format type. For custom type it is 0. I figured it out when reading the results of getConditionalFormats()

function main(workbook: ExcelScript.Workbook) {
    const range = workbook.getWorksheet("Declaration").getRange("D56"); // Replace with your desired cell or range

    // Retrieve the background color of the cell
    const cellColor = range.getConditionalFormat("0").getCustom().getFormat().getFill().getColor()
    // Log the background color
    console.log(cellColor);
}