PnP PowerShell to Retrieve SharePoint List Items: Get-PnPListItem with -Query

This should be one of the most obvious tools in the toolkit. PnP PowerShell is awesome, and we so often want to retrieve a set of items in a list. Get-PnPListItem is the magic cmdlet to do so.

This post is yet another of the many where I get to admit being an idiot about something in the hopes that the rest of you won’t have to feel that way. If it’s an obvious thing to you, then bravo to you! It wasn’t to me, so maybe it will be helpful to some of you.

I think of SharePoint lists as rectangles of data, where the columns (Fields in PowerShell terms) go across the top and the items are the rows. That should be no surprise: that’s what list views look like, and it’s the logical way a database table is structured.

Usually when we want to retrieve items from a list, we want to carve out some of that rectangle to meet our needs. It the list is small, then we can just retrieve everything and filter as we need to once we have all the data.

But that’s inefficient, and with my background of client side development (not to mention remembering storing information in bits or nibbles to save space!), I always want to retrieve as little data as possible to accomplish my object.

Thus, when I write queries against lists in PowerShell, I often use a CAML query. The CAML query is great for limiting the items returned to those which meet some criteria.

Here’s an example from something I was doing today:

$items = Get-PnPListItem -Connection $site -List $list `
    -Query "<View><Query><Where><Eq><FieldRef Name='EventDateText'/><Value Type='Text'>20200702</Value></Eq></Where></Query></View>"

This retrieves all the items from the list where EventDateText equals “20200702”. (Sorry about the formatting of the CAML. You can mentally do the search and replace to the > and < characters.) This list has 617k items in it and grows by ~1200 items per day (yes, that works just fine in this case). I only wanted today’s events, which amount to ~1100. The query allows me to “slice out” those items easily.

The problem is, when you use the -Query parameter, you can’t use the -Fields parameter. and given the way the Get-PnPListItem cmdlet works, I wasn’t getting the fields (columns) I needed for my processing.

In the past (and here’s the idiot part), I would set up a foreach loop to make a second call for each item to retrieve the fields I wanted, something like so:

foreach ($event in $items) {
    $item = Get-PnPListItem -List $list -Identity $event.ID -Fields "EventID","Modified"
    # Do some stuff with the item - usually add to an array
}

So while I was trying to be efficient with the -Query, I was being very inefficient with the loop.

And here’s the really embarrassing part: I know CAML pretty much backward from my SPServices days. In CAML, we can specify other things as well – like ViewFields!

The examples for the cmdlet haven’t kicked me in the head on this one. The only one showing the use of -Query is this:

Get-PnPListItem -List Tasks -Query "<View><Query><Where><Eq><FieldRef Name='GUID'/><Value Type='Guid'>bd6c5b3b-d960-4ee7-a02c-85dc6cd78cc3</Value></Eq></Where></Query></View>"

That’s not a query I would probably ever make, so as soon as I’m done writing this post, I’m going to go submit a pull request to the repo with a better example.

The end result of all this is my improved query which makes the foreach loop unnecessary:

$items = Get-PnPListItem -Connection $site -List $list `
    -Query "<View><ViewFields><FieldRef Name='Title'/><FieldRef Name='EventID'/><FieldRef Name='Modified'/></ViewFields><Query><Where><Eq><FieldRef Name='EventDateText'/><Value Type='Text'>20200702</Value></Eq></Where></Query></View>"

Yup, just add the ViewFields clause and you get back exactly the fields you need.