Microsoft – SharePoint – List – Programmatically list contents ( Using Web Services)

Background

To quickly gather data we have quickly provisioned a List on our workgroup’s Microsoft SharePoint web site.

And, now we need a way to collect the gathered data and persists it into a text-file.

 

Introduction

We chose to write a little power-shell script.  The script will use Web Services and CAML ( Collaborative Application Markup Language ).

 

SharePoint List

SharePoint List – Sample – Music

To preserve Corporate anonymity, we will create a sample generic SharePoint List that holds music data.

SharepointList

 

Configuration Data

To support flexibility we chose to create a configuration file.

Prior to choosing to go with a config file, we thought that command line arguments will suffice.

But, now as we continue to add more options, it is increasingly apparent that the multiplicity of options might be a bit much for command line args.

 

Item Definition Values
SharepointURL URL of the Sharepoint Site http://sharepointLAB
SharepointList List name music
SharepointView View Name 2F551156-9553-43AC-9D5C-700DD25EBEB0
CredentialExplicit Use current AD Account
SharepointListColumns Comma separated list of columns
IncludeMandatoryColumns If yes, system columns are included Y, Yes, True
MaxNumberofRows Maximum number of rows 100
XMLFileExtract XML File Name e:\extractmusic.xml
TextFileExtract Text File Name e:\extractmusic.txt
TextFileSeparator Text File Separator ;

 

Sample

music.config

<?xml version="1.0"?>
<configuration>
  <startup>
    <supportedRuntime version="v2.0.50727" safemode="true"/>
    <requiredRuntime version="v2.0.50727" safemode="true"/>
  </startup>
  <appSettings>
 
    <add 
		 key="SharepointURL" 
		 value="http://music:50"
		 />

	<add 
		 key="SharepointList" 
		 value="Music" 
		 />

	<add 
		 key="SharepointListColumns" 
		 value="Title,Artist,linkVideo" 
		 />		 

	<add 
		 key="CredentialExplicit" 
		 value="Y" 
		 />		


	<add 
		 key="XMLFileExtract" 
		 value="c:\tmp\music.xml" 
		 />	
		 
	<add 
		 key="TextFileExtract" 
		 value="e:\tmp\music.txt" 
		 />		
		 
	<add 
		 key="TextFileSeparator" 
		 value="~" 
		 />		
		 
  </appSettings>
</configuration>

 

 

Configuration Details

 

SharepointList

One can use the List name or the List ID.

List ID

If you want to use the ID, then you need to determine the List’s GUID ID.

Using your Browser, access the page’s source-code and search for ListID.  You will likely find
Searching for the following strings might likely grant you more precise
matches:

  • pageListId:
  • toolbarData[‘ListId’]

 

pageListID:

PageListID_v1

 

toolbarData[‘ListId’]

PageListID_v2

 

List Name
  • Find “application/rss+xml” and record down the corresponding the title

 

rss/xml:

ListName

 

If you try running the app with an incorrect List name \ List ID, you will

get a broad error:

Image:

InvalidListNameError
Tabulate:

 

Item Definition Values
Exception Type System.Management.Automation.MethodInvocationException http://sharepointLAB
Exception Message Exception calling “GetListItems” with “7” argument(s): “Exception of type ‘Micro-2146233087
soft.SharePoint.SoapServer.SoapServerException’ was thrown.”
music
Exception Source System.Management.Automation 2F551156-9553-43AC-9D5C-700DD25EBEB0
Exception HResult -2146233087
Exception InnerException System.Web.Services.Protocols.SoapException: Exception of type ‘Microsoft.ShareP
oint.SoapServer.SoapServerException’ was thrown.at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse
(
SoapClientMessage message
, WebResponse response
, Stream responseStream
, Boolean asyncCall
)

at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(
String methodName
, Object[] parameters)at Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebSer
viceProxy1Team___vti_bin_Lists_asmx_wsdl.Lists.GetListItems(String listName
, String viewName
, XmlNode query
, XmlNode viewFields
, String rowLimit
, XmlNode queryOptions
, String webID)
at CallSite.Target(Closure , CallSite , Object , Object , Object , XmlElement
, XmlElement , Object , XmlElement , Object )
 Excep at System.Management.Automation.ExceptionHandlingOps.ConvertToMethodInvocatio
