SharePoint Online: PowerShell to Read List Items

Requirement: PowerShell to read list items in SharePoint Online.

Read List Items in SharePoint Online using PowerShell

SharePoint Online: PowerShell to Read List Items

In this blog post, we will be going over how to use PowerShell to read list items in SharePoint Online. We will cover the basics of using PowerShell to connect to SharePoint Online and then walk through some script examples on reading data from a SharePoint Online list. By the end of this post, you should understand how to get started using PowerShell with your SharePoint Online instances. Happy scripting!

You can read the SharePoint Online list with PowerShell in these three simple steps:

Step 1: Connect to SharePoint Online Site

To start with, we need the connection context to the SharePoint Online site.  

#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"
   
#Variables for Processing
$SiteUrl = "https://crescent.sharepoint.com/"
  
#Get Credentials to connect
$Cred = Get-Credential

#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)

Step 2: Get List Items from the SharePoint Online List

Once the connection is established, we can read or update any object from the SharePoint Online site. Here is how to get data from the SharePoint Online list using PowerShell.

#SharePoint List Name
$ListName = "Projects"
 
#Get the list
$List = $Ctx.Web.Lists.GetByTitle($ListName)
 
#Read All Items from the list
$ListItems = $List.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
Write-host "Total Number of Items Found in the List:"$ListItems.Count

Step 3: Loop through the List Items Collection to Read List Items

Once we get the list items collection, we can read the list items by iterating through each item. 

#Iterate through List Items
ForEach($Item in $ListItems)
{
    #sharepoint online powershell read list items 
    Write-Host ("List Item ID:{0} - Title:{1}" -f $Item["ID"], $Item["Title"])
}

PowerShell to Read SharePoint Online List

Here is the complete PowerShell script to read SharePoint Online List Items:

#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"
   
#Variables for Processing
$SiteUrl = "https://crescent.sharepoint.com"
$ListName = "Projects"
  
#Get Credentials to connect
$Cred = Get-Credential

#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)
 
#sharepoint online powershell read list items
$ListItems = $List.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
Write-host "Total Number of Items Found in the List:"$ListItems.Count

#Iterate through List Items
ForEach($Item in $ListItems)
{
    #sharepoint online powershell read list items 
    Write-Host ("List Item ID:{0} - Title:{1}" -f $Item["ID"], $Item["Title"])
}

Suppose you have a large list with >5000 items. In that case, You may have to batch process list items to avoid throttling issues: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.” PowerShell to Get All List Items from Large Lists ( >5000 Items) in SharePoint Online

Read List Items using PnP PowerShell

Getting data from the SharePoint Online list through PnP PowerShell is much simpler! Here is the PnP PowerShell to get list items from a SharePoint Online list:

#Config Variables
$SiteURL="https://crescent.sharepoint.com/sites/marketing"
$ListName= "Projects"
 
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential)
 
#PnP powershell to get list items 
$ListItems = Get-PnPListItem -List $ListName -PageSize 2000
 
#Iterate through each Item in the list
ForEach($ListItem in $ListItems)
{  
    Write-Host "Title:" $ListItem["Title"] 
}

You can execute this PowerShell script from Windows PowerShell ISE or Visual Studio Code.

Get-PnPListItem powershell

You can query a list by looping through list items using ForEach-Object and export to a CSV file using Export-CSV cmdlet: How to Export SharePoint List Items to CSV using PowerShell?

Summary

In summary, following the steps outlined in this article, You can easily read list items in SharePoint Online and use the information to perform various tasks, such as creating reports. If you would like to export SharePoint Online List items to Excel, use: Export SharePoint Online List Items to CSV using PowerShell

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

5 thoughts on “SharePoint Online: PowerShell to Read List Items

  • I clicked the reply button but my original comment isn’t showing so this may not make a lot of sense.

    I tried to install the PNP stuff and was presented with a very long list of things “they” wanted access to. At the end of the list, “they” even went to the trouble of telling me that I should read their “Terms and conditions”. However, they also added, in bold, that the “Terms and conditions” were not available. At that point I clicked “Cancel” and will not install the PNP stuff. I don’t know who “they” are as it is a third party and not Microsoft. I am surprised that anyone had installed that.

    Unfortunately this is a PowerShell script that runs as a scheduled task and is not interactive.

    Reply
  • This will not work if MFA is enabled and it will be for everyone in a few weeks time.

    Reply
    • You can use App Password to handle MFA enabled accounts. With PnP PowerShell, use: -Interactive switch.

      Reply
  • Is there a way to get other field values like Name and Url? I can only ever get ID, Title, and Guid, which aren’t useful.

    Reply
    • Do you mean, other custom columns you had created in the list or library? Sure! Use $ListItem[“Your-Custom-Column-Internal-Name”]

      For getting values from fields such as Hyperlink, Metadata, etc. Do a search on this site, I’m sure you’ll find a lot.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *