Uncategorized

Run Your Own Custom Functions in Excel (JavaScript Add-in)

As you know, you can build your own custom functions (UDF) with VBA since long before. But if you’re ISV folks, you must deliver your functions for your all customers by easy installation. VBA doesn’t matter in such a case.
With new capability in Excel Web Add-in, you will be able to deliver your functions all over the world through marketplace. For instance, you can deliver your own advanced Excel functions, such as AI integrated functions, etc. You could expose your AI-driven web service with new Azure Machine Learning (v2) and python, and then the user can consume this service with your given Excel functions. (The user doesn’t need to know about the web services or complicated installation process.)

The following is the advantages of this new JavaScript custom functions.

  • It’s JavaScript. It can run on any platforms like Mac, Online, etc. (For currently available platforms, see “Custom functions requirements“.)
  • Are you a power user or a professional developer ? For the latter folks, once you’ve created your custom functions, you will be able to submit and deliver through marketplace (Office store).

Let’s dive into 5 minutes’ example !

Note : Currently (May 2019) you cannot run custom functions without early builds of Office (Excel). Especially, for commercial users, you should use Office Insider channel. (See “Custom functions requirements” for details.)

Simple Excel Function

First you must create the html page (.html) and script file (.js). Then put (expose) in the public site.
The following is our example.

Following getrandom() is the function’s logic. As you can see, here we’re just creating the various random values, such as phone number and zip code.
Finally your custom function is registered by CustomFunctions.associate() . As you can see later, this function is exposed by the name of “{Your Namespace}.RANDOM”. (The namespace will be defined in the manifest later.)

Unlike other Office Add-ins, custom function runs on the separated runtime, not on browser engine runtime. Then you cannot include external references as you do in normal html page.
If you want to interact with external resources, you can request data through XMLHttpRequest() (as well as stream in real time) in your custom functions.

This HTML page (mypage.html) is used only on loading-time.

mypage.html

<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8" />
  <meta http-equiv="X-UA-Compatible" content="IE=Edge" />
  <meta http-equiv="Expires" content="0" />
  <title></title>
  <script src="https://appsforoffice.microsoft.com/lib/1.1/hosted/custom-functions-runtime.js" type="text/javascript"></script>
  <script src="myfunc.js" type="text/javascript"></script>
</head>
<body>
</body>
</html>

myfunc.js

function getrandom(x) {
  if (x == "phone") {
    var n1 = Math.floor(Math.random() * 1000);
    var n2 = Math.floor(Math.random() * 1000);
    var n3 = Math.floor(Math.random() * 10000);
    return "(" + ("000" + n1).slice(-3) + ") " + ("000" + n2).slice(-3) + "-" + ("0000" + n3).slice(-4);
  }
  else if (x == "zip") {
    var n1 = Math.floor(Math.random() * 1000);
    var n2 = Math.floor(Math.random() * 10000);
    return ("000" + n1).slice(-3) + "-" + ("0000" + n2).slice(-4);
  }
  else {
    return "not supported"
  }
}
CustomFunctions.associate("RANDOM", getrandom);

You can expose the function’s spec as json file. In this example, we put the following myfunc.json in the public site. These descriptions in JSON file are displayed for users (such as hint or suggestions) when using this function.
Here we’re using scalar value for inputs and outputs, but you can also use matrix dimensions for parameters.

myfunc.json

{
  "functions": [
    {
      "id": "RANDOM",
      "name": "RANDOM",
      "description": "Create various random data (phone, zip)",
      "result": {
        "type": "string",
        "dimensionality": "scalar"
      },
      "parameters": [
        {
          "name": "type of data",
          "description": "Which type of data (\"phone\" or \"zip\")",
          "type": "string",
          "dimensionality": "scalar"
        }
      ]
    }
  ]
}

Manifest and Install

It’s ready to register your custom functions in Excel.
Even when development time, only side-loading installation (which is the installation process for developers) is supported for running custom functions. (For instance, Visual Studio debugger cannot run custom functions.) Then I’ve just created shared folder in my local computer, and set this folder as Trusted Add-in Catalogs by the following steps.

  1. Open Excel
  2. Select “File” – “Options” menu.
  3. Select “Trust Center” tab and click “Trust Center Settings” button.
  4. Add your shared folder in Trusted Catalogs Table. (See the following screenshot.)

Now, please create the following manifest file (UTF-8 encoding) and put this file (.xml) in this shared folder.

<?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>bdb0c148-86d1-4789-ae6a-3e5fb97a61b5</Id>
  <Version>1.0.0.0</Version>
  <ProviderName>Tsuyoshi Matsuzaki</ProviderName>
  <DefaultLocale>en-US</DefaultLocale>
  <DisplayName DefaultValue="My test functions" />
  <Description DefaultValue="Generate random sample data"/>
  <Hosts>
    <Host Name="Workbook" />
  </Hosts>
  <Requirements>
    <Sets DefaultMinVersion="1.1">
      <Set Name="CustomFunctionsRuntime" MinVersion="1.1"/>
    </Sets>
  </Requirements>
  <DefaultSettings>
    <SourceLocation DefaultValue="https://localhost:44301/index.html" />
  </DefaultSettings>
  <Permissions>ReadWriteDocument</Permissions>
  <VersionOverrides xmlns="http://schemas.microsoft.com/office/taskpaneappversionoverrides" xsi:type="VersionOverridesV1_0">
    <Hosts>
      <Host xsi:type="Workbook">
        <AllFormFactors>
          <ExtensionPoint xsi:type="CustomFunctions">
            <Script>
              <SourceLocation resid="Funcjs" />
            </Script>
            <Page>
              <SourceLocation resid="Funchtml"/>
            </Page>
            <Metadata>
              <SourceLocation resid="Funcmeta"/>
            </Metadata>
            <Namespace resid="Funcns" />
          </ExtensionPoint>
        </AllFormFactors>        
      </Host>
    </Hosts>
    <Resources>
      <bt:Urls>
        <bt:Url id="Funcjs" DefaultValue="https://localhost:44301/asset/myfunc.js" />
        <bt:Url id="Funchtml" DefaultValue="https://localhost:44301/asset/mypage.html" />
        <bt:Url id="Funcmeta" DefaultValue="https://localhost:44301/asset/myfunc.json" />
      </bt:Urls>
      <bt:ShortStrings>
        <bt:String id="Funcns" DefaultValue="TEST" />
      </bt:ShortStrings>
    </Resources>
  </VersionOverrides>
</OfficeApp>

Open Excel and select “My add-ins” in “Insert” tab. Then you can find your custom add-in as follows.

As I mentioned earlier, custom functions run on the separated runtime in Excel, not on the internet browser runtime.
Because of this mechanism, your custom function can also run offline, once it’s installed in your Excel.

Run !

After you add (install) this add-in in your Excel, you can use your custom functions with auto-complete suggestion or hints. See the following screenshot.

Of course, you can use your function with several fantastic Excel built-in capabilities !
For example, you can use together with “auto-fill” by just dragging range in sheet as follows. It will bring you drastically high productivity with the power of your custom functions !

All functions are called (invoked) simultaneously and executed one at a time. (Because JavaScript is single-threading.) But, you can use batch calling in custom function’s runtime, which aggregates multiple calls into one function call, and you can improve running performance.

Let’s enjoy Excel !

 

Update History :

May 2019  Custom functions has reached GA (generally available). Updated source code for the latest GA release

 

 

Advertisements

Categories: Uncategorized

Tagged as:

1 reply »

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s