PowerShell script for SharePoint Audit

Hi MubinAbd123,

It's good that you've attempted to convert user GUIDs to user names, but it seems there might still be a hiccup in how the user data is processed. Your approach of using Get-PnPUserProfileProperty for each user to fetch the 'PreferredName' property is solid, but it could be failing for some reason. Here's how you can enhance that part of your script:

$Users = $Group.Users | ForEach-Object {
    $UserGUID = $_.Id
    $User = Get-PnPUserProfileProperty -Account $UserGUID -PropertyName 'PreferredName'
    if ($User -ne $null) {
        $User.PreferredName
    }
}

By adding an if condition to check if the user data is not null before attempting to extract the preferred name, you can prevent empty or null values from causing issues.

Empty Values Resulting in Commas: The commas you're seeing in the output are likely due to empty values in the $Users array. When you join an array using .Join(","), empty values can lead to those extra commas. The improvement you made in the previous step should help with this as well.

By filtering out null or empty values using the if condition, you should be able to avoid these unnecessary commas.

Give this revised version a shot, and it should help you handle the user GUID to user name conversion and prevent empty values from causing those extra commas in the output.

# 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'
            if ($User -ne $null) {
                $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"