Office-js Excel addin : when to return context.sync()

Question

I have trouble to understand when to use context.sync().

Here is a basic example but which resumes my lack of understanding:

Excel.run(function (context){
    const wb = context.workbook;
    const ws = wb.worksheets.getActiveWorksheet();

    // should never happened, but is it correct to check like this ?
    if (wb === null) {
        // IS IT CORRECT TO DO THIS ?
        // I just want to exit the function
        // return; would be enough ? What's going on in the callstack?
        return context.sync();
    }
    ws.load("name");
    return context.sync().then(function() {
        var name = wb.name;
        // do stuff
        var range = ws.getRangeByIndexes(1,1,10,10);
        return context.sync().then(function() {
             ws.names.add("NEWRANGE", range);
             // mandatory return context.sync() to refresh Excel object ?
             // doesn't work otherwise for me
             return context.sync();
        });
    }
}).catch(function(error) {
  // do stuff
}

If somebody could explain, it would be more than welcome :)

Cheers.

Answer 1

I think it'll help if you think of these objects as proxy objects. They are only a representation of the real object and not all properties will be available on the proxy object because they don't need to be available. Similarly, changes made to the proxy object won't update the real object. context.sync() is used to sync the proxy objects with the real objects.

Looking at your code, the first context.sync() is unnecessary because you don't need to retrieve anything or make any changes. Actually the entire condition if (wb === null) is unnecessary because context.workbook cannot be null.

As soon as you try to ws.load("name");, you need a context.sync() because you've tried to access a property on the proxy object that needs to be loaded from the real object.

When you call var range = ws.getRangeByIndexes(1,1,10,10);, you don't need a context.sync() because you're just grabbing another proxy object but no changes have been made and no properties are accessed.

But since ws.names.add("NEWRANGE", range); is a real change, you'll need a context.sync() to reflect the change on the real object. Technically, the last context.sync() is not necessary because Excel.run will actually call context.sync() after running everything inside the Excel.run(). That said, it's good practice to have an ending context.sync() anyway.

You can also batch independent operations in one context.sync(). Since var range = ws.getRangeByIndexes(1,1,10,10); has nothing to do with ws.names.add("NEWRANGE", range);, you can actually put them behind a single context.sync().

I'd also suggest switching to using TypeScript to keep your code cleaner and easier to understand. Try using ScriptLab in Excel. There are a lot of samples that should help you understand context.sync() and office-js in general.

Lastly, here's the code that you could have written to do the same thing.

Excel.run(function (context) {
    const wb = context.workbook;
    const ws = wb.worksheets.getActiveWorksheet();

    ws.load("name");
    return context.sync().then(function () {
        var name = wb.name;
        // do stuff
        var range = ws.getRangeByIndexes(1, 1, 10, 10);
        ws.names.add("NEWRANGE", range);
        return context.sync();
    });
}).catch(function (error) {
    // do stuff
});

Oh and you should take a look at Michael's book as Cindy suggested.