Skip to main content

Use Microsoft Power Automate to control your Nooxl Apps

Within Nooxl Apps, the work processes for spreadsheet models can be easily automated, such as importing, exporting or copying data, calculating spreadsheet models or complete Nooxl Apps and preparing cube data. To control the automation processes from outside, Nooxl Apps offers a REST API that makes it possible to control Nooxl Apps from other applications. One of these applications is Microsoft Power Automate.

What is Microsoft Power Automate?​

Microsoft Power Automate allows users to create automation rules, called “flows,” without writing code to automate repetitive tasks such as email notifications, data synchronization between applications, or complex business processes. Microsoft Power Automate is part of the Microsoft Power Platform, which also includes Power BI, Power Apps and Power Virtual Agents.

Power Flow Demo for Nooxl​

This sample solution Nooxl Power Automate Solution ZIP was created for the automation of a Nooxl spreadsheet model. The ZIP file can be imported into the Microsoft Power Automate Cloud environment as a solution (1) and tested.

Flow Solution Import

Using the Nooxl REST API in Power Flows​

The Power Flow sets the necessary parameters and then calls the Nooxl REST-API. The parameters are determined by the demo model used. Further details can be found in REST API “model calculate”.

Nooxl demo model

There are two areas in our example flow. In the first area, API authentication takes place with the return of an AccessToken and in the second area, a demo spreadsheet model is called.

Authentication​

The REST API “authenticate” is used for authentication.

power flow auth

First, the parameters for the REST API “authenticate” are set. For our demo, they are:

text title="Flow ‘authenticate’ - Demo Parameters” nooxlApiHostName: https://ncs-demo.nooxl.com nooxlApiLogin: API-Tester nooxlApiPassword: ivtP@t>UN>KF9

After calling the REST API “authenticate”, the response is converted into a JSON object, of which the token is then used as a JWT 'Bearer' token for authorization in the next step.

Model calculation​

Further parameters are set for the model calculation via the REST API “model calculate”.

power flow calculate

The parameters are used to select the spreadsheet model modelName, the data set modelSelections, specify input values dataInputs for named ranges in the model and define the output ranges dataOutputMarkupNames. A detailed description of the parameters can be found at REST API “model calculate”.

Flow 'model calculate' - Demo-Parameter
    nooxlAppNuid: tJel71R1YEGFu80GinVMCw

    request body:
        {
            "modelName":"Cash flow",
            "modelSelections":[
                {"dataLabelName":"2024-01"},
                {"dataLabelName":"Asset A"}
            ],
            "dataInputs":[
                {"markupName":"Usage Inputs","
                  cellValues":[
                    ["810","16","","20.00","4%"],
                    ["245","4","","25.75","4%"],
                    ["140","1","","14.80","4%"],
                    ["950","15","","14.30","1%"]
                ]
                },
                {"markupName":"Rent Inputs",
                  "cellValues":[
                    ["20.11","3.00%"],["21.00","2.00%"],["34.70","1.50%"],["15.40","5.00%"]
                  ]
                },
                {"markupName":"Parameter Inputs",
                 "cellValues":[
                    ["1.84%"],["7.79%"],["7.53%"],["466.81"],["1.00%"],["35.00"],["2.00%"],
                    ["3.15"],["18.75%"],["2.00%"],["3.73%"],["4.29%"],["1.49%"],["0.00%"],
                    ["2.50"],["1.50%"],["5.00"]
                  ]
                }
            ],
            "dataOutputMarkupNames":[
                "Total annual revenue Outputs",
                "Total annual expense Outputs"
            ],
            "modelOptions":{
                "saveModel":false
            }
        }

With the "saveModel" option, the model is saved if there is sufficient authorization. This triggers existing actions in the model, such as the creation of a new version with a copy of data.

As a result, the values from the Ouput areas are returned.

REST-API 'model calculate' demo result JSON
{
    "dataOutputs":[
        {
            "markupName":"Total annual revenue Outputs",
            "cellValues":[
                ["477,715.82","493,584.47","502,651.10","512,028.82","521,734.43","531,788.61","542,213.04","553,030.50","564,264.85","575,941.13"]
            ]
        },
        {
            "markupName":"Total annual expense Outputs",
            "cellValues":[
                ["221,563.00","223,969.94","226,453.86","228,968.19","231,513.78","234,091.03","236,700.40","239,342.33","242,017.28","244,725.71"]
            ]
         }
    ],
    "errorCodes":[]
}

The result is converted into a JSON object in Power Flow. Individual values can then be used in a targeted manner.