Category: Office Development

Using Azure Functions in Excel

davecraLeave a comment

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:

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.

Visual Studio 2017: This is an invalid xsi:type… mailappversionoverrides/1.1:Event

davecraLeave a comment

If you have been using Visual Studio 2017 to create an Outlook Web Add-in and are trying to use the new ItemSend event, you will note per the documentation the you need to add the MailAppVersionOverrides 1.1 to your manifest:

<VersionOverrides xmlns="http://schemas.microsoft.com/office/mailappversionoverrides/1.1" xsi:type="VersionOverridesV1_1">

The problem is that when you try to build and run the project, you get this exception:

Severity Code Description
Warning This is an invalid xsi:type ‘http://schemas.microsoft.com/office/mailappversionoverrides/1.1:Events&#8217;.

a

Specifically, Visual Studio 2017 does not like this line:

<ExtensionPoint xsi:type="Events">

If you are like me you have been hoping for an update to Visual Studio 2017 to fix this, but I just got word today that it did not get into the last update to Visual Studio 2017, but will make it into Visual Studio 2019. However, you do not need to run off and install Visual Studio 2019 once it is available. There is a workaround, and here are the steps to follow:

  • If you have an updated Exchange 2016 Server CU9 or later, you can browse to this folder: 
    C:\Program Files\Microsoft\Exchange Server\V15\Bin
  • Locate and copy the file MailAppVersionOverridesV1_1.xsd and copy it to a removable drive or a network share.
  • On your development machine, locate the existing MailAppVersionOverridesV1_1.xsd. It should be located here: 
    C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Xml\Schemas\1033
  • Rename the existing file to MailAppVersionOverridesV1_1.old.
  • Copy the version of the MailAppVersionOverridesV1_1.xsd you got from your Exchange Server here.

At this point you should be good to go.

But wait!?!?! You say you don’t have an Exchange Server? You use Office 365 and your instance of Exchange is up in the cloud? OK… Now this is not the “preferred” method, but it will get the job done. I created a GIST, and you can download it from here. wlEmoticon-hotsmile.png Simply view RAW, copy, open Notepad, paste, Save As: MailAppVersionOverridesV1_1.xsd, and then you have your file.

makeEwsRequestAsync Error: “The requested web method is unavailable to this caller or application”

davecraLeave a comment

If you get the following error when using the Office.context.mailbox.makeEwsRequestAsync() method, it is probably because the supported method is not available to this function:

The requested web method is unavailable to this caller or application

The details of the supported function are outlined in this article on MSDN. If you do not see the method you are trying to call in that table, it is not supported.

However, if you are like me and you do see your function in there, like “ExpandDL” it might be because when you recently updated to CU9 or CU10 (this method was added in CU9), you did not run setup with the “PrepareAD” switch. This last step is required in order to update the policies that allow this function to work. You can also run these two commands from the Exchange PowerShell to update your server:

  • Install-CannedRbacRoles
  • Install-CannedRbacRoleAssignments

NOTE: If these commands are not found, you need to first run this command:
Add-PsSnapin Microsoft*

 

EWS: ExpandDL documentation is wrong

davecraLeave a comment

I am not certain how prevalent this issue is in the wild and/or if anyone else has encountered it or had to workaround it, but there is a documentation bug that had me pulling my hair our for nearly a week.

If you have a need to get the members of an Exchange public distribution group using Exchange Web Services (EWS) ExpandDL operation via SOAP, and you followed this documentation, you will have encountered this super unhelpful error:

The problem is that the documentation is wrong. The namespaces used and the namespace prefixes are wrong. Here is what is on the documentation site:

What I ended up doing was to use the EwsEditor tool and Fiddler (per the guidance of an awesome Exchange guru I work with – thanks Dan!), and I captured the proper soap coming from the Managed API. To get ExpandDL to work properly, you need this SOAP:

Anyway, I will be issuing an update to easyEws very soon to include the correct SOAP.

How to Fix IntelliSense in VSCode for OfficeJS

davecraLeave a comment

I spent the better part of this morning trying to figure out why a new Yeoman generated Outlook add-in was not working with IntelliSense in VSCode. I would type “Office.” and nothing would appear. I would press CTRL+{SPACE} and still nothing.

After a lot of digging and comparing projects that did work to those that did not, I found that the @types were missing. So I then installed @types/office-js:

npm install @types/office-js

Once I did this, viola! IntelliSense started working like a charm. From my experience this has happened to me a few different times, in these given scenarios:

  • Moving an OfficeJS project from VS2017 to VSCode
  • Moving a Script Lab project to VSCode
  • Creating a blank OfficeJS project from scratch with just two files (manifest and webpage).

In my continued research I found that the way VSCode handles types is through NPM and you need to have data type definition files to get it all to work, that is why you need to install both NPM and the OfficeJS @types to get this to work. Per this page:  https://code.visualstudio.com/Docs/languages/javascript

Automatic Type Acquisition (ATA) uses npm, the Node.js package manager, to install and manage Type Declaration (typings) files. To ensure that Automatic Type Acquisition works properly, first ensure that you have npm installed on your machine.

Secondly to that, for OfficeJS development, make sure you have @types/office-js.

easyEws v1.0.10 Released

davecraLeave a comment

