Microsoft 365 Community Community Demos, 25 Nov 2020

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: PnP: Custom function batching for Excel add-ins

Description

If your custom functions call a remote service you may want to use a batching pattern to reduce the number of network calls to the remote service. This is useful when a spreadsheet recalculates and it contains many of your custom functions. Recalculate will result in many calls to your custom functions, but you can batch them into one or a few calls to the remote service.

Sample code: https://github.com/OfficeDev/PnP-OfficeAddins/tree/master/Excel-custom-functions/Batching (github.com)

Additional Office Add-ins PnP samples: https://aka.ms/pnpofficeaddins (github.com)

A

Hello, everyone- this is david chestnut with the office platform, extensibility team and in this video I'm going to show a demo of our patterns and practices. Batching sample for excel custom functions. So let's take a look at the scenario. Let's say you have an excel add-in with an excel custom function and in that custom function. In order to calculate the result, you need to call a network resource, so you can imagine that a user might create a table.

A

Let's say if they do, and it has a thousand rows in it, then, when excel calculates a spreadsheet, that's actually going to result in a thousand network calls to the network resource, that's a lot of network traffic, and we would prefer to avoid that by just putting everything into a batch and then sending over that batch all at once to the network resource.

A

So let's take a look at how that works over here I have the add-in running and you can see the task. Pane is just the default task. Pane you get from yo office. uh The interesting part is to take a look at the custom functions. So if you look at the contoso namespace, there's an add no batch, which takes two values and adds them together.

A

Now, that's just a reference function to show not doing batching, not calling a network resource. It's just returning the result right away, and we also have div two which takes two numbers and divides them, and this one is doing a batching call to a network service, and then we also have contoso.mul2, which takes two numbers and multiplies them and that one as well is calling a network resource.

A

Basically, you think it's calling a network service to calculate the result and return it, and then you can see like if we copy this down into a bunch of rows, like the user might do. If they're creating a table, you can see the add no batch returns right away, but we see pound busy for a split second on the div 2 and the mul2 before we see the answer and that's because, as we'll see in a second, the code is emulating network latency and calling that network resource.

A

So let's go ahead and take a look at the code to see how these functions work. I have a visual studio code open and the sample project code is here. If you go down into the src folder, which is where the source is and expand, the functions folder there's a functions.ts file which has the typescript for the functions.

A

The first function in here is add no batch, and you can see that's just returning the first and second parameters by adding them together. Then, in the div two function, you'll notice, it's calling a push operation, so in this case we're going to push this operation into a batch to eventually be passed over to the network service. So we're going to give it a name. The operations name is div two in a string and then we pass the parameters as an array mul2 to multiply also does the same thing: it's going to call push operation.

A

Only this time the name of the operation is mul2 and again we pass the two parameters to be multiplied now in push operation.

A

Its job is to push each operation into the batch. So you can see the way it does, that is, it creates this invocation entry variable which has four items in it. It has the name of the operation that needs to be run the arguments for that operation and then it has a resolve and reject for the promise. That's eventually going to return the result back to excel.

A

So next we'll go ahead and create that promise. We create a new promise and then for each. You know for the resolve and the reject we wire those up to the result and reject functions that are in the invocation entry. Then. Finally, we push that invocation entry onto the batch array, so we have this variable called batch, and so what will happen is these will keep getting pushed until it at some point, we need to pass into the server.

A

So we're going to schedule that and that's what this next slide of code does we have a variable called, is batched request scheduled if, uh if it's not, then we'll go ahead and set that variable to true and call set timeout to call our make remote request function in this case after 100 milliseconds.

A

um So basically, every 100 milliseconds we're going to send whatever batch we have over to the service unless it's empty, in which case we won't make the call.

A

So you can change this value and play with it like if you make this say a second you're effectively going to make the batches a little bit larger when they're passed to the service, which will be fewer network calls, but that could be higher latency for users uh watching the spreadsheet calculate you can make this value smaller, in which case you'll, send smaller batches and perhaps more network calls to the service, but you also have a faster response time for the most part, so you need to play with this.

A

It's a balancing act between latency and batch size for how you want this to operate, and this is where you would configure that now, let's take a look at make remote request.

A

First thing: we're going to do is run a splice on that batch array and move all of those operations into this batch copy. So that way, the original batch variable is freed up for the next batch to come in then we'll take this batch copy and map it, and what we're effectively doing is using the map to strip out that promise. So all we have is the operation and the arguments.

A

That's because the promises aren't going to work on the service. So there's no need to be passing those so we'll create this new request batch, which just has operation arguments then we're going to call fetch from remote service, which is a mock function. That's basically pretending to be our service.

A

We didn't want to put a full server service sample because it would make it overly complicated, but I'll show you in a second how you can take the pattern from this function to implement your own service, but basically the idea is we want to pass that batch over the network and then we're going to get a response with all the answers to all the results for each operation. That was run so for each operation.

A

In that response, we want to pull out the response, as well as its index location in the array, and then we check if we got an error for some reason, then we want to reject that promise. With the error information, if it succeeded, then we want to call resolve and pass response.result, and this is the point where you would. This is where we're passing the actual value back to excel, and it's going to display for the user.

A

Now, let's take a look at fetch. Let's take a look at fetch, remote service and in this function. This is just a mock uh pretending to be the server, but this is the same pattern. You would want to implement in your own service. So first, it's going to have a little pause for a second.

A

This is just for emulating network latency, obviously for your own code. You want to take that out. Next, we're going to map that request batch and we're going to pull out each operation and its arguments and then we're going to look at the operation. We're going to say: is it a div two?

A

If it is we'll, go ahead and divide those arguments and return, the result, uh if not, if it's an mul2 which is to multiply then we'll, multiply the arguments and return that result, and then we also check to see if we may have been asked for an operation we don't have, in which case we'll return, an error. We also check in case an operation fails. We have an error condition for that as well.

A

So those are our two error handlers for everything coming to the server, so this is essentially how you can implement a batching setup for your own network resource.

A

If you want to take a look at the code, just go to our office.org on github and in the pmp-office add-ins repo. If you go to the excel dash custom functions, folder under the batching folder you'll find all the code that I just showed you there's a readme here. That explains how to get it set up and running. It's actually pretty simple. There's also some more explanation about the code and uh definitely go take a look if you're interested give us feedback.

A

If you have any, you can submit an issue on this repo or let us comments below on the video. I hope you find this useful and I will see you on the next video thank.

A