Manipulating remote SharePoint Lists with PowerShell

Someone on the PowerShell usenet group asked if it was possible to interact with SharePoint lists through our favourite little shell. Marco Shaw responded and put the pressure on by saying this was my bag of tricks. Who am I to say otherwise? so lets take a look at the recipe:

  1. One Get-WebService script
  2. One SharePoint List (any flavour but document library based)
  3. One Invoke-ListOperation script
Note: this script can be invoked from any workstation running PowerShell. You don't need access to SharePoint DLLs or other such nonsense.

Step One: Creating the Web Service proxy

Download my get-webservice2.ps1 (save and rename to .ps1) script and build a proxy to any remote Lists webservice. I say any, because you only need build the proxy once. You can switch sites at any time by changing the Url property of the $service object. You will be prompted by PowerShell with a graphical prompt for credentials for the SharePoint site, unless you specify -Anonymous as a switch (of course, if your SharePoint site is not anonymous enabled, this is a bit of a silly move.)

$service = .\get-webservice2.ps1 http://sharepoint/_vti_bin/lists.asmx

later, if you want to work with a different site, change the Url property like this:

$service.Url = "http://sharepoint/sites/root/subsite/_vti_bin/lists.asmx"

Ok, now we're ready to try the different operations: New, Update and Delete.

Step Two: Insert a new item

I chose the [hashtable] object to represent a new item as there's a nice syntax baked into powershell creating an initialising such a structure. Lets work with the Announcements list type. The two main fields I'm going to work with are the Title and Body fields. Note that even in a localised version of SharePoint - let's say French - where the fields are displayed as "Titre" (title) and "Corps" (body), we still use Title and Body for specifying the fields.
PS C:\> $item = @{Title = "Oisin"; Body="Hello, Word."}
PS C:\> $result = .\invoke-listoperation.ps1 $service new announcements $item
Success.
PS C:\> $result.row.ows_ID
2
If you get a success result back, the $result variable can be interrogated for the row that was just inserted. Above, I am retreiving the new ID for use in the next step.

Step Three: Modify an existing item

You probably noticed that the body of the announcemenet I just posted says "Word" instead of "World." Ooops! All we need do now is assign the $item variable the ID of the newly inserted row, modify the Body and use the Update command for our script:
PS C:\> $item.ID = 2
PS C:\> $item.Body = "Hello, World."
PS C:\> $result = .\invoke-listoperation.ps1 $service update announcements $item
Success.
Yee-ha. Fixed. Now it's time to remove such a pointless announcement.

Step Four: Delete an existing item

All we need to do now is pass the Delete command and a hashtable with an ID key set to the ID of the item we wish to delete. We can reuse the $item object at this point since it has the ID set now from the last update operation (alternatively, you could just pass @{ID=2} as the item argument - same effect).
PS C:\> $result = .\invoke-listoperation.ps1 $service delete announcements $item
Success.
or alternatively:
PS C:\> $result = .\invoke-listoperation.ps1 $service delete announcements @{ID=2}
Success.
And there you have it - announcement deleted. It's not that hard once you have the web service proxy bit done -- believe me, that was the hard part ;-)

Step Five: Profit!

Sorry, this part is up to you! Seriously though, if you are not on the SharePoint bandwagon by now, what's wrong with you?! It is an utterly incredible product. I've been working with SharePoint in all its various guises since the days of the Digital Dashboard (remember that?) and WSS 3.0 / MOSS 2007 is the most amazing iteration yet. Btw I have't tested this script on WSS 2.0, but it should work without any modification.

Here's the invoke-listoperation.ps1 script. Just copy and paste it into notepad, save it and away you go!

  1. param (   
  2.     $Service = $(throw "need service reference!"),   
  3.     $Operation = $(throw "need operation: Update, Delete or New"),   
  4.     $ListName = $(throw "need name of list."),     
  5.     [hashtable]$Item = $(throw "need list item in hashtable format.")   
  6. )    
  7.   
  8. # check if valid service reference provided   
  9. [void][system.Reflection.Assembly]::LoadWithPartialName("system.web.services")   
  10. if ($service -isnot [Web.Services.Protocols.SoapHttpClientProtocol]) {   
  11.     Write-Warning "`$Service is not a webservice instance; exiting."  
  12.     return  
  13. }   
  14.   
  15. # check if valid operation (and fix casing)   
  16. $Operation = [string]("Update","Delete","New" -like $Operation)   
  17. if (-not $Operation) {   
  18.     Write-Warning "`$Operation should be Update, Delete or New."  
  19.     return  
  20. }   
  21.   
  22. $xml = @"  
  23. <Batch OnError='Continue' ListVersion='1' ViewName='{0}'>  
  24.     <Method ID='1' Cmd='{1}'>{2}</Method>  
  25. </Batch>  
  26. "@   
  27.   
  28. if ($service) {   
  29.     trap [Exception] {   
  30.         Write-Warning "Error: $_"  
  31.         return;        
  32.     }   
  33.        
  34.     $listInfo = $service.GetListAndView($ListName"")   
  35.        
  36.     $listItem = ""  
  37.     foreach ($key in $item.Keys) {   
  38.         $listItem += ("<Field Name='{0}'>{1}</Field>" -f $key,$item[$key])   
  39.     }   
  40.        
  41.     $batch = [xml]($xml -f $listInfo.View.Name,$operation,$listItem)   
  42. }   
  43.   
  44. $response = $service.UpdateListItems($listInfo.List.Name, $batch)   
  45.   
  46. $code = [int]$response.result.errorcode   
  47.   
  48. if ($code -ne 0) {   
  49.     Write-Warning "Error $code - $($response.result.errormessage)"     
  50. else {   
  51.     Write-Host "Success."  
  52.     $response.Result   
  53. }  
blog comments powered by Disqus

About the author

Irish, PowerShell MVP, .NET/ASP.NET/SharePoint Developer, Budding Architect. Developer. Montrealer. Opinionated. Montreal, Quebec.

Month List

Page List