SharePoint Online: Get Files and Sub-Folders Count on Each Folder in a Document Library using PowerShell
Requirement: Get the count of files and sub-folders at each folder in a SharePoint Online document library
How to Get the Number of Files and Sub-Folders at Each Folder of a Document Library?
When a document library has multiple folders, sub-folder, and files, you can get the number of files and sub-folders at each level by adding “Folder Child Count” and “Item Child Count” fields to the view. Follow these steps to show the number of files in a folder:
- Navigate to the document library >> Click on View drop-down menu >> “Edit Current View”
- Select “Folder Child Count” and “Item Child Count” columns and Click on OK.
Now, the view shows the number of sub-folders and the number of items at each folder’s top level.
Get Files, Sub-Folders Count at Each Folder in a Document Library using PowerShell
Here is the PowerShell to find the number of files and sub-folders at each folder in a SharePoint Online document library using PnP PowerShell:
#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$ListName = "Project Documents"
#Function to get number of Sub-folder and Files count recursively
Function Get-SPOFolderStats
{
[cmdletbinding()]
param
(
[Parameter(Mandatory=$true,ValueFromPipeline=$true)][Microsoft.SharePoint.Client.Folder]$Folder
)
#Get Sub-folders of the folder
Get-PnPProperty -ClientObject $Folder -Property ServerRelativeUrl, Folders | Out-Null
#Get the SiteRelativeUrl
$Web = Get-PnPWeb -Includes ServerRelativeUrl
$SiteRelativeUrl = $Folder.ServerRelativeUrl -replace "$($web.ServerRelativeUrl)", [string]::Empty
[PSCustomObject] @{
Folder = $Folder.Name
Path = $Folder.ServerRelativeUrl
ItemCount = Get-PnPFolderItem -FolderSiteRelativeUrl $SiteRelativeUrl -ItemType File | Measure-Object | Select -ExpandProperty Count
SubFolderCount = Get-PnPFolderItem -FolderSiteRelativeUrl $SiteRelativeUrl -ItemType Folder | Measure-Object | Select -ExpandProperty Count
}
#Process Sub-folders
ForEach($SubFolder in $Folder.Folders)
{
Get-SPOFolderStats -Folder $SubFolder
}
}
#Connect to SharePoint Online
Connect-PnPOnline $SiteURL -Credentials (Get-Credential)
#Call the Function to Get the Library Statistics - Number of Files and Folders at each level
$FolderStats = Get-PnPList -Identity $ListName -Includes RootFolder | Select -ExpandProperty RootFolder | Get-SPOFolderStats | Sort Path
$FolderStats
#Export to CSV
$FolderStats | Export-Csv -Path "C:\Temp\DocLibStats.csv" -NoTypeInformation
This script outputs the data on the screen and generates a CSV file!
An Alternate approach to count items in Larger Document Libraries
While the above script works perfectly fine for smaller document libraries, it gets a “The attempted operation is prohibited because it exceeds the list view threshold.” error as the Get-PnPFolderItem can’t handle libraries with more than 5000 items as of today! So, here is the alternative approach to get files and folders count on each folder in a given document library.
#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$ListName = "Branding"
$CSVFile = "C:\Temp\FolderStats.csv"
#Connect to SharePoint Online
Connect-PnPOnline $SiteURL -Interactive
#Get the list
$List = Get-PnPList -Identity $ListName
#Get Folders from the Library - with progress bar
$global:counter = 0
$FolderItems = Get-PnPListItem -List $ListName -PageSize 500 -Fields FileLeafRef -ScriptBlock { Param($items) $global:counter += $items.Count; Write-Progress -PercentComplete `
($global:Counter / ($List.ItemCount) * 100) -Activity "Getting Items from List:" -Status "Processing Items $global:Counter to $($List.ItemCount)";} | Where {$_.FileSystemObjectType -eq "Folder"}
Write-Progress -Activity "Completed Retrieving Folders from List $ListName" -Completed
$FolderStats = @()
#Get Files and Subfolders count on each folder in the library
ForEach($FolderItem in $FolderItems)
{
#Get Files and Folders of the Folder
Get-PnPProperty -ClientObject $FolderItem.Folder -Property Files, Folders | Out-Null
#Collect data
$Data = [PSCustomObject][ordered]@{
FolderName = $FolderItem.FieldValues.FileLeafRef
URL = $FolderItem.FieldValues.FileRef
FilesCount = $FolderItem.Folder.Files.Count
SubFolderCount = $FolderItem.Folder.Folders.Count
}
$Data
$FolderStats+= $Data
}
#Export the data to CSV
$FolderStats | Export-Csv -Path $CSVFile -NoTypeInformation
Hi,
Any way to get total number of files in a site collection reading from a list of sites on a csv?
You can obtain it from usage reports! Here is the direct URL: https://admin.microsoft.com/Adminportal/Home#/reportsUsage/SharePointSiteUsageV1
Getting an error:
Get-SPOFolderStats : The input object cannot be bound to any parameters for the command either because the command
does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline
input.
At C:\temp\test3.ps1:39 char:107
+ … lder | Select -ExpandProperty RootFolder | Get-SPOFolderStats | Sort …
+ ~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (Microsoft.SharePoint.Client.Folder:PSObject) [Get-SPOFolderStats], Par
ameterBindingException
+ FullyQualifiedErrorId : InputObjectNotBound,Get-SPOFolderStats
Get-PnPProperty : Cannot bind argument to parameter ‘ClientObject’ because it is null.
At C:\temp\test3.ps1:15 char:35
+ Get-PnPProperty -ClientObject $Folder -Property ServerRelativeUrl …
+ ~~~~~~~
+ CategoryInfo : InvalidData: (:) [Get-PnPProperty], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,PnP.PowerShell.Commands.Base.EnsureProper
ty
Folder Path ItemCount SubFolderCount
—— —- ——— ————–
0 23
Check the parameters are valid! E.g. the $ListName. Also make sure you have the latest PnP.PowerShell Module installed. How to Install the PnP PowerShell Module for SharePoint Online?
Thanks! That got me a little further. Now I’m getting a list view threshold error (see below), which is why I need the numbers so I can find where I’m over the threshold.
Get-PnPProperty : The attempted operation is prohibited because it exceeds the list view threshold.
At C:\temp\test4.ps1:15 char:5
+ Get-PnPProperty -ClientObject $Folder -Property ServerRelativeUrl …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (:) [Get-PnPProperty], ServerException
+ FullyQualifiedErrorId : EXCEPTION,PnP.PowerShell.Commands.Base.EnsureProperty
Get-PnPFolderItem : The attempted operation is prohibited because it exceeds the list view threshold.
At C:\temp\test4.ps1:24 char:21
+ … ItemCount = Get-PnPFolderItem -FolderSiteRelativeUrl $SiteRelativeUrl …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (:) [Get-PnPFolderItem], ServerException
+ FullyQualifiedErrorId : EXCEPTION,PnP.PowerShell.Commands.Files.GetFolderItem
Get-PnPFolderItem : The attempted operation is prohibited because it exceeds the list view threshold.
At C:\temp\test4.ps1:25 char:26
+ … lderCount = Get-PnPFolderItem -FolderSiteRelativeUrl $SiteRelativeUrl …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : WriteError: (:) [Get-PnPFolderItem], ServerException
+ FullyQualifiedErrorId : EXCEPTION,PnP.PowerShell.Commands.Files.GetFolderItem
The collection has not been initialized. It has not been requested or the request has not been executed. It may need
to be explicitly requested.
At C:\temp\test4.ps1:29 char:13
+ ForEach($SubFolder in $Folder.Folders)
+ ~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], CollectionNotInitializedException
+ FullyQualifiedErrorId : Microsoft.SharePoint.Client.CollectionNotInitializedException
Yes! This is because the Get-PnPFolderItem cmdlet doesn’t have the capacity to handle libraries with more than 5000 items as of today! So, use the another script under “Alternative approach”
Thank you! I’m almost there. The only count I do not see is the count from the root folder. Would you be able to help me out with that?
My CSV is blank with no data.
I cannot get this script to report sub-folders Past the top level. Something might be broken with the latest PnP module. I get the following output.
Folder Path ItemCount SubFolderCount
—— —- ——— ————–
Test Library /Test Library 0 2
Forms /Test Library/Forms 0 0
Document /Test Library/Forms/Document 0 0
Level 1 /Test Library/Level 1 0 0
Level 2-1 /Test Library/Level 1/Level 2-1 0 0
Level 3-1 /Test Library/Level 1/Level 2-1/Level 3-1 0 0
Level 2-2 /Test Library/Level 1/Level 2-2 0 0