There are many threads that discuss about guaranteeing execution order of promises in loops. I would like to know what is the best practice in JavaScript API for Office Add-ins. Most of the time, the promise in question is ctx.sync().

Here is a snippet to print the address of a list of Excel ranges one by one. The test shows that it respects well the order of Excel ranges. But the question is whether and how to guarantee the execution order?

function loadAll () {
   var ranges = ["A:A", "B:B", "C:C", "D:D", "E:E"];
   var sheet = "Sheet1";
   for (var i = 0; i < ranges.length; i++) {
       loadRange(ranges[i], sheet);        
   }
}

function loadRange (range, sheet) {
   Excel.run(function (ctx) { 
       var r = ctx.workbook.worksheets.getItem(sheet).getRange(range);
       r.load('address');
       return ctx.sync().then(function() {
          console.log(r.address);
       });
   });
}

Could anyone help?

There are many threads that discuss about guaranteeing execution order of promises in loops. I would like to know what is the best practice in JavaScript API for Office Add-ins. Most of the time, the promise in question is ctx.sync().

Here is a snippet to print the address of a list of Excel ranges one by one. The test shows that it respects well the order of Excel ranges. But the question is whether and how to guarantee the execution order?

function loadAll () {
   var ranges = ["A:A", "B:B", "C:C", "D:D", "E:E"];
   var sheet = "Sheet1";
   for (var i = 0; i < ranges.length; i++) {
       loadRange(ranges[i], sheet);        
   }
}

function loadRange (range, sheet) {
   Excel.run(function (ctx) { 
       var r = ctx.workbook.worksheets.getItem(sheet).getRange(range);
       r.load('address');
       return ctx.sync().then(function() {
          console.log(r.address);
       });
   });
}

Could anyone help?

• javascript
• asynchronous
• promise
• ms-office
• office-js

Share Improve this question Follow edited Jun 18, 2016 at 18:54 SoftTimur asked Jun 18, 2016 at 18:20 SoftTimurSoftTimur 5,6104545 gold badges166166 silver badges347347 bronze badges 11

• Why do you care about execution order? What does Excel.run return? – Bergi Commented Jun 18, 2016 at 18:53
• Excel.run() executes a batch script that performs actions on the Excel object model. The batch mands include definitions of local JavaScript proxy objects and sync() methods that synchronize the state between local and Excel objects and promise resolution. (overview) – SoftTimur Commented Jun 18, 2016 at 18:56
• I do care about execution order... because I am going to some plex things inside rather than loadRange or console.log(r.address), they are dependent to the order... – SoftTimur Commented Jun 18, 2016 at 18:58
• Then chain those things onto each other with then. I don't think you want to call Excel.run multiple times (or do you?) – Bergi Commented Jun 18, 2016 at 19:00
• 1 From the docs you linked above: "The run method takes in RequestContext and returns a promise (typically, just the result of ctx.sync())". It's seems to be used only for garbage collection / resource allocation. – Bergi Commented Jun 18, 2016 at 19:14

 |  Show 6 more ments

2 Answers 2

Sorted by: Reset to default 7

Because Excel.run returns a Promise, you can chain it with a .then and guarantee order. I.e.,

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

That being said... this would be pretty dang inefficient. A much better approach would be to load all the objects you need in one batch, creating only one network roundtrip (especially important with Excel Online... but noticeable even on the Desktop):

function loadAll () {
    Excel.run(function(ctx) {
        var ranges = ["A:A", "B:B", "C:C", "D:D", "E:E"];
        var sheet = "Sheet1";

        var loadedRanges = [];
        for (var i = 0; i < ranges.length; i++) {
            var r = ctx.workbook.worksheets.getItem(sheet).getRange(ranges[i]);
            r.load('address');
            loadedRange.push(r);
        }

        return ctx.sync()
            .then(function() {
                for (var i = 0; i < loadedRanges.length; i++) {
                    console.log(loadedRanges[i].address);
                }
            });
    });
}

UPDATE

If, as per ment, you do end up needing to do separate tasks that depend on each other and that each require a roundtrip, and hence do need to be sequenced via chaining Excel.run, I would remend something as follows:

function loadAll () {
    var ranges = ["A:A", "B:B", "C:C", "D:D", "E:E"];
    var sheet = "Sheet1";

    // Create a starter promise object
    var promise = new OfficeExtension.Promise(function(resolve, reject) { resolve (null); });

    for (var i = 0; i < ranges.length; i++) {
        // Create a closure over i, since it's used inside a function that won't be immediately executed.
        (function(i) {
            // Chain the promise by appending to it:
            promise = promise.then(function() {
                return loadRange(ranges[i], sheet);
            })
        })(i);       
    }
}

function loadRange (range, sheet) {
    return Excel.run(function (ctx) { 
        var r = ctx.workbook.worksheets.getItem(sheet).getRange(range);
        r.load('address');
        return ctx.sync().then(function() {
            console.log(r.address);
        });
    });
}

~ Michael Zlatkovsky, developer on Office Extensibility team, MSFT

The update of @Michael Zlatkovsky solves the problem of appending promises perfectly. The plement added by @SoftTimur allows to wait for all promises to be done before doing a .then(), which is also very convenient !

My only remark about these posts would be if ANY promises throws an error, the other appended promises stop being treated.

In my case, the scenario is a little different. Just to clarify:

Excel.run(function(context){
    return runWorkbook(context, context.workbook)
        .then(function(){ var cool = "all promises worked !" }
        .catch(function(error)) { var bad = "do not want to be here :(" });
}

function runWorkbook(context, workbook){
    const sheets = workbook.worksheets;
    sheets.load("$none");
    return context.sync().then(function(){
        let promise = new window.OfficeExtension.Promise(function(resolve, reject) { resolve(null); });
        sheets.items.forEach(function(ws) {
            promise = promise.then(function() {
                return makeWorkOnWorksheet(ws)
                    .then(context.sync())
                    .catch(function(error)){
                        // DO NOTHING BUT CAN NOT THROW ERROR OTHERWISE IT BREAKS THE NEXT APPENDED PROMISES
                    });
        }
        return promise;
    }
}

This solution works.. (catch the error as in ment and doing nothing with it)

I don't like this solution but this is the only way I found to allow all appended promises to be done.

If somebody has a better idea, it's weled ;)

Cheers,