Manipulating SharePoint Lists with PowerShell

Manipulating SharePoint Lists with PowerShell

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

Manipulating SharePoint Lists with PowerShell

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!

Share this post

2 thoughts on “Manipulating SharePoint Lists with PowerShell

Post Comment