azure

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.

Recently, I was asked what is needed in order to create the most basic add-in you can. The least amount of files, work and effort. Technically, you only need two files:

An HTML file with all your JavaScript inline and all the Office and JQuery libraries being pulled from a CDN.
An XML Manifest file that defines the add-in.

You publish the HTML file to a web server, get the address to where it is published, update the manifest with that information and then install the XML file as an add-in. The XML file will define the add-in name/description and points to the HTML file for the code. When you launch the add-in, the task pane will open and the HTML page will be loaded in it. From there your JavaScript will execute and your add-in will come alive. It is THAT easy. 2 files.

To demonstrate this, I created a simple Excel add-in. Here is the code for the bare minimum manifest XML:

[code lang=”xml” collapse=”true” title=”click to expand if the github.com embedding below is not visible.”]
<?xml version="1.0" encoding="UTF-8"?>
<OfficeApp
xmlns="http://schemas.microsoft.com/office/appforoffice/1.1"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:bt="http://schemas.microsoft.com/office/officeappbasictypes/1.0"
xmlns:ov="http://schemas.microsoft.com/office/taskpaneappversionoverrides"
xsi:type="TaskPaneApp">
<Id>5226365f-62d0-435f-a4af-cc6a7bd753b2</Id>
<Version>1.0.0.1</Version>
<ProviderName>TheOfficeContext.com</ProviderName>
<DefaultLocale>en-US</DefaultLocale>
<DisplayName DefaultValue="BasicAddin" />
<Description DefaultValue="This is a basic addin."/>
<IconUrl DefaultValue="" />
<Hosts>
<Host Name="Workbook" />
</Hosts>
<DefaultSettings>
<SourceLocation DefaultValue="https://basicaddin.azurewebsites.net/home.html" />
</DefaultSettings>
<Permissions>ReadWriteDocument</Permissions>
</OfficeApp>
[/code]

	<?xml version="1.0" encoding="UTF-8"?>
	<OfficeApp
	xmlns="http://schemas.microsoft.com/office/appforoffice/1.1"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:bt="http://schemas.microsoft.com/office/officeappbasictypes/1.0"
	xmlns:ov="http://schemas.microsoft.com/office/taskpaneappversionoverrides"
	xsi:type="TaskPaneApp">
	<Id>5226365f-62d0-435f-a4af-cc6a7bd753b2</Id>
	<Version>1.0.0.1</Version>
	<ProviderName>TheOfficeContext.com</ProviderName>
	<DefaultLocale>en-US</DefaultLocale>
	<DisplayName DefaultValue="BasicAddin" />
	<Description DefaultValue="This is a basic addin."/>
	<IconUrl DefaultValue="" />
	<Hosts>
	<Host Name="Workbook" />
	</Hosts>
	<DefaultSettings>
	<SourceLocation DefaultValue="https://basicaddin.azurewebsites.net/home.html" />
	</DefaultSettings>
	<Permissions>ReadWriteDocument</Permissions>
	</OfficeApp>

view raw

BasicAddin.xml

hosted with ❤ by GitHub

Here is the HTML file that is quite basic, I am not even using the Microsoft Fabric for the controls. This is a simple HTML page, period:

[code lang=”xml” collapse=”true” title=”click to expand if the github.com embedding below is not visible.”]
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=Edge" />
<title>Excel Basic Add-In</title>
<!–using JQuery CDN so we do not need to include–>
<script src="https://code.jquery.com/jquery-1.12.4.min.js" type="text/javascript"></script>
    <script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js" type="text/javascript"></script>
<script type="text/javascript">
        (function () {
            "use strict";
            // The initialize function must be run each time a new page is loaded.
            Office.initialize = function (reason) {
                $(document).ready(function () {
                    // Add a click event handler for the button.
                    $(‘#simple-button’).click(function () {
                        Office.context.document.getSelectedDataAsync(Office.CoercionType.Text,
                        function (result) {
                            if (result.status === Office.AsyncResultStatus.Succeeded) {
                                $("#banner-text").text(‘The selected text is: "’ + result.value + ‘"’);
                                $("#banner").show();
                            } else {
                                $("#banner-text").text(‘Error: ‘ + result.error.message);
                                $("#banner").show();
                            }
                        });
                    });
                    $("#banner-close").click(function () { $("#banner").hide(); });
                    $("#banner").hide();
                });
            }
        })();
    </script>
