Throughout the preceding topics, you will have seen the term – and seen example usage of –
context.sync(). The purpose of
context.sync() is for the developer to declare that he/she is done with either the entire batch or a portion of it. An invocation of
context.sync() returns a Promise object, which can then be
.then-ed with follow-up operations (which in turn might also call to
context.sync() to signal the completion of their portion of the batch). Importantly, chaining Promises together returns a meta-Promise, which resolves only when all of the child Promises have finished chaining. This Promise is what must be returned out of the batch function, so that
.run-method knows that the OM operations have completed.
As an add-in author, your job is to minimize the number of
context.sync() calls. Each
sync is an extra round-trip to the host application; and when that application is Office Online, the cost of each of those round-trip adds up quickly.
If you set out to write your add-in with this in principle in mind, you will find that you need a surprisingly small number of
sync calls. In fact, when writing this chapter, I found that I really needed to rack my brain to come up with a scenario that did need more than two
sync calls. The trick for minimizing
sync calls is to arrange the application logic in such a way that you’re initially scraping the document for whatever information you need (and queuing it all up for loading), and then following up with a bunch of operations that modify the document (based on the previously-loaded data).
Still, there are some scenarios where multiple loads may be required. And in fact, there may be legitimate scenarios where even doing an extra
sync is the right thing to do – if it saves on loading a bunch of unneeded data. You will see an example of this very soon.
Before we dive into an actual example, it’s worth pointing out a common pitfall and a way to get around it. Because a batch will typically contain two or more
sync calls – each of which returns a Promise – it is pivotal NOT to break the Promise chain, or else you may encounter some unexpected and often hard-to-diagnose behavior. This means that:
- You must remember to return
context.sync()out of the batch function.
- You must also remember that the body of each
.thenmust either be fully synchronous, or return a Promise.
#1 is fairly straightforward. In fact, if you’re using TypeScript, you will get a compilation error if you forget the
return (the batch function must return a Promise, whereas by forgetting the
return you’re making the function
.catch statement, you will see an error during runtime.
RunMustReturnPromise: The batch function passed to the “.run” method didn’t return a promise. The function must return a promise, so that any automatically-tracked objects can be released at the completion of the batch operation. Typically, you return a promise by returning the response from “context.sync()”.
Remembering #2, on the other hand, takes more effort. There is unfortunately no error-checking that can catch the issue for you, as it’s perfectly reasonable (and very common) to have a
.then-function that doesn’t return any values (i.e., is
void). And moreover, when you do forget the
return statement, you end up in non-deterministic territory, where the code might or might not work based on timing; and where diagnosing the issue is notoriously difficult (with a broken Promise chain, you also lose error-handling; yay!).
Having unintentionally broken the Promise chain myself, on a number of occasions – and having seen beginner Office.js developers break the Promise chain time and time again – I personally prefer to sidestep the issue altogether. To do this, I make all functions that make OM calls be synchronous (i.e., only queue up the operations, but not dispatch the
context.sync() yet), and then I follow up these synchronous “
.then”s with corresponding
.then(context.sync)-s. Notice how the latter does not invoke
context.sync (there are no
sync, as you’re passing in a function reference, not a return value). And you can’t be guilty of forgetting a
return statement, if there is physically no
return statement to begin with!
The resulting code looks as follows:
If you follow this pattern, you’ll find that typically, the only
return statement you’ll need is the first
return context.sync() invocation – which, between the TypeScript compiler and the runtime error, should be relatively straightforward. For the rest, the problem is avoided altogether via the “reference-style” approach.
Real-world example of multiple
Enough with theory! Let’s try out
context.sync() using a simple but reasonably-realistic example.
To give a concrete example: for the three assignments in the image above (and let’s assume there’s a whole bunch more, omitted for brevity), when talking with Matthias D’Armon’s parents, the teacher would only want to show columns A (to see the student’s name) and columns B & D from the assignments category. Column C, meanwhile, does not need to be shown, as it’s the one assignment were Matthias got an over-80% grade (might I say he had a “Eureka!” moment?) – and so this isn’t a problem area that the teacher needs to discuss with Matthias’ parents.
This scenario is reasonably similar to the other two scenarios mentioned earlier – but with the notable exception that here you only want to look at one student’s data, which represents a small fraction of the data on the sheet. I would argue that transferring a whole bunch of unneeded data is even worse than doing an extra sync, so let’s see how we can do this task most efficiently, even if it means fudging a bit on the minimal-syncs principle for the sake of the avoiding-reading-copious-amounts-of-unneeded-data principle.
I think the most efficient breakdown of tasks is as follows:
STEP 1: Use the left column to find the student name.
First off, we need to find the student, so we can load just his/her data. This could be done by creating a Range object corresponding to
A:A, trimming it down to just its used range (to reduce the one-million-plus cells into something that makes sense), and loading the cell values. However, in this particular case, we have even more efficient means: we can use an Excel function – invoked from JS – to do the lookup for us. The Excel function that fits this particular scenario is =
MATCH(...) 1MATCH(lookup_value, lookup_array, match_type): Returns the relative position of an item in an array that matches a specified value in a specified order. For more information, see https://exceljet.net/excel-functions/excel-match-function, or one of mnay other online resources for Excel formulas. which which looks up a particular value and returns its row number[^row-number-1-indexed]. We’ll assign the function result to a variable and load its “value” property.
Since we can’t proceed with any further operations without knowing the row number (and it, in turn, can’t be read without first doing a
sync), perform a sync.
STEP 2: Retrieve the appropriate row, and request to load values
From the row number, retrieve the appropriate row, trim it down to just the used range (there is no sense in loading values for all 16,384 columns, when likely it’s only a few dozen that are used), and load the cell values.
Again, without reading back the values, there is nothing further we can do. So, sync.
STEP 3: Do the processing & document-manipulation
Having retrieved the values, hide any column where the score is equal or greater to 80%, since the teacher’s goal is only to discuss problem areas in this meeting (we’ll assume that complementing good grades will have been done separately). This is where we finally issue a bunch of write calls to the object model, where we’re manipulating the document, instead of just reading from it. So, to commit the pending queue of changes, do a final sync.
This seems like a reasonable plan, so let’s code it up. I encourage you to try this out yourself as an exercise, before flipping to the following page and seeing the finished code.
return context.sync() call on line #14 (where the
return statement ensures that the value returned out of the batch function is a Promise), and then the two
.then(context.sync)-s on lines #23 and #40.
When to sync
There are only a few valid reasons for doing a
- You are done with the batch function, having read and/or manipulated the document in whichever way the scenario saw fit. And so, this final
context.syncis your declaration of “Dear Excel / Word /etc: I’m done working with you for the time being. Feel free to resolve the
.runPromise, and to clean up any resources that you so kindly provided to me. I’ll get back to you as soon as I need you again.”
- You are in the middle of a batch function, and you can’t proceed without reading back some data from the document, which you’ve previously requested to be loaded. Because you’re dependant on the loaded data, and the load can’t be fulfilled without a
sync, you need to sync.
- You were holding on to some object, but then needed to go off and do some [possibly-length] web calls, or have been waiting on some user input. Depending on the scenario, and on how paranoid you are, it may be worth to re-load and re-sync the data on the object in case it’s changed in the meantime.
- [A corner-case]: You are in the middle of a batch function, and you’ve queued up some “write” operations… but then you need to go and fetch some information from the internet. You’re not awaiting on anything from the document, but you feel that it would be kinder to your users if you show them what work you have done, rather than having it all queued up but un-dispatched.
- [A really really corner-case]: You are in the middle of a batch function, where you had requested some really-temporal object (something like selection or active worksheet). You want to get a reference to this fleeting object as soon as possible (before the user changes his selection), so even though you don’t need any data from the document, you do a
syncto ensure that the fleeting object is correctly captured.
References [ + ]
|1.||↑||MATCH(lookup_value, lookup_array, match_type): Returns the relative position of an item in an array that matches a specified value in a specified order. For more information, see https://exceljet.net/excel-functions/excel-match-function, or one of mnay other online resources for Excel formulas.|