kwizcom banner advertisement

SharePoint Online: Get All List Items using PowerShell Script

SharePoint Online: PowerShell to Get All List Items
This PowerShell script gets 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)
$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"]
}  

Get a single List Item using PowerShell:
If you want to get a single item, use:
#Get the List Item
$List = $Context.web.Lists.GetByTitle($ListName)
$ListItem = $List.GetItemById(15)
$Context.Load($ListItem)
$Context.ExecuteQuery()

#Get the Title field value
write-host $ListItem["Title"]

PowerShell to Filter and Get List Items with CAML:
How about filter and get List Items using CAML Query? Here is how to get items from a SharePoint online list using PowerShell.
#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>"
$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)

#Get All List Items 
$ListItems = Get-PnPListItem -List $ListName -Fields "Title"

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

Similarly, You can use -Query parameter to filter list items:
#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 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"]
}
SharePoint Online: Get All List Items using PowerShell Script SharePoint Online: Get All List Items using PowerShell Script Reviewed by Salaudeen Rajack on September 23, 2015 Rating: 5

4 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

Please Login and comment to get your questions answered!

Powered by Blogger.