Office.js: Can I set the fill of a range of cells from a 2d array?

Question

So far, with office.js, I'm getting used to this pattern, where you assign an array of data to a corresponding excel range:

 range.values = [["Eurasia", "29.96", "0.25", "15-Feb" ]];
 range.numberFormat = [[null, null, null, "m/d/yyyy;@"]];

However, I'm trying to change the background color of a range of cells using different colors in different cells and I already have a 2d array of colors which pertain to each cell.

As far as I can tell, it looks like you can assign the fill color by looping over every single cell one by one:

    var rowCount = selectionRange.values.length;
    var columnCount = selectionRange.values[0].length;
    for (var row = 0; row < rowCount; row++) {
        for (var column = 0; column < columnCount; column ++) {
            if (selectionRange.values[row][column] > 50) {
                selectionRange.getCell(row, column)
                    .format.fill.color = "yellow";
            }
        }
    }

Or by assinging a single color to an entire range:

range.format.fill.color = "yellow";  

But I'm looking for something like:

range.fill = [["yellow", "red", "red", "white"]];

It seems like there'd be an option similar to how you set values or numberFormat, but I haven't been able to find it.

Does this exist, or do I need to set it one by one?

Answer 1

For the formatting properties (and basically, non-arrays), you do have to set one cell at a time.

We have an item on the backlog to allow array-like reading and setting of these properties, but we don't have them yet. I'll bring it up again to the team, but it's not at the forefront of the queue.

Depending on the size of your array (and essentially, how many ranges you are creating inside of the one Excel.run call), there may be some perf implications. If you run into issues there, let me know, and I can provide more info (I recommend opening a separate thread for it, though).

By the way, there are some fun samples of setting multiple formats at a time shown as snippet in Script Lab.