Loading properties

The 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, object.load calls 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.values and 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:

 1 var myRange = context.workbook.worksheets
 2     .getItem("Sheet1").getRange("A1:B2");
 3 
 4 myRange.load("values, numberFormat");
 5 
 6 context.sync()
 7     .then(function () {
 8         console.log(JSON.stringify(myRange.values));
 9         console.log(JSON.stringify(myRange.numberFormat));
10     })
11     .catch(...);

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:

    ...
    .then(function() {
        return worksheet.getRange("A1").load("values")
    })
    ...

    // Is equivalent to the more verbose rendition, of
    // fetching the object, assigning it to a variable,
    // calling "load", and finally returning the object:
    
    ...
    .then(function() {
        var range = worksheet.getRange("A1");
        range.load("values");
        return range;
    })
    ...

Scalar vs. navigation properties – and their impact on load behavior

A bit of terminology: simple properties like values or 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 Range object.

• Any leaf node properties – whether on the Range object 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):

    myRange.load("address, format/fill/color");

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., myRange.load() or 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:

1. 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 .values property (that’s upwards of a million cells in each row, and so the API blocks unbounded ranges!), but would respond just fine to the address property.
2. 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.
3. 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.
4. 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:

 1 // ...
 2 // Initial loading of values:
 3 myRange.load("values");
 4 
 5 return context.sync()
 6     .then(function() {
 7         // ... Some operations that impact range values
 8 
 9         // Re-load the cell values to retrieve the latest:
10         myRange.load("values");
11     });
12 // ....

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 values property!

Beware misspellings!

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

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:

 1     // ...
 2     var sheets = context.workbook.worksheets;
 3     sheets.load("name");
 4     
 5     return context.sync().then(function() {
 6         for (var i = 0; i < sheets.items.length; i++) {
 7             console.log(sheets.items[i].name)
 8         }
 9     })
10     .catch(...);

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 .items property on the collection. The items property 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 name child 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 load("name").

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:
  • worksheets.getItem("Sheet1")    or
  • workbook.getSelectedRange()    or
  • range.getIntersection(anotherRange)
• OR, you are accessing the object via a property. For example:
  • worksheets.items[0]    or
  • chart.title,    or
    *range.worksheet, etc.

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.

1 Excel.run(function(context) {
2     var book = context.workbook;
3     book.getSelectedRange().load("address");
4     return context.sync()
5         .then(function() {
6             console.log(book.getSelectedRange().address);
7         });
8 })
9 .catch(OfficeHelpers.Utilities.log);

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.

Debug info: {"errorLocation":"Range.address"}

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.

 1 Excel.run(function(context) {
 2     var book = context.workbook;
 3     // Store the range into a variable:
 4     var range = book.getSelectedRange();
 5     range.load("address");
 6     return context.sync()
 7         .then(function() {
 8             console.log(range.address);
 9         });
10 })
11 .catch(OfficeHelpers.Utilities.log);

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.

 1 Excel.run(function(context) {
 2     var sheet = context.workbook.worksheets.getItem("Sheet1");
 3     var chart = sheet.charts.getItemAt(0);
 4     
 5     // Load the text of the chart title. Note that we
 6     // aren't creating a local variable for the title
 7     chart.title.load("text");
 8 
 9     return context.sync()
10         .then(function() {
11             // Note the "text" scalar-property access via
12             // the ".title" navigation property on the chart.
13             // This would *not* have worked if ".title"" were
14             // instead a method (a hypothetical "getTitle()").
15             console.log(chart.title.text);
16         });
17 })
18 .catch(OfficeHelpers.Utilities.log);