Use Case

It is a very common scenario for automation workflow to maintain configuration data in SharePoint or Excel or Dataverse or any other data source. We repeated the logic for reading & accessing configuration object for all workflows. By leveraging reusability, I have designed a child flow in cloud that reads configuration data & convert to JSON object using expressions. We will pass the JSON object as input parameter in Power Automate Desktop flow & access the object property using dot notation in the desktop flow actions.

Solution

1.Configuration SharePoint list

Firstly, lets create a custom SharePoint list. Provide the name of the list & add a new column ‘Value’ as multiple line of text as shown below:

Here, Title column will act as configuration key.

Enter 2 items or records in SharePoint list:

Title: WebURL
Value: https://www.microsoft.com/en-us/

Title: InputExcelFilePath
Value: //mynetwork/process1/input

2. Power Automate Desktop Workflow

Next, create a new desktop flow. Enter the name of the flow. Add an input parameter and enter the following details:

Variable name: ConfigObject
Data type: Custom object
External name: ConfigObject
Description: Object that stores all the configuration settings

Now, add “Launch new Microsoft Edge” action to launch the chrome browser to open the web application mentioned in the configuration. Pass the custom object ‘WebURL’ property value to the ‘Initial URL’ parameter using dot notation:

%ConfigObject.WebURL%

Finally, add “Close web browser” action to close the browser. Choose browser variable from the drop down:

Clik ‘Save’ to save the action & also save the desktop flow.

3. Reusable Child flow

Create a new ‘Instant flow’ & name it as ‘Get Configuration Object’. Add 2 input variables of type data type – SharePointSiteURL, ConfigListName

Note: Create the child flow inside solution as this is required for showing this flow in a list of child flow while calling it.

Next, add “Send an HTTP request to SharePoint” action to read items from SharePoint configuration list. Dynamically pass the value Site URL from the dynamic content window:

Copy & paste the below line in the URI textbox:

_api/web/lists/getbytitle('@{triggerBody()?['text_1']}')/items?$select=Title,Value&$top=5000

Note: To get entries more than 5k, you need to use pagination concept.

Post that add “Select” action to transform input array. Enter the below expression in ‘Function’ window:

body('Send_an_HTTP_request_to_SharePoint')?['d/results']

Click ‘Switch to text mode’ button on the right side of ‘Map’ parameter:

Enter the below expression in the ‘Function’ window for Map parameter. We are using replace function to replace ‘/’ with ‘//’ as json function will fail for shared network paths in configuration.

concat('"',item()?['Title'],'":"',if(startsWith(item()?['Value'],'/'),replace(item()?['Value'],'/','//'),item()?['Value']),'"')

Add “Compose” action to transform array to JSON object using expressions. Enter the below expression to convert to JSON object:

Finally, add “Respond to a Power App or flow” action to return the output of compose action to the caller:

Before calling this child flow from main flow, we need to modify the run only user settings. On flow details screen or page, scroll down to ‘Run only user’ settings and click ‘Edit’ button:

Select static or fixed connection of service account instead of ‘Provided by run-only user’ from the drop down:

Click ‘Ok’ on the warning pop up. Click ‘Save’ to make the changes.

4. Main Process Cloud flow

Finally, we will design an actual cloud flow to trigger desktop flow in attended or unattended mode. In this flow, we will call the reusable child flow to read the configuration settings & pass it to a desktop flow.

Note: Create the main flow inside solution as this is prerequisite for calling child cloud flows.

Choose the trigger as per the requirement for main flow. Add action “Run a Child Flow” to call the child flow to read the configuration entries. Select reusable configuration flow in the drop down and pass the value of SharePoint site & list name:

Finally, add “” action to call the desktop flow. Pass the ‘configObject’ from the output of child flow as shown below:

Save the main flow & run to test it.

Output

Output of reusable child flow:

Configuration JSON object passes as desktop flow in input parameter:

Input JSON object parameter in the log of desktop flow:

Conclusion

Following are the takeways:

• Cloud child flow can be reused for reading the configuration entries for desktop flows that saves development effort
• We can transform JSON array to JSON object using ‘Select’ action with the help of expressions
• Same reusability technique can be applied while reading items or configuration entries from online excel file.