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. }  

Writing Portable Code

My last post got me thinking about the problems experienced when trying to write culture aware software. Yeah, I know it was actually me that was unaware of the culture, but this time it's about the software end of the deal; in particular, the recently updated Microsoft Business Data Catalog Definition Editor for Microsoft's popular SharePoint 2007 server. If you read some of the comments on the blog, you'll see that various people (using a non US English version of Windows) have installed it and have come across a problem where the tool cannot find the local security group called "Builtin\Users." Oops. In the world of cutting-edge technology, people often install software that doesn't match the installed language of their O/S. The fact of the world is that all major symbolic computer languages are based around English, and the most popular software gets written in English first. Here in Quebec, Canada, French is the primary language with English coming second (Canada is officially bilingual - although most of the country only speaks English). Localization of software takes a fair amount of time. It's not just translating a resources file - there are hot-keys to reassign (the Bold shotcut in French MSWord is CTRL+G for example, bold being Gras in French) dialog boxes to resize, labels and controls to reposition etc. Some languages are more verbose than others and end up with text that won't fit. However, there are things you do to avoid certain problems -- lets take the issue above as an example.

Logins and Group names are just an abstraction in the Windows security subsystem. These things are actually represented by value called a SID ( system.security.principal.securityidentifier ). No matter what version of Windows you use, the SIDs for built-in accounts and groups are the same:

First using an en-US system:

  1. PS > $acc = new-object System.Security.Principal.NTAccount "Users" 
  2. PS > $acc.Translate( [System.Security.Principal.SecurityIdentifier] ).value  
  3. S-1-5-32-545 

and a French (fr-FR) system:

  1. PS > $acc = new-object System.Security.Principal.NTAccount "Utilisateurs" 
  2. PS > $acc.Translate( [System.Security.Principal.SecurityIdentifier] ).value  
  3. S-1-5-32-545 

As you can see, the SID is the same: S-1-5-32-545. An example of this is shown below - a simple If-Elevated function that takes two Scriptblocks: the first is executed if the user is running as an administrator, the second is running if the user is just a plain well, user:

  1. # Usage:  
  2. #  
  3. # If-Elevated { .. admin code .. } { "sorry, need admin" }   
  4. #  
  5.  
  6. function If-Elevated {  
  7.   param(  
  8.     [scriptblock]$AsAdmin = $(Throw "Missing 'as admin' script"),  
  9.     [scriptblock]$AsUser= $(Throw "Missing 'as user' script")  
  10.   )  
  11.    
  12.   $identity = [security.principal.windowsidentity]::Getcurrent()  
  13.   $principal = new-object  security.principal.windowsprincipal $identity 
  14.   $adminsRole =  [system.security.principal.securityidentifier]"S-1-5-32-544" 
  15.                   
  16.   if ($principal.IsInRole($adminsRole)) {  
  17.     & $AsAdmin 
  18.   } else {  
  19.     & $AsUser 
  20.   }  
  21. }  
So ok, it doesn't have localized messages, but at least it will execute correctly on other locales ;-) Have fun.

About the author

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

Month List

Page List