Usually SharePoint lists are updated like so:
#Get the List $List = $Ctx.Web.Lists.GetByTitle[$ListName] $Ctx.Load[$List] $Ctx.ExecuteQuery[] #Get All List items $ListItemsCAML = New-Object Microsoft.SharePoint.Client.CamlQuery $ListItemsCAML.ViewXml = "" $ListItems = $List.GetItems[$ListItemsCAML] $Ctx.Load[$ListItems] $Ctx.ExecuteQuery[] Foreach [$ListItem in $ListItems] { #Set New value for List column $ListItem["Reference"] = $ListItem["ID"] $ListItem.Update[] $Ctx.ExecuteQuery[] }I am trying to update SharePoint list with values from JSON files, therefore I am looping through them, and not through ListItems variable [ListItemCollection]. So in case there is no record in the SP list for new JSON, add one. For that, I'd like to skip one loop, and update $ListItems directly:
ForEach [$json in $json_files] { $jsonSONitem = Get-Content $json.FullName | ConvertFrom-Json # somehow get ID of list item from $ListItems # $ID = $ListItems.FieldValues.Where{$_.Name -eq $json.Name}.ID $ListItems[$ID].FieldValues.Name="JSONitem.Name } $ListItem$.Update[] $Ctx.ExecuteQuery[]However, I am struggling to get ID for child objects of $ListItems. Or maybe I am just trying to use CSOM in a wrong way and I have to loop 'ForEach [$ListItem in $ListItems]' no matter what ?
SharePoint PowerShell CSOM API provides the capability to connect to SharePoint 2010, 2013 and Online. The major advantage is that these can be run from a remote machine. The API is available in CodePlex.
I will focus on how to use the API and read a SharePoint list in this article. First we need to download the source from CodePlex and extract it to a directory with necessary permissions. I am using ‘C:SPPSSource’ as the location for extraction.
Next we need to get a client context as in a normal CSOM.
Function Get-SPOContext[[string]$Url,[string]$UserName,[string]$Password]{
$secpasswd = ConvertTo-SecureString $Password -AsPlainText -Force
$userCredentials=New-Object System.Management.Automation.PSCredential [$UserName, $secpasswd]
Initialize-SPPS -siteURL //www.dfd.dk/ -UserCredential $userCredentials
$context = [Microsoft.SharePoint.Client.ClientContext][New-Object Microsoft.SharePoint.Client.ClientContext[$Url]]
$context.Credentials = $userCredentials
return $context
}
Then we can use the following function to read the list.
Function Get-ListItems[[Microsoft.SharePoint.Client.ClientContext]$Context, [String]$ListTitle] {
$list = $Context.Web.Lists.GetByTitle[$listTitle]
$camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
$camlQuery.ViewXml = ”
$items = $list.GetItems[$camlQuery]
$Context.Load[$items]
$Context.ExecuteQuery[]
return $items
}
At last, somewhere from the necessary place, these few commands can be used to read the lists.
Import-Module ‘C:SPPSSourcespps.psm1’
$context = Get-SPOContext -Url ‘’ -UserName ‘’ -Password ‘’
$listItems = Get-ListItems -Context $context -ListTitle ‘’
You can even schedule a Windows Scheduler and make this a schedule job which is very handy as it can be used even with SharePoint Online.
About the Author:
Malin is a SharePoint Specialist at Exilesoft and has been working on SharePoint since SharePoint 2007. He has been involved in many custom development projects, intranets, search, and other SharePoint projects. Malin is a regular presenter in many forums, including Sri Lanka .NET Forum and SharePoint Sri Lanka. Malin also presented in the Share-The-Point Conference Southeast Asia and at ESPC15 in Stockholm.
In this article, I am going write a simple Powershell script using client object model [CSOM] to find and retrieve all files from a document library in SharePoint Online. To use csom in Powershell, we need to load the required Microsoft SharePoint Online SDK assembly files.
The below Powershell script simply load and list all the files from given document library in a SharePoint Online site. You need to replace sharepoint site url, list name and required admin credentials with your own details.
#Add required references to SharePoint client assembly to use CSOM [System.Reflection.Assembly]::LoadWithPartialName["Microsoft.SharePoint.Client"] [System.Reflection.Assembly]::LoadWithPartialName["Microsoft.SharePoint.Client.Runtime"] [System.Reflection.Assembly]::LoadWithPartialName["Microsoft.SharePoint.Client.UserProfiles"] $siteUrl="//spotenant.sharepoint.com/sites/testsite" $UserName = "" $SecPwd = $[ConvertTo-SecureString 'adminPassword' -asplaintext -force] $ctx = New-Object Microsoft.SharePoint.Client.ClientContext[$siteUrl] $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials[$UserName,$SecPwd] $ctx.credentials = $credentials $ctx.Load[$ctx.Web] $ctx.ExecuteQuery[] $list=$ctx.Web.Lists.GetByTitle["Documents"] $ctx.Load[$list] $ctx.ExecuteQuery[] $camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery $camlQuery.ViewXml =""; $allItems=$list.GetItems[$camlQuery] $ctx.Load[$allItems] $ctx.ExecuteQuery[] foreach[$item in $allItems] { Write-Host "##############" Write-Host $item["FileRef"] $file = $ctx.Web.GetFileByServerRelativeUrl[$item["FileRef"]]; $ctx.Load[$file] $ctx.Load[$file.ListItemAllFields] $Author=$file.Author $CheckedOutByUser=$file.CheckedOutByUser $ModifiedBy=$file.ModifiedBy $ctx.Load[$Author] $ctx.Load[$CheckedOutByUser] $ctx.Load[$ModifiedBy] try { $ctx.ExecuteQuery[] Write-Host "File:" $file.Name Write-Host "Author:" $Author.LoginName Write-Host "ModifiedBy:" $ModifiedBy.LoginName if[$CheckedOutByUser.LoginName -ne $null]{ Write-Host "CheckedOutBy:" $CheckedOutByUser.LoginName }} catch{} Write-Host "##############" Write-Host "" }Along with file name and file relative url, the above script also retrieves useful information such as Author name of the file, modified username and if the file was checked out, it also returns the name of the user who currently checked-out the file and yet to checked-in.
Hi,
The following PowerShell script for your reference:
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" $UserName= "***@dennis.onmicrosoft.com" $Password = "***" $credentials= New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials[$UserName,[ConvertTo-SecureString $Password -AsPlainText -Force]] $webURL="//dennis.sharepoint.com/sites/Team" $ctx= New-Object Microsoft.SharePoint.Client.ClientContext[$webURL] $ctx.Credentials = $credentials try{ $lists = $ctx.web.Lists $list = $lists.GetByTitle["TestList"] $listItems = $list.GetItems[[Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery[]] $ctx.load[$listItems] $ctx.executeQuery[] foreach[$listItem in $listItems] { Write-Host "ID - " $listItem["ID"] "Title - " $listItem["Title"] "DateTime - " $listItem["DateTime"] "Choices - " $listItem["Choices"] } } catch{ write-host "$[$_.Exception.Message]" -foregroundcolor red }If you want to get lookup field data, we can use this:
$lookup = [Microsoft.SharePoint.Client.FieldLookupValue]$listItem["Lookup"] $lookupValue=$lookup.LookupValueBest Regards,
Dennis
Please remember to mark the replies as answers if they help.
If you have feedback for TechNet Subscriber Support, contact
SharePoint Diary » PowerShell »
Requirement: PowerShell to read list items in SharePoint Online In this blog post, we will be going over how to use PowerShell to read list items in SharePoint Online. We will cover the basics of using PowerShell to connect to SharePoint Online and then walk through some script examples on reading data from a SharePoint Online list. By the end of this post, you should understand how to get started using PowerShell with your SharePoint Online instances. Happy scripting! You can read SharePoint Online list with PowerShell in these three simple steps:
To start with, we need the connection context to the SharePoint Online site.
#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 = "//crescent.sharepoint.com/" #Get Credentials to connect $Cred = Get-Credential #Setup the context $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext[$SiteURL] $Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials[$Cred.UserName,$Cred.Password]
Once the connection is established, we can read or update any object from the SharePoint Online site. Here is how to get data from the SharePoint Online list using PowerShell.
#List Name Variable $ListName = "Projects" #Get the list $List = $Ctx.Web.Lists.GetByTitle[$ListName] #Read All Items from the list $ListItems = $List.GetItems[[Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery[]] $Ctx.Load[$ListItems] $Ctx.ExecuteQuery[] Write-host "Total Number of Items Found in the List:"$ListItems.Count
Step 3: Loop through List Items Collection to Read List Items
Once we get the list items collection, we can read list items by iterating through each item.
#Iterate through List Items ForEach[$Item in $ListItems] { #sharepoint online powershell read list items Write-Host ["List Item ID:{0} - Title:{1}" -f $Item["ID"], $Item["Title"]] }
Here is the complete PowerShell script to read SharePoint Online List Items:
#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 = "//crescent.sharepoint.com/" $ListName = "Projects" #Get Credentials to connect $Cred = Get-Credential #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 list $List = $Ctx.Web.Lists.GetByTitle[$ListName] #sharepoint online powershell read list items $ListItems = $List.GetItems[[Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery[]] $Ctx.Load[$ListItems] $Ctx.ExecuteQuery[] Write-host "Total Number of Items Found in the List:"$ListItems.Count #Iterate through List Items ForEach[$Item in $ListItems] { #sharepoint online powershell read list items Write-Host ["List Item ID:{0} - Title:{1}" -f $Item["ID"], $Item["Title"]] }
Suppose you have a large list with >5000 items. In that case, You may have to batch process list items to avoid throttling issues: 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
Read List Items using PnP PowerShell
Getting data from the SharePoint Online list through PnP PowerShell is much simpler! Here is the PnP PowerShell to get list items from a SharePoint Online list:
#Config Variables $SiteURL="//crescent.sharepoint.com/sites/marketing" $ListName= "Projects" #Connect to PnP Online Connect-PnPOnline -Url $SiteURL -Interactive #PnP powershell to get list items $ListItems = Get-PnPListItem -List $ListName -PageSize 2000 #Iterate through each Item in the list foreach[$ListItem in $ListItems] { Write-Host "Title:" $ListItem["Title"] }
Related Posts
Salaudeen Rajack – SharePoint Architect. Primarily on Infrastructure, Operations, Administration and Architecture!
Want to contribute?
Are you a SharePoint expert? Well, You can start contributing to this site! Find out now: How to Contribute and What you’ll get in return?