Export All SharePoint Online Sites Using PnP PowerShell

What does this script do?

This script creates a consolidated SharePoint Online inventory containing site information, storage usage, site type, web creation date, site collection administrators, SharePoint owners and Microsoft 365 Group visibility. It connects to the SharePoint Admin Center, retrieves sites, processes each site and exports the final report to CSV.

Prerequisites

1. PowerShell 7 or later

PowerShell 7 or later is required because the script uses parallel processing.

$PSVersionTable

2. PnP PowerShell

Install-Module PnP.PowerShell -Scope CurrentUser
Get-Module PnP.PowerShell -ListAvailable
Import-Module PnP.PowerShell

3. Microsoft Entra ID application

You need an Entra application, client ID, tenant information, public certificate, private certificate, PFX password, the permissions required by the commands below, and admin consent where required.

Required access for this report

Get-PnPCommandPermission -CommandName Get-PnPTenantSite
Get-PnPCommandPermission -CommandName Get-PnPTenant
Get-PnPCommandPermission -CommandName Get-PnPMicrosoft365Group
Get-PnPCommandPermission -CommandName Get-PnPWeb
Get-PnPCommandPermission -CommandName Get-PnPUser
Get-PnPCommandPermission -CommandName Get-PnPGroup
Get-PnPCommandPermission -CommandName Get-PnPGroupMember

Use the returned application-permission requirements alongside the PnP documentation for your installed version. The SharePoint tenant-administration access requirement is separate from Microsoft Graph permissions.

4. Certificate file

Update the PFX path in the script. The certificate must include the private key and match the certificate registered on the Entra application.

5. SMTP configuration — only if you want email

The standard version emails the CSV through the configured SMTP server. If you do not need email, you can use the same script as a CSV-only report — see Do not want email? Make it CSV-only.

If you want email but do not have an SMTP server or relay, do not add a fake SMTP value. See What if you do not have an SMTP server? for the recommended Microsoft 365 alternatives.

6. Output folder

The account running the script needs permission to write to the configured output folder.

Configuration values

Review the configuration section before execution and use placeholders when publishing the script publicly.

$Tenant = "contoso.onmicrosoft.com"
$ClientId = "YOUR-APPLICATION-CLIENT-ID"
$PfxPath = "C:\Certificates\SharePointAutomationCert.pfx"
$AdminCenterUrl = "https://contoso-admin.sharepoint.com"
$TenantRootUrl = "https://contoso.sharepoint.com"
$OutputDirectory = "C:\Reports\SharePointSiteInventory"

How to execute the script

Step 1 — View or download

Click View Script to inspect the code. Click Open in Script Editor to modify a copy in the browser. Click Download .PS1 to download the PowerShell file to your PC.

Step 2 — Open PowerShell 7

Open PowerShell 7 using the account that will run the report. Administrator elevation is not inherently required for the script's cloud operations. Use an elevated session only when your setup requires it—for example, installing modules for all users or changing machine-level settings. The account still needs access to the PFX/private key and write access to the output folder.

cd "C:\Scripts\SharePoint"

Step 3 — Run the script

.\SharePoint_Site_Inventory.ps1

Step 4 — Check and resolve PowerShell execution policy

If PowerShell shows an error such as running scripts is disabled on this system, first check the effective policy:

Get-ExecutionPolicy
Get-ExecutionPolicy -List

For a normal user workstation, the preferred approach is to allow locally created scripts for your user account:

Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned

If the downloaded .ps1 file is being treated as a file from the Internet, unblock just this script:

Unblock-File -Path "C:\Scripts\SharePoint\SharePoint_Site_Inventory.ps1"

Then run the script again:

cd "C:\Scripts\SharePoint"
.\SharePoint_Site_Inventory.ps1

Use PowerShell 7 as Administrator only if the setup action requires elevation, such as installing modules for all users or changing a machine-level setting. The CurrentUser execution-policy change itself does not require administrator rights.

What results will be generated?

Illustrative CSV row

The values below are fictional examples. This table shows how one row from the exported CSV maps to the report columns.

On smaller screens, scroll the table horizontally to see all nine columns.

Storage and over-utilization

For each site, the script converts StorageUsageCurrent from MB to GB using StorageUsageCurrent / 1024. It sums those site values, reads the tenant's StorageQuota, and calculates over-utilization as used storage minus allocated storage. The percentage is over-utilized storage divided by allocated storage, multiplied by 100. Negative over-utilization is displayed as zero.

Email report behavior

The standard version creates the CSV first, builds an HTML email summary, attaches the CSV and sends it through Send-MailMessage. The temporary CSV is deleted only after the send command completes successfully. If email delivery fails, the CSV is retained for recovery. The current filename includes the date (not the time), so rerunning the script on the same day uses the same CSV path and can overwrite an earlier run's file.

The email's Report Date and Report Generated labels currently both display the date only; they do not show a time.

Do not want email? Make it CSV-only

$From = "SharePointReport@contoso.com"

$To = @(
    "admin@contoso.com"
)

$SmtpServer = "smtp.contoso.com"

Send-MailMessage `
    -From $From `
    -To $To `
    -Subject $Subject `
    -Body $Body `
    -BodyAsHtml `
    -Attachments $OutputCsv `
    -SmtpServer $SmtpServer `
    -ErrorAction Stop

What if you do not have an SMTP server?

You have two practical choices depending on whether email is actually required:

Troubleshooting

Certificate file not found

Test-Path "C:\Certificates\SharePointAutomationCert.pfx"

PnP PowerShell module not found

Get-Module PnP.PowerShell -ListAvailable
Install-Module PnP.PowerShell -Scope CurrentUser

Access denied or blank report fields

Check admin consent, application permissions, certificate matching, tenant/client ID and SharePoint access. This script intentionally suppresses per-site lookup errors to keep the console and CSV clean. If a field is blank, validate the relevant app permission and access to that site separately; do not interpret a blank value as a confirmed negative result.

SMTP email failed

Check SMTP server, sender, recipients, relay permissions, firewall/port and authentication. If email is not required, remove the email block and use CSV-only mode.

Security recommendations

• Never publish PFX files or private keys.
• Never publish certificate passwords, client secrets or SMTP passwords.
• Replace internal tenant names, IDs, SMTP servers, email addresses and local paths with placeholders.
• Use approved secret management for unattended automation.

Use the script