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()
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 
#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"]
}
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
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

8 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

Please Login and comment to get your questions answered!

Powered by Blogger.