Manipulating SharePoint Lists with PowerShell
Manipulating SharePoint Lists with PowerShell – PowerShell Gives us an easy way to manipulate SharePoint data using New-WebServiceProxy cmdlet, without using Microsoft.SharePoint.dll which is not easily accesible, also Microsoft SharePoint dll file has some dependencies which might cause it not to run even if you are able to get hold of it from some one.Using New-WebServiceProxy cmdlet is an interesting way of getting access to SharePoint site and manipulate its lists
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
Get All 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
<#######################################################################################################################>
Update List Items in SharePoint List : –
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}
#>
# Next Create an xmldocument object and construct a batch element and its attributes.
$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_HostName : N01
ows_ClusterName : MHV2
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;#
<#######################################################################################################################>
Add New Items to SharePoint List : –
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;#
Delete Items from SharePoint List : –
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