Best way to batch calls and send data to and from a sheet?

[Kris]

Hi all,

New to excel development, so I have an elementary question. I'm looking to create a UDF that will allow a user to input a string that gets sent to a Web Api, and then returns data.

I was able to use the AsyncBatch example to set up a version of my idea that queues up multiple 1 cell instances of the call to this api, but performance is pretty bad, since it's making a new request for each lookup. 

Then I changed my batch so instead of creating a new request, it just populated a concurrentQueue of values to look up using the API (the handler would send  fewer requests with multiple lookup values in the post body),

 however it seems (https://groups.google.com/forum/#!topic/exceldna/_HV8rtrMuwc) that there's not a great way for me to take this array of asynchronously returned values and update the original cells each value was associated with.

Am I going down the right path here, or is my architecture of how one would achieve something like this in Excel misguided? Should I be looking into creating an RTD or something instead?

Thanks,

Kris

[Govert van Drimmelen]

Hi Kris,

I presume you are using the AsyncBatch example from here: https://github.com/Excel-DNA/Samples/blob/master/Misc/AsyncBatchSample.cs

This sample should allow you to aggregate calls from single-cell functions, then make a batched back-end call, then process and return the values to the individual cells.

The signature of your batch handler is:

static async Task<List<object>> RunBatch(List<AsyncBatchUtil.AsyncCall> calls)

The intention is that you aggregate the information from the AsyncCall objects, make the back-end call and split the result into the list of objects as the result.

If you do it this way, you should get good performance through effective batching of calls to the back-end service.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Kris [kpurush...@solveadvisors.com]
Sent: 27 July 2018 11:02 PM
To: Excel-DNA
Subject: [ExcelDna] Best way to batch calls and send data to and from a sheet?

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Kris]

The problem was that although I was basing my code on the batch example, I wasn't actually batching my requests. Now that I'm "intercepting" the function calls in my RunBatch and building larger envelopes, the performance has increased. Thanks for the pointers, Govert!

To post to this group, send email to exc...@googlegroups.com.