udf

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:

Azure Functions
Excel WEBSERVICE function

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

Log into https://portal.azure.com
Click “Create a Resource” in the upper left
Type “Functions App” then “Create”
Then follow the directions to create your own Functions App called “ExcelFunctions,” for example.
You will want to create an HTTP Trigger function and I called mine “AddNumbers”
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.
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:

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:

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.

Open Excel and create a new Workbook
In A1 type 100, in B1 type -58
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.

Tag: udf

Using Azure Functions in Excel