nException(Exception exception, Type typeToThrow, String methodName, Int32 numArgs, MemberInfo memberInfo)at CallSite.Target(Closure , CallSite , Object , Object , Object
, XmlElement, XmlElement , Object , XmlElement , Object )at System.Dynamic.UpdateDelegates.UpdateAndExecute8[T0,T1,T2,T3,T4,T5,T6,T7
,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2, T3 arg3, T4 arg4, T5 arg5
, T6 arg6, T7 arg7)at System.Management.Automation.Interpreter.DynamicInstruction`9.Run(Interpre
tedFrame frame)at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.R
un(InterpretedFrame frame)
SharepointView

One has to be careful when specifying the view name.

Please do not use the view’s literal name, use the GUID.

To get the GUID, one can access the view and select the

Browser’s source view:

viewName

SharePoint Administrators can also use the Site Settings, specifically

“Views” panel to manage and access Views
listViews

Once we select a view, the browser’s URL will contain the view name.

 

Source Code

Github.com

getListItems.ps1

https://github.com/DanielAdeniji/MSSharePointListRetrieveContentsUsingPowerShell/blob/master/getMSSharePointListItems.ps1

 

Review Source Code

Read Configuration File

Let us read the configuration file.  What better code to steal than Keith Hill’s.

Creating and using a configuration file for your Powershell Script
http://rkeithhill.wordpress.com/2006/06/01/creating-and-using-a-configuration-file-for-your-powershell-scripts/

# Creating and Using a Configuration File for Your PowerShell Scripts
# http://rkeithhill.wordpress.com/2006/06/01/creating-and-using-a-configuration-file-for-your-powershell-scripts/
function readConfigurationFile()
{

   $global:appSettings = @{}
   $config = [xml](get-content $configurationFile)
   foreach ($addNode in $config.configuration.appsettings.add)
   {
	
	  if ($addNode.Value.Contains(',')) 
	  {
		  
		# Array case
		$value = $addNode.Value.Split(',')
			
   	    for ($i = 0; $i -lt $value.length; $i++) 
	    { 
			$value[$i] = $value[$i].Trim() 
	    }
	 }
	 else 
	 {
		 
		#Scalar case
		$value = $addNode.Value
	  
		  
	 }
		 
	 $global:appSettings[$addNode.Key] = $value
	 
    }

}	

 

GetListItems

GetListItems

 

##############################################################################
#.SYNOPSIS
# Gets list items using New-WebServiceProxy
#
#.DESCRIPTION
# This function connects to the web service Lists.asmx and retrieves the contents
# of the SharePoint List.
#
#.PARAMETER webURL
# URL
#
#.EXAMPLE
##############################################################################
Function GetListItems
{

     param 
     (
      [String]$webURL
     )

	 [System.Xml.XmlNode]$nodeListItems = $null
	  
      $uri=$webURL+"/_vti_bin/Lists.asmx?wsdl"

	  if ($bCredentialExplicit)
	  {

		#Explicit Credentials
	    Write-Host -ForegroundColor Green "Please pass the credentials that have access to the site: "$webURL

	    $credential=Get-Credential
		
	   #Explicit Credentials
	   $listsWebServiceReference = New-WebServiceProxy -Uri $uri -Credential $credential
		
	  }
	  else
	  {

		#Implicit Credentials
		$listsWebServiceReference = New-WebServiceProxy -Uri $uri  -UseDefaultCredential
		
	  }	
	  
	  if ( $listsWebServiceReference -eq $null)
	  {
	  	  	Write-Host "Connection failed"
			return $null
	  }
	  
	  try
	  {
	  
	
		$xmlDoc=New-Object -TypeName System.Xml.XmlDocument
		 $query = $xmlDoc.CreateElement("Query")
		 $viewFields =$xmlDoc.CreateElement("ViewFields")
		 $queryOptions =$xmlDoc.CreateElement("QueryOptions")
		  $viewFields.InnerXml = $viewFieldsValue
		  $query.InnerXml = $queryValue
		  $queryOptions.InnerXml = $queryOptionsValue
		  $nodeListItems =$listsWebServiceReference.GetListItems(
																  $listName
		, $viewName
		, $query
		, $viewFields
		, $rowLimit
		, $queryOptions
		, $null
		)

																
	    }
		catch 
	    # Powershell try/catch/finally
	    # http://stackoverflow.com/questions/6779186/powershell-try-catch-finally
	    {
	
		$errLine = "Ex Name :- {0}" -f $_.Exception.GetType().FullName
		Write-Host $errLine -ForegroundColor red

		$errLine = "Ex Message :- {0}" -f $_.Exception.Message

		Write-Host $errLine -ForegroundColor red

		}		
		
		if ($nodeListItems -eq $null)
		{
			Return $null
		}
 
		Return $nodeListItems
		
}

 

writeFileXML

 

##############################################################################
#.SYNOPSIS
# Writes file into an XML File
#
#.DESCRIPTION
# This function writes data out as XML
#
#.PARAMETER nodeListItemsLocal
# nodeListItemsLocal
#
#
#.PARAMETER outputXmlFilePathLocal
# outputXmlFilePathLocal
#
#.EXAMPLE
##############################################################################
Function writeFileXML
{

    param 
     (

          [Object]$nodeListItemsLocal
	, [String]$outputXmlFilePathLocal
     )


    $output = New-Object -TypeName System.IO.StreamWriter `
                -ArgumentList $outputXmlFilePathLocal, $false
	  
    $output.WriteLine($nodeListItemsLocal.Outerxml)
	  
    $output.WriteLine()

}

 

 

