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.

Similar Posts

6 Comments

  1. Nice article Mark.
    It would be even more useful, if you had pointed out some of the quirks of Get-PnPListItem cmdlet – especially when working with large lists (BTW. why did you stop slaming MS for 5000 view limits? It’s 2020 and they’re still there!). I would also describe PageSize parameter and remind that it’s not currently working when Query param is used as well (or at least it wasn’t working last time I worked with this).

  2. Thanks Marc, Just what I needed!

    Also note that the -pagesize parameter works for me.
    Im currently working on a list with over 40k items using Get-PnPListItem with -Query and -pagesize successfully.
    Using a SP AppID for authentication if that makes any difference?

    Cheers,
    Dan

  3. I’ve never had luck combining -Query and -Pagesize. Almost all of our lists are >5000 items and I think it’s cleaner to filter on retrieval instead of piping to Where-Object after the fact. If I use a query with 5000 it works fine but I get the error regarding the threshold if I add a or query. Could I be messing up the syntax?

  4. “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” – probably being to hard on your self.

    How big a stretch of imagination is it for the guy that wrote Get-PnPListItem to understand you might actually need to get to data from the list, not just the title and the id. Why did it and SharePoint have to be designed in such a way as to make that some monumental hurdle you have to over come with 20 lines of code. why isn’t there a flag that says “yes, i would like all the columns please”.

    sorry, just having a bad night after a worse day. I’ve been using sharepoint since 2001, and frankly i just loathe the product more and more every year.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.