Uncategorized

Deliver Your Own Excel Custom Functions (JavaScript Add-in) for Users

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 can deliver your functions all over the world through marketplace. You can deliver your own advanced Excel functions, such as AI integrated functions, etc.

For example, you can easily 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.)
This concept is shown in Microsoft Ignite 2017 and see “Announcing tools for the AI-driven digital transformation” in team blog. This will be the sample of 1st party implementation for this experience.

The following is the advantages of this new custom functions. (As I write as “Note”, now this capability is in Preview and several limitations exist.)

  • It’s JavaScript. It can run on any platforms like Mac, Online, Mobile, etc.
    (Note : But now you must use only Excel Windows desktop client in current Preview.)
  • Are you a power user or a professional developer ? For the latter folks, once you’ve created your custom functions, you can submit and deliver through marketplace (Office store).
    (Note : But now you cannot submit your custom function add-in in current Preview.)

It’s now in Preview and you need Office Insider version (build 8711 or later) for running. Therefore please sign-up Office Insider before starting.

Let’s dive into 5 minutes’ example !

Logic and Registration

First you must create the html page (.html) and script file (.js), and locate (expose) in the internet.
The following is our example. Here we are using famous chance.js and it can be downloaded (copied) from chance.js site.

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.edog.officeapps.live.com/lib/beta/hosted/office.js" type="text/javascript"></script>
  <script src="https://example.com/chance.js" type="text/javascript"></script>
  <script src="https://example.com/myfunc.js" type="text/javascript"></script>
</head>
<body>
</body>
</html>

myfunc.js

Office.initialize = function(reason){
  function getrandom(x) {
    if (x == "phone")
      return chance.phone();
    else if (x == "zip")
      return chance.zip();
    else if (x == "address")
      return chance.address();
    else
      return "not supported";
  }
  
  Excel.Script.CustomFunctions = {};
  Excel.Script.CustomFunctions["TEST"] = {};
  Excel.Script.CustomFunctions["TEST"]["RANDOM"] = {
    call: getrandom,
    description: "Create various random data (phone, zip, etc)",
    result: {
      resultType: Excel.CustomFunctionValueType.string,
      resultDimensionality: Excel.CustomFunctionDimensionality.scalar,
    },
    parameters: [
      {
        name: "type of data",
        description: "Which type of data you need (phone, zip, etc)",
        valueType: Excel.CustomFunctionValueType.string,
        valueDimensionality: Excel.CustomFunctionDimensionality.scalar,
      }
    ]
  };
  
  Excel.run(function (context) {    
    context.workbook.customFunctions.addAll();
    return context.sync().then(function(){});
  }).catch(function(error){});
};

Let’s see what’s doing in this code.
First getrandom() is the logic of our custom function. As you can see, here we’re just creating the various random values using chance.js. If your custom function retrieves data from the web (i.e, async IO is invoked), you need an asynchronous function. (See “Github : Create custom functions in Excel (Preview)” for details about the asynchronous function.)

Note : It seems that some libraries (which are dynamically loaded libraries and so on) cannot be used in custom functions now …

Next we must define our custom function by Excel.Script.CustomFunctions (see Script.CustomFunctions reference) before registration. Here we’re using scalar value for input parameters and output result, but if you need some lookup for Excel ranges, you can also use matrix dimensions for parameters.

When the user add this custom function, this definition is registered by context.workbook.customFunctions.addAll(). Once the custom function is registered, the user can use this function in all the workbooks in Excel.
If you want to delete the registered custom functions, please use deleteAll() instead in the same add-in application. (See the following.)

Note : Or you can delete registered custom functions by removing %userprofile%\AppData\Local\Microsoft\Office\16.0\Wef\CustomFunctions folder in your local machine.

Office.initialize = function(reason){
  Excel.run(function (context) {
    // check if exists your custom function
    ...
    context.workbook.customFunctions.deleteAll();
    ...
  }).catch(function(error){});
};

Manifest

It’s ready to register your custom functions in Excel.
Now in current Preview, only sideloading installation (which is the installatoin process for developers) is supported. Therefore you must create shared folder in your local computer, and set this folder as Trusted Add-in Catalogs by the following steps.
In the future, the user will be able to install from marketplace with a few clicks !

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

Please create the following manifest file (UTF-8 encoding) and locate 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>a4b7679d-7758-48a7-8bf3-7fb37fc2f20b</Id>
  <Version>1.0.0.0</Version>
  <ProviderName>Tsuyoshi Matsuzaki</ProviderName>
  <DefaultLocale>en-US</DefaultLocale>
  <DisplayName DefaultValue="My test functions" />
  <Description DefaultValue="custom functions test" />
  <Hosts>
    <Host Name="Workbook" />
  </Hosts>
  <DefaultSettings>
    <SourceLocation DefaultValue="https://example.com/mypage.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="functionsjs" />
              <SourceLocation resid="chancejs" />
            </Script>
            <Page>
              <SourceLocation resid="pagehtml"/>
            </Page>
          </ExtensionPoint>
        </AllFormFactors>
      </Host>
    </Hosts>
    <Resources>
      <bt:Urls>
        <bt:Url id="functionsjs" DefaultValue="https://example.com/myfunc.js" />
        <bt:Url id="chancejs" DefaultValue="https://example.com/chance.js" />
        <bt:Url id="pagehtml" DefaultValue="https://example.com/mypage.html" />
      </bt:Urls>
    </Resources>
  </VersionOverrides>
</OfficeApp>

You may wonder why the JavaScript reference is needed in this manifest (see <Script> element above). In fact, this tag is not used in current Preview, but in the future the script will be downloaded and can be used offline and packaged. Once the custom function is installed, it can run offline in Excel in the future.

Run !

Now let’s see how it works.

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

After you add this add-in in your client, you can use your custom functions with auto complete suggestion, hint, help file, etc.

Of course, you can use several existing Excel capabilities like “auto-fill” together. The users can accelerate their works integrating a lot of Excel capabilities with your advanced functions.

All functions are called (invoked) simultaneously and executed each one at a time. (Because JavaScript is single-threading…) In the future, you can use batch calling which aggregates multiple calls into one function call and you can improve performance.

You can also use the streamed function which can dynamically update the cell, such as IoT sensor’s inputs. (The associated graph is also updated by real-time.)

You can download the following complete example by Microsoft. Please try.

Github: Create custom functions in Excel
https://github.com/OfficeDev/office-js-docs/blob/master/docs/excel/custom-functions-overview.md

 

Now this is just initial Preview. The capabilities and performance will be accelerated in the future. (Currently, the add-ins rely on a hidden browser process, but it will work in the same process in the future.)
Let’s enjoy and look forward to the future update !

 

Advertisements

Categories: Uncategorized

Tagged as:

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 )

w

Connecting to %s