Understanding proxy objects

Setting basic object properties

At the fundamental level, the Office 2016+ API model consists of local JavaScript proxy objects, which are the local placeholders for real objects in the document. You get a proxy object by calling some property or method (or a chain of properties or methods) that originate somewhere off of the request context. For example, if you have an Excel object called workbook, you can get cells A1:B2 on Sheet1 by calling

    var myRange = workbook.worksheets.getItem("Sheet1")
        .getRange("A1:B2");

To apply values to the object, you would simply set the corresponding properties. For example:

    myRange.values = [["A", "B"], [1, 2]];
    myRange.format.fill.color = "yellow";

The same applies to calling a method – you simply invoke it. For example,

    myRange.clear();

Importantly, what happens beneath the covers is that the object – or, more accurately, the request context from which the object derives – accumulates the changes locally, much like a changelist in a version-control system. Nothing is “committed” back into the document until you recite the magical incantation:

    context.sync();

How long can you keep going?  And what happens on errors?

Let’s pause for a moment. If all of these JavaScript objects are simply proxy objects, and all you’re doing is queuing up commands, how long can you keep going? It turns out that as long as you don’t need any information back from the document (i.e., you’re not reading back some values and then multiplying or formatting or doing whatever else with them), you can create an arbitrarily-long queue of commands. In the population-data example, within the block of code that’s inside of .then(function() { ... }), I issued a bunch of commands all at once: creating a new sheet, setting some values on it, overlaying it with a table and adding a bunch of rows, formatting the table, adding and positioning a chart, and finally setting focus on the newly-created output sheet.

On the JavaScript side, whenever I called a property that involved primitive types – so, for example, setting sheetHeader.values – the action would get noted and added to the internal queue, and then the JavaScript operations would proceed as is. Likewise, whenever I called a void returning method – such as sheetHeader.merge() – this too would get internally noted and added to the queue. If I called a property or method that returns an API object, though – for example, context.workbook.worksheets.add(...), or outputSheet.getRange("B2:D2") – Office.js would go and create a new on-the-fly proxy object and return it to me, noting internally how this object was derived.

In all those cases, the overarching paradigm is that Office.js tries to create the illusion that the operations are synchronous, even though in reality it simply added them to an internal queue, and waits until they can be “flushed” as part of a context.sync().

An interesting corollary to this is that even “erroneous” code (such as accessing a non-existing item) won’t throw until it’s part of the “context.sync()” execution – up to that point, the changes are merely queued up. So for example, you can easily write the following code:

Oh-oh. Code executing past an obvious OM error
1 context.workbook.worksheets.getItem
2     ("SheetThatDoesNotExist").getRange("A1:B2").clear();
3 console.log("I'm past the call");
4 context.workbook.getSelectedRange().format.fill.color = "yellow";
5 
6 context.sync().then(...).catch(...);

When you run the above code, execution at the JavaScript layer will happily keep going past the SheetThatDoesNotExist line to the next JavaScript call: console.log("I'm past the call)". And in fact, it will keep on going all the way through till context.sync().

This is probably not what you’d expect – after all, shouldn’t the invalid-sheet-fetching throw an error? In a synchronous VBA/VSTO-like world, it absolutely would. But remember, in this proxy-object batched-execution world of Office.js, all you’re doing is building up an array of commands, but not yet executing them! As discussed in “Why is Office.js async?”, querying the document every time to perform a document-object-model request would be impractical. And so, without that direct feedback, the JavaScript layer has no way of knowing that SheetThatDoesNotExist does not exist in the workbook. Only when the JS layer is waiting on the asynchronous completion of context.sync() – and when the host application is meanwhile processing the queue of commands – does the error become apparent. At that point, the execution halts on the host applications’ side, dropping any other calls that were queued up in the batch (in this case, setting the selected range to yellow). The host then signals back to the JavaScript engine to return a failure in the form of a rejected promise. But importantly, any non-API-object JavaScript calls that preceded context.sync() will have already run!

This is the trickiest part of the new Office.js’s async model, and the most common pitfall (especially if you accidentally let a context.sync() run loose without awaiting it, thereby executing a bunch of JavaScript before the document object-model calls came back). So remember: it’s all proxy objects – and nothing happens until you call context.sync()"!