Using Azure Functions in Excel

Posted on by David E. Craig

I have been delving more and more into Azure recently. In looking for a way to build an Excel User Defined Function (UDF) that did not require Visual Basic for Applications (VBA) or similar, I looked at what I could do with:

And to my surprise it was actually very easy to set this up. Here are the steps:

  1. Log into https://portal.azure.com
  2. Click “Create a Resource” in the upper left
  3. Type “Functions App” then “Create”
  4. Then follow the directions to create your own Functions App called “ExcelFunctions,” for example.
  5. You will want to create an HTTP Trigger function and I called mine “AddNumbers”
  6. You will be asked where you want to edit the function and I chose online in the browser, which is what makes this the coolest thing ever.
  7. Here is what my function looks like:


#r "Newtonsoft.Json"
using System.Net;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Primitives;
using Newtonsoft.Json;
public static async Task<IActionResult> Run(HttpRequest req, ILogger log)
{
log.LogInformation("C# HTTP trigger function processed a request.");
string v1 = req.Query["value1"];
string v2 = req.Query["value2"];
if(v1!=null && v2!=null)
{
int result = int.Parse(v1) + int.Parse(v2);
return (ActionResult)new OkObjectResult($"{result}");
}
else
{
return new BadRequestObjectResult("Invalid input");
}
}

view raw

AddNumbersFunction.cs

hosted with ❤ by GitHub

Once you have created your function, you can also test it right there in Azure. On the Right is a tab called “Test.” You can select it, and then add parameters like this:

azurefunctiontest

You can click “Save and Run” at the top of the page to run the function and verify the result: 42. Now, to get this to work in Excel, you need the URL. To do this click the “</> Get Function URL” button:

geturl

This will give you value like this:

https://xxx.azurewebsites.net/api/AddNumbers?code=123456…==

Now the fun part, getting this to work in Excel.

  1. Open Excel and create a new Workbook
  2. In  A1 type 100, in B1 type -58
  3. Select C1 and add the following function:

=WEBSERVICE("https://xxx.azurewebsites.net/api/AddNumbers?code=123456...==&value1=" & A1 & "&value2=" & B1)

Once you press enter and Excel updates you should see the value of 42 appear.

This is a very simple example of how you can implement an Azure Function in Excel as a User Defined Function. Imagine all the possibilities where you can collect data, analyze results, use Cognitive Services and vast stores of data from a Data Lake and much, much more. The possibilities are endless.

3 thoughts on “Using Azure Functions in Excel”

  1. Hello,

    Thanks for this information.
    I want to call Azure Function from excel but Azure function is protected by Azure Active Directory authentication. How can I get data from Azure function in this scenario?

    Thanks in advance.

    1. Hi Rahul!
      There is not a mechanism via the WebService formula if the Azure Function is AD protected. What you would need to do is create your own UDF. One way is to write an OfficeJS Custom Function (https://docs.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-overview). I plan to blog about this soon as it looks like it just came out of preview. You would need to handle the Oauth like this (https://docs.microsoft.com/en-us/office/dev/add-ins/develop/sso-in-office-add-ins). You call getAccessTokenAsync(), grab the token for the logged in user. If it is the same credential needed for the function you should not need to prompt. But if it is a different credential that is needed then you can prompt, store the token and then use it for each subsequent call to your function.

Leave a Reply