I'm new to SharePoint and trying to find a way to hide a folder or file, specifically an Excel document, without affecting its permissions. Right now, we have 6 users who frequently work with our main shared Excel file for orders in the desktop app. Additionally, there are 3 users utilizing power queries to extract their order data into a different Excel file. We want to ensure those 3 users cannot access the main Excel file. However, I've learned that if I break permissions for them, it could cause issues with their power queries since they need access to the main file. I've heard that PowerShell might help with hiding a file or folder, but it seems that functionality is only available in classic SharePoint and not the modern version. My plan is to have all main files on a Document SharePoint site, create a separate site for the 6 users with a link to the main Excel file, and then set up individual sites for the 3 users while somehow hiding the main Excel from them without breaking permissions. I'm looking for advice or alternatives to achieve this!
3 Answers
Have you thought about running the power queries under a service account? You could remove permissions from the main files for the users who don’t need access. This would likely be the cleanest solution since other methods could get complicated. Just be aware that you can't hide a folder without altering the permissions in some way.
It sounds like your main issue is trying to use Excel as both a database and a reporting tool. Unfortunately, Excel doesn't really support row-level security, so you might want to consider reorganizing your data. You could look into moving subsets of the data to a data warehouse instead or rethinking how you structure your documents.
Can you share what code you've got so far? It's much easier to help troubleshoot if we see your setup. Just remember, it’s important to put in some effort on your end too!
I've thought about that, but I'm not sure how to set it up. Could you point me to a resource or guide for setting a service account in Power Query to access the main file? I'd really appreciate it!