writeFileTextDeprecated

##############################################################################
#.SYNOPSIS
# Writes file into a Textfile
#
#.DESCRIPTION
# This function writes data as text file
#
#.PARAMETER nodeListItemsLocal
# nodeListItemsLocal
#
#
#.PARAMETER outputTextFilePathLocal
# outputTextFilePathLocal
#
#
#.PARAMETER TextFileSeparator
# TextFileSeparator
#
#.EXAMPLE
##############################################################################
Function writeFileTextDeprecated( $nodeListItemsDataRowLocal `
                                  , $outputTextFilePathLocal `
                                  , $TextFileSeparator)
{
 
  $stream = [System.IO.StreamWriter] $outputTextFilePathLocal
	
   foreach($dataRow in $nodeListItemsDataRowLocal)
   {
	
	  $itemID = $dataRow.GetAttribute("ows_ID")
		
	  $country = $dataRow.GetAttribute("ows_Country")
	  $region = $dataRow.GetAttribute("ows_Region")
	  $city = $dataRow.GetAttribute("ows_City")
		
	  $itemCreated = $dataRow.GetAttribute("ows_Created")
	  $itemModified = $dataRow.GetAttribute("ows_Modified")
		
	  $dataOut = $itemID + $TextFileSeparator
          $dataOut = $dataOut + $country + $TextFileSeparator 
          $dataOut = $dataOut + $region + $TextFileSeparator 
          $dataOut = $dataOut + $city
		
	  $stream.WriteLine($dataOut)
		
	
  }

  $stream.close()

	  
}

 

 

writeFileTextObject

##############################################################################
#.SYNOPSIS
# Writes file into a Textfile
#
#.DESCRIPTION
# This function writes data out as a text file
#.PARAMETER nodeListItemsDataRowLocal
# nodeListItemsDataRowLocal
#
#
#.PARAMETER outputFileLocal
# outputFileLocal
#
#
#.PARAMETER TextFileSeparator
# TextFileSeparator
#
#.EXAMPLE
##############################################################################
Function writeFileTextObject
{

   param 
    (
          [Object]$nodeListItemsDataRowLocal
	, [String]$outputFileLocal
        , [String]$TextFileSeparator		  
     )

    $objTextFileList = @()
	 
	
    foreach($dataRow in $nodeListItemsDataRowLocal)
    {
	
	$dataRec = new-object System.Object
		
	$dataRec | Add-Member -type NoteProperty -name ID `
                              -value $dataRow.ows_ID
		
	foreach ($element in $SharePointListColumnArray) 
	{
		
		$columnName = "ows_$element"
			
		$colValue = $dataRow.GetAttribute($columnName)
			
		$dataRec | Add-Member -type NoteProperty `
                   -name $element -value $colValue
			
	}
		
	$objTextFileList += $dataRec
		
	
   }
	
   $objTextFileList| export-csv $outputFileLocal `
            -notype -force `
            -Delimiter $TextFileSeparator
		
	  
}

 

 Main Module

# Check for file existence
# Using the Test-Path Cmdlet
# http://technet.microsoft.com/en-us/library/ee177015.aspx
$bFileExists = Test-Path $configurationFile

if ($bFileExists -eq $False)
{
    Write-Host "Configuration File ($configurationFile) File Existence Check failed!"
    return 
}

