Power Apps- SharePoint Row Level Security (Using parseJSON)

One of the biggest negatives for using SharePoint in Power Apps is the lack of row level security. What do I mean? Well, SharePoint only has 3 levels of security:

• Full Read & Write
• Full Read Only
• Read & Write rows you have created

Which sounds enough, but what happens when you want multiple people to access certain rows (like a certain team or job role)? Well then you need Dataverse, which is great, but is a premium connector and isn't cheap (even not compared to free SharePoint).

There is a setting where you can deactivate the users access through SharePoint with a modified contribute role (Check this blog out for how michelcarlo.com).

But this isn't fully secure, as a determined user could bypass this by making their own Power App/Flow or using the API.

Luckily there are other ways, one is to use Power Automate to share each row individually (though this requires groups to keep it useable and isn't great for complex logic). The other way is a little more complex to setup but a lot easier to maintain, and more flexible with the logic. And we get to play with the new parseJSON, which is cool.

Before we start there are a few things you will need:

1. Environment with Dataverse
2. A Solution containing your App and flows
3. parseJSON turned on in Experimental Features

This may not be available in your tennent yet, so you may have to wait a couple of weeks.

So we have everything we need, what's the plan? Well we are going to use a Child Flow to avoid 'run only user', this flows owner can access the SharePoint list, then pass the data back to the App, where we use parseJSON to convert it back to an array/collection.
Child flows are now not heeded, see end of below for update

All the flows and App must be in a solution.

Child Flow

The child flow will receive the filter value and query the list. The returned array will be passed back as a string.

In the settings of the flow we need to change the 'run only users', here we set the SharePoint connector to use the flow owners credentials (so the user doesn't need access to the list).

App Flow

The next flow is your standard flow called by the Power App. It simply passes the filter value to the Child Flow (The logic to filter the user could be here or in the app, e.g a lookup list that everyone has a read only view of).

App

So the first bit is the easy bit, call the App Flow with the filter (or pass the user for the Flow to do the filter logic).

The Flow returns the SharePoint Get_Items JSON array, which looks like below.

To extract this we have 2 options. Instead of a normal return we can use Response, which returns an array. One problem, it's a premium connector. The second is the parseJSON function, which can extract a collection from a string.

Sadly it is not a simple function, but a nest of them, and here it is:

ClearCollect(colList,
    AddColumns(
            Table(
                ParseJSON(Flow.Run("test").jsonresult)
            )
        ,"Title",Text(Value.Title)
        ,"Number",Value(Value.field_1)
        ,"Boolean",Boolean(Value.Boolean)
    )
);

So how does it work?

First we call the flow (this case test) and we want the return with the json string (jsonresult), we return this to the parseJSON. The parseJSON can extract an object from the string directly, but if we want to get an array/collection, we need to turn it into a Table first.

Table(
     ParseJSON(Flow.Run("test").jsonresult)
)

There's one problem, and it's parseJSON doesn't know what type each field is, so we get just one 'Value' field.

So we need to use Text(), Value() and Boolean() to set the type of each Field, and we need a new field to put them in. So we need the AddColumns function.

AddColumns(CollectionFromJSON,
     ,"Title",Text(Value.Title)
     ,"Number",Value(Value.field_1)
     ,"Boolean",Boolean(Value.Boolean)
)

SharePoint lists have Display Names and names, this is how you can rename a column but without need to update links

As you can see field_1 is a String so we use Text(), field_2 is a number/integer so we use Value(), and Boolean is a boolean, so we use Boolean().

And that's it, we are now able to not only use 2 different accounts/security roles, but also convert strings back to arrays/collections.

Update

Good news, withthe release of PowerApps (V2) connector we no longer need the complexity of passing between a ChildFlow, we can do it in one.

The main change is the V2 connector allows you to change the run only user (like in a button press/childflow). What is particulary cool is now you can mix and match (e.g one SharePoint action us the user, another use the flow owners).

Above you can see now we can manage the run-only permissions.

In the above example the first list folder will only see what the app user has access to, in the second list folder we will see what the owner of the flow has access to (ideally a Service Account).