</head>
<body>
<div id="content-main">
<div class="padding">
<h1>Basic Addin</h1>
<div>Select a cell with text and then…</div>
<button id="simple-button">Click Here!</button></div>
</div>
<div id="banner" style="position:absolute;bottom: 0;">
<div id="banner-text"></div>
<button id="banner-close"> <i>X</i> </button></div>
</body>
</html>
[/code]

	<!DOCTYPE html>
	<html>
	<head>
	<meta charset="UTF-8" />
	<meta http-equiv="X-UA-Compatible" content="IE=Edge" />
	<title>Excel Basic Add-In</title>
	<!–using JQuery CDN so we do not need to include–>
	<script src="https://code.jquery.com/jquery-1.12.4.min.js" type="text/javascript"></script>
	<script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js" type="text/javascript"></script>
	<script type="text/javascript">
	(function () {
	"use strict";

	// The initialize function must be run each time a new page is loaded.
	Office.initialize = function (reason) {
	$(document).ready(function () {
	// Add a click event handler for the button.
	$('#simple-button').click(function () {
	Office.context.document.getSelectedDataAsync(Office.CoercionType.Text,
	function (result) {
	if (result.status === Office.AsyncResultStatus.Succeeded) {
	$("#banner-text").text('The selected text is: "' + result.value + '"');
	$("#banner").show();
	} else {
	$("#banner-text").text('Error: ' + result.error.message);
	$("#banner").show();
	}
	});
	});
	$("#banner-close").click(function () { $("#banner").hide(); });
	$("#banner").hide();
	});
	}
	})();
	</script>
	</head>
	<body>
	<div id="content-main">
	<div class="padding">
	<h1>Basic Addin</h1>
	<div>Select a cell with text and then…</div>
	<button id="simple-button">Click Here!</button>
	</div>
	</div>
	<div id="banner" style="position:absolute;bottom: 0;">
	<div id="banner-text"></div>
	<button id="banner-close"> <i>X</i> </button>
	</div>
	</body>
	</html>

view raw

Home.html

hosted with ❤ by GitHub

What this add-in does is very simple. It opens the task pane with a single button. Enter some text in a cell in Excel, select that cell, and then click the button. It gets the text and displays it in a popup at the bottom of the pane. I have published this add-in manifest here:

https://basicaddin.azurewebsites.net/basicaddin.xml

You can run it by using these steps:

Open Excel Online. I first log into https://outlook.office365.com. Then I click on the menu button in the upper left and select Excel.
I select to create a “New blank workbook.”
On the Insert tab, I click Add-ins.
In the upper right, I select “Upload My Add-in”
I click Browse and in the Filename box, I put https://basicaddin.azurewebsites.net/basicaddin.xml and then click Open. This will download the add-in to the cache on your system and you will get a file name like “basicaddin[1].xml.”
Click Upload.
The add-in will load from my Azure site and you will see the button demonstrated above.

Now, to go ahead and answer a question I know are coming. How did I upload this Azure without Visual Studio publishing tools:

I went to my Azure Protal (portal.azure.com).
I clicked the +New button on the left side.
I selected Web + Mobile, then Web App
I filled in the required information: Name and resource group and clicked Create.
After it was done, I followed these steps to get to the site via FTP: https://blogs.msdn.microsoft.com/kaushal/2014/08/01/microsoft-azure-web-site-connect-to-your-site-via-ftp-and-uploaddownload-files/
I uploaded ONLY the HTML file.
I got the path to the location from the Azure portal:
Next, I updated the XML manifest to point to https://basicaddin.azurewebsites.net/home.html
Then I used the steps above to load it into Excel Online.

This is fairly simple from a web developer perspective. Now, if you are not a web developer (but a traditional Office developer), you are probably still on the fence on how/why you would want to go through all of this. Again, the primary thing I am demonstrating here is how you can build an add-in with as few files as possible. But, I want to reiterate how these new add-ins are truly cross-platform. Follow these steps on your iPad and you will know what I mean. It is incredible and this work really is not that difficult to perform. It might just be a tad time consuming in the beginning as you are getting used to the new interfaces.

If you have questions, please feel free to reach out to me.

Solutions for Microsoft Office, and more…

Tag: azure

Using Azure Functions in Excel

OfficeJS: Create a (VERY) Basic Add-in for Excel