How to prepare the Endpoint List

To preconfigure the list of endpoints, the Lua module has to be prepared on the server.

In the following example the module is created in the Script Library of the Core since the context of the Web API Server is redirected to Core (see below).

Make sure to always check the Context Path of the Web API Server before you create syslib.ExcelAddIn.Definitions module.
Checking the Context Path on the Web API Server
Figure 1. Checking the Context Path on the Web API Server

After these preparations the Lua module can be entered into the Script Library.

It needs to be called syslib.ExcelAddIn.Definitions and has to implement the function GetAdvancedEndpoints.

Adding syslib.ExcelAddIn.Definitions to the Script Library of the Core
Figure 2. Adding syslib.ExcelAddIn.Definitions to the Script Library of the Core.

The following Lua code is an example for such a module.

local lib = {}

function lib.GetAdvancedEndpoints()
    local result = {}
    local configuration = {}
    configuration.result = result

    -- Definition of the endpoint which is using two additional
    -- parameters (control type double).

    local f1 = {}
    result[1] = f1
    -- The name of the Lua module where the function is defined.
    f1.moduleName = "my-calc"
    -- The Lua function name.
    f1.functionName = "sum"
    -- The text visible in the selection list.
    f1.displayName = "sum"
    -- Default number of result rows for this function (will be
    -- visible in the Excel Template Actions Pane).
    f1.defaultRows = 1
    -- Default number of result columns for this function (will be
    -- visible in the Excel Template Actions Pane).
    f1.defaultColumns = 1

    -- Definition of the function parameters.
    f1.parameters = {}

    local f1_p1 = {}
    f1.parameters[1] = f1_p1
    -- This name will be used in JSON string as a field name.
    f1_p1.parameterName = "s1"
    -- This name will be used to label the parameter field on the form.
    -- If omitted then parameterName will be used.
    f1_p1.displayName = "Parameter 1:"
    -- Defines what kind of control to use for entering parameter.
    -- Valid values are: double, integer, string, combo, aggregate.
    f1_p1.controlType = "double"
    -- Form created for entering parameters will be initialized using this value.
    f1_p1.defaultValue = 0

    local f1_p2 = {}
    f1.parameters[2] = f1_p2
    f1_p2.parameterName = "s2"
    f1_p2.displayName = "Parameter 2:"
    f1_p2.controlType = "double"
    f1_p2.defaultValue = 0

    -- Definition of the endpoint which is using two additional
    -- parameters (control type integer).

    local f2 = {}
    result[2] = f2
    f2.moduleName = "my-calc"
    f2.functionName = "mult"
    f2.displayName = "mult"
    f1.defaultRows = 1
    f1.defaultColumns = 1
    f2.parameters = {}

    local f2_p1 = {}
    f2.parameters[1] = f2_p1
    f2_p1.parameterName = "i1"
    f2_p1.displayName = "Parameter 1:"
    -- This control make possible to enter values of type integer.
    f2_p1.controlType = "integer"
    f2_p1.defaultValue = 1

    local f2_p2 = {}
    f2.parameters[2] = f2_p2
    f2_p2.parameterName = "i2"
    f2_p2.displayName = "Parameter 2:"
    f2_p2.controlType = "integer"
    f2_p2.defaultValue = 1

    -- Definition of the endpoint which is using five additional
    -- parameters (control type boolean, double, string, aggregate and combo).

    local f3 = {}
    result[3] = f3
    f3.moduleName = "my-calc"
    f3.functionName = "test"
    f3.displayName = "My Test"
    f3.defaultRows = 5
    f3.defaultColumns = 3
    f3.parameters = {}

    local f3_p1 = {}
    f3.parameters[1] = f3_p1
    f3_p1.parameterName = "number"
    f3_p1.displayName = "Amount:"
    -- This control make possible to enter values of type double.
    f3_p1.controlType = "double"
    f3_p1.defaultValue = 1.25

    local f3_p2 = {}
    f3.parameters[2] = f3_p2
    f3_p2.parameterName = "exact"
    f3_p2.displayName = "Exact"
    -- This control make possible to enter values of type boolean.
    f3_p2.controlType = "boolean"
    f3_p2.defaultValue = true

    local f3_p3 = {}
    f3.parameters[3] = f3_p3
    f3_p3.parameterName = "itemname"
    f3_p3.displayName = "Item name:"
    -- This control make possible to enter values of type string.
    f3_p3.controlType = "string"
    f3_p3.defaultValue = "<item name>"

    local f3_p4 = {}
    f3.parameters[4] = f3_p4
    f3_p4.parameterName = "aggregate"
    f3_p4.displayName = "Aggregate:"
    -- This control type shows the list of aggregates which can
    -- be used to retrieve historical data.
    f3_p4.controlType = "aggregate"
    f3_p4.defaultValue = "AGG_TYPE_INTERPOLATIVE"

    local f3_p5 = {}
    f3.parameters[5] = f3_p5
    f3_p5.parameterName = "list"
    f3_p5.displayName = "Aggregate:"
    -- This control type can be used to create a list of values.
    f3_p5.controlType = "combo"
    -- This property defines a contents of the control
    f3_p5.comboValues = {{value=1,text="AVERAGE"},{value=2,text="INTERPOLATIVE"}}
    -- Use text of the item which should be selected.
    f3_p5.defaultValue = "AVERAGE"

    -- Definition of the endpoint which is not using additional parameters.
    local f4 = {}
    result[4] = f4
    f4.moduleName = "my-calc"
    f4.functionName = "empty"
    f4.displayName = "empty"
    f4.defaultRows = 1
    f4.defaultColumns = 1
    f4.parameters = {}
    return configuration
end

return lib

The function GetAdvancedEndpoints returns a Lua table of the follwoing structure:

{
result = {
{
    moduleName="my-calc",
    functionName="sum",
    displayName = "Add",
    defaultRows = 1,
    defaultColumns = 1,
    parameters = {
        {
            parameterName="element1",
            displayName = "Element 1:",
            controlType = "double",
            defaultValue = 0
        },
        {
            parameterName="element2",
            displayName = "Element 2:",
            controlType = "double",
            defaultValue = 0
        }
    }},
{
    moduleName="my-calc",
    functionName="mult",
    displayName = "Multiply",
    defaultRows = 1,
    defaultColumns = 1,
    parameters = {
        {
            parameterName="f1",
            displayName = "Factor 1:",
            controlType = "integer",
            defaultValue = 1
        },
        {
            parameterName="f2",
            displayName = "Factor 2:",
            controlType = "integer",
            defaultValue = 1
        }
    }
}}

Function definition

  • moduleName – the name of the Lua module where the function is defined

  • functionName – the name of the function

  • displayName – the text which will be visible on the list

  • defaultColumns – the field “Result Columns” will be initialized using this value

  • defaultRows – the field “Result Rows” will be initialized using this value

Parameter definition

  • parameterName – the name used as the field name in the JSON string

  • displayName – the text used to label parameter field on the form

  • controlType – the control type used to input parameter value. Possible control types are: string, integer, double, boolean, aggregate and combo

    • string – accepts any text

    • integer – accepts integer numbers

    • double – accepts double numbers

    • boolean – accepts true/false

    • aggregate – enables selection from the list of aggregates (can be used to retrieve historical data)

    • combo – enables selection from the user defined list. Use comboValues to define the list (see example code above)