🎉 Our Office 365 Reporting Tool is now available in Azure Marketplace 🚀
This website uses cookies to improve your experience. We'll assume you're ok with this. Know more.

How to Get List Item Count in SharePoint Online

Are slow load times and unresponsive performance holding you back from getting the most out of your SPO sites? One common factor behind these issues is an excessive number of items in SharePoint lists. By optimizing these overloaded lists, you can improve site responsiveness and avoid threshold errors. Therefore, this guide will show you how to get the total item count in SharePoint lists within your Microsoft 365 environment.

Native Solution

Microsoft 365 Permission Required

High
Least Privilege

Site Owner

Highest Privilege

Global Admin

Option 1 Using SharePoint Online Site

  • Go to the respective SharePoint Online site using a web browser.
  • Navigate to the Settings (⚙️)»Site contents.
  • Here you will see all the unhidden lists available on the SharePoint site along with their item counts displayed in the Items column.
Using SharePoint Online Site

Option 2 Using Windows PowerShell

  • Connect to the SharePoint Online site using PnP PowerShell (requires PowerShell 7 or above) with the below cmdlet.
  • Windows PowerShell Windows PowerShell 
     Connect-PnPOnline –Url <SiteURL> -TenantName <TenantName> -ClientId <ClientId> -CertificateThumbprint <CertThumbprint>
  • Then, run the following cmdlet to get the item count from both hidden and unhidden lists for the respective SharePoint site.
  • Windows PowerShell Windows PowerShell 
     Get-PnPList | Where-Object {$_.BaseType -ne "DocumentLibrary"} | Select Title, ItemCount
Using Windows PowerShell

Option 3 Using Windows PowerShell Script

  • While the above cmdlet displays the item count in a SharePoint list from the specified site, retrieving the lists and their item counts from all sites requires multiple loops and operations.
  • Therefore, we've prepared a PowerShell script that helps you identify lists, retrieve their item counts, and filter out inactive lists on each SharePoint site.
  • Download and run the following script in Administrator PowerShell (version 7 or higher required) as outlined below.
  • Windows PowerShell Windows PowerShell 
     ./GetSPOListItemsCount.ps1 -TenantName <TenantName> -ClientId <ClientId> -CertificateThumbprint <CertThumbprint>
GetSPOListItemsCount.ps1
Using Windows PowerShell Script
AdminDroid Solution
More than 150 reports are under the free edition.

AdminDroid Permission Required

Delegated

Any user with report access assigned by the Super Admin.

StepsUsing AdminDroid

ad
  • Login to the AdminDroid Office 365 reporter.
  • Navigate to the All Lists report under Reports»SharePoint»List.
Using AdminDroid

This report consolidates all lists available in your SharePoint Online with its item count, created date, site name, and more.

Note: You can use the Site filter to view all lists, and their item counts available in a specific SharePoint online site.

spo-list-items-count-chart
  • Customize the graphical representation chart to visually depict the distribution of SharePoint list item across various sites. This helps identify large lists that might need migration to improve site performance.
geo-map
birds-eye-chart
donut
exported-excel
pie-chart
alert
Explore a full range of reporting options

Transform how you manage large lists in SharePoint Online!

Use AdminDroid’s SPO list reports to identify and remove space-consuming or unwanted empty lists. Additionally, get detailed insights to effectively manage SharePoint list permissions and security settings effectively.

Witness the report in action using the

Live Demo

SharePoint OnlineTrack SharePoint Online List Items to Prevent Storage Issues and Optimize Management!

Showing 1 of 5
How to find unused SharePoint list based on last modified time? How to change the number of items displayed in a SharePoint Online list view? How to get number of items in a SharePoint list using Power Automate? What are the limitations of SharePoint Online list? What is the purpose of getting items count in SharePoint Online lists?

How to find unused SharePoint list based on last modified time?

SharePoint Online lists are essential for asset management and collaboration in an organization. However, as usage grows, it's common for some lists to become inactive. These SharePoint Online inactive lists can impact performance and occupy valuable storage space, making it crucial to address them for a more efficient collaborative environment.

