Chaining Promises in Office.js with Excel.run()

Question

I’m working with the new office.js. I’m using the Excel.run functionality that returns a promise. I have a question about the promises pattern implemented by the library.

The samples all show this pattern

Excel.run( function (ctx) {

  //set up something

  return ctx.sync().then (function () {
    //call another function somewhere to chain operations
  });

}).then ( function () {
  //do something else if you want
}).catch (function (error) {
  handle errors
});

The problem is the ctx.sync().then() contained within Excel.run() The way it is presented, you can’t chain promises in accordance with the promises spec because you lose the context object if you try and handle the then() outside of Excel.run() So, the pattern seems to be promoting nested function calls, which is what promises are supposed to eliminate.

What I want to do is sequence several calls together through chaining like this:

Excel.run( function (ctx) {
  return ctx.sync();
}).then ( function (ctx) {
  return ctx.sync();
}).then ( function (ctx) {
  return ctx.sync();
}).then ( function (ctx) {
  return ctx.sync();
}).catch (function (error) {

});

Is this possible?

Answer 1

In general, the purpose of Excel.run is for a sequential operation against the OM with automatic cleanup at the end. That is, Excel.run creates a context, runs you operation, and then cleans up any host objects that were allocated.

That being said, as mentioned by Gab Royer, you can pass objects out. And moreover, each Excel object has a back-pointer to its "context" via the ".context" property. So for example, you can do this:

Excel.run(function (ctx) {
    var worksheet = ctx.workbook.worksheets.getActiveWorksheet();
    return ctx.sync(worksheet);
}).then(function(worksheet) {
    worksheet.name = "Test"
    return worksheet.context.sync();
}).catch(function(e) {
    console.log(e)  
});

As you can see, in the code above, you had created the worksheet object inside the Excel.run, but are using it outside.

If you have something like a Range object, it gets a little trickier. Ranges, unlike Worksheets, do not have persistent IDs (How could they? There is essentially a countless number of permutations of all possible combinations of cells). Instead, during Excel.run, we automatically create persistent pointers to the backing Range objects that get adjusted and kept-track-of by Excel. When the batch inside of Excel.run completes, we tell the host to destroy these references. So if you had code like this:

Excel.run(function (ctx) {
    var range = ctx.workbook.getSelectedRange();
    return ctx.sync(range);
}).then(function(range) {
    range.format.fill.color = "red";
    return ctx.sync();
}).catch(function(e) {
    console.log(e)  
})

It would run into an "InvalidObjectPath" error.

However, you can opt out of the tracked-object cleanup by manually adding the object to the ctx.trackedObjects collection. In doing this, however, you are taking it upon yourself to clean up at the end -- and you need to be extra careful, to remember to cleanup on not only on success, but on failure. Otherwise, you're essentially creating a memory leak that will keep slowing down the Excel host application.

var range;
Excel.run(function (ctx) {
    range = ctx.workbook.getSelectedRange();
    ctx.trackedObjects.add(range);
    return ctx.sync(range);
}).then(function(range) {
    range.format.fill.color = "red";
    return range.context.sync();
}).then(function() {
    // Attempt to clean up any orphaned references
    range.context.trackedObjects.remove(range);
    range.context.sync(); // don't need to await it, since it's just the final cleanup call
}).catch(function(e) {
    console.log(e);
})

Long story short: it is certainly doable, and you can use objects after Excel.run. You'll just need to be responsible for memory-management for any objects that require "tracking". In the example above, there is no reason to go through this effort, since you could just as well have had the same code inside of Excel.run (remember, you can chain promises within the batch inside of Excel.run, too -- no need to do this on the outside). But if you have a scenario, where, say, you have a timer job that needs to run every so often (e.g., to update a stock ticker), or you want to create a button with an onclick handler for a particular object, etc. the technique above will let you create the objects inside of Excel.run, and then use them outside of it.

PS: With regards to the pattern requiring nesting: It is true that if you need to chain ctx.sync() calls within Excel.run, you will end up with a layer of nesting -- but just a single extra layer. Within, you still would still be able to chain your promises without the callback pyramid. E.g.,:

Excel.run(function (ctx) {
    var range = ctx.workbook.worksheets.getActiveWorksheet().getRange("A1:C3");
    range.load("values");
    return ctx.sync()
        .then(function () {
            // Some set of actions against the OM, now that the "values"
            // property has been loaded and can be read from the "range" object.
        })
        .then(ctx.sync)
        .then(function () {
            // Another set of actions against the OM, presumably after doing
            // another load-requiring operation (otherwise could have
            // been part of the same .then as above)
        })
        .then(ctx.sync)
        .then(function() {
            // One final set of actions
        });     
}).catch(function(error) {
    console.log("Error: " + error);
});

Answer 2

While this would be possible since Excel.RequestContext.sync takes in a pass-through value, the goal of Excel.run is to manage trackedObjects for the function that it gets passed in. In promises chained after Excel.Run you would have to manage trackedObjects yourself, hence defeating the purpose of Excel.Run.

I'd suggest either declaring your function outside the Excel.Run if you don't like the added indentation, or creating your own RequestContext object.