SharePoint Online: PowerShell to Read List Items
Requirement: PowerShell to read list items in SharePoint Online.
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.
#List Name Variable
$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 -Interactive
#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"]
}
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
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.
This will not work if MFA is enabled and it will be for everyone in a few weeks time.
You can use App Password to handle MFA enabled accounts. With PnP PowerShell, use: -Interactive switch.
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.
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.