SharePoint Online: Get All Items from a List View using PowerShell
Requirement: Get All Items from a SharePoint Online List View in PowerShell.
PowerShell to Get List Items from a View in SharePoint Online:
In SharePoint Online, you can use PowerShell to get a list of items from a view. This can be useful if you want to automate tasks that are based on the data in a view. This article will show you how to use the CAML query and PowerShell script to get a list of items from a view.
#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 Variables
$SiteURL="https://crescent.sharepoint.com/sites/pmo"
$ListName="Projects"
$ViewName="Active Projects"
Try {
$Cred= Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = $Credentials
#Get the View
$List = $Ctx.web.Lists.GetByTitle($ListName)
$View = $List.Views.GetByTitle($ViewName)
$Ctx.Load($View)
$Ctx.ExecuteQuery()
#Get all list items from the view
$CAMLQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
$CAMLQuery.ViewXml = $View.ListViewXml
$ListItems = $List.GetItems($CAMLQuery)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
#Iterate throgh each item:
#ForEach($Item in $ListItems) { Write-host $Item[ColumnName]}
Write-host "Total List Items Found in the Given View: $($ListItems.Count)" -ForegroundColor Green
}
Catch {
write-host -f Red "Error Getting List Items from the List View!" $_.Exception.Message
}
This can be useful if you need to export data from a view or if you need to troubleshoot issues with the view. But there is a problem! If your view is paged and configured to display items in batches, the above script gets you only items from the 1st page! So, here is the fix:
#Config Variables
$SiteURL="https://crescentintranet.sharepoint.com/sites/PMO"
$ListName="Projects"
$ViewName="Active Projects"
Try {
$Cred= Get-Credential
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = $Credentials
#Get the View
$List = $Ctx.web.Lists.GetByTitle($ListName)
$View = $List.Views.GetByTitle($ViewName)
$Ctx.Load($View)
$Ctx.ExecuteQuery()
#Get the View Query
$View.Retrieve("ViewQuery")
$Ctx.ExecuteQuery()
#Get all list items from the view
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "<View Scope='RecursiveAll'><Query>$($View.ViewQuery)</Query></View>"
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
Write-host "Total List Items Found in the Given View: $($ListItems.Count)" -ForegroundColor Green
#Loop through each List item
ForEach($Item in $ListItems)
{
Write-host $Item.Id #$Item["Title"]
}
}
Catch {
write-host -f Red "Error Getting List Items from the List View!" $_.Exception.Message
}
PnP PowerShell to Get Items from a List View
Here is how to get all items from a SharePoint list view using PnP PowerShell:
#Parameters
$SiteURL="https://crescent.sharepoint.com/sites/PMO"
$ListName="Projects"
$ViewName= "Active Projects"
#Connect to the Site
Connect-PnPOnline -Url $SiteURL -Interactive
#Get the List
$List = Get-PnPList -Identity $ListName
#Get the List View from the list
$ListView = Get-PnPView -List $ListName -Identity $ViewName -Includes ListViewXml
#Get List Items from the view
Get-PnPListItem -List $ListName -Query $ListView.ListViewXml
I have to make a change for it to work for me as with above code it was giving me all items instead of just view items.
$Query.ViewXml = $View.ListViewXml instead of $Query.ViewXml = $View.ViewQuery
then I have looped thru the items.
Thanks for the direction.
Thanks, that was very helpful as I had the same issue with ViewQuery. However, I am encountering a problem whereby I am only returning the first 30 items for the view, as it is 30 items per page, and I require all of them (150+). Is there a way to bypass the rowlimit? What would I have to amend to the code?
True! The article is now updated with a script to handle this issue.