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:

$colListRows.data.ItemCount

You can pull out specific items with:

$colListRows.Data.Row

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

$colListRows.Data.Row[0]

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

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

or match on title:

$colListRows.data.row | 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 (http://www.sharepoint-tips.com/2007/02/how-to-use-getlistitems-web-service.html), 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:DevEnvDir="$env:VSINSTALLDIR\Common7\IDE"

$env:FrameworkSDKDir="$env:VSINSTALLDIR\SDK\v2.0"

$FrameworkPath=$([System.Runtime.InteropServices.RuntimeEnvironment]::GetRuntimeDirectory())

$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"

$env:INCLUDE="$env:VCINSTALLDIR\ATLMFC\INCLUDE;$env:VCINSTALLDIR\INCLUDE;$env:VCINSTALLDIR\PlatformSDK\include;$env:FrameworkSDKDir\include;$env:INCLUDE"

$env:LIB="$env:VCINSTALLDIR\ATLMFC\LIB;$env:VCINSTALLDIR\LIB;$env:VCINSTALLDIR\PlatformSDK\lib;$env:FrameworkSDKDir\lib;$env:LIB"

$env:LIBPATH="$FrameworkPath;$env:VCINSTALLDIR\ATLMFC\LIB"

 

 

function get-SPList {

      #sharepoint helper function to get list data from sharepoint

      param(

            $strWebPath,

            $strListname

      )

      #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.Credentials=[System.Net.CredentialCache]::DefaultCredentials

      $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($objList.name, $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.Credentials=[System.Net.CredentialCache]::DefaultCredentials

      $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

}