Call external service once for all the rows in Custom Functions Excel Add-in

Question

Consider this example from this link: https://learn.microsoft.com/en-us/office/dev/add-ins/excel/excel-tutorial-custom-functions#create-a-custom-function-that-requests-data-from-the-web

function stockPrice(ticker) {
var url = "https://api.iextrading.com/1.0/stock/" + ticker + "/price";
return fetch(url)
    .then(function(response) {
        return response.text();
    })
    .then(function(text) {
        return parseFloat(text);
    });

// Note: in case of an error, the returned rejected Promise
//    will be bubbled up to Excel to indicate an error.
}

CustomFunctionMappings.STOCKPRICE = stockPrice;

Is there a possibility to batch the web service request and calling it just once by giving it all the parameters of each row in one request, and hence updating all the rows with the respective responses returned from the service at once, rather than calling web service for each row? This will help in case of large number of rows.

score 0 · Accepted Answer · answered Oct 24 '18 at 22:28

Yes, it is possible to batch up requests in this way. One of our engineers on custom functions, Michael Zlatkovsky, prepped a sample in a GitHub gist about this that shows how to do this. Additionally, in the coming month the Custom Functions docs team is working on a document specifically to address batching as we know there is a lot of interest in this topic. Let me know if you need additional help - thanks!

Call external service once for all the rows in Custom Functions Excel Add-in

1 Answers1