Second Life of a Hungarian SharePoint Geek

March 29, 2017

Working with the REST / OData Interface from PowerShell

Filed under: OData, PowerShell, REST, SP 2013 — Tags: , , , — Peter Holpar @ 20:56

If you follow my blog you might already know that I am not a big fan of the REST / OData interface. I prefer using the client object model. However there are cases, when REST provides a simple (or even the only available) solution.

For example, we are working a lot with PowerShell. If you are working with SharePoint on the client side at a customer, and you are not allowed to install / download / copy the assemblies for the managed client object model (CSOM), you have a problem.

Some possible reasons (you should know, that the SharePoint Server 2013 Client Components SDK is available to download as an .msi, or you can get the assemblies directly from an on-premise SharePoint installation):

  • You might have no internet access, so you cannot download anything from the web.
  • If you happen to have internet access, you are typically not allowed to install such things without administrator permissions on the PC. It’s quite rare case, if you or the business user you are working with has this permission.
  • You have no direct access on the SharePoint server, so you cannot copy the assemblies from it.
  • You are not allowed to use your own memory stick (or other storage device) to copy the assemblies from it.
  • Even if there is no technical barrier, company policies might still prohibit you using external software components like the CSOM assemblies.

In this case, using the REST interface is a reasonable choice. You can have a quick overview of the REST-based list operations here.

The main questions I try to answer in this post:

  • Which object should I use to send the request?
  • How to authenticate my request?
  • How to build up the payload for the request?

First of all, I suggest you to read this post to learn some possible pitfalls when working with REST URLs from PowerShell and how to avoid them with escaping.

Reading data with the SharePoint REST interface

Reading data with a GET request

Sending a GET request for a REST-based service in PowerShell is not really a challenge, might you think, and you are right, it is really straightforward most of the cases. But take the following example, listing the Id and Title fields of items in a list:

$listTitle = "YourList"
$url = "http://YourSharePoint/_api/Web/Lists/GetByTitle(‘$listTitle‘)/Items?`$select=Id,Title"

$request = [System.Net.WebRequest]::Create($url)
$request.UseDefaultCredentials = $true
$request.Accept = ‘application/json;odata=verbose’

$response = $request.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
# ConvertFrom-Json : Cannot convert the Json string because a dictionary converted from it contains duplicated keys ‘Id’ and ‘ID’.
#$response = $reader.ReadToEnd()
$response = $reader.ReadToEnd() -creplace ‘"ID":’, ‘"DummyId":’

$result = ConvertFrom-Json -InputObject $response
$result.d.results | select Id, Title

If you would use

$response = $reader.ReadToEnd()

instead of

$response = $reader.ReadToEnd() -creplace ‘"ID":’, ‘"DummyId":’

then you became this exception, when trying to convert the JSON response:

ConvertFrom-Json : Cannot convert the Json string because a dictionary converted from it contains duplicated keys ‘Id’ and ‘ID’.

The reason, that the JSON response of the server contains the fields Id and ID. JSON is case-sensitive, but PowerShell is not, so it is an issue if you want to convert the JSON response to a PowerShell object. You can read more about it in this post, although I don’t like the solution proposed there. Although it really helps to avoid the error, but it uses the case insensitive replace operator instead of the case sensitive creplace, so it converts both fields into a dummy field. PowerShell seems to have no problem with the duplicated properties.

Instead of using a System.Net.WebRequest object, we can achieve a shorter version using the Invoke-RestMethod cmdlet. Note, that we don’t select and display the Id property in this case to avoid complications. See my comments about that in the next section discussing the POST request.

$listTitle = "YourList"
$url = "http://YourSharePoint/_api/Web/Lists/GetByTitle(‘$listTitle‘)/Items?`$select=Title"
$headers = @{ ‘Accept’ = ‘application/json; odata=verbose’}
$result = Invoke-RestMethod -Uri $url -Method Get -Headers $headers -UseDefaultCredentials
$result.d.results | select Title

Reading data with a POST request

There are cases when you have to use the POST method instead of GET to read some data from SharePoint. For example, if you need to filter the items via a CAML query. In the following example I show you how to query the file names all documents in a library recursively that are older than a threshold value:

$listTitle = "YourDocuments"
$offsetDays = -30

$urlBase = "http://YourSharePointSite/"
$urlAuth = $urlBase +"_api/ContextInfo"
$url = $urlBase + "_api/Web/Lists/GetByTitle(‘$listTitle’)/GetItems?`$select=FileLeafRef"

$viewXml = "<View Scope=’Recursive’><ViewFields><FieldRef Name=’Created’/><FieldRef Name=’FileLeafRef’/></ViewFields><Query><Where><Lt><FieldRef Name=’Created’ /><Value Type=’DateTime’><Today OffsetDays=’$offsetDays’ /></Value></Lt></Where></Query></View>"

