Avoid PowerShell strings issues and REST API with OData queries

PowerShell - Watch out for those strings when working with oData and RestAPI

Have you bumped into string manipulation issues with PowerShell scripts using PnP PowerShell, and want to call services by their REST APIs with the Invoke-PnPSPRestMethod cmdlet, where need to use a Rest API URL that contains oData clauses? (Phew, that’s like the longest question ever)

I have seen this a few times when helping folks out and wanted to share a solution.

E.g.

$listUrl = "/_api/web/GetList('/sites/atestsite/Shared%20Documents')/items?$select=Id,Title,File/Name&$top=10&$expand=File"

Invoke-PnPSPRestMethod -Method Get -URL $listUrl

You will find PowerShell tries to resolve the $select, $top, $expand within the string as variables, leading to malformed URLs and returns either an error or a false positive result, giving you the impression this worked.

You can try to stop this behaviour with apostrophes, BUT we already using apostrophes so for this scenario:

$listUrl = '/_api/web/GetList("/sites/atestsite/Shared%20Documents")/items?$select=Id,Title,File/Name&$top=10&$expand=File'

Invoke-PnPSPRestMethod -Method Get -URL $listUrl

Again can lead to a malformed URL for the oData query:

Here comes string formatting to save the day…

So what do you do if you want to include other variables? The best way (i’ve found) is to use the string formatting in PowerShell e.g.

Ensuring that you wrap any string elements where you DON’T want variable resolution with an apostrophe, but be mindful of elements of the query that may require the apostrophe to be present - this produces the following result:

$topItems = 20
$library = "Shared%20Documents"

$listUrl = "{0}{1}{2}{3}{4}" -f "/_api/web/GetList('/sites/atestsite/", $library, "')/items?", '$select=Id,Title,File/Name&$expand=File&$top=', $topItems
$listUrl # Call this to validate the URL is well formed
Invoke-PnPSPRestMethod -Method Get -URL $listUrl

Now, we have a well formed URL and the service is returned the expected results.

There are a some tips during testing to make sure you are not getting false positives:

• Work with a simple dataset, where you understand the data you are working with, when the results are returned you can easily see it looks correct
• Constrain the results returned to allow you to validate the response without sifting through a large dataset
• Write test cases (can be simple) to validate response e.g. the result set must include X and exclude item Y
• Refer to the REST API documentation to ensure the syntax is correct, refer here for Working with Lists and List Items with REST API | Microsoft Docs

This has some up a few times in writing scripts and when reviewing scripts from other engineers. Hope this helps you avoid the string manipulation trap.

Enjoy!

Credit: Blog Photo by THIS IS ZUN from Pexels