SharePoint Online: Get List Item Count using PowerShell

Requirement: Get list item count in SharePoint Online using PowerShell

How to Get List Items Count in SharePoint Online?
The "Site Contents" page in SharePoint Online gives you all lists and the number of items in each. E.g. https://YourTenant.sharepoint.com/_layouts/Viewlsts.aspx
get list item count sharepoint online powershell

SharePoint Online: PowerShell to Get List Item Count
Use this CSOM PowerShell script to find the number of items in a SharePoint Online list.
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
    
#Config Parameters
$SiteURL= "https://crescent.sharepoint.com/sites/marketing"
$ListName = "Documents"

#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()
 
    #Get List Item Count
    Write-host -f Green "Total Number of Items in the List:"$List.ItemCount
}
Catch {
    write-host -f Red "Error Getting List Item Count!" $_.Exception.Message
}
Let's wrap the above script into a re-usable function and get list item count for a List
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

Function Get-SPOListItemCount{
    <#
        .SYNOPSYS
           Returns the Item Count for the specified list or library
        .DESCRIPTION
           Returns the Item Count for the specified list or library
        .PARAMETER WebUrl
            The URL of the site that contains the list or library
        .PARAMETER ListName
            The title of the list or library to get count
        .EXAMPLE
           Get-SPOListItemCount -WebUrl "https://tenant.sharepoint.com/teams/marketing" -ListName "Shared Documents"
    #>
    [CmdletBinding()]
    [OutputType([int])]
    Param
    (
        [Parameter(Mandatory=$true, HelpMessage="The URL of the site that contains the list or library", Position=0)] [string]$WebUrl,
        [Parameter(Mandatory=$true, HelpMessage="The Title of the list or library", Position=1)] [string]$ListName
    )

    Begin{
        #Get Credentials to connect
        $Credential = Get-Credential
        #Setup context to connect
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($WebUrl)
        $Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Credential.UserName, $Credential.Password)
    }
    Process{
        try{
            #Get the Web
            $Web = $Ctx.Web
            #Get the List
            $List = $web.Lists.GetByTitle($ListName)
            $Ctx.Load($List)
            $Ctx.ExecuteQuery()
            #sharepoint online count items in list
            Return $List.ItemCount
        }
        catch{
            Write-Host -ForegroundColor Red $_.Exception.Message
            return 0
        }

    }
    End{
        $Ctx.Dispose()
    }
}
    
#Call the function to get item count
Get-SPOListItemCount -WebUrl "https://crescent.sharepoint.com/sites/marketing" -ListName "Documents"
This PowerShell script gets list item count in SharePoint Online

Get List Item Count in SharePoint Online using PnP PowerShell 
Get number of items from all lists in a SharePoint Online site.
#Variable
$SiteURL= "https://crescent.sharepoint.com/sites/marketing"

#Connect to PnP Online
Connect-PnPOnline $SiteURL -Credentials (Get-Credential)

#Get List Item count from all Lists from the Web
Get-PnPList | Select Title, ItemCount
You can also use this PowerShell script to count items in document library in SharePoint Online. Similarly, you can filter list items based on a specific condition and get its count:
#Set Variables
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$ListName = "Tasks"

#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential)

#Get list items - Filtered by given condition
$ListItems = Get-PnPListItem -List $ListName -Query "<View><Query><Where><Eq><FieldRef Name='Status'/><Value Type='Text'>Completed</Value></Eq></Where></Query></View>"

#Get List Items Count
$ListItems.Count

How to Export List Item Count for All lists in a Site Collection?
Here is how to export item count from all lists and libraries from a site collection using PnP PowerShell.
#Function to Get Lists and Libraries of a web
Function Get-PnPListInventory([Microsoft.SharePoint.Client.Web]$Web)
{
    Write-host -f Yellow "Getting List Item count from site:" $Web.URL
 
    #Get all lists and libraries of the Web
    $ListInventory= @()
    $ExcludedLists  = @("Reusable Content","Content and Structure Reports","Form Templates","Images","Pages","Workflow History","Workflow Tasks")
    $Lists= Get-PnPList -Web $Web | Where {$_.Hidden -eq $False -and $ExcludedLists -notcontains $_.Title}
    foreach ($List in $Lists)
    {
        $Data = new-object PSObject
        $Data | Add-member NoteProperty -Name "Site Name" -Value $Web.Title
        $Data | Add-member NoteProperty -Name "Site URL" -Value $Web.Url
        $Data | Add-member NoteProperty -Name "List Title" -Value $List.Title
        $Data | Add-member NoteProperty -Name "List URL" -Value $List.RootFolder.ServerRelativeUrl
        $Data | Add-member NoteProperty -Name "List Item Count" -Value $List.ItemCount
        $ListInventory += $Data
    }
 
    #Get All Sub sites from the web
    $SubWebs = Get-PnPSubWebs -Web $Web
    Foreach ($Web in $SubWebs)
    {
        $ListInventory+= Get-PnPListInventory -Web $Web
    }
    Return $ListInventory
}
 
#Config Variables
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$CSVFile = "C:\temp\ListItemCount.csv"
 
#Get Credentials to connect
$Cred = Get-Credential
 
Try {
    #Connect to PNP Online
    Connect-PnPOnline -Url $SiteURL -UseWebLogin -Credentials $Cred
 
    #Get the Root Web
    $Web = Get-PnPWeb
 
    #Call the function and export results to CSV file
    Get-PnPListInventory -Web $Web | Export-CSV $CSVFile -NoTypeInformation
    Write-host -f Green "List Inventory Exported to Excel Successfully!"
}
Catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}
Result:
get item count in sharepoint online using powershell
What if you want to count files in a document library?

SharePoint Online: Count Items in Document Library using PowerShell
Because the ItemCount property of a SharePoint Online list gives you the count of items including files and folders together, How do we count files and folders in a library? Well, here is the PnP PowerShell to get the count of files and folders:
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
    
#Config Parameters
$SiteURL= "https://abraaj.sharepoint.com/sites/marketing"
$ListName = "Documents"

#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
}
You can also loop through list items and check $ListItem.FileSystemObjectType -eq "File" or "Folder" to get Files and Folders count.
SharePoint Online: Get List Item Count using PowerShell SharePoint Online: Get List Item Count using PowerShell Reviewed by Salaudeen Rajack on February 14, 2019 Rating: 5

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.