Overview

SharePoint administrators are often tasked with producing reports like:

• Which sites contain outdated or unused lists?
• Where are document libraries being duplicated across teams?
• How can we identify content sprawl in our Microsoft 365 tenant?

This is especially common when preparing for governance reviews, restructuring initiatives, or compliance audits. Manual collection is too slow and prone to error.

To solve this quickly, HarjTech built a production-ready PowerShell script using PnP PowerShell that loops through all sites, collects every list, and outputs a clean CSV.

This guide walks you through:

• Installing PnP PowerShell
• Connecting to your tenant securely
• Running the script
• Understanding what it does
• Exporting a full Excel-ready list audit in minutes

Step 1 – Install PnP PowerShell

Open PowerShell in administrator mode and run the following command to install the module:

Install-Module -Name PnP.PowerShell -Force -AllowClobber

When prompted to trust the repository, type Y and press Enter.

Step 2 – Connect to SharePoint Online

Use the command below to log in securely. This supports multi-factor authentication.

Connect-PnPOnline -Url "https://yourtenant.sharepoint.com" -Interactive

Replace yourtenant with your Microsoft 365 tenant name.

Step 3 – Run the HarjTech PowerShell Script

This script connects to the SharePoint admin center, retrieves all non-personal site collections, and collects data on every list found.

# -------------------------------------------------------------
# Script by HarjTech | www.harjtech.com
# Purpose: List all SharePoint Lists across the tenant
# Outputs: Clean CSV with site title, list name, type, item count
# -------------------------------------------------------------

Import-Module PnP.PowerShell

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

# Get all site collections, excluding OneDrive personal sites
$sites = Get-PnPTenantSite | Where-Object { $_.Template -ne "SPSPERS" }

# Prepare output array
$report = @()

# Loop through each site and retrieve lists
foreach ($site in $sites) {
    Write-Host "Processing site:" $site.Url -ForegroundColor Cyan

    try {
        Connect-PnPOnline -Url $site.Url -Interactive

        $lists = Get-PnPList

        foreach ($list in $lists) {
            $report += [PSCustomObject]@{
                SiteTitle     = $site.Title
                SiteUrl       = $site.Url
                ListTitle     = $list.Title
                ItemCount     = $list.ItemCount
                ListTemplate  = $list.BaseTemplate
                Created       = $list.Created
                LastModified  = $list.LastItemModifiedDate
            }
        }
    }
    catch {
        Write-Warning "Could not access site: $($site.Url)"
    }
}

# Export to CSV
$csvPath = "$env:USERPROFILE\Desktop\Tenant-List-Audit-HarjTech.csv"
$report | Export-Csv -Path $csvPath -NoTypeInformation -Encoding UTF8

Write-Host "`nReport saved to $csvPath" -ForegroundColor Green

What This Script Does

• Connects to your tenant’s SharePoint Admin Center
• Retrieves every non-personal site collection
• Loops through each site and fetches:
  • List title
  • List type/template
  • Item count
  • Creation and last modified dates
• Exports everything to a CSV on your desktop

Benefits of This Script

• Saves time during audits, migrations, or governance reviews
• Prevents manual site-by-site clicks to gather basic data
• Helps surface unused lists and potential for cleanup
• Provides IT admins and governance teams with clear reporting

The Final Report

The exported file opens in Excel or any CSV-compatible tool.

It includes:

• Site title and URL
• List name and item count
• List type/template ID
• Date created and last modified

You can filter by:

• High item count
• Lists not modified in over 12 months
• Template type (e.g., document libraries vs. calendars)

Need More Than a Script? HarjTech Can Help

This script gives you visibility. But governance, cleanup, and restructuring still require planning and execution. That’s where HarjTech delivers.

We help organizations:

• Audit and restructure SharePoint environments
• Clean up abandoned content
• Migrate or archive old data
• Build governance models for long-term control
• Automate reporting and access reviews

Whether you're cleaning up after years of unstructured growth or preparing for an M365 rollout — we can help.