Identify inactive/unused lists in SharePoint Online

  • Although the UI offers the ability to get all lists in a SharePoint site, it does not allow filtering to see only inactive lists based on the last activity time. However, you can achieve this by executing our PowerShell script, 'GetSPOListItemsCount.ps1', with the ‘InactiveDays’ parameter.
  • ./GetSPOListItemsCount.ps1 -TenantName <TenantName> -ClientId <ClientId> -CertificateThumbprint <CertThumbprint> -InactiveDays <Days>
  • For example, by executing the above cmdlet with the 'Inactive days' parameter set to 100, you can retrieve all SharePoint lists that have been inactive for over 100 days, along with their item counts.
  • Similarly, you can include the 'SiteUrl' parameter along with the respective site URL in the cmdlet to list only the unused lists within a specific site.
  • ./GetSPOListItemsCount.ps1 -SiteUrl <URL> -TenantName <TenantName> -ClientId <ClientId> -CertificateThumbprint <CertThumbprint> -InactiveDays <Days>

Tired of endless scrolling through site after site to find unused SharePoint Online lists?

  • With the SharePoint Online inactive lists report in AdminDroid, you can view all inactive lists across your sites in one dedicated place.
  • This report provides detailed information on each list's last activity date and the number of inactive days, and more.
inactive-list-in-spo-with-scheduling-admindroid

Handy tip: Use the Schedule (⏰) option to receive automated insights on inactive SharePoint lists, allowing you to take appropriate actions.

How to change the number of items displayed in a SharePoint Online list view?

Managing large lists in SharePoint Online can impact performance, especially when viewing thousands of items at once. To maintain site’s efficiency, it's important to limit the number of items displayed in a view for SharePoint lists. This helps ensure faster load times and more consistent performance across the lists.

Follow these steps to modify the number of items displayed in a view in SharePoint Online:

Change the number of items displayed in a list via the SPO site

  • Open the desired SharePoint list and select Edit current view from the 'view' drop-down.
  • Expand the Limit Item section on the 'Edit View' page to view the number of items displayed in that specific view, which defaults to 30.
  • Change the value to display the desired number of items in that view and click OK.
number-of-items-displayed-in-list-view

Limit results per page in SharePoint Online using PowerShell

  • Connect to the desired SharePoint Online site using the 'Connect-PnPOnline' cmdlet. Then, run the following cmdlet, replacing <ListTitle> with the list's name, to check the item count in the views.
    Get-PnPView -List <ListTitle> | Select Title, RowLimit, DefaultView | Format-Table –Autosize
  • To change the number of items displayed in the specified view, run the cmdlet below, replacing <ViewName> with the name of your view and <ItemCount> with the number of items to display.
    Set-PnPView –List <ListTitle> -Identity <ViewName> -Values @{Paged=$true;RowLimit=[UInt32]"<ItemCount>"}
set-items-displayed-in-list-view-with-cmdlet

How to get number of items in a SharePoint list using Power Automate?

SharePoint Online lists often manage key data like project tasks or support tickets. For example, a project management team needs to quickly assess the number of open tasks to prioritize workloads and meet deadlines. Automating the tracking of list items with Power Automate helps ensure timely and accurate updates, eliminating the need for manual monitoring.

Power Automate flow to count items in a SharePoint Online list

To automate the process of counting the number of rows in a SharePoint list using Power Automate, follow these steps:

  • Sign in to the Power Automate and create a new Scheduled cloud flow. Set a name for the flow and specify the desired frequency for how often you want it to run.
  • Add the Send an HTTP request to SharePoint action. Configure it with your SharePoint site URL and use the following Uri endpoint to get the item count from your list: 
    /_api/web/lists/getbytitle('<ListTitle>')/ItemCount
  • Add a Compose action to extract the item count from the HTTP request response using the provided expression. 
    body('Send_an_HTTP_request_to_SharePoint')?['d/ItemCount']
  • Add the Initialize variable action to create a variable. Set its type to Integer and give it an appropriate name.
  • Add the Set variable action to assign the item count obtained from the "Compose" action to your initialized variable.
  • Add the Send an email (V2) action to notify yourself or others. Include the variable output in the email body or subject. Finally, save and test the flow to ensure it operates correctly.

For a clear visual representation of the flow, please refer to the GIF provided below.

list-count-power-automate-flow

What are the limitations of SharePoint Online list?

Understanding the limitations of SharePoint Online lists is essential for designing lists that maintain good performance and usability on your site.

Here are some key limitations of SPO lists that you should be aware of:

  • List view threshold The list view threshold limit is 5000 items. This means that when a list exceeds this number, you can’t perform certain operations like filtering and sorting.
  • Item limit A single list can store up to 30 million items. However, you can only view up to 5000 items at a time. Viewing more than this can cause a list view threshold error.
  • File size limit The maximum size for the file attached to the list item is 250 MB. There is no restriction on the number of attachments per list item, but having many attachments can impact performance.
  • Custom permission limit When a SharePoint Online list exceeds 100,000 items, you cannot modify permissions at the list level. However, you can still set unique permissions for individual list items. For better performance, it's recommended to manage unique permissions for up to 5,000 items, even though the maximum limit is 50,000.

