Using the ParseJSON function and a generic flow to call any SharePoint Rest API endpoint and manipulate the results from Power Apps

Even though there is a ‘Send an HTTP request to SharePoint’ action in Power Automate that allows us to call any SharePoint Rest API endpoint, there is no equivalent action for the same in Power Apps.

We can leverage Power Automate Flows to help with any action that is exposed through the SharePoint Rest API, but as previously there was no easy way to parse JSON objects in Power Apps without a premium license, the process could be a little bit cumbersome (using RegEx techniques with the MatchAll function).

Now with an experimental feature that enables the parsing of JSON objects, the process gets way simpler (as it is experimental, it should be used only in POCs/tests and not production scenarios by now).

We can use a generic flow accepting the SharePoint rest endpoint parameters as we would do to call it from Power Automat, in combination with the ParseJSON function to call the SharePoint Rest API and manipulate the results in a simple way from a Canvas App as in the sample below:

Check this post to know how to create and use the Flow that helps to accomplish this.

Enabling the experimental feature

To enable the feature, your app will need to be in an authoring version that is equal to 3.22091.11. This can be changed under your app settings/support:

Then after changing authoring version, search for the ParseJSON experimental feature and enable it:

Creating the Flow

To begin the flow let’s use the ‘Power Apps V2’ trigger, with the following parameters (keep the same order in order for the formula shown later to work):

Now you simply add a call to the ‘Send an HTTP request to SharePoint’ action, passing those parameters accordingly.

We then do a little bit of manipulation, given that the headers value are going to be sent as string, we need to enable the text mode in the headers field, and convert the headers value to JSON using the json function in the expression pane (name of the trigger input vary in your flow according to the order you add the parameters, but use the reference to the Headers parameter):

json(triggerBody()['text_3'])

Finally add a ‘Respond to a Power App or Flow’ action and pass the body as the output:

Adding the Flow to a Canvas App

To call the flow from a canvas app, add it first from the Power Automate pane:

Calling the Flow

In this example, we named the flow as SharePointHTTP.

In the case below we are calling the endpoint which lists the associated site members group members, and storing the converted results in a collection.

The headers of the Rest call should be passed as a JSON string. If no request body is needed in the call, simply pass an empty string as a parameter.

We get the results that were previously stored in the variable locResponseBody, convert them to a proper array of typed objects by using the ForAll function, and save the array in a collection named colSPGroupMembers.

UpdateContext(
    { //keeping it as a separate variable in case debugging is needed
        locResponseBody: SharePointHTTP.Run(
            "https://contoso.sharepoint.com/sites/somesite",//SharePoint Site Address
            "GET",//HTTP Method
            "_api/web/AssociatedMemberGroup/users",//endpoint
            "{
                'content-type': 'application/json',
                 'accept': 'application/json;odata=nometadata'
            }",//http headers
            ""//body if needed
        ).body
    }
);
ClearCollect(colSPGroupMembers, ForAll(
    Table(ParseJSON(locResponseBody).value) As rawItem,//convert results to table first
    {
        Id: Value(rawItem.Value.Id),
        LoginName: Text(rawItem.Value.LoginName),
        IsSiteAdmin: Boolean(rawItem.Value.IsSiteAdmin),
        Email: Text(rawItem.Value.Email),
        UserPrincipalName: Text(rawItem.Value.UserPrincipalName),
        Title: Text(rawItem.Value.Title)
    }
))

Worth noting that the outputs of the ParseJSON function are always untyped, so you need to cast them to the proper format, using a conversion function. You can use primitive values, or a table for arrays/tables.

Alternatively, the example above can also be achieved by using a combination of AddColumns + DropColumns formulas. Add the new values you want to parse, and then remove the ‘Value’ property of the item:

UpdateContext(
    { //keeping it as a separate variable in case debugging is needed
        locResponseBody: SharePointHTTP.Run(
            "https://contoso.sharepoint.com/sites/somesite",//SharePoint Site Address
            "GET",//HTTP Method
            "_api/web/AssociatedMemberGroup/users",//endpoint
            "{
                'content-type': 'application/json',
                 'accept': 'application/json;odata=nometadata'
            }",//http headers
            ""//body if needed
        ).body
    }
);
ClearCollect(
    colSPGroupMembers,
    DropColumns(
        AddColumns(
            Table(ParseJSON(locResponseBody).value),
            "Id", Value(Value.Id),
            "LoginName",Text(Value.LoginName),
            "IsSiteAdmin", Boolean(Value.IsSiteAdmin),
            "Email",Text(Value.Email),
            "UserPrincipalName",Text(Value.UserPrincipalName),
            "Title",Text(Value.Title)
        ),
        "Value"
    )
)

In the following example, we are adding a member to the site members using the Rest API. As the call returns a single object, no casting to table is needed (only for the object properties):

UpdateContext(
    {
        locResponseBody: SharePointHTTP.Run(
            "https://contoso.sharepoint.com/sites/somesite",//SharePoint Site Address
            "POST",//HTTP Method
            "_api/web/AssociatedMemberGroup/users",//endpoint
            "{
                'content-type': 'application/json',                
                 'accept': 'application/json;odata=nometadata'
            }",//http headers
           "{'LoginName':'i:0#.f|membership|"&cboUsers.Selected.UserPrincipalName&"'}"//body if needed
        ).body
    }
);

UpdateContext({locUserObject: With({jsonItem:ParseJSON(locResponseBody)},
    {
        Id: Value(jsonItem.Id),
        LoginName: Text(jsonItem.LoginName),
        IsSiteAdmin: Boolean(jsonItem.IsSiteAdmin),
        Email: Text(jsonItem.Email),
        UserPrincipalName: Text(jsonItem.UserPrincipalName),
        Title: Text(jsonItem.Title)
    }
)});
Notify(locUserObject.Title &" was added")

In this case, we are calling a POST method, and passing the details of the user to be added in the body (based on a selection in a drop-down).

Conclusion

Even though there is no direct way to call the SharePoint Rest API from Power Apps, we can use now a single generic flow to do most calls, and parse the results easily without a premium license.

This enables more advanced scenarios for SharePoint queries, and even for querying SharePoint lists with dynamic filters/retrieving a large number of items without facing delegation issues.

Always bear in mind that the ParseJSON feature is experimental, so for now it should not be used in Production apps.

If you want to download this Flow as a package, you can get it from my GitHub samples repository.

References

Convert JSON data in Power Apps without Premium license by Hiro Nagao

Introduction to Parse JSON in Power Apps | ParseJSON Arrays as Table; Return Array from flow by Reza Dorrani

ParseJSON function in Power Apps (experimental) – Power Platform | Microsoft Docs