Thursday, June 11, 2009

Powershell and Sharepoint Lists

Maybe I don't see the true beauty of sharepoint's API.  in my opinion it is needlessly complex.  After spending a day or so sifting through various powershell / sharepoint API information, I have come up with a couple of functions that simplify getting data out of sharepoint lists.  The functions are below.  I have saved the code into a file called sharepointlib.ps1 and i reference it when I need to speak to sharepoint.  You can do this with

. ./sharepointlib.ps1

If you don’t have the file in the local directory you will have specify a path instead of ./


once referenced, you pass it a sharepoint site URL and a List Name. I am using it as such:

$colListRows = get-SPList "http://SERVER/sites/SITE" "Calendar"


This gives me a collection of items on the calendar.  This only has the default view, a TODO on this would be to look up a view that matches the 'All Items' view.  It is also limited by a variable in there to only return 100 items.  not sure if that is a sorted list or not. You should be able to specify any list type.


Once you have your collection of list items, you can check how many items you have with:


You can pull out specific items with:


That will give you all the rows, may be overwhelming.  Limit it to the first row:


Or, what I did was to spin it through a where to get a match on a day:

$ | where {([datetime]$_.ows_EventDate).get_dayofyear() -eq $myDay.get_dayofyear()}

or match on title:

$ | where {$_.ows_Title -like "*OnCall*"}



The difficulty i had in sharepoint was constructing the CAML query.  I assume this is an interface or legacy type issue.  I found SCOM and hell even Project server much easier, I was able to grab my objects and interrogate them directly.  It didn't seem to be the way in sharepoint.  Eventually, thanks to Ishai (, I found a simple, essentially blank query could be passed.  Actually, looking at that URL again, this is more or less a powershell translation of his code.


One key is that you can pass $null for the view.  You can also create empty XML objects with the right name to pass into the query so I didn't really have to learn a bunch of CAML to make this work. 


Anyway, I am not a dev, I am an ops guy so I am sure there are suggestions.  Released under the license that says, feel free to use it but drop me a line if you have improvements or it helps you out.



Start the script below this line:

#set up some env variables.  this is largely to find wsdl and csc, maybe other libraries, i stole it from the web.

$env:VSINSTALLDIR="$env:ProgramFiles\Microsoft Visual Studio 9.0"

$env:VCINSTALLDIR="$env:ProgramFiles\Microsoft Visual Studio $obj\VC"




$env:FrameworkDir=$(split-path $FrameworkPath -Parent)

$env:FrameworkVersion=$(split-path $FrameworkPath -Leaf)

$env:PATH="$env:VSINSTALLDIR\Common7\IDE;$env:VCINSTALLDIR\BIN;$env:VSINSTALLDIR\Common7\Tools;$env:VSINSTALLDIR\Common7\Tools\bin;$env:VCINSTALLDIR\PlatformSDK\bin;$env:FrameworkSDKDir\bin;$env:FrameworkDir\$env:FrameworkVersion;$env:VCINSTALLDIR\VCPackages;C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin;$env:PATH"






function get-SPList {

      #sharepoint helper function to get list data from sharepoint





      #connect to the web service and make my dll

      $void = wsdl $strWebPath/_vti_bin/lists.asmx

      $void += csc /t:library Lists.cs

      $void += [Reflection.Assembly]::LoadFrom("$pwd\lists.dll")


      #reference our new object type and connect to sp

      $ListWS = new-object Lists


      $ListWS.url = "$strWebPath/_vti_bin/lists.asmx"


      #get our list by name

      $objList = $ListWS.GetList($strListname)


      #build xml annoyingness

      $objXML = New-Object System.Xml.XmlDocument

      $xQuery = $objXML.CreateElement("Query")

      $xViewFields = $objXML.CreateElement("ViewFields")

      $xQueryOptions = $objXML.CreateElement("QueryOptions")


      #you could do a CAML query here but we are just returning the first 100 rows.

      $intRowCount = 100


      #get the webID Guid

      $gWebID = Get-WebID($strWebPath)


      #get and return list items

      return $ListWS.GetListItems($, $null, $xQuery, $xViewFields, $intRowCount, $xQueryOptions, $gWebID)



function Get-WebID {

      #sharepoint helper function to get the guid of a sharepoing Web

      param ( $strWebPath )


      #Sharepoint hell


      #connect to the web service and make my dll

      $void += wsdl $strWebPath/_vti_bin/sitedata.asmx

      $void += csc /t:library SiteData.cs

      $void += [Reflection.Assembly]::LoadFrom("$pwd\sitedata.dll")


      #reference our new object type and connect to sp

      $SiteDataWS = new-object sitedata


      $SiteDataWS.url = "$strWebPath/_vti_bin/sitedata.asmx"


      #create a bunch of annoying variables so I can deal w/ sharepoint crap

      $arrwebmetadata = new-object _swebmetadata

      $arrwebwithtime = new-object _swebwithtime

      $listwithtime = new-object _slistwithtime

      $arrUrls = new-object _sFPUrl

      $roles = ""

      $roleusers = ""

      $rolegroups = ""


      #finally call my one line to get data

      $void += $SiteDataWS.GetWeb([ref] $arrwebmetadata, [ref]$arrwebwithtime, [ref]$Listwithtime, [ref]$arrUrls, [ref]$roles, [ref]$roleusers, [ref]$rolegroups)


      #my data has been stashed in teh $arrWebMetaData

      #return the guid

      return $arrwebmetadata.WebID


1 comment:

  1. What whiner!!! I started working on SharePoint stuff about 2 months ago and it has its own approach doing its own things... like anything else in programming, duh?. It's not that you don't see the beauty in the SharePoint API the reality is that you should seriously thinking about a career change!
    Programmers always take bold challenges no matter what... it looks like you're not one of us.