Get the fill color of a range of cells officejs

Question

I'm new to office.js and making add ins and I'm trying to make an add in for Excel. I've run into an issue for one thing that seems like it should be very easy, but isn't. I'm just trying to get the background color of the selected cells. From what I can tell, I'll need to loop through each selected cell and check the fill.color value individually, which is fine, except I keep getting an error when trying to read this property.

Error PropertyNotLoaded: The property 'color' is not available. Before reading the property's value, call the load method on the containing object and call "context.sync()" on the associated request context.

I don't quite understand why I would have to run the context.sync() for this, when it's already being run and I'm trying to use the code that was already generated by Visual Studio for the add in.

The error is confusing because I'm able to set the color like this without any issues. Here is the code I've added trying to get the fill color. The first line is commented out, but adds an orange fill to the selected cells no problem. I only added this to see if I could read out a value I knew was already set. I'm trying to get the user defined fill for a selected range though. The second line is where the error gets thrown.

//sourceRange.getCell(i, j).format.fill.color = "orange"; // this sets the color no problem when uncommented
$('#fa-output').append("color: " + sourceRange.getCell(i,j).format.fill.color + "<br>"); //this is where it can't get the fill color

I'm using the example that Visual Studio generates where it will randomly generate 9 cells of random numbers and highlight the highest number in the selected range. Here is the full code for this method:

// Run a batch operation against the Excel object model
    Excel.run(function (ctx) {
        // Create a proxy object for the selected range and load its properties
        var sourceRange = ctx.workbook.getSelectedRange().load("values, rowCount, columnCount, format");

        // Run the queued-up command, and return a promise to indicate task completion
        return ctx.sync()
            .then(function () {
                var highestRow = 0;
                var highestCol = 0;
                var highestValue = sourceRange.values[0][0];

                // Find the cell to highlight
                for (var i = 0; i < sourceRange.rowCount; i++) {
                    for (var j = 0; j < sourceRange.columnCount; j++) {

                        //sourceRange.getCell(i, j).format.fill.color = "orange"; // this sets the color no problem when uncommented
                        $('#fa-output').append("color: " + sourceRange.getCell(i,j).format.fill.color + "<br>"); //this is where it can't get the fill color

                        if (!isNaN(sourceRange.values[i][j]) && sourceRange.values[i][j] > highestValue) {
                            highestRow = i;
                            highestCol = j;
                            highestValue = sourceRange.values[i][j];
                        }
                    }
                }

                cellToHighlight = sourceRange.getCell(highestRow, highestCol);
                sourceRange.worksheet.getUsedRange().format.font.bold = false;

                // Highlight the cell
                cellToHighlight.format.font.bold = true;

                $('#fa-output').append("<br>The highest value is " + highestValue);

            })
            .then(ctx.sync);
    })
    .catch(errorHandler);

Answer 1

You have a lot of commented out code in your code that makes it hard to read.

At any rate, this is expected behavior. You have to load() and then sync() when you want to read a property of an object in the workbook. It's the load-and-sync that brings the value of the property from the workbook to the JavaScript in your add-in so you can read it. Your code is trying to read a property that it hasn't first loaded. The following is a simple example:

const cell = context.workbook.getActiveCell();
cell.load('format/fill/color');
await context.sync();
console.log(cell.format.fill.color);

ES5 version:

const cell = context.workbook.getActiveCell();
cell.load('format/fill/color');
return context.sync()
.then(function () {
    console.log(cell.format.fill.color);
});    

You should also take a look at the Range.getCellProperties() method, which is a kind of wrapper around the load.