PowerShell SharePoint file count is not accurate

Aase Nomad 246 Reputation points
2022-04-11T15:54:33+00:00

I'm trying to write a powershell script that count all the files inside the main SharePoint folder but some reason my count is not accurate so I'll be really appreciated if I can get any help or suggestion on what might be the reason/

So right now I have "General" folder in my SharePoint Site and it has a ton of file and also nested subfolders. I'm try to count all the files including the files that is located inside the nested subfolders.

$SiteURL = "https://comapny.sharepoint.com/sites/Office365Sandbox/"
$searchfor = "sites/Office365Sandbox/Shared Documents/General"
$folderpath = "Shared Documents/General"

$CSVFile = "C:\FileCount\FolderStats.csv"

#Connect to SharePoint Online
Connect-PnPOnline $SiteURL -useWebLogin

#Get the list
$List = Get-PnPList -Identity $folderpath | Where-Object {$_.Title -eq 'Documents'}
#Get Folders from the Library - with progress bar

$global:counter = 0
$FolderItems = Get-PnPListItem -List $folderpath -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

$fieldvalues = $FolderItems.Fieldvalues

$result = @()
foreach ($field in $fieldvalues) {
    $obj = New-object psobject -property $field 
    $result += $obj.fileref
}

$final = $result | where-object { $_ -match $searchfor }

$item = New-Object psobject -Property @{
    FolderName       = Split-Path -Path $searchfor -Leaf 
    URL              = $searchfor 
    filesfoldercount = $final.count
}

$item 
$item  |  Export-Csv -Path $CSVFile -NoTypeInformation
Microsoft 365 and Office | SharePoint | Development
Microsoft 365 and Office | SharePoint | For business | Windows
Microsoft 365 and Office | SharePoint Server | Development
Windows for business | Windows Server | User experience | PowerShell
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. CaseyYang-MSFT 10,461 Reputation points
    2022-04-12T03:43:57.92+00:00

    Hi @Aase Nomad ,

    You could use following PowerShell commands to get files and folders count on each folder in a given document library.

    PowerShell commands:

    #Parameters  
    $SiteURL = "https://xxx.sharepoint.com/sites/xxx"  
    $ListName = "xxx"  
    $CSVFile = "C:\Temp\FolderStats.csv"  
       
    #Connect to SharePoint Online  
    Connect-PnPOnline $SiteURL  
        
    #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  
    

    My test result:

    192152-1.jpg

    And you could use following PowerShell to get the count of files and folders.

    PowerShell commands:

    #Config Parameters  
    $SiteURL= "https://xxx.sharepoint.com/sites/xxx"  
    $ListName = "xxx"  
       
    #Setup Credentials to connect  
    $Cred = Get-Credential  
         
    Try {  
        #Setup the context  
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)  
        $Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)  
           
        #Get the List  
        $List= $Ctx.web.lists.GetByTitle($ListName)  
        $Ctx.Load($List)  
        $Ctx.ExecuteQuery()  
        
        #Define Query to Filter and Get All Files from the list  
        $Query = "@  
        <View Scope='RecursiveAll'>   
                <Query>  
                   <Where>  
                         <Eq>  
                               <FieldRef Name='FSObjType' /><Value Type='Integer'>0</Value>  
                         </Eq>  
                   </Where>  
                </Query>  
        </View>"  
        $FilesQuery = New-Object Microsoft.SharePoint.Client.CamlQuery  
        $FilesQuery.ViewXml =$Query  
        $Files = $List.GetItems($FilesQuery)  
        $Ctx.Load($Files)  
        $Ctx.ExecuteQuery()  
       
        #Define Query to Filter and Get All Folders from the list  
        $Query = "@  
        <View Scope='RecursiveAll'>   
                <Query>  
                   <Where>  
                         <Eq>  
                               <FieldRef Name='FSObjType' /><Value Type='Integer'>1</Value>  
                         </Eq>  
                   </Where>  
                </Query>  
        </View>"  
        $FoldersQuery = New-Object Microsoft.SharePoint.Client.CamlQuery  
        $FoldersQuery.ViewXml =$Query  
        $Folders = $List.GetItems($FoldersQuery)  
        $Ctx.Load($Folders)  
        $Ctx.ExecuteQuery()  
       
        #Get List Item Count  
        Write-host -f Green "Total Number of Items in the List:"$List.ItemCount  
        Write-host -f Green "Total Number of Files in the List:"$Files.Count  
        Write-host -f Green "Total Number of Folders in the List:"$Folders.Count  
    }  
    Catch {  
        write-host -f Red "Error Getting List Item Count!" $_.Exception.Message  
    }  
    

    My test result:

    192147-2.jpg

    For Reference:
    SharePoint Online: Get Files and Sub-Folders Count on Each Folder in a Document Library using PowerShell
    SharePoint Online: Get List Item Count using PowerShell
    Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link.


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.