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
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="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:
#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 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 
#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
}

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

5 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

Please Login and comment to get your questions answered!

Powered by Blogger.