Extract the details of the SharePoint Online Sites that includes Site URL, Administrator Email, Group Email Id, Storage Space Used, and User Count

Get-SharePointDetails.ps1

#####################################################################################
######Author: Neeraj Kumar
######Created: 28/09/2023
######Usage: Extract the details of the SharePoint Online Sites that includes Site URL, Administrator Email, Group Email Id, Storage Space Used, and User Count. Please create required variables under shared resources.
#####################################################################################

# Retrieve the stored username and password from Azure Automation variables
$adminUrl = Get-AutomationVariable -Name 'AdminURL'
$username = Get-AutomationVariable -Name 'SPAdminUsername'
$password = Get-AutomationVariable -Name 'SPAdminPassword' | ConvertTo-SecureString -AsPlainText -Force

# Create the PSCredential object
$credential = New-Object System.Management.Automation.PSCredential($username, $password)

# Connect to SharePoint Online
Connect-SPOService -Url $adminUrl -Credential $credential

# Retrieve site collections
$sites = Get-SPOSite -Limit All

# Loop through site collections to retrieve the information
$results = @()
foreach ($site in $sites) {
    $siteUrl = $site.Url
    
    # Get the owner/administrator email
    $adminEmail = $site.Owner
    
    # Get storage space used (in MB)
    $storageUsed = [math]::Round($site.StorageUsageCurrent / 1MB)

    # Get the number of users in the site
    $usersCount = (Get-SPOUser -Site $siteUrl -Limit All).Count

    # Get SharePoint Group email ids
    $groupEmails = Get-SPOSiteGroup -Site $siteUrl | Where-Object { $_.Email -ne $null } | ForEach-Object { $_.Email }

    # Add to results array
    $results += [PSCustomObject]@{
        "SiteUrl"        = $siteUrl
        "AdminEmail"     = $adminEmail
        "StorageUsedMB"  = $storageUsed
        "UsersCount"     = $usersCount
        "GroupEmails"    = $groupEmails -join ", "
    }
}

# Export the results to CSV locally
$tempFile = "$env:TEMP\SPSitesInfo.csv"
$results | Export-Csv -Path $tempFile -NoTypeInformation

# Connect to SharePoint Online Document Library using PnP
$docLibraryUrl = Get-AutomationVariable -Name 'SPDocLibraryUrl'
Connect-PnPOnline -Url $docLibraryUrl -Credentials $credential

# Delete the existing file if it exists
$existingFile = "/Shared Documents/SPSitesInfo.csv"
if (Get-PnPFile -Url $existingFile -ErrorAction SilentlyContinue) {
    Remove-PnPFile -Url $existingFile -Confirm:$false
}

# Upload the new CSV to SharePoint Document Library
Add-PnPFile -Path $tempFile -Folder "/Shared Documents" -FileName "SPSitesInfo.csv"

# Clean up local CSV
Remove-Item -Path $tempFile