Custom Functions

https://docs.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-overview


They can leverage Azure Function calculations in the cloud
Previewed in October 2018



Function Code

function method_to_call(arg1) { 
        return arg1 + 20;
}

Function Code Definition

Excel.Script.CustomFunctions["BETTER_Namespace"]["Function_One"] = { 
   call : method_to_call,
   description : "Returns something really useful",
   helpUrl : "https://bet/help.htm",
   result : {
        resultType : Excel.CustomFunctionValueType.number,
        resultDimensionality : Excel.CustomFunctionDimensionality.scalar,
    },
    parameters : [{
        name: "arg1",
        description: "This is the first number",
        valueType: Excel.CustomFunctionValueType.number,
        valueDimensionality: Excel.CustomFunctionDimensionality.scalar,
    }],
    options: { batch: false, stream: false }
};

call - The JavaScript function to call. This function does not need to have the same name as the one that is registered (below)
description - The text that will appear during an autocomplete.
helpUrl - This is the URL of a web page that will appear in a task pane if the user requests help.
result - This defines the information that is going to be returned back to Excel.
resultType is the type of data that is returned.
resultDimensionality is the size of the data, either scalar or matrix. When returning a matrix of values, your function returns an array, where each array element is another array that represents a row of values.
An example of a matrix - return [["first","row"],["second","row"],["third","row"]];
arguments - [Optional] The order of the parameters must match the order in the JavaScript function. Each parameter must have the following properties:
name - The text for the name of the argument
description - The text that gives more information about the argument
valueType - The type of value to be submitted, similar to the resultType
valueDimensionality - The size of the data, similar to the resultDimensionality
options - Additional options


Function Registration

Excel.run (function(ctx) { 
        ctx.workbook.customFunctions.add("BETTER_Namespace.Function_One");
        return ctx.sync();
});


Manifest Entry (new Extension Point)

When we declare add-in commands we do not need a javascript file, just an html file
We include the functionjs javascript file to allow for the custom functions to be packaged as a standalone add-in
This is to allow for this javascript (add-in) to be downloaded from the endpoint and used as an embedded package in the local runtime.


In the developer preview the SCRIPT element is required but is not used
For the developer preview we only need the html file


<Hosts> 
   <Host xsi:type="Workbook">
      <AllFormFactors>
         <ExtensionPoint xsi:type="CustomFunctions">
            <Script>
               <SourceLocation resid="functionsjs" />
            </Script>
            <Page>
               <SourceLocation resid="functionshtml" />
            </Page>
         </ExtensionPoint>
      </AllFormFactors>>
   </Host>
</Hosts>
<Resources>
   <bt:Urls>
      <bt:Url id="functionsjs" DefaultValue="https://myfile.js" />
      <bt:Url id="functionshtml" DefaultValue="https://myfile.html" />
   </bt:Urls>
</Resources>


Custom Functions - Developer Preview

1 string parameter
1 range parameter
refreshes automatically when data on the worksheet changes
1 stream function, with an interval argument
1 function that returns a range into multiple cells (that is streaming)
making a web service call - sample data finance/stock
finance sources - alpha vantage




© 2018 Better Solutions Limited. All Rights Reserved. © 2018 Better Solutions Limited TopNext