readConfigurationFile

#------------- Input Parameters --------------

## Specify the site URL from where you need to get all the list items
$webURL = $appSettings["SharepointURL"]
Write-Host("XML - Configuration File - SharepointURL = $webURL ")


## Specify the list name from where you need to get the list items
$listName = $appSettings["SharepointList"]
Write-Host("XML - Configuration File - SharepointList = $listName ")

## read SharePoint List Columns
$SharePointListColumnArray = $appSettings["SharepointListColumns"]
Write-Host("XML - Configuration File - SharepointListColumns = $SharePointListColumnArray ")


# Credential Explicit
$CredentialExplicit = $appSettings["CredentialExplicit"]

if ( ($CredentialExplicit -eq "Y") -Or ($CredentialExplicit -eq "y") )
{
	$bCredentialExplicit = $true
}
else
{
	$bCredentialExplicit = $false
}

Write-Host("XML - Configuration File - Credential ?  $CredentialExplicit -- Flag $bCredentialExplicit")


$XMLFileExtract = $appSettings["XMLFileExtract"]
Write-Host("XML - Configuration File - XMLFileExtract = $XMLFileExtract ")

$TextFileExtract = $appSettings["TextFileExtract"]
Write-Host("XML - Configuration File - TextFileExtract = $TextFileExtract ")

$TextFileSeparator = $appSettings["TextFileSeparator"]
Write-Host("XML - Configuration File - TextFileSeparator = $TextFileSeparator")


## $viewName is a string that contains the GUID of the view. 
#If you give empty value it will take the values from default view
[string]$viewName = ""

## GetListItems only returns 100 items
## http://sharepoint.infoyen.com/2014/06/16/getlistitems-only-returns-100-items/
## $rowLimit is a string that contains number of items to be retrieved from the list
[string]$rowLimit = "50000"

$viewFieldsBuffer = ""
foreach ($element in $SharePointListColumnArray) 
{
	$fieldRef = "<FieldRef Name='$element' />"
	$viewFieldsBuffer = $viewFieldsBuffer + $fieldRef
}

[String]$viewFieldsValue = $viewFieldsBuffer 
Write-Host("XML - Configuration File - SharepointList Column = $viewFieldsValue ")

[String]$queryValue="<Where><Gt><FieldRef Name='ID'/><Value Type='Number'>0</Value></Gt></Where>"

[String]$queryOptionsValue=""


# Get List Items
$nodeListItems = GetListItems $webURL

if ($nodeListItems -eq $null)
{
    Write-Host "Abnormal App Termination -- GetListItems returned null"
    return
}

#convert to Data Row
$nodeListItemsDataRow = $nodeListItems.data.row

#Output File - XML
if ($XMLFileExtract)
{
	writeFileXML $nodeListItems $XMLFileExtract
}

#Output File  - Text
if ($TextFileExtract)
{
	writeFileTextObject $nodeListItemsDataRow `
                        $TextFileExtract $TextFileSeparator
}


 

Invocation

Sample invocation

powershell c:\tmp\getListItems.ps1 c:\tmp\music.config

Sample Output

Sample XML Extract

XMLExtract

 

Sample Textfile Extract

TextfileExtract

 

 

 

Errors

Here are probable errors.

 

Error – Method Invocation Error



Exception Full Name :- System.Management.Automation.MethodInvocationException
Exception Message :- Exception calling "GetListItems" with "7" argument(s): 
"Exception of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was 
thrown."


In our case this was caused when we invoked “GetListItems” with an invalid Listname.

 

Listening To

Listening to “Let her go” by Passengers.

Let her go

Went out last weekend and was listening to a Radio Station when “Let her go” came on the Air.

BTW, “Nice one” to the young lady who talked up little Bissap Baobab ( http://www.bissapbaobab.com/ ) to me.

Made the trip across the Bay and really enjoyed it, very nice International Dance Music Friday and Saturday nights.

 

References

References – SharePoint

References – SharePoint – Client Object Models

 

References – SharePoint – Powershell

 

References – SharePoint/Powershell – SharePoint SnapIn

 

References – SharePoint/Powershell – Credentials

 

References – SharePoint/Powershell – CAML

 

References – SharePoint/Powershell – SOAP

 

 

References – Powershell – ExportCSV

 

References – Powershell – Exception

 

References – Powershell – Parameters

 

References – Powershell – Functions

 

References – Powershell – Configuration File

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s