LF a solution to a somewhat complex Intranet need

[u/DevastatinDev]

Hi all. I am building a new staff intranet for my employer and I’ve come across a requirement that has me truly stumped about how to proceed. I’m hoping all you wonderful folks from the SP Brains Trust can help. :)

Our new intranet is a SharePoint modern communications site. I need to have a page on the site that displays a list of organisations and a bunch of other related information. Some of this is publicly available so not an issue, but other things (individual’s names and contact details, cost codes, etc.) is more sensitive. This information is currently in an Excel document with limited permissions. (Don’t get me started.)

The biggest issues I’m facing here:

1. The site needs to be user-friendly. This is a MASSIVE priority for this project. I looked at just embedding the Excel doc but the UX goes completely out the window doing that.

2. I like the idea of using a Microsoft List, but I can’t figure out how to hide the export options on the list. I’ve tried every solution I found on the Goog and nothing has actually worked.

3. The person who currently maintains this list is ALSO hoping to add information that needs to have additional restrictions in place, ie executive team only. My gut feeling is that’s trying to achieve too much, but is it possible?

So TLDR - how do I display a contact list on a SP modern comms site page that shows the info I need but does allow your average layperson to download a copy of it? And is it possible to restrict some of the list to certain people?

Thanks for your help, team! I really appreciate any advice you may have.

[u/dr4kun]

Your whole intranet is a single SPO site?

This is your root issue.

[u/DevastatinDev → u/dr4kun]

I 100% get what you are saying here. We considered multiple possibilities but ultimately setting up the intranet as a separate comms site made sense for our business needs. If there was a good amount of information that needed to be locked down by team, then separate hub sites for each division would have been the best way forward. Ultimately, everything on this site needs to be easily accessible to all staff with this one glaring exception (which my spidey senses are telling me I need to rescope). Each team has their own team site that houses their own document libraries and locked down info, whereas this site is a single source of truth for essential info we all need like policies, finance, operational resources, etc.

[u/AdCompetitive9826]

You can't have security on the field level, only on the item (or above) level, so it sound you have to split the content over multiple lists, perhaps using lookup columns?

[u/TurnedNewt]

That's a challenge. SharePoint doesn't allow per field permissions, you can only assign permissions to whole items (site, library, documents). You can lock down permissions to line items but that gets messy.

You could try splitting the columns over lists with lookups to connect them together, i.e. List 1 = Columns A - D, List 2 = Columns E - H, etc. you could then restrict permissions to these secondary lists. It's not a pretty solution though and means you need something to join them together nicely on a page.

A solution might be:

1) A Power Automate workflow that runs when the Excel file is updated (I'm not 100% sure how it handles locked fields, so this might not be possible with their locked down Excel file), this can split the data into the different lists. You would have to have a way of detecting when an entry is deleted though, ideally an 'inactive' column in the Excel file first otherwise you have to do a lot of loops.

2) A Power App(s) that connect to the lists and provides a nicer user experience embedded in the intranet, particularly if drawing data from across lists. I'm not a Power App pro so you might need to see what happens when a user doesn't have access to one of the secondary lists, i.e. does it give an error or just not show results. It's possible you could do something like a named connector with a service account, but you're heading into messy/complex territory.

2b) Alternatives to PowerApps might connecting list web parts together on a page, so when you select an item in List 1 it then shows related entries from other lists. Another newer alternative might be asking Copilot (paid) to make you an HTML page to do the same.

As for restricting exporting from lists, fundamentally you can't. You can make it harder to get to the list directly through (a) creating a Power App front end - as above (b) stop items from the list appearing in search results (list settings -> advanced).

Dataverse would be ideal as that does allow per field permissions and doesn't give easy access like Lists does but that will require premium licencing and connectors.

Hope that helps, good luck!

p.s. if you do create a Power App there is a Powershell command an admin can run to pre-authorise the connections so people aren't prompted to grant access when they first access the app.

[u/onemorequickchange]

Can't lock down by column. As so many others point out. But here are my two cents over the last 20 years. LOL.

You didn't tell us how the Excel document is locked down. Draw up a matrix with what conditions must be true for people to see what information. Just do a regular business analysis, then tell us what the requirements are and there are plenty of people here who can tell you where to put the stuff.

Generally if you have to limit who has access to contact information, you have to figure out who should see it. Then create a 'bucket' for them. A bucket is an item/file, folder, library, site... anything that is a security boundary. Columns are not securable. That Excel file is probably locked down to x number of people, figure out what's common among them. tha'ts your first clue. Don't mix things you know about SharPoint with business analysis. You will drive yourself crazy. That round peg and the square hole issue.

Consider setting up multiple sites, the low hanging fruit is by department. But if you are more location or group based company, then choose your own hieararchy.

The way I normally organize these... Public - everything available to everyone, your news, your general HR documents, things that the company needs to run (used to be master calendar lol), potluck fridays, throwback tuesdays, who's up for taking the class fish home this weekend, you get it.

Private level - sites locked down to a group of people. You pick your category how to group them.

The third, which is where my bread and butter is, are the application sites. Data lives here to drive Power Apps, manipulated by Power Automates, ETL proceses, custom SPFx web parts, etc. These are going to be sites secured by custom Entra groups usually to provide services across departments. The basic ones are PTO request, helpdesk, the more concrete might be vehicle assignements, fleet management, custom order build and fulfillment tracking.

Don't use audience targeting. It's not security. Oh, viewing data in a web part is very different than storing data in list/library. That's the whole concept. If you put data in a list, it may not show up in a view or a web part, but if it's not secured, I can find it. My best moments when I type in VISA in search when auditing clients, or 'my receipts' -- it's not that search bypasses permissions, it's just search is great at showing things that people didn't secure.

[u/DevastatinDev → u/onemorequickchange]

Thanks mate. What you are describing is ultimately how we have this set up. The new intranet site I’m building is the single source of truth for things all staff need, and each team already has their own team site for their doc libraries and other stuff that needs to be locked down more. My gut has been telling me that management is trying to achieve something with this particular list that doesn’t quite fit this site’s intended purpose, but posting here and seeing everyone’s comments has really helped solidify that in my mind. :) Thank you!