Post

I’ve created 100 posts in the last few years and this will be me 101st. I’m currently planning on writing a series of posts on retrieving different kinds of information about your SharePoint Online environment and adding these scripts to the PowerShell Gallery. The PowerShell Gallery is a perfect solution to store your own scripts which you use on a regular basis where other people can use them also. The script will be explained on each blog post. In this post we will be retrieving SharePoint Online Lists with more than 5000 items or an amount you specify.

The script can be found at  https://www.powershellgallery.com/packages/Get-ListsWithXAmountOfItems. I always recommend reading the code first as this is a script from the internet which can be found after clicking on “Show” at “FileList”

You can install the script using the below command

Install-Script -Name Get-ListsWithXAmountOfItems

Press Y if you want to install the script from the PSGallery where you can then just enter the following to retrieve SharePoint Online Lists with more than 5000 items.

Get-ListsWithXAmountOfItems.ps1 -web "https://spfire.sharepoint.com" -amount 5000 -logPath "C:/install" -MFA

Note that you can use the -MFA or -ADFS switch if needed.
You can find the log file at the location you specified. Note that I’ve set the amount to 10 in my command to retrieve the most lists.

Script breakdown

I will highlight a few section from the script to describe what it does.

The below tries to find the required modules and will import them if available and otherwise install them. You will still need to accept installing these modules using the PowerShell window!

#Microsoft Online SharePoint PowerShell
$SharePointOnlineModule = Get-Module -ListAvailable "Microsoft.Online.SharePoint.PowerShell"
if(!$SharePointOnlineModule){
Install-Module "Microsoft.Online.SharePoint.PowerShell"
}else{
Import-Module "Microsoft.Online.SharePoint.PowerShell"
}

#SharePoint PNP PowerShell Online
$SharePointPnPPowerShellOnline = Get-Module -ListAvailable "SharePointPnPPowerShellOnline"
if(!$SharePointPnPPowerShellOnline){
Install-Module "SharePointPnPPowerShellOnline"
}else{
Import-Module "SharePointPnPPowerShellOnline"
}

PnPOnline has different ways of connecting to SharePoint Online where you can use a switch parameter to let the script know which authentication method you want to use.

if($MFA){
$context = Connect-PnPOnline -Url $web -UseWebLogin
}elseif($ADFS){
$context = Connect-PnPOnline -Url $web -Credentials (Get-Credential) -UseAdfs
}else{
$context = Connect-PnPOnline -Url $web -Credentials (Get-Credential)
}

The results are being added to a .CSV file which will be created using the below lines of code.
I normally use the get-date with seconds, hours, months, days and years to make it as unique as possible.

$fileName = "Lists$((get-date).tostring('sshhMMddyyyy')).csv"
$file = New-Item -Path $logPath -Name $fileName -ItemType "file"
Add-Content -Path "$($logPath)/$($fileName)" -Value "Site;List;Items"

$rootWeb = Get-PnPWeb
$lists = Get-PnPList -web $rootWeb

write-host "Indexing $($rootWeb.title)" -foregroundcolor green
foreach($list in $lists){
$items = (Get-PnPListItem -List $list -Fields "GUID").FieldValues

if($items.count -ge $amount){
Add-Content -Path "$($logPath)/$($fileName)" -Value "$($rootWeb.title);$($list.title);$($items.count)"
}
}

$subWebs = Get-PnPSubWebs -Recurse

foreach($subWeb in $subWebs){
write-host "Indexing $($subWeb.title)" -foregroundcolor green
$lists = Get-PnPList -web $subWeb

foreach($list in $lists){
$items = (Get-PnPListItem -List $list -web $subWeb -Fields "GUID" ).FieldValues

if($items.count -ge $amount){
Add-Content -Path "$($logPath)/$($fileName)" -Value "$($subWeb.title);$($list.title);$($items.count)"
}
}
}