The Functions Tab

In the Functions tab, you can create functions in the Excel sheet to Read/Write to items in the connected Core namespace. You can execute real-time and history reads on items and the value will be retrieved and returned in the selected worksheet cells. Expand the ReadValue part of the tab to see the options:

Figure 1. Functions - ReadValue

ReadValue

The items to read can be configured in the following ways:

Manual - Enter the path of the item to be read manually
Selected - All the items added to the Selected tags field (see previous section) will be read
Range - Paths of items to be read can be selected from a range of cells in the worksheet

It can also be selected to return the quality and timestamp from the items by checking the appropriate check boxes. For this example we will use the "Selected tags" added in the System Explorer tab and check the quality and timestamp boxes. Click Create to retrieve the values and return the selections in the worksheet:

Figure 2. ReadValue Example

To use the Range option, select Range in the panel then click "…". The paths from the previous example can be selected in the worksheet.

Figure 3. ReadValue - Range option

Click OK, then select an empty cell in the worksheet and click Create.

Figure 4. ReadValue - Range option Values

To refresh the values in the cells you can click on Force Calculate button in the "INMATION" tab of the Excel sheet.

Figure 5. Force Calculate button

ReadHistoricalData

The ReadHistoricalData function works in a similar manner to ReadValue with the same method for selecting paths. If the Selected paths is chosen, the items selected in the Explorer tab are used.

Figure 6. ReadHistoricalData function

The Start and End time for the data can be selected, as well as the aggregate and number of retrieved intervals for the history call.

Clicking Create will place the historical data in the worksheet with the object paths for the selected items heading the columns of retrieved data.

Figure 7. Retrieved Historical Data in Worksheet

If the RAW aggregate is used for history calls then the maximum number of values retrieved will be equal to the entered Intervals number. This is different to the way Raw history calls are handled in the rest of the system and is specific to the inExcel Template. To retrieve the full raw historical data for the selected time period, a high interval number (equal or greater than the estimated number of raw values) should be entered.

ReadHistoricalDataAtTime

The ReadHistoricalDataAtTime function works in a similar manner to ReadValue with the same method for selecting paths. If the Selected paths is chosen the items selected in the Explorer tab are used.

Figure 8. ReadHistoricalDataAtTime function

The timestamp for the data can be selected.

Clicking Create will place the historical data in the worksheet with the object paths for the selected items heading the columns of retrieved data.

Figure 9. [Retrieved Historical Data (at time) in Worksheet

The internal call to the Historian, which is used to retrieve the value at the specified time is using the Interpolated Aggregate. StartTime is the provided timestamp, EndTime is StartTime + 1 millisecond.

ReadAdvancedEndpoint

The ReadAdvancedEndpoint function can be used to utilize WebAPI Advanced Endpoints in Excel Template. Use-case specific Lua code can be developed, which returns data to Excel Template. The Lua code is processed by the Core - this allows efficient server side processing of the data.

The ReadAdvancedEndpoint function works in a manner similar to ReadValue with the same method for selecting paths. If Selected is chosen, the items selected in the System Explorer tab are used.

Figure 10. ReadAdvancedEndpoint function

The Endpoint field specifies the address of a WebAPI Advanced Endpoint.

The address of the endpoint can either be entered manually (like in the Read functions) or selected from a list of preconfigured endpoints. Follow this link to learn How to prepare the Endpoint List.

Figure 11. Selecting an Endpoint

In the later case, additional parameters needed for this endpoint can be entered in the Parameters text box or edited using a dynamically created form. To use this form, simply click on the … button next to Parameters value field.

Figure 12. Additional Parameters

The Start and End fields can be used to define the earliest and latest data to retrieved from the system. These limits can be provided as valid timestamps or as a references to the field containing a valid ISO8601 UTC timestamp. Can be empty.
The fields Result Rows and Result Columns are used to specify the number of rows and columnes returned from the Advanced Endpoint.
If more parameters are needed for the query, these can be specified in the Parameters field. The contents of this field can either be empty, a valid JSON string or a reference to the field containing JSON string.

Clicking Create will place the data in the worksheet with the object paths for the selected items heading rows of retrieved data.

Figure 13. Retrieved Advanced Endpoint data in Worksheet

An Example of Advanced Endpoint Functions:

local lib = {}
local STR = require "esi-string"

-- this function provides time and paths using standard fields of "args" object
function lib:echo(args, req, hlp)
    local matrix = {}
    matrix.rows = {}
    local row1 = {}
    local row2 = {}
    local row3 = {}
    local row4 = {}

    if args.paths then
        row1[1] = args.paths[1]
        row1[2] = args.paths[2]
    end

    row2[1] = args.tstart
    row2[2] = args.tend

    row3[1] = args.numberOfRows
    row3[2] = args.numberOfColumns

    row4[1] = args.parameters.a
    row4[2] = args.parameters.b

    matrix.rows[1] = row1
    matrix.rows[2] = row2
    matrix.rows[3] = row3
    matrix.rows[4] = row4
    return matrix
end


-- this function provides time and path using custom "parameters" object
function lib:Sum(args, req, hlp)
    local path = args.parameters.path
    local startT = args.parameters.startT
    local endT = args.parameters.endT

    local time_start = syslib.gettime(startT)
    local time_end = syslib.gettime(endT)

    local sum = 10
    local rs, more = syslib.getrawhistory(path, false, time_start, time_end)

    for T, v, q in rs() do -- note the order: timestamp, value and quality
        if q == 0 and type(v)=="number" then
            sum = sum + v
        end
    end

    local result = {}
    result.rows = {}
    result.rows[1] = {}
    result.rows[1][1] = sum

    return result
end

return lib

The Advanced Endpoint function should return an Lua table which contains only one field: rows. The contents of this field should be two-dimensional array. The dimensions should be equal to Result Rows and Result Columns.
This is an example of the table, converted to a JSON string:

{
    "rows":[
        ["System/Core/Speed", "System/Core/Temp"],
        ["2021-03-05T15:24:59.000Z", "2021-03-05T16:24:59.000Z"],
        [120,110],
        [1,2]
    ]
}

An example for the contents of the args parameter:

{
    "paths": ["/System/Core/Speed", "/System/Core/Temp"],
    "tstart": "2021-03-05T15:24:59.000Z",
    "tend": "2021-03-05T16:24:59.000Z",
    "numberOfRows": 4,
    "numberOfColumns": 2,
    "parameters": {"a":1, "b":2}
}

WriteValue

The WriteValue functions work in a similar manner to ReadValue with the same method for selecting paths.

Figure 14. WriteValue function

To write a value to the selected object path(s), enter a value in the field and click Create.

Figure 15. WriteValue Results

The worksheet displays the paths of the selected objects and the result of the write operation.

Users can also specify the datatype for the WriteValue operation by selecting from the dropdown menu. The written value will then be saved as the selected datatype.

Figure 16. WriteValue choose datatype

If you want to write an array value then the Array checkbox should be selected and the array value entered using the vertical bar character as a delimiter (for example 1|6|99 or a|b|c|z).

Figure 17. WriteValue Array