In this release I have incorporated two new functions to help with recipients (To/CC/BCC) and groups/distribution lists:

  • getAllRecipientsAsync() – helps you by getting all the unique recipients on the To/CC/BCC lines. It returns two lists in the callback:
    • A list of standard users
    • A list of groups
  • splitGroupsAsync() – accepts an array of groups (or Distribution Groups) and expands them to a unique set of users.

I have also corrected a few small bugs, like one with MailBoxUser (where Address was incorrectly JSDoc’d as Email).

I have also included a minified version of the library. So you can access easyEws.min.js in your code to reduce the download.

You can access it from:

 

Getting Started as an OfficeJS Developer

davecraLeave a comment

At the MVP Summit 2018, I have had a number of conversations with various MVP’s that there really should be more documentation on how to get started with OfficeJS from scratch. Like web developer – from scratch.

So, this blog post is my attempt to get you started as if you have little to no web developer experience. The hope is that once you have read this and studied the linked content you will be able to:

  • Write HTML and JavaScript
  • Understand what JQuery is and how to use it
  • Setup your VSCode environment
  • Identify the key parts of an add-in (source code files)
  • Begin writing, and debugging your own code.

To get started, if you are completely new to web development (or even a little rusty and need a refresher), you might want to read the following articles/tutorials. If you already know this stuff, then you can move on:

 

NOTE: From these links you need to come away with an understanding of these basic fundamental concepts that are used heavily in OfficeJS:

  1. How JavaScript and HTML work together
  2. The HTML DOM model
  3. How to write HTML and define controls (buttons, text boxes, etc.) with an id
  4. How to use JQuery(*) to get a reference to a control on the HTML page and then manipulate it with JavaScript.

(*) JQuery is simply a library that, for simplicity sake, makes it easy for you to access and manipulate HTML controls via the DOM. You access it with a “$” character. This is why you might see so many “$”‘s in the sample/example code out there.

Once you know the basics of creating a web page, and manipulating it, you can then begin by setting up VSCode and learning how to code OfficeJS solutions. As for development environments, there are many out there, and many are nearly the same, but I suggest VSCode because it is simply a better environment to learn “webby” development. Especially if you are on a Mac. With that said, you will want to:

  1. Setup and configure VSCode
  2. Start learning Office from the new quick start and tutorials on docs.office.com.
  3. You can also stick to the basics (no development environment needed) if you install and use Script Lab.

NOTE: A fundamental concept you need to come away with after working through the tutorials is to understand the “parts” of an add-in. I think the best way to understand the PARTS of an add-in:

  • All you really need for an OfficeJS solution are two files. A manifest and an HTML file.

To understand the anatomy of an add-in, you can read this post where I describe the most basic add-in you can create. Here is a simplified explanation to get your started:

  • The Manifest – an XML file that defines to Office your add-in. It defines things like the name, a unique id (GUID), the platform and app it supports, and most importantly your Ribbon (text, icons, etc), and what happens when you click those buttons. Specifically, it tell Office where to navigate when that button is clicked. Then this XML file is what gets “installed” in your Office app. Office reads it, places the button(s) on the Ribbon which loads the defined web page when clicked.
  • The HTML file – this contains “script” tags which point to all your code, supporting libraries (like JQuery) which similar to VBA/VSTO references. The HTML page also contains your Task Pane user interface (buttons, etc.).
  • The JavaScript file – This is your code, which will contain an Office.initialize() function that Office locates and executes when the HTML page is loaded. This can be a separate file or like the most basic add-in an inline script tag in your HTML file.

The entire load and run process for an add-in works like this:

  • The Manifest is loaded by Office and adds a button to the ribbon.
  • When the button is clicked, the HTML file specified in the manifest is loaded in the task pane.
  • When the HTML file is loaded, all the script tags (references and your own JS file) are loaded.
  • Once all the scripts are loaded, Office locates the Office.initialize() function and executes it.
  • It is in this code block that you can initialize your controls, like buttons on the HTML page, and what happens when the user clicks them.

Once you have your VSCode development environment setup for Office, and you are ready to start your first project, follow these steps I outlined for using the Yeoman generator.

I hope this is enough to get even the most novice non-web developer going. If you have any questions, please post them below. If there is any additional content you would like to see, please let me know.

The last thing I will leave you with is to answer an interesting question that arose this week:

Why would I learn to develop in OfficeJS? I am not interested in just making a web widget for Excel.

I think this is a common misconception. Office 365 Web Add-ins (OfficeJS) are at all not like Windows Vista Sidebar Gadgets for Office. They are actually quite powerful and there are many useful scenarios. Here are a few:

  • Integration with internal applications like CRM
  • Document tagging, or placing metadata on parts of the document, possibly linked with internal systems, controls or processes
  • Document building, either interactively through a task pane or dialog or from predefined rules
  • Advanced content editing, identifying and replacing specific content based on more complex rules than simple search/replace
  • Sending data from the document for more complex analysis and/or manipulation via back-end services and then updating the document with the results.

Once you delve a bit more into the object model and the capabilities, you will learn that you do not always have to open a task pane. You can open a dialog instead. Or, you can have no user interface at all, just have a button click on the ribbon perform work against the document like a macro used to do.

And here is another thought: Office is used by one-billion people world-wide across each of the supported platforms of Windows, Mac, web, iOS, and Android. The goal of these add-ins is to support the same code base (mostly) across all of these different platforms. You really can write-once/run-anywhere. You can build an add-in and then place it in the Microsoft Store and have it reach a huge audience. Do you need more of a reason to get started?

Happy coding.