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
I ran this on the upmost folder in a library and it’s returning a Sharepoint “reserved” folder called “Forms” Is there any way to exclude this and other auto-created folders made by Sharepoint?
Hi. This is a very useful script! I’m using the alternative one for large libraries. Is it possible to modify this script to target a specific folder in the library rather than the entire library? If so what would need adding/changing please?
Is there a way to have a total count on all files from subfolders on the parent folder on SharePoint site
is it possible to run a version of this script to get the file and folder counts for all or multiple libraries within a Site?
I would suggest you completely eliminate the second half of the script where you create $folderstats and run get-pnpproperty. This information was collected in the first half and can just be selected using something like the command below to decrease run time:
$folderItems | Select @{Name=”FolderName”;Expression={$_.FieldValues.FileLeafRef}},@{Name=”URL”;Expression={$_.FieldValues.FileRef}},@{Name=”FilesCount”;Expression={$_.Folder.Files.Count}},@{Name=”SubFolderCount”;Expression={$_.Folder.Folders.Count}} | Export-Csv -Path $CSVFile -NoTypeInformation
Hi, first of all thanks to Salaudeen Rajack for this very useful script !
Second of all, I’m not a PowerShell expert, so sorry by advance if my remarks/question don’t make sense for some of you 😉
I’m trying the alternative script and, indeed it’s a bit long. I’m wondering if the displaying of data in the PowerShell console window part is not a little bit superfluous because when you work with this number of folders/files you finally handle them into an Excel file.
@David Kennedy, I understood that you’re trying with your suggestion to decrease run time 🙂 Not being a PowerShell expert, I don’t understand how you retrieve all subfolders/files without “foreach” in your suggestion ?
Then, my question is about to make exactly what the original alternative script does but without displaying results at screen, just export them into a CSV file : what part of the script can I eliminate ?
Second question, I want to run this script for multiple libraries of a site so I add few things, what do you think of it ?
#Parameters
#Instead of $ListName
$Libraries = Import-Csv .\Libraries.csv
foreach ($Library in $Libraries)
{
#Rename CSV
#Instead of the $CSVfile in the parameters part, I put it in the “foreach” to rename it function of the library processing
$CSVFile = “.\${Library}_FolderStats.csv”
#Get the list
#$Library instead of the $ListName
$List = Get-PnPList -Identity $Library
#Get Folders from the Library – with progress bar
#$Library instead of the $ListName
$global:counter = 0
$FolderItems = Get-PnPListItem -List $Library -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 $LibraryName” -Completed
…
}
Thank you for your answers
I used the alternative script for larger libraries and it worked perfectly. Thank you!
Wondering if you could help me take this a little further. Bear with me as I try to explain my process. To give some context, my end goal here is to find folders nested very deep for audit and clean-up purposes.
First, is it possible to run a version of this script to get the file and folder counts for all or multiple libraries within a Site? Running this for one Site library at a time takes time!
For the folder depth part of this, I have a workaround. For everyone’s reference, I take the script output, open it in excel, and add a column named Folder Depth. I add the len formula to the cell, adding up all the Forward slashes/ in the URL path, and subtracting 2 of the forward slashes/.
The output tells me how deep that folder is nested.
Ex: /sites/Project Folder/Folder 1/Folder 2/Folder 3/Folder 4/Folder 5/Last folder 0 2 6 – Last folder is nested 6 levels deep.
With this workaround, I’ve been able to find folders nested almost 25 levels deep.
Do you know if there is any way to incorporate something in the script that will give this type of output?
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
Hi! I was wanting the same as the poster you replied to, but I was wanting it for use within another script so I can, basically, do a “working on X of Y” (where Y is the total count) type of deal. Specifically, I’m using your script from here (https://www.sharepointdiary.com/2016/02/sharepoint-online-delete-version-history-using-powershell.html) and have successfully modified the “Trim Excess Versions for All Libraries in a Site” to give me a running count of which batch set it is working on, but it’d still be nice for it to show me the total count so I can have a fair idea of how far along it is. One of our document libraries has over 400,000 files….
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