azure

I have been working on building out services for the back-end which can be consumed by both my OfficeJS Add-ins and any other host. In researching different options to deploy those services I started to delve into using Docker. I have Visual Studio 2019 installed and I have “Container development tools” added.

Next I followed the steps here to this Quick Start. Except when I was trying to publish or even run in Docker for Windows I kept getting errors. When I tried to run Docker locally using Docker for Windows, I got an error that said “Docker for Windows is required” and the details said it could not start/find/access the “MobyLinuxVM.” It turns out, after a LOT of research, this was because it did not exist. It did not exist because I have Hyper-V default folder pointed to a removable drive that was not installed. The fix there was one of two things:

Change the Hyper-V default folder to something on the C:\ drive.
Install the removable drive.

So I chose #2, and then I had to uninstall and then reinstall Docker for Windows. Once I did this the “MobyLinuxVM” was there and once I launched Docker for Windows, it also started the VM.

Next, I was getting a very odd error CT1002 about an error with the Docker container service or something. I cannot remember the exact error now but in the details I kept seeing an error where it was attempting to mount folders in my user folder “c:\users\david.” I found this article on MSDN after a lot of digging:

https://blogs.msdn.microsoft.com/stevelasker/2016/06/14/configuring-docker-for-windows-volumes/

This essentially tells you to create a new user called DockerHost, set the password to never change/expire, and add the administrators group. Next you sign in with that account and try to access your personal users folder. For me that was “c:\users\david.” You get the permissions prompt and click “Continue.” Mine took over 5 minutes, but eventually I was able to open the folder and see everything. Then I logged out and this is the step that article forgets, you go into Docker for Windows, Settings, Shared Drives, and Reset Credentials. Then click the C:\ drive again and enter in the DockerHost username and password.

This resolved running in Docker for Windows, locally. Shortly after all this work, I found this page which might also work (adding your account to the docker-users group). I did not try this, but here is the article on that:

https://docs.microsoft.com/en-us/visualstudio/containers/troubleshooting-docker-errors?view=vs-2019

Finally, I am at the point where I want to publish to my Azure Container Registry in the cloud. This I still have not resolved. It gets most of the way done and then I get this error:

I am working on trying to get this sorted but have run out of time for the day and wanted to add what I did to solve everything else. I am exhausted and a tad frustrated.

What I have learned from this experience is that “out of the box” most of this stuff does not work. Looking for help online I see lots of command line junkies throwing out complex scripts and esoteric answers. It is a really interesting community but it seems to come with a high barrier to entry. With Visual Studio embracing it and trying to get you started “easy” there are still too many speed bumps for most folks trying to delve in.

If anyone knows a better answer on this stuff, please let me know. And if you have any idea what the publish error is I am getting and can give me some pointers, I would GREATLY appreciate it.

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.

Category: azure

Docker Hell on Window 10

Using Azure Functions in Excel