object.load("property-names") method call
The preceding topic only talked about setting properties or calling methods on document objects. What about when you need to read data back from the document?
For reading back document data, there is a special command on each object,
object.load(properties). An identically-functioning method can also be found on the context object:
context.load(object, properties). The two are 100% equivalent (in fact,
context.load internally), so which one you use is purely stylistic preference. I generally prefer
object.load, just because it feels like I’m dealing with the object directly, rather than going through some context middleman/overlord. But again, it’s purely stylistic.
The load command is queued up just like any of the others, but at the completion of a
context.sync() – which will be covered in greater detail in a subsequent “Syncing object state” section – an internal post-processing step will automatically populate the object’s properties with the requested data. The property names that you pass in into the
object.load(properties) call are the very properties that – after a
sync – you will be able to access directly off of the object.
For example, if you look at the IntelliSense for the Excel
Range object, you will see that it contains properties like
range.numberFormat. If you need these values and number formats in order to execute your function (maybe to display them to the user, or to calculate the minimum/maximum, or to copy them to a different section of the workbook, or to use them to make a web service call, etc.), you would simply list them as a comma-separated list in the
.load function, as show in the example below:
Importantly, just as in the code above, the reason to call
object.load is that you intend to read back the values in the code following the
context.sync(), and that your operation can’t continue without having that information. This brings us to arguably the most important rule in all of Office.js:
The return value of
object.load(...) is the object itself. This is done purely for convenience, saving a line or two of vertical space:
Scalar vs. navigation properties – and their impact on
A bit of terminology: simple properties like
numberFormat off of an API object like
Excel.Range are called scalar properties. These scalar properties are either primitive types (numbers, Booleans, strings), arrays of primitive types, or complex objects (i.e., plain JS objects composed of primitives or arrays of primitives; the moral equivalent of a C++/C# struct). One way to think about scalar properties is that they are the sort of simple properties you could serialize and send over the wire via JSON, XML, etc.
By contrast, any API objects are instances of API classes, and are not serializable (they are classes, with methods and all, not just pure data!). Such properties are called “navigation properties”, because they’re used to navigate you from one API object to another – but they ultimately carry no data of their own. So, whereas you need to load the scalar properties that you intend to use, you do *not* need to load navigation properties.
In case an analogy helps, you can imagine the object hierarchy as a tree structure. In this case, let’s imagine we start with the
- Any leaf node properties – whether on the
Rangeobject directly, or on one of its descendants – are scalar properties (marked in green).
- Any non-scalars are navigation properties (marked in blue).
When specifying properties to load, you can specify either scalar properties either directly off the object, or scalar properties that are accessible via navigation properties off the object. For example, to load both the cell address and the fill color of a Range, you would specify the following (note that the scalar property is always the “leaf-node” one – and any preceding slashes indicate navigation properties):
Technically speaking, you are not required to include the property names. You could (though you shouldn’t!) write a
load statement with no property names passed in (e.g.,
context.load(myRange)). But if you do that, Office.js will load all scalar properties on the object – which on the Excel Range object, for example, is over a dozen! – even if you end up using only one of them! Moreover:
- For some properties (i.e., Range values in Excel), loading a particular property might fail, even if some of the other properties would load just fine. Concrete example: an unbounded range like columns A:C will throw an error if you try to load their
.valuesproperty (that’s upwards of a million cells in each row, and so the API blocks unbounded ranges!), but would respond just fine to the
- Even if you don’t notice the difference on Desktop, the delay (and bandwidth costs!) of the extra properties will almost certainly show in Office Online.
- Even if an object exposes just two properties today, and you’re using them both, that’s not to say that more won’t be exposed eventually! This means that your add-in might slow down (and start chewing through more bandwidth) over time, as more APIs are added. In short, even if you’re currently using all properties of the object, and hence don’t see a need to load the properties explicitly, you are still better off writing them out to prevent future slowdown.
- The effects of #2 and #3 are multiplied ten-fold or hundred-fold when loading a collection (proportional to the number of elements in the collection – which often-times is something that the user controls, but not the developer!)
Loading and re-loading
If you receive a
PropertyNotLoaded error when accessing a property, you must have forgotten to load it! The fix is fortunately very simple: add the
load, and ensure there is a
sync somewhere upstream from where you’re using the property.
Note that the
PropertyNotLoaded error will only be thrown when you initially forget to load the property. If you manipulate the object (i.e., set the
formula property on a cell where you’ve previously loaded a
value), or if it gets impacted by other external factors (i.e.: a formula dependency on another cell), it is your responsibility to remember to re-load the property:
When loading or re-loading properties on a regular (non-collection) object, note that only the specified property names will be re-loaded; the rest will be kept as is. So, for example, loading
myRange.load("address") within the
.then above would not have refreshed the
There is one easily-overlooked “gotcha” with the
load method: it does not throw an exception, even if you specify incorrect property names. Instead, it simply no-ops on names that it doesn’t recognize.
So: if you think you’ve loaded a property, and you know you did a
sync upstream, and yet you’re still getting a “PropertyNotLoaded” error, check your spelling! For better or worse, the
load method always succeeds – even if you pass in bogus property names! If
load sees properties it doesn’t know about, it simply ignores them, causing the property that you wanted to stubbornly (and justifiably) insist that it hasn’t been loaded.
Loading collections is similar to loading regular objects – except that the properties you specify are the properties of the children. There are a few nuances to collections in particular (see “Loading collections” for more details), but the general use is easy enough. Simply call
load on the collection, passing in the names of the child properties. After syncing, you can access the items using the collection’s
items property. For example, to list out the names of all worksheets in Excel, you would do:
Again, notice that with a collection:
- When loading, you specify the name(s) of the child property(ies), not properties of the collection itself (of which there are usually very few, typically just a
.count, if that).
- When accessing loaded items, you use the
.itemsproperty on the collection. The
itemsproperty returns a plain 0-indexed JS array containing the loaded elements (with the specified properties pre-loaded).
- If you find it unsymmetrical to load the
namechild property, but use it as
items[x].name, you can specify the load statement as
load("items/name")instead. Internally, when the runtime sees this syntax, it simply strip out the
items/portion of the load statement. It’s up to you which syntax you wish to use, but know that the canonical load statement is just
Loading on methods versus properties
When working with API objects, it is crucial to take note whether
- you are accessing the object via a method. For example:
- OR, you are accessing the object via a property. For example:
The reason for caring about the distinction is that method invocations – unlike property access – always return a new object! Let me show this via two examples: first the incorrect usage, and then the correct one.
The above code will produce the following error:
PropertyNotLoaded: The property ‘address’ 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.
The reason that this is an error is that you are retrieving the object anew the second time when you reference it – and so you get a brand new proxy object, which has no knowledge of the information you loaded on its twin. This, by the way, is a very common mistake when working with collections: to first fetch
collection.getItem("key") and call
load on it, then sync, and then re-query
collection.getItem("key") – with the latter being a brand new copy of the original object, which defeats the purpose of having loaded the item to begin with!
Thus, the proper way to use
load on an object that was retrieved via a method call is to keep a reference to the variable, and use it when reading back the data, rather than re-fetching the item via the method.
Again, it’s important to call out that the guidance above is purely for objects retrieved via a method call. In the case of property access, you don’t have to store the intermediate navigation properties** (unless you want to): after the initial property invocation, the exact same instance of the object will be returned time and time again. For example, the following is perfectly valid.