Export All SharePoint Sites and Subsites to Excel Using PnP PowerShell

Introduction

Managing SharePoint Online environments often requires exporting data, such as a list of sites and subsites, for auditing, reporting, or documentation purposes. This tutorial provides a step-by-step guide to export all sites and their subsites into an Excel file using PnP PowerShell, making it easy for administrators to analyze and manage their SharePoint environment.

Prerequisites

To follow this guide, ensure you meet the following prerequisites:

1. PnP PowerShell Installed: Install the PnP PowerShell module if you haven’t already: Install-Module -Name PnP.PowerShell -Scope CurrentUser
2. Excel Module Installed: Install the ImportExcel module to work with Excel files: Install-Module -Name ImportExcel -Scope CurrentUser
3. Admin Permissions: Your account must have SharePoint Online Admin permissions to access all site collections and subsites.
4. Modern Authentication: Ensure you have multi-factor authentication enabled for added security.

Step-by-Step Guide

1. Connect to the Tenant Admin Site

Use the Connect-PnPOnline command to authenticate with your SharePoint Admin Center. This connection is essential to access the list of sites within your tenant.

# Define the Admin Center URL
$adminCenterUrl = "https://yourtenant-admin.sharepoint.com"

# Connect to the Admin Center using modern authentication
Connect-PnPOnline -Url $adminCenterUrl -Interactive

2. Retrieve All Sites

The Get-PnPTenantSite cmdlet retrieves all top-level sites in the tenant. This serves as the starting point for collecting site data.

# Retrieve all top-level sites in the tenant
$allSites = Get-PnPTenantSite

3. Retrieve Subsites for Each Site

Loop through each top-level site and retrieve its subsites using the Get-PnPSubWeb cmdlet. This process ensures all subsites are captured recursively.

# Initialize an array to store site and subsite information
$siteData = @()

# Loop through each top-level site
foreach ($site in $allSites) {
    Write-Host "Processing site: $($site.Url)"

    # Connect to the top-level site
    Connect-PnPOnline -Url $site.Url -Interactive

    # Add the top-level site to the data array
    $siteData += [PSCustomObject]@{
        Title = $site.Title
        Url = $site.Url
        ParentSite = "N/A"
    }

    # Retrieve all subsites
    $subsites = Get-PnPSubWeb -Recurse

    # Add each subsite to the data array
    foreach ($subsite in $subsites) {
        $siteData += [PSCustomObject]@{
            Title = $subsite.Title
            Url = $subsite.Url
            ParentSite = $site.Url
        }
    }
}

4. Export Data to Excel

Use the Export-Excel cmdlet from the ImportExcel module to create an Excel file with the collected data. This file can be customized and shared with stakeholders as needed.

# Define the output file path
$outputFile = "C:\Reports\SharePointSites.xlsx"

# Export the site data to Excel
$siteData | Export-Excel -Path $outputFile -AutoSize -Title "SharePoint Sites and Subsites"

Write-Host "Site data exported to: $outputFile"

Script Summary

Here’s the complete script for convenience:

# Prerequisites
Install-Module -Name PnP.PowerShell -Scope CurrentUser
Install-Module -Name ImportExcel -Scope CurrentUser

# Connect to Admin Center
$adminCenterUrl = "https://yourtenant-admin.sharepoint.com"
Connect-PnPOnline -Url $adminCenterUrl -Interactive

# Retrieve all top-level sites
$allSites = Get-PnPTenantSite

# Initialize array for site data
$siteData = @()

# Loop through sites and collect data
foreach ($site in $allSites) {
    Write-Host "Processing site: $($site.Url)"
    Connect-PnPOnline -Url $site.Url -Interactive

    $siteData += [PSCustomObject]@{
        Title = $site.Title
        Url = $site.Url
        ParentSite = "N/A"
    }

    $subsites = Get-PnPSubWeb -Recurse
    foreach ($subsite in $subsites) {
        $siteData += [PSCustomObject]@{
            Title = $subsite.Title
            Url = $subsite.Url
            ParentSite = $site.Url
        }
    }
}

# Export to Excel
$outputFile = "C:\Reports\SharePointSites.xlsx"
$siteData | Export-Excel -Path $outputFile -AutoSize -Title "SharePoint Sites and Subsites"
Write-Host "Site data exported to: $outputFile"

# Disconnect session
Disconnect-PnPOnline

Output Example

The exported Excel file will contain the following columns:

• Title: The name of the site or subsite.
• Url: The URL of the site or subsite.
• ParentSite: The URL of the parent site (or “N/A” for top-level sites).

Final Notes

• Ensure you have the required permissions to access the sites and subsites. For tenant-level data, SharePoint Administrator or Global Administrator permissions are necessary.
• The script exports data to an Excel file that can be used for reporting, auditing, or documentation.
• If you encounter issues, verify that both PnP PowerShell and ImportExcel modules are installed correctly.