PowerShell script for SharePoint Audit

Hi all,

I've been trying to run a script on PowerShell that will loop through each site on my company SharePoint and get all the users in each site and their user permission for the site (read, write, owner, etc.).

Initially I ran this script:

#Site collection URL
$AdminSiteURL = "my site"
$CSVPath = "my path"

#Connect to SharePoint Online Admin Center
Connect-PnPOnline -Url $AdminSiteURL -UseWebLogin

#Array to store group data from all site collections
$AllGroupsData = @()

#Get all site collections in the SharePoint environment
$SiteCollections = Get-PnPTenantSite

#Loop through each site collection
foreach ($SiteCollection in $SiteCollections) {
$SiteURL = $SiteCollection.Url
Write-Host "Processing Site Collection: $SiteURL"

#Connect to the current site collection
Connect-PnPOnline -Url $SiteURL -UseWebLogin

#Get All Groups from the site collection - Exclude Hidden, Limited Access, and SharingLinks Groups
$Groups = Get-PnPSiteGroup | Where { $_.LoginName -notlike "Limited Access*" -and $_.LoginName -notlike "SharingLinks*"}

$GroupsData = @()
foreach ($Group in $Groups) {
$GroupsData += New-Object PSObject -Property @{
'Group Name' = $Group.Title
'Permissions' = $Group.Roles -join ","
'Users' = $Group.Users -join ","
}
}

#Add the group data of the current site collection to the array for all site collections
$AllGroupsData += $GroupsData
}

#Export the data to CSV
$AllGroupsData | Export-Csv $CSVPath -NoTypeInformation

Write-Host "Group data for all site collections has been exported to: $CSVPath"

This returned the permissions of some users that were on the site but not part of the site group. For the the users who were part of the site group, they showed up on the report as GUID's like this ('4cbc76d6-7da9-42bb-a403-85594b1a4cf9').

I then edited the script to display user names instead of GUID's using this script:

#Site collection URL
$AdminSiteURL = "my site"
$CSVPath = "my path"

#Connect to SharePoint Online Admin Center
Connect-PnPOnline -Url $AdminSiteURL -UseWebLogin

#Array to store group data from all site collections
$AllGroupsData = @()

#Get all site collections in the SharePoint environment
$SiteCollections = Get-PnPTenantSite

#Loop through each site collection
foreach ($SiteCollection in $SiteCollections) {
$SiteURL = $SiteCollection.Url
Write-Host "Processing Site Collection: $SiteURL"

#Connect to the current site collection
Connect-PnPOnline -Url $SiteURL -UseWebLogin

#Get All Groups from the site collection - Exclude Hidden, Limited Access, and SharingLinks Groups
$Groups = Get-PnPSiteGroup | Where { $_.LoginName -notlike "Limited Access*" -and $_.LoginName -notlike "SharingLinks*"}

$GroupsData = @()
foreach ($Group in $Groups) {
$Users = $Group.Users | ForEach-Object {
$UserGUID = $_.Id
$User = Get-PnPUserProfileProperty -Account $UserGUID -PropertyName 'PreferredName'
$User.PreferredName
}

$GroupsData += New-Object PSObject -Property @{
'Group Name' = $Group.Title
'Permissions' = $Group.Roles -join ","
'Users' = $Users -join ","
}
}

#Add the group data of the current site collection to the array for all site collections
$AllGroupsData += $GroupsData
}

#Export the data to CSV
$AllGroupsData | Export-Csv $CSVPath -NoTypeInformation

Write-Host "Group data for all site collections has been exported to: $CSVPath"

However, this resulted in the users showing up as commas on the report like this ',' or ',,'. 

My understanding is that this suggests that there might be an issue with how the user data is being processed and concatenated and that it's possible that the '$Group.Users' array contains empty or null values, leading to these commas in the output.

If anyone could provide me with any information on how I could resolve this, I would be very grateful.