Manipulating SharePoint Lists with PowerShell
If i navigate to the uri which gives a list fo services available to us we can see the following entries
https://space.com/sites/dn/_vti_bin/lists.asmx
Now let me show you some examples which i was able to achieve using GetListItems service to
- Get All Items from SharePoint List
- Update List Items in SharePoint List
- Add New Items to SharePoint List
- Delete Items from SharePoint List
<# The New-WebServiceProxy Cmdlet creates a web service proxy object which lets us to manage web services #> <# First Get the Credential to Authenticate to SharePoint Site #> $cred = Get-Credential <# Lets Now assign the Name of the SharePoint List in which we want to do our manipulations #> $listName = 'VM Host Inventory' # Lets now enter the path of the service description, e.g. the .asmx page $uri = "https://space.com/sites/dn/_vti_bin/lists.asmx" # Lets Create the connection service using New-WebServiceProxy # $service = New-WebServiceProxy -Uri $uri -Credential $cred <# To Get all contents from list set all values to default #> $list = $service.GetListItems($listname, $null, $null, $null, $null, $null, $null) <#The items of the list are located in $list.data.row.#> <#If i run it i get the below output, as the information would be huge i have truncated the output and kept only one row output as sample#> $list.data.row ows_HostName : N01 ows_ClusterName : MHV1 ows_HostGroup Clusters ows_OperatngSystem : Microsoft Windows Server 2008 R2 Datacenter , Service Pack 1 ows_TotalMemory : 62.7500000000000 ows_Site : ADows__ModerationStatus : 0 ows__Level : 1 ows_Title : N01 ows_ID : 16683 ows_owshiddenversion : 1 ows_UniqueId : 16683;#{D7E3FA26-63A1-4C86-A1B5-35A515DE0EB8} ows_FSObjType: 16683;#0 ows_Created: 2012-08-08 14:06:16 ows_FileRef: 16683;#sites/dn/Lists/VM Host Inventory/16683_.000 ows_MetaInfo: 16683;# ... ....etc.... Hence we can see that we can extract all information from SharePoint lists using web-services
<#######################################################################################################################>
Lets assume that we need to to modify the ClusterName field value from “mhv1” to “mhv2”.
# Get name attribute values (guids) for list and view $ndlistview = $service.getlistandview($listname, "") $strlistid = $ndlistview.childnodes.item(0).name $strviewid = $ndlistview.childnodes.item(1).name
Here’s the output which i got when i ran for my list
<# Posh (0159) > $ndlistview List View ---- ---- List View Posh (0154) > $ndlistview = $service.getlistandview($listname, "") Posh (0155) > $strlistid = $ndlistview.childnodes.item(0).name Posh (0156) > $strviewid = $ndlistview.childnodes.item(1).name Posh (0157) > $strlistid {026246D6-1C8F-4601-9DBA-FBFBA060B0C1} Posh (0158) > $strviewid {1A273BEA-E700-477A-92C3-A327E246884F} #>
$xmldoc = new-object system.xml.xmldocument # If we specify an empty viewname parameter we see the default view $batchelement = $xmldoc.createelement("Batch")$batchelement.setattribute("onerror", "continue")$batchelement.setattribute("listversion", "1")$batchelement.setattribute("viewname", $strviewid) # Specify methods for the batch post using caml. $id= 1 $xml= "" # The row to be modified$rowId = 16683 # New field value$ClusterName="mhv2" # Create the XML Query, Notice that we pass Cmd='Update' $xml+= "<Method ID='$id' Cmd='Update'>" + "<Field Name='ID'>16724</Field>" + "<Field Name='ClusterName'>$ClusterName</Field>" + "</Method>" # Set the xml content $batchelement.innerxml = $xml try { $service.updatelistitems($listname, $batchelement) } catch { write-error $_ -erroraction:'SilentlyContinue' }
Now if i again run a query against the list i do see the clustername value was updated to “mhv2”
$list = $service.GetListItems($listname, $null, $null, $null, $null, $null, $null) $list.data.row | ?{$_.ows_ID -eq 16683}
ows_ClusterName : MHV2
ows_MetaInfo: 16683;#
<#######################################################################################################################>
Let me add a new item to the list with the below properties. To Add a new list item to a SharePoint list, we just need to modify the xml content and update Cmd=’New’
$xml+= "<Method ID='$id' Cmd='New'>" + "<Field Name='HostName'>'N02'</Field>" + "<Field Name='ClusterName'>'MHV3'</Field>" + "<Field Name='HostGroup'>'StandAlone'</Field>" + "<Field Name='OperatingSystem'>'Windows Server 2008'</Field>" + "<Field Name='TotalMemory'>'65.75'</Field>" + "</Method>" # Set the xml content $batchelement.innerxml = $xml $service.updatelistitems($listname, $batchelement) Now if i again run a query against the list i do see the New Item was Created $list = $service.GetListItems($listname, $null, $null, $null, $null, $null, $null) $list.data.row | ?{$_.ows_HostName -eq "N02"} ows_HostName : N02 ows_ClusterName : MHV3 ows_HostGroup StandAlone ows_OperatngSystem : Windows Server 2008 ows_TotalMemory : 65.75 ows_Site : ADows__ModerationStatus : 0 ows__Level : 1 ows_Title : N02 ows_ID : 16684 ows_owshiddenversion : 1 ows_UniqueId : 16684;#{D7E3FA26-63A1-4C86-A1B5-35A515DE0EB9} ows_FSObjType: 16684;#0 ows_Created: 2012-08-08 14:06:16 ows_FileRef: 16684;#sites/dn/Lists/VM Host Inventory/16684_.000 ows_MetaInfo: 16684;#
Now let me show an example to delete a list item from SharePoint list, we just need to modify the xml content and update Cmd=’Delete’
Here im deleting the row which has an ID of 16683 which we previously modified.
$xml += "<Method ID='$id' Cmd='Delete'>" + "<Field Name='ID'>16683</Field>" + "</Method>" # Set the xml content $batchelement.innerxml = $xml $service.updatelistitems($listname, $batchelement) Now if i again run a query against the list i do see that there is no output and the listitem was deleted $list = $service.GetListItems($listname, $null, $null, $null, $null, $null, $null) $list.data.row | ?{$_.ows_ID -eq 16683} i get no output as the list item was deleted :)
I hope you learned something good from this post “Manipulating SharePoint Lists with PowerShell” 🙂 Thanks!
$list = $service.GetListItems($listname, $null, $null, $null, $null, $null, $null)
This will return default number of rows (100) and not all rows. How to return all rows?
There is great alternate and short method to open any blocked site very easily in just one click and you can open any site very easily.
Reddit UK proxy