Best way to write loops with promises (ctx.sync) i

问题:

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?

回答1:

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 comment, 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 recommend 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

回答2:

The update of @Michael Zlatkovsky solves the problem of appending promises perfectly. The complement 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 comment 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 welcomed ;)

Cheers,