SharePoint Online: Get All List Items using PowerShell Script

Requirement: SharePoint Online PowerShell to Get List Items
SharePoint Online Get All List Items using PowerShell

SharePoint Online: PowerShell to Get All List Items
How to get items from a SharePoint Online list using PowerShell? 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="[email protected]"
$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:
#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 filter and query List Items using CAML Query? Here is how to get items from a SharePoint online list using PowerShell.

SharePoint Online: PowerShell to Get 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 list item by title in SharePoint Online. Similarly, you can filter any other data type like drop downs, 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:
Lets remove all Folder objects from the "ListItems" collection and get all 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
Here is the PnP PowerShell to get all list items from a SharePoint Online list.
#Config Variables
$SiteURL="https://crescenttech.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"

#Loop through each Item
foreach($ListItem in $ListItems)
{  
    Write-Host "Title:" $ListItem["Title"] 
}

Get List Items using PowerShell in SharePoint Online
Similarly, You can use -Query parameter to filter and get list items in SharePoint Online with PowerShell:
#Config Variables
$SiteURL = "https://crescenttech.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"]
}
If you have a large list with >5000 items, You may have to batch process list items to avoid throttling issue: 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

12 comments:

  1. 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.

    ReplyDelete
  2. What do I have to do get the "....\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"?

    ReplyDelete
  3. 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!

    ReplyDelete
  4. Hi! I create a new view list with the option query "Exclude Folder objects" but not works! Can anyone help me please?

    ReplyDelete
  5. Hi,
    this is my code

    #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 = ""
    $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

    ReplyDelete
  6. 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?

    ReplyDelete
  7. very thankful for the code examples here - so well laid out

    ReplyDelete
  8. 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.

    ReplyDelete

Please Login and comment to get your questions answered!

Powered by Blogger.