In SharePoint Online, site collections contain various sites, libraries, and data. Exporting Site Collection details to a CSV file is useful for auditing, monitoring, and reporting.
Using PnP PowerShell, you can retrieve and save detailed information about site collections in a CSV format, making it easy to analyze and share.
Prerequisites
Before proceeding, ensure the following:
You have SharePoint Admin permissions.
You have PnP PowerShell installed.
You are connected to SharePoint Online.
Step 1: Install and Import PnP PowerShell
If you haven’t installed PnP PowerShell, install it using:
Install-Module -Name PnP.PowerShell -Scope CurrentUser -AllowClobber -Force
Then, import the module:
Import-Module PnP.PowerShell
Step 2: Connect to SharePoint Online
To retrieve site collections, first connect to SharePoint Online:
Connect-PnPOnline -Url "https://yourtenant-admin.sharepoint.com" -Interactive
🔹 Replace "yourtenant" with your actual tenant name.
For app-based authentication, use:
Connect-PnPOnline -Url "https://yourtenant-admin.sharepoint.com" -ClientId "Your-App-Client-ID" -Tenant "yourtenant.onmicrosoft.com" -CertificatePath "Path\To\Certificate.pfx"
Step 3: Retrieve and Export Site Collection Details
Now, retrieve all site collections and export them to a CSV file using:
# Retrieve all site collections
$sites = Get-PnPTenantSite
# Define CSV file path
$csvPath = "C:\ExportedSiteCollections.csv"
# Export site details to CSV
$sites | Select-Object URL, Title, StorageUsageCurrent, Owner, Template, LastContentModifiedDate | Export-Csv -Path $csvPath -NoTypeInformation
Write-Host "Site collection details exported to $csvPath successfully."
🔹 Get-PnPTenantSite retrieves all SharePoint Online site collections.
🔹 The Select-Object command filters key details:
- URL → Site collection URL.
- Title → Name of the site.
- StorageUsageCurrent → Current storage usage in MB.
- Owner → Site collection administrator.
- Template → Site template type.
- LastContentModifiedDate → Last modification date.
🔹 Export-Csv saves the data to a CSV file.
Output: A CSV file containing SharePoint site collection details.
Step 4: Open and Verify the Exported File
After running the script, open the CSV file to verify the data.
To open the CSV file in PowerShell:
Invoke-Item "C:\ExportedSiteCollections.csv"
OR manually navigate to C:\ExportedSiteCollections.csv and open it in Excel.
Step 5: Export Additional Site Collection Properties (Optional)
If you need more details, modify the Select-Object section:
$sites | Select-Object URL, Title, StorageUsageCurrent, StorageQuota, Owner, Template, SharingCapability, LastContentModifiedDate, TimeZoneId, Status | Export-Csv -Path $csvPath -NoTypeInformation
Additional Fields:
| Property | Description |
|---|---|
StorageQuota | Maximum storage allocated (in MB). |
SharingCapability | Sharing settings (e.g., ExternalUserAndGuestSharing). |
TimeZoneId | Time zone of the site collection. |
Status | Active or Deleted status of the site. |
Common Errors & Troubleshooting
| Error | Cause | Solution |
|---|---|---|
The term 'Get-PnPTenantSite' is not recognized | You are not using SharePoint Admin Center URL | Use -Url "https://yourtenant-admin.sharepoint.com" in Connect-PnPOnline |
Access Denied | Insufficient permissions | Ensure you are a SharePoint Admin |
Cannot connect to SharePoint Online | Authentication issue | Use -Interactive login or App-based authentication |
Export-Csv: Path is invalid | Incorrect file path | Ensure the folder *C:* exists or change the path |
Step 6: Disconnect the PowerShell Session
After exporting the site collection details, securely disconnect the session:
Disconnect-PnPOnline
This prevents unauthorized access.