Reading and writing array properties

Relative to regular properties, where you can easily read a value and then set it to something else, array properties are special.

The way that the proxy-object model works, whenever you set a property on an object, the Office.js runtime has a hook into the setter and getter, which is used to intercept the call and add the command to the queue. For the technically-curious, the code use TypeScript getters and setters, which get compiled down to JavaScript’s Object.defineProperty API.

Let’s take an example of a regular property first. Per the above, whenever you set something like
    range.format.fill.color = "red"
the setter for the color property intercepts the request and internally adds a command into the queue for setting the range fill color to red (to be dispatched with the next context.sync).

On the other hand, if all you had was
    var color = range.format.fill.color
(after a load and a sync, of course), the getter would fire instead of the setter, and the color variable would get the the range’s current fill color.

Now, what about array properties?  Suppose you’ve loaded and synced a Range object, and you now want to iterate over its values, modifying some of them in place. For example, suppose you want to loop through the values, and replace any zeros with dashes. The naive (but sadly, incorrect) approach would be:

… // load and sync the range object
for (var r = 0; r < range.values.length; r++) {
    for (var c = 0; c < range.values[0].length; c++) {
       if (range.values[r][c] === 0) {
           range.values[r][c] = ‘-‘;
        }
    }
}

Why doesn’t this work?  On line #5, when setting a particular array value to a dash, you are effectively accessing the array value as a getter. From a runtime perspective, this line is no different from a slightly more verbose version:

var array = range.values;
array[r][c] = ‘-‘;

Because the getter for range.values returns a perfectly plain JS array object, accessing it and then setting its value does nothing to propagate it back to the original Range object.

If you want the values to get reflected back, the best thing is to get a reference to the array right after the sync (i.e., var array = range.values, just as above), then set the values on the array as needed, and then finally set it back to the object:
   range.values = array

It means you could also modify the values array in place, and then assign the values property back to itself at the completion of the loop  (range.values = range.values). However, this looks awkward, as if it’s a no-op, whereas in reality it is not. So personally, I prefer to retrieve the array at the beginning and assign it to its own variable, then do any necessary modifications, and finally set the full array back.