SharePoint Online: Get All List Items using PowerShell Script
Requirement: SharePoint Online PowerShell to Get List Items.
SharePoint Online: PowerShell to Get All List Items
PowerShell is a powerful scripting language that can be used to automate tasks. In this blog post, we will show you how to use PowerShell to query items in a SharePoint Online list. We will be covering the basics of how to get started and providing some PowerShell examples on how to query list items in SharePoint Online. Let’s get started!
How to get items from a SharePoint Online list using PowerShell?
Are you looking for a quick and easy way to get a list item in SharePoint Online? Well, here is an example PowerShell CSOM script to get you all items in the given list or library, including items from all sub-folders, recursively.
#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"
$UserName="admin@crescent.com"
$Password ="Password goes here"
#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))
#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Context.Credentials = $credentials
#Get the List
$List = $Context.web.Lists.GetByTitle($ListName)
#sharepoint online get list items powershell
$ListItems = $List.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
$Context.Load($ListItems)
$Context.ExecuteQuery()
write-host "Total Number of List Items found:"$ListItems.Count
#Loop through each item
$ListItems | ForEach-Object {
#Get the Title field value
write-host $_["Title"]
}
SharePoint Online: PowerShell to Get List Item by ID
If you want to get a single list item by its ID, use the following:
#Get the List Item
$List = $Context.web.Lists.GetByTitle($ListName)
$ListItem = $List.GetItemById(15)
$Context.Load($ListItem)
$Context.ExecuteQuery()
Once you get the Item, You can get list item properties as:
#sharepoint online powershell get list item properties
write-host $ListItem["Title"]
PowerShell to Filter and Get List Items with CAML:
How about filtering and querying List Items using CAML Query? Here is how to get items from a SharePoint Online list using PowerShell.
SharePoint Online: PowerShell to Get a List Item by Title
Here is how to get list items using PowerShell in SharePoint Online filtered by CAML queries:
#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="Project Tasks"
$ItemTitle = "Project Kickoff"
#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 web and List
$Web=$Ctx.Web
$List=$web.Lists.GetByTitle($ListName)
#Get a List item by its Title
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml = "@
<View>
<Query>
<Where>
<Eq>
<FieldRef Name='Title'/><Value Type='Text'>$ItemTitle</Value>
</Eq>
</Where>
</Query>
</View>"
#Get All List Items matching the query
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
Write-host "Total List Items:" $ListItems.count
#Loop through each file in the library
Foreach($Item in $ListItems)
{
Write-host -f Green $Item["Title"]
}
}
Catch {
write-host -f Red "Error:" $_.Exception.Message
}
This PowerShell gets a list item by title in SharePoint Online. Similarly, you can filter any other data type like dropdowns, etc.
#Filter and Get the List Items using CAML
$List = $Context.web.Lists.GetByTitle($ListName)
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery;
$Query.ViewXml = "<View><Query><Where><Eq><FieldRef Name='Priority' /><Value Type='Choice'>(3) High</Value></Eq></Where></Query></View>"
#sharepoint online powershell query list items
$ListItems = $list.GetItems($Query);
$Context.Load($ListItems)
$Context.ExecuteQuery()
#sharepoint online powershell loop through list items
$ListItems | ForEach-Object {
#Get the Title field value
write-host $_["Title"]
}
PowerShell to Filter Folders and Get All List Items
Let’s filter all Folder objects from the “ListItems” collection and get only files.
#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"
$LibraryName="Project Docs"
#Setup Credentials to connect
$Cred = Get-Credential
$Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)
Try {
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = $Cred
#Get the web and Library
$Web=$Ctx.Web
$List=$web.Lists.GetByTitle($LibraryName)
#Get all List items from the library - Exclude "Folder" objects
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$Query.ViewXml="<View Scope='RecursiveAll'><Query><Where><Eq><FieldRef Name='FSObjType'/><Value Type='Integer'>0</Value></Eq></Where></Query></View>"
$ListItems = $List.GetItems($Query)
$Ctx.Load($ListItems)
$Ctx.ExecuteQuery()
Write-host "Total List Items:" $ListItems.count
#Loop through each file in the library
Foreach($Item in $ListItems)
{
Write-host -f Green $Item["Title"]
}
}
Catch {
write-host -f Red "Error deleting versions!" $_.Exception.Message
}
SharePoint Online: PnP PowerShell to Get List Items using Get-PnPListItem cmdlet
How do I get items from a SharePoint Online list in PowerShell? Here is the PnP PowerShell to get all list items from a SharePoint Online list using the Get-PnPListItem cmdlet. Ensure the PnP PowerShell Module is installed in your system, and you have permission to access the SharePoint site before executing this PowerShell script.
#Config Variables
$SiteURL="https://Crescent.sharepoint.com/sites/marketing"
$ListName= "Documents"
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential)
#sharepoint online pnp powershell get list items
$ListItems = Get-PnPListItem -List $ListName -Fields "Title" -PageSize 2000
#Loop through each Item
foreach($ListItem in $ListItems)
{
Write-Host "Title:" $ListItem["Title"]
}
This script gets all files and folders as list items from the list “Documents” in the given SharePoint Online site collection. On larger lists, make sure you use the “PageSize” parameter. Otherwise, you may encounter “Get-PnPListItem : The attempted operation is prohibited because it exceeds the list view threshold.” error message. Also, The Get-PnPListItem cmdlet with the “Query” parameter with the “Where” condition or the “FolderServerRelativeurl” fails on larger lists. The “PageSize” has no effect on these parameters as of today.
Get-PnPListItem -List $ListName -Fields "Title" -PageSize 2000
Get List Items using PnP PowerShell in SharePoint Online
Similarly, You can use the -Query parameter to filter and get list items in SharePoint Online with PowerShell:
#Config Variables
$SiteURL = "https://Crescent.sharepoint.com"
$ListName = "Projects"
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential)
#CAML Query to Filter List Items
$Query = "<View><Query><Where><Eq><FieldRef Name='ProjectStatus' /><Value Type='Choice'>Active</Value></Eq></Where></Query></View>"
#get all list items using powershell sharepoint online - matching given query
$ListItems = Get-PnPListItem -List $ListName -Query $Query
#Loop through each Item
foreach($ListItem in $ListItems)
{
Write-Host "Id :" $ListItem["ID"]
Write-Host "Title :" $ListItem["Title"]
}
Using Script Block to Retrieve List Items from SharePoint Online
Here is how to use the script block and a progress bar to retrieve items from a SharePoint Online list.
$List= Get-PnPList $ListName
$global:counter = 0;
$ListItems = Get-PnPListItem -List $ListName -PageSize 2000 -Fields "Title" -ScriptBlock { Param($items) $global:counter += $items.Count; Write-Progress -PercentComplete ($global:Counter / ($List.ItemCount) * 100) -Activity "Getting List Items of '$($List.Title)'" -Status "Processing Items $global:Counter to $($List.ItemCount)";}
Write-host $ListItems.Count
Similarly, You can get List Items with particular fields by specifying the ViewFields element.
#CAML Query to Get List Items with Specific fields
$Query = "<View><ViewFields><FieldRef Name='Title'/></ViewFields></View>"
#sharepoint online pnp powershell get list items
$ListItems = Get-PnPListItem -List $ListName -PageSize 2000 -Query $Query
Suppose you have a large list with >5000 items. In that case, You may have to batch-process list items to overcome the threshold limit: “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
You can also use the Get-PnPListItem with -PageSize parameter.
To export a SharePoint Online list to a CSV file in PowerShell, use this PowerShell script: SharePoint Online PowerShell to Export list items to CSV
Use the “List.ItemCount” property to retrieve the total number of items in a SharePoint Online list or document library. Here is a sample PowerShell script to get the item count of a SharePoint Online list: SharePoint Online PowerShell to Get list item count
To retrieve all files from a SharePoint Online document library, use the PnP PowerShell cmdlet:”Get-PnPListItem -List “Library Name”. Here is an example script: SharePoint Online: Get list of files in a document library using PowerShell
Hi Thank You so much for this. I tried this and able to see the list but could not get the Managed metadata columns from my sharepoint online document library as is in tabular format. Can you please help me in getting through this. I am new to sharepoint and powershell and still in learning stage. Any help will be appreciated.
For fields like Managed Metadata, Hyperlink, Lookup, Person group, etc., You have to handle them differently. Refer: How to Export SharePoint Online List to CSV using PowerShell?
very thankful for the code examples here – so well laid out
Hi, Currently trying to figure out this functionality & whether better to use CSOM or PNP?… – using the code in example 1, everything is fine right up until I get to $context.ExecuteQuery(), whereupon I always get the error: “Exception calling “ExecuteQuery” with “0” argument(s): “The sign-in name or password does not match one in the Microsoft account system.” The credentials are correct, so this must perhaps be related to 2FA being enabled on my account?… – given that failure I tried using PNP instead, I get further… I can connect to SPO and find the list, but when I try to get list items, I can only ever get 3 values (Title, GUID, and… one other) – none of which are the columns I want to access… any thoughts/comments on doing this when 2FA is enabled?
If you are using 2FA/MFA, use “Interactive” switch in PnP PowerShell. E.g., Instead of:
Connect-PnPOnline -Url $SiteURL -Credentials (Get-Credential)
Use:
Connect-PnPOnline -Url $SiteURL -Interactive
Thanks, saved some time.
Hi,
this is my code
#Load SharePoint CSOM Assemblies
Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.dll”
Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.Runtime.dll”
#Variables for Processing
$SiteUrl = “”
$ListName = “”
#Get Credentials to connect
$password = ConvertTo-SecureString “” -AsPlainText -Force
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials(”, $password)
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Ctx.Credentials = $Credentials
#Get the List
$List = $Ctx.web.Lists.GetByTitle($ListName)
#Get All List Items
$Query = New-Object Microsoft.SharePoint.Client.CamlQuery
$ListItems = $List.GetItems($Query)
$FieldColl = $List.Fields
$Ctx.Load($ListItems)
$Ctx.Load($FieldColl)
$Ctx.ExecuteQuery()
#Fetch each list item value to export to excel
Foreach($Item in $ListItems)
{
$Item.FieldValues.Keys
Foreach($Field in $FieldColl)
{
$Field.Internalname
}
}
I have this Column in my SHP list “Project_x0020__x002f__x0020_Offi3” that is not listed in keys when I query $Item.FieldValues.Keys but is displayed when I query $Field.Internalname
I don’t understand why.
Could you please help me
Hi! I create a new view list with the option query “Exclude Folder objects” but not works! Can anyone help me please?
Hi Salaudeen, Thank you so much for your contributions. They’ve been very helpful. I was hoping you may be able to help with a script that moves a column (field?) in the default ‘All Items’ view. The idea is that I have an existing list named ‘Employees’ with 3 columns (LastName, FirstName, Office). I want to move FirstName to position 1 to have FirstName, LastName, Office. This is easily done in the UI and automatically shifts the other columns over but I have many lists and was hoping to do this in PowerShell and CSOM.
I couldn’t find this on your site. Is this something you may already have? If so, your help would be greatly appreciated. Thank you!
Hi There,
This should help: How to Add or Remove Fields in SharePoint Online List View using PowerShell , You have to remove all fields and add them in the order you need!
What do I have to do get the “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll”?
Install SharePoint Online CSOM client SDK to your client machine from: https://www.microsoft.com/en-us/download/details.aspx?id=42038
We need to export the sharepoint online 2016 list to csv.One of the fileds which is date in sharepoint list when exported to csv was in the format “mm/dd/yyy hh:mm:ss AM”.I need to add a filter condition for this date.We will be passing start date and end date as arguments and they are in the format “yyyy/mm/dd”.When i did using caml query it didn’t work.please help to resolve this issue asap.
Content here is worth in gold.