$queryPayload = @{ 
                   ‘query’ = @{
                          ‘__metadata’ = @{ ‘type’ = ‘SP.CamlQuery’ };                      
                          ‘ViewXml’ = $viewXml
                 } | ConvertTo-Json

# authentication
$auth = Invoke-RestMethod -Uri $urlAuth -Method Post -UseDefaultCredentials
$digestValue = $auth.GetContextWebInformation.FormDigestValue

# the actual request
$headers = @{ ‘X-RequestDigest’ = $digestValue; ‘Accept’ = ‘application/json; odata=verbose’ }
$result = Invoke-RestMethod -Uri $url -Method Post -Body $queryPayload -ContentType ‘application/json; odata=verbose’ -Headers $headers –UseDefaultCredentials

# displaying results
$result.d.results | select FileLeafRef

Just for the case of comparison I include the same payload in JavaScript format:

var queryPayload = {
                     ‘query’ : {
‘__metadata’ : { ‘type’ : ‘SP.CamlQuery’ },
                         ‘ViewXml’ : viewXml

As you can see, these are the most relevant differences in the format we need in PowerShell:

  • We use an equal sign ( = ) instead of  ( : ) to separate the name and its value.
  • We use a semicolon ( ; ) instead of the comma ( , ) to separate object fields.
  • We need a leading at sign ( @ ) before the curly braces ( { ).

The Invoke-RestMethod tries to automatically convert the response to the corresponding object based on the content type of the response. If it is an XML response (see the authentication part above) then the result will be a XmlDocument. If it is a JSON response then the result will be a PSCustomObject representing the structure of the response. However, if the response can not be converted, it remains a single String.

For example, if we don’t limit the fields we need in response via the $select query option:

$url = $urlBase + "_api/Web/Lists/GetByTitle(‘$listTitle’)/GetItems"

then the response includes the fields Id and ID again. In this case we should remove one of these fields using the technique illustrated above with the simple GET request, before we try to convert the response via the ConvertFrom-Json cmdlet.

Note: If you still use PowerShell v3.0 you get this error message when you invoke Invoke-RestMethod setting the Accept header:

Invoke-RestMethod : The ‘Accept’ header must be modified using the appropriate property or method.
Parameter name: name

So if it is possible, you should consider upgrading to PowerShell v4.0. Otherwise, you can use the workaround suggested in this forum thread, where you can read more about the issue as well.

If you are not sure, which version you have, you can use $PSVersionTable.PSVersion to query the version number, or another option as suggested here.

Creating objects

In this case we send a request with the POST method to the server. The following code snippet shows, how you can create a new custom list:

$listTitle = "YourList"

$urlBase = "http://YourSharePoint/&quot;
$urlAuth = $urlBase +"_api/ContextInfo"
$url = $urlBase + "_api/Web/Lists"

$queryPayload = @{ 
                    ‘__metadata’ = @{ ‘type’ = ‘SP.List’ }; ‘AllowContentTypes’ = $true; ‘BaseTemplate’ = 100;
                    ‘ContentTypesEnabled’ = $true; ‘Description’ = ‘Your list description’; ‘Title’ = $listTitle                      
    } | ConvertTo-Json

$auth = Invoke-RestMethod -Uri $urlAuth -Method Post -UseDefaultCredentials
$digestValue = $auth.GetContextWebInformation.FormDigestValue

$headers = @{ ‘X-RequestDigest’ = $digestValue; ‘Accept’ = ‘application/json; odata=verbose’ }

$result = Invoke-RestMethod -Uri $url -Method Post -Body $queryPayload -ContentType ‘application/json; odata=verbose’ -Headers $headers –UseDefaultCredentials

The response we receive in the $result variable contains the properties of the list we just created. For example, the Id (GUID) of the list is available as $result.d.Id.

Updating objects

In this case we send a request with the POST method to the server and set the X-HTTP-Method header to MERGE. The following code snippet shows, how to change the title of the list we created in the previous step:

$listTitle = "YourList"

$urlBase = "http://YourSharePoint/&quot;
$urlAuth = $urlBase +"_api/ContextInfo"
$url = $urlBase + "_api/Web/Lists/GetByTitle(‘$listTitle’)"

$queryPayload = @{ 
                    ‘__metadata’ = @{ ‘type’ = ‘SP.List’ }; ‘Title’ = ‘YourListNewTitle’                      
    } | ConvertTo-Json

$auth = Invoke-RestMethod -Uri $urlAuth -Method Post -UseDefaultCredentials
$digestValue = $auth.GetContextWebInformation.FormDigestValue

$headers = @{ ‘X-RequestDigest’ = $digestValue; ‘Accept’ = ‘application/json; odata=verbose’; ‘IF-MATCH’ = ‘*‘; ‘X-HTTP-Method’ = ‘MERGE’ }

$result = Invoke-RestMethod -Uri $url -Method Post -Body $queryPayload -ContentType ‘application/json; odata=verbose’ -Headers $headers –UseDefaultCredentials

Deleting objects

In this case we send a request with the POST method to the server and set the X-HTTP-Method header to DELETE. The following code snippet shows, how you can delete a list item:

$listTitle = "YourList"

$urlBase = "http://YourSharePoint/&quot;
$urlAuth = $urlBase +"_api/ContextInfo"
$url = $urlBase + "_api/Web/Lists/GetByTitle(‘$listTitle’)/Items(1)"

# authentication
$auth = Invoke-RestMethod -Uri $urlAuth -Method Post -UseDefaultCredentials
$digestValue = $auth.GetContextWebInformation.FormDigestValue

# the actual request
$headers = @{ ‘X-RequestDigest’ = $digestValue; ‘IF-MATCH’ = ‘*’; ‘X-HTTP-Method’ = ‘DELETE’ }
$result = Invoke-RestMethod -Uri $url -Method Post -Headers $headers -UseDefaultCredentials

Note: Although the documentation states, that “in the case of recyclable objects, such as lists, files, and list items, this results in a Recycle operation”, based on my tests it is false, as the objects got really deleted.

Final Note: This one applies to all of the operations discussed in the post. If the SharePoint site you are working with available via HTTPS and there is an issue with the certificate, you can turn off the certificate validation, although it is not recommended in a production environment. You should include this line in your code before making any web requests:

[System.Net.ServicePointManager]::ServerCertificateValidationCallback = { $true }

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

Create a free website or blog at

%d bloggers like this: