r/sharepoint • u/Dismal_Insurance6816 • Aug 18 '26

SharePoint Online How to prevent users from extracting SharePoint list data in Excel/Power Query while keeping CRUD access in Power Apps?

Hi everyone,

I'm facing a security/architecture challenge with a Power Apps canvas app using a SharePoint list as its backend.

The Goal: Users must be able to read, create, and maintain records through the Power Apps application. However, they should not be able to extract the raw table directly (e.g., via Excel Get Data > From SharePoint List / Power Query) or have the list indexed by Microsoft 365 Copilot.

The Issue & What I've Checked So Far:

  1. Direct Permissions: Granting standard Read/Contribute permissions allows users to query the entire list endpoint using Excel / OData.
  2. Custom Permission Levels: Disabling "Use Remote Interfaces" breaks the Power Apps connector (403 errors). Disabling "Client Integration Features" only hides the export button, but doesn't stop Power Query.
  3. Item-Level Permissions: Only works if users strictly view their own records, which is too restrictive for collaborative maintenance.
  4. Power Automate Proxy Pattern: Running flows with a service account works, but introduces overhead and makes native Created By / Modified By tracking more complex to maintain.

My Questions:

  • Is there any clean, lightweight pattern in SharePoint to restrict direct querying without breaking Power Apps?
  • For those who had this exact requirement, did you end up using Dataverse for Teams, or did you stick with SharePoint via elevated proxy flows?

Any advice, battle-tested workarounds, or best practices would be greatly appreciated!

4 Upvotes

83% Upvoted

23 comments sorted by

9

u/Suhail-Sayed Aug 18 '26

I don't think there's a solution. If they have access, they have access.

2

u/Dismal_Insurance6816 Aug 18 '26

Shane's method works well for hiding lists from the UI, but it's "security through obscurity." Because the underlying REST API permissions stay active, anyone with the list URL can still load it in Power Query.

2

u/OddWriter7199 Aug 18 '26

So name it with numbers on the end like he suggests, so the URL can’t be guessed. But you are correct, it is security through obscurity.

1

u/shirpars Aug 18 '26

I've come across your scenario so many times, the only solution I've had is to use powershell to hide the list from Site Contents and turn off indexing on the list so no one can find the list

1

u/Megatwan Aug 18 '26

I like to browse sharepoint with ms access

1

u/Dani5h87 Aug 18 '26

Yerp. #3 would have been the only option that even comes close, but as you stated it doesn’t fit your use.

I’m curious though; why prohibit the access through Power Query?

2

u/Dismal_Insurance6816 Aug 18 '26

The main reasons are data governance and compliance. We want users to interact only through the Power Apps UI where validation rules and business logic are strictly enforced, avoiding bulk data scraping into local Excel files and preventing Copilot from indexing the full dataset.

4

u/Suhail-Sayed Aug 18 '26

With Dataverse, you have slightly more control like assigned rows only etc. But you still can't selectively block a channel. Security is managed at the table and column permissions level.

2

u/Dani5h87 Aug 18 '26

Yeah, so you’re not really going to have an option here with SharePoint. You’ll probably want to look at other options for managing the key data.

Even with REST APIs disabled, users can still grab the list data with Power Query. If it’s on a SharePoint, and that user has permissions in that SharePoint…they’re gonna get it if they’re determined.

6

u/Megatwan Aug 18 '26

You cant (without gutting an on prem sp farm)

You want a custom db/app where you can create an abstraction layer via perms, impersonation logic and stored procs... not SPO.

5

u/ComradeJJaxon Aug 18 '26

Power Automate Flows would work.

Basically you configure your flow with a run as user meaning it acts on the spo list only through a specific user like a service account. The flow returns data to the app or can write back to the list. Your power app users only need to have run permissions for the flow - no other permissions for the list. If you're worried that they might discover the flows in the environments - you can set a specific code that the app will give the flow as a parameter and the flow will only trigger if the code is correct - since users can't edit the app or the flows they won't discover it.

Beware: this is only allowed for flows that do not contain any kind of premium functionality otherwise it would be license misuse!

4

u/ComradeJJaxon Aug 18 '26

Also: The code might be crackable if a user is savy enough to check the internet traffic but again if this is your worry i think power apps and spo as a whole is not the right usecase.

3

u/ComradeJJaxon Aug 18 '26

I think this is the only way without restricting too much. As you said created by and modified by are hard to track but manageable via parameters to the flow.

Good luck.

4

u/toddklindt MVP Aug 18 '26

Restricted Content Discovery keeps Copilot from crawling a site.

3

u/OddWriter7199 Aug 18 '26

Shane Young has a YT video or two about hiding the SharePoint list(s).

3

u/DailyHoodie Aug 18 '26

Option 4 is your best bet my friend. You have to weight its complexity vs compliance trade-off.

2

u/blud_13 Aug 18 '26

You cant win this one with SharePoint permissions. If a user has read on the list, the OData endpoint is available to them, and Excel and Power Query are just calling that endpoint AS the user. There is no permission level that grants Power Apps read and denies Graph/OData read, they are the same read.

The pattern that works is taking users OFF the list entirely and having the app write on their behalf. Put the list in a site the users have no access to, then use a Power Automate flow with an owner-level connection (or HTTP with Azure AD against an app registration) as the middle layer, and the app calls the flow for CRUD. Users get zero direct access, Copilot cant index what they cant see, and your audit story gets better too.

Also, if this data is sensitive enough to matter, Dataverse is the right answer and you likely already have some capacity with your Power Apps licensing. Also, a sensitivity label with encryption on the site keeps Copilot out regardless. Happy to go deeper on the flow-as-middle-layer setup if it helps.

1

u/OddWriter7199 Aug 18 '26

How many people need to do collaborative maintenance, a few admins? Give them Full Control, all others Contribute. Set item level permissions. Those with full control will still be able to see and edit everything.

3

u/Dismal_Insurance6816 Aug 18 '26

It's a team of regular operational users/peers, not just admins. They need to view and edit each other's records. Giving them Full Control is too risky governance-wise, while standard Contribute with item-level locks them out of each other's items.

1

u/kindoramns Aug 18 '26

Have the form submit to a list, use power automate to copy to another list with stricter permissions, delete original from submittal list

1

u/shirpars Aug 18 '26

Editing can't work in this scenario