2

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.

Val
  • 33
  • 7
  • I recommend this e-book: https://leanpub.com/buildingofficeaddins where Michael Zlatkovsky, a member of the MS team developing the JS APIs, explains this. FWIW the last of your context.sync calls is defiitely *not* necessary. The second is. For the first: not necessary with what you show us, but it depends what's going on in the `if`. – Cindy Meister Aug 21 '18 at 13:09
  • From what I have understood, I need to use context.sync() is I need an interaction with Excel, such as ws.names.add("new_range", range). I'll modify the example to be clearer. – Val Aug 21 '18 at 13:27
  • Example modified with add the new ranged name. – Val Aug 21 '18 at 13:35
  • You need to use context.sync whenever you require information from Excel in order to use it in the code. In your example, the only time you "query" something in Excel is the `name` property, so you load that property and then you need to sync. Really, the best way to get a feel for it if you've read the book and still aren't sure, is to simply try. – Cindy Meister Aug 21 '18 at 13:35
  • @Val Please use the Script Lab tool (https://appsource.microsoft.com/en-us/product/office/WA104380862?corrid=ed93ce54-3f2c-48ab-9df7-d9913f7b190b&omexanonuid=4a0102fb-b31a-4b9f-9bb0-39d4cc6b789d) to make sure that you've got at least formally correct code. You've got at least one missing close parentheses. You load the sheet's name, but then you try to read the *workbook*'s name. If wb was null, the 3rd line would throw an error, so your if condition would never be reached. – Rick Kirkham Aug 21 '18 at 17:01

1 Answers1

6

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.

shahzebasif
  • 140
  • 5