SharePoint Online: How to get all lists and libraries

Hey folks, at some time we come to a point, where we want to know, what is going on in our intranet. Either if we plan to migrate the content to another tenant or to clean up our intranet. I wrote a step-by-step instruction for you to find get all lists and libraries in your SharePoint environment. At the end of this blog post, you’ll find also the ready-to-use script.

Since the official module of Microsoft does not include a cmdlet for lists, I will use the PNP.PowerShell module.

Prerequisites

Before we start with coding, we should be aware, that there are some prerequisites, we need to fulfil in order to get all lists and libraries in our farm.

Step-by-Step Instruction to get all lists and libraries

Add your service account as admin to all sites

You might ask yourself, why is it necessary to add a service account to all sites. In SharePoint Server it works as soon as you have access to the SharePoint Shell. You need it because the cloud model does not provide access to all sites just by having the SharePoint admin role. You have to enable your service account with the rights to access the sites, prior accessing them.

Even if you have connected to the admin site, you will notice, that it does not work:

Error message for not found list

To add the service account to all sites, you have to make use of the module Microsoft.Online.SharePoint.PowerShell. If you are not familiar with this, check out SharePointOnlinePowerShell: How to connect to SharePoint (sposcripts.com).

Connecting to the environment, can be achieved with this cmdlets:.


$AdminUrl = "https://m365x388358-admin.sharepoint.com/" 
$Credential = Get-Credential


Import-Module -Name Microsoft.Online.SharePoint.PowerShell
Connect-SPOService -Url $AdminUrl -Credential $Credential

You’ll get a prompt for credentials. Provide the service account with SharePoint admin role here. We will reuse it later for the PNP module.

Prompt for credential

So after connecting, we can add our service user as site collection administrator with this script. Keep in mind to change the user variable to your service account’s user principal name.

$User = "[email protected]"

$SPOSites = Get-SPOSite 
foreach ($SPOSite in $SPOSites)
{
    Set-SPOUser -Site $SPOSite.Url -LoginName $User -IsSiteCollectionAdmin $true
}

Disconnect-SPOService

This is how the output looks for me:

Output of the cmdlet, after adding site collection administrator permission

Since our service user has access to all sites, we can now proceed with our analysis.

Get all lists and libraries with PowerShell

For the purpose of an interactive analysis of all lists and libraries, it is sufficient to connect interactively to the tenant with this script:


$AdminUrl = "https://m365x388358-admin.sharepoint.com/" 
$Credential = Get-Credential

Import-Module PNP.PowerShell
Connect-PnPOnline -Url $AdminUrl -Credentials $Credential

You have to replace the URL with your SharePoint admins URL.

If you run the cmdlet, credentials will be prompted. Please use a user account, which has the SharePoint administrator role granted. If you don’t know how to grant it, check out the official Microsoft references, they explain it with a video, which will help you.

Prompt for credential

After you have provided the credentials, you are connected. You can test it by querying all sites.

Get-PnPTenantSite

As you can see, I have a bunch of sites, which we will analyze further on.

All sites in PowerShell
$Export = New-Object System.Collections.Generic.List[object]
$Sites = Get-PnPTenantSite

$SitesCount = $Sites.Count
$i= 1


foreach ($Site in $Sites)
{
    Write-Host "($i / $SitesCount) Processing site $($Site.Url)"
    Disconnect-PnPOnline
    Connect-PnPOnline -Url $Site.Url -Credentials $Credential
    $Site = Get-PnPSite
    
    #get the information of the list
    Get-PnPList | ForEach-Object { 

        $NewExport = New-Object PSObject -Property @{
            Title = $_.Title
            Id = $_.ID
            DefaultViewUrl =   $_.DefaultViewUrl
            ItemCount = $_.ItemCount
            ParentWebUrl = $_.ParentWebUrl
        }
        $Export.Add($NewExport)
    }
    
    $i++
}

You can export the information like this:

$Export | Export-Csv -Path "C:\Users\Serkar\Desktop\lists.csv" -Delimiter ";" -NoTypeInformation

Based on your location, you have to change your delimiter to comma instead of semicolon.

The result of our scripting is, that we now have the possiblity to see all lists and libraries and also to identify lists and libraries with huge amount of data in it. Since it is a CSV file, you can open it with Excel to analyze the data:

get all lists and libraries in Excel

You can group all data with a pivot table, to see all lists to the corresponding web.

get all lists and libraries in a pivot table

Bonus: Ready-to-use Script

#Provided by Sposcripts.com

$User = "[email protected]"
$AdminUrl = "https://m365x388358-admin.sharepoint.com/"
$ExportPath = "C:\Users\Serkar\Desktop\lists.csv"

$Credential = Get-Credential

#region Set admin permissions

Import-Module -Name Microsoft.Online.SharePoint.PowerShell
Connect-SPOService -Url $AdminUrl -Credential $Credential

$SPOSites = Get-SPOSite 
foreach ($SPOSite in $SPOSites)
{
    Set-SPOUser -Site $SPOSite.Url -LoginName $User -IsSiteCollectionAdmin $true
}

Disconnect-SPOService

#endregion

#region get all lists and libraries
Import-Module PNP.PowerShell
Connect-PnPOnline -Url $AdminUrl -Credentials $Credential

$Export = New-Object System.Collections.Generic.List[object]
$Sites = Get-PnPTenantSite

$SitesCount = $Sites.Count
$i= 1


foreach ($Site in $Sites)
{
    Write-Host "($i / $SitesCount) Processing site $($Site.Url)"
    Disconnect-PnPOnline
    Connect-PnPOnline -Url $Site.Url -Credentials $Credential
    $Site = Get-PnPSite
    
    #get the information of the list
    Get-PnPList | ForEach-Object { 

        $NewExport = New-Object PSObject -Property @{
            Title = $_.Title
            Id = $_.ID
            DefaultViewUrl =   $_.DefaultViewUrl
            ItemCount = $_.ItemCount
            ParentWebUrl = $_.ParentWebUrl
        }
        $Export.Add($NewExport)
    }
    
    $i++
}
#endregion

$Export | Export-Csv -Path $ExportPath -Delimiter ";" -NoTypeInformation