What is the purpose of getting items count in SharePoint Online lists?

In certain situations, finding the count of items in SharePoint Online lists is crucial for M365 admins! Here are some of the most common purposes:

  • Content organization and management Tracking the SPO lists item count helps manage data growth, allowing you to organize and maintain your content more efficiently.
  • Workflow management Many SharePoint lists are used in workflows. Knowing the number of list items allows admins to track the number of pending tasks, approvals, or workflow stages, aiding in workload management.
  • Data validation and integrity During data imports, item count can help identify and cross-check any additional data manipulations.
  • Storage optimization Archiving old or unused important lists with a large number of items helps free up space for active lists, minimize clutter, and ensure quicker access to frequently used data.

AdminDroid SharePoint Online ReporterEffortlessly manage lists in SharePoint Online for enhanced efficiency with just a few clicks!

The AdminDroid SharePoint Online reporting tool provides you detailed report on all lists in your SharePoint Online environment. The following capabilities give you full control over your SharePoint Online lists and reduce the time spent managing them.

A Quick Summary

Protect Important Data with List Versioning

Review the lists with no versioning report and enable versioning for critical lists to prevent the risk of permanent data loss or to retain existing content after modifications in SharePoint Online.

Get a Quick Glimpse of SPO Lists

Explore the SharePoint Online lists dashboard to instantly gain valuable insights into all lists across the organization, based on their configurations and activities.

Easily Delete Inactive Hidden SharePoint Lists

Utilize the SharePoint Online hidden lists report to identify hidden lists that are no longer active and remove the unwanted ones to optimize storage in your SPO environment.

Ensure Data Security with IRM-Enabled Lists Tracking

Monitor IRM enabled lists report to identify any critical lists that lack protection and take corrective action to prevent unauthorized access or data leaks.

Export SPO Lists Details in Various File Formats

Get instant access to your list item count details by exporting report in various formats, such as CSV, XLSX or RAW, with direct values for easy processing in other apps.

Secure Sensitive Lists with Unique Permission Tracking

Rectify permission inconsistencies with the help of the custom permission lists report, ensuring that sensitive lists are secured with granular access control.

In addition to getting the list item count in SharePoint Online, AdminDroid’s SPO management tool provides robust features that allow you to effectively track every aspect of SharePoint Online. This includes tracking document libraries, site collections, inactive sites, site groups, and a summary of daily activity and site usage. This enables you to streamline SPO environment management.

Kickstart Your Journey with AdminDroid

Your Microsoft 365 Companion with Enormous Reporting Capabilities!

Download

Common Errors and Resolution Steps While Retrieving List Item Count in SharePoint Online

The following are the possible errors and troubleshooting hints while dealing with SharePoint Online list item count.

Error: WARNING: Setting property 'RowLimit' to '50' failed with exception 'Object of type 'System.String' cannot be converted to type 'System.UInt32' Value will be ignored.

This error occurs because the RowLimit property expects a value of type UInt32 (an unsigned 32-bit integer), but a string is passed instead.

Troubleshooting hint :To resolve this error, ensure that the 'RowLimit' value is properly cast to ‘UInt32’. Here's how you can modify the cmdlet to correctly cast the value: 

Set-PnPView –List <ListTitle> -Identity <ViewName> -Values @{Paged=$true;RowLimit=[UInt32]"35"}

Error: Connect-PnPOnline: A parameter cannot be found that matches parameter name 'Interactive'.

This error occurs when you try to execute the script or ‘Connect-PnPOnline’ cmdlet in a version of PowerShell earlier than 7.

Troubleshooting hint :Install PowerShell 7 or a later version, and then execute the script or cmdlet.

Error: Try refreshing the page. Something went wrong and we couldn’t get the page to display. The number of items in the list exceeds the list view threshold, which is 5000 items. You can return to the list to select a different view or visit the following article for guidance on how to work around the issue: manage list and libraries with many items.

This error occurs when the number of items in the list exceeds SharePoint's limit of 5,000 items, making it too large to display all at once.

Troubleshooting hint :To resolve these types of list view threshold errors, follow effective strategies to manage large lists and libraries in SharePoint Online.

Error: Get-PnPList: Attempted to perform an unauthorized operation.

This error occurs when you try to get lists from a site that you don’t have access to. Even if you’re a global admin or SPO admin, accessing specific sites requires site level permissions.

Troubleshooting hint :Assign yourself as the site owner or admin of the respective site to retrieve the lists from it.