range.address throws context related errors

Question

We've been developing using Excel JavaScript API for quite a few months now. We have been coming across context related issues which got resolved for unknown reasons. We weren't able to replicate these issues and wondered how they got resolved. Recently similar issues have started popping up again. Error we consistently get:

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.

We thought as we have multiple functions defined to modularise code in project, may be context differs somewhere among these functions which has gone unnoticed. So we came up with single context solution implemented via JavaScript Module pattern.

    var ContextManager = (function () {
        var xlContext;//single context for entire project/application.
        function loadContext() {
            xlContext = new Excel.RequestContext();
        }
        function sync(object) {
            return (object === undefined) ? xlContext.sync() : xlContext.sync(object);
        }
        function getWorksheetByName(name) {
            return xlContext.workbook.worksheets.getItem(name.toString());
        }
        //public
        return {
            loadContext: loadContext,
            sync: sync,
            getWorksheetByName: getWorksheetByName
        };
    })();

NOTE: above code shortened. There are other methods added to ensure that single context gets used throughout application. While implementing single context, this time round, we have been able to replicate the issue though.

    Office.initialize = function (reason) {
        $(document).ready(function () {
            ContextManager.loadContext();
            function loadRangeAddress(rng, index) {
                rng.load("address");
                ContextManager.sync().then(function () {
                    console.log("Address: " + rng.address);
                }).catch(function (e) {
                    console.log("Failed address for index: " + index);
                });
            }
            for (var i = 1; i <= 1000; i++) {
                var sheet = ContextManager.getWorksheetByName("Sheet1");
                loadRangeAddress(sheet.getRange("A" + i), i);//I expect to see a1 to a1000 addresses in console. Order doesn't matter.
            }
        });
    }

In above case, only "A1" gets printed as range address to console. I can't see any of the other addresses (A2 to A1000)being printed. Only catch block executes. Can anyone explain why this happens? Although I've written for loop above, that isn't my use case. In real use case, such situations occur where one range object in function a needs to load range address. In the mean while another function b also wants to load range address. Both function a and function b work asynchronously on separate tasks such as one creates table object (table needs address) and other pastes data to sheet (there's debug statement to see where data was pasted).

This is something our team hasn't been able to figure out or find a solution for.

Answer 1

There is a lot packed into this code, but the issue you have is that you're calling sync a whole bunch of times without awaiting the previous sync.

There are several problems with this:

• If you were using different contexts, you would actually see that there is a limit of ~50 simultaneous requests, after which you'll get errors.
• In your case, you're running into a different (and almost opposite) problem. Given the async nature of the APIs, and the fact that you're not awaiting on the sync-s, your first sync request (which you'd think is for just A1) will actually contain all the load requests from the execution of the entire for loop. Now, once this first sync is dispatched, the action queue will be cleared. Which means that your second, third, etc. sync will see that there is no pending work, and will no-op, executing before the first sync ever came back with the values!
  • [This might be considered a bug, and I'll discuss with the team about fixing it. But it's still a very dangerous thing to not await the completion of a sync before moving on to the next batch of instructions that use the same context.]

The fix is to await the sync. This is far and away the simplest to do in TypeScript 2.1 and its async/await feature, otherwise you need to do the async version of the for loop, which you can look up, but it's rather unintuitive (requires creating an uber-promise that keeps chaining a bunch of .then-s)

So, your modified TypeScript-ified code would be

ContextManager.loadContext();
async function loadRangeAddress(rng, index) {
    rng.load("address");
    await ContextManager.sync().then(function () {
        console.log("Address: " + rng.address);
    }).catch(function (e) {
        OfficeHelpers.Utilities.log(e);
    });
}
for (var i = 1; i <= 1000; i++) {
    var sheet = ContextManager.getWorksheetByName("Sheet1");
    await loadRangeAddress(sheet.getRange("A" + i), i);//I expect to see a1 to a1000 addresses in console. Order doesn't matter.
}

Note the async in front of the loadRangeAddress function, and the two await-s in front of ContextManager.sync() and loadRangeAddress.

Note that this code will also run quite slowly, as you're making an async roundtrip for each cell. Which means you're not using batching, which is at the very core of the object-model for the new APIs.

For completeness sake, I should also note that creating a "raw" RequestContext instead of using Excel.run has some disadvantages. Excel.run does a number of useful things, the most important of which is automatic object tracking and un-tracking (not relevant here, since you're only reading back data; but would be relevant if you were loading and then wanting to write back into the object).

Finally, if I may recommend (full disclosure: I am the author of the book), you will probably find a good bit of useful info about Office.js in the e-book "Building Office Add-ins using Office.js", available at https://leanpub.com/buildingofficeaddins. In particular, it has a very detailed (10-page) section on the internal workings of the object model ("Section 5.5: Implementation details, for those who want to know how it really works"). It also offers advice on using TypeScript, has a general Promise/async-await primer, describes what .run does, and has a bunch more info about the OM. Also, though not available yet, it will soon offer information on how to resume using the same context (using a newer technique than what was originally described in How can a range be used across different Word.run contexts?). The book is a lean-published "evergreen" book, son once I write the topic in the coming weeks, an update will be available to all existing readers.

Hope this helps!