Using PNP Powershell to create a SharePoint list or library view that groups, filters and sorts

You might have already guessed that I just love PNP Powershell: It just brings me joy to have a readable script that just does what I need it to do! And that is what I say to my customers too. They too love it for being easy to read and to write, well at least how I write it..which might not be the best or the fastest, but in any case the most readable!
So when I needed to create a new view in a document library, I struggled a bit to get the SharePoint view functionality in there. A quick search on the internet didn’t bring me the results I was looking for (aka the code to copy/paste) hence this blogpost.

I start with a document library and a few test documents, and an extra choice field called “Topic”.

Creating a view in PNP Powershell

Let’s start by creating an easy, default view that just shows the file icon, file name, topic and when it was modified. That would look something like this:

Add-PnPView 
  -List "Documents"
  -Title "View1"
  -Fields "Type","LinkFilename","Topic","Modified" 

What you need to do, after connecting to the site, is using

  • -List to reference the correct list
  • -Title to give a name to your new view
  • -Fields to add all the fields you want to show

Important to note here is that when you want to show a document with the Name field, you need to use the “LinkFilename”. If you are using a list and want to show the name, use the “LinkTitle” field (thanks to Matthew McDermott and Marcus Blennegard for figuring this out!).

There are a few extra bits that can be done here like

  • -RowLimit to show only a number of results (perfect for example the last 5 modified documents)
  • -SetAsDefault to set this view as the default view
  • -Personal to make this a personal view that only you can see

The end result is this:

Our first scripted view that shows 2 rows and the columns we added!

ViewType

Before we get into the sorting, filtering and grouping of our view, I wanted to quickly dive into the -ViewType parameter. This will bring you:

  • -ViewType Grid : this will bring your list into “Quick edit” mode

The option for Calendar don’t seem to do anything in SharePoint Online, as there is no Calendar view being outputted in modern lists/libraries. The Chart, Gantt and HTML option doesn’t bring any visual change neither (for now).

CAML Query

When you want to go sort, filter or group items in a view, you will need to create some CAML Query. Thankfully, for the things we want to use it for here, it isn’t that difficult.

  • OrderBy to sort things
  • Where to filter
  • GroupBy to group our items

You write this CAML Query as XML tags, where you open and close them. In between, you add the field that you want to use. There is a ton of great learning material out there if you want to get deep in CAML, but we only use it here in an easy way.

Sort items in a view

Sorting items on our Topic field would be:

Add-PnPView
  -List "Documents"
  -Title "Sort1"
  -Fields "Type","LinkFilename","Topic","Modified" 
  -Query "<OrderBy>
             <FieldRef Name= 'Topic' Ascending='false' />
         </OrderBy>"

Next to the name of the field that we want to sort on (FieldRef), we also need to add if we are ordering ascending or descending.

The result is this:

Order

Let’s say we want to sort our items based on when they were modified, last modified on top. That would be:

Add-PnPView
  -List "Documents"
  -Title "Sort2"
  -Fields "Type","LinkFilename","Topic","Modified" 
  -Query "<OrderBy>
          <FieldRef Name= 'Modified' Ascending='false' />
         </OrderBy>"

This is an easy change for an optimal result!

Filter out items from our view

What if we only wanted to show items that have “Topic 2” as topic ?
With a little CAML magic we can do that easily!

Add-PnPView
  -List "Documents"
  -Title "Filter1"
  -Fields "Type","LinkFilename","Topic","Modified" 
  -Query "<Where>
            <Eq>
               <FieldRef Name = 'Topic' />
               <Value Type = 'Text'>Topic 2</Value>
             </Eq>
          </Where>"

The result looks like this:

Filter

Next to equals (“Eq”), you can also use

  • Contains
  • BeginsWith
  • Neq (Not equal)
  • Gt  (Greater than)
  • Lt (Less than)
  • Geq (Greater than or equal to)
  • Leq (Less than or equal to)
  • Neq (Not equal)

Group items

Grouping items also requires some CAML query, the “<GroupBy>” in this case.

The result of this query where we group the items on the Topic field:

Add-PnPView
  -List "Documents"
  -Title "group1"
  -Fields "Type","LinkFilename","Topic","Modified" 
  -Query "<GroupBy>
             <FieldRef Name = 'Topic' />
          </GroupBy>"

The result is this:

Group by

The real power of this script is in the Query. You can combine these parts as well, to create a very complicated view that will have a massive business impact when you create sites or list and libraries in a powershell way!

About: Marijn

Marijn Somers (MVP) has over 14 years experience in the SharePoint world, starting out with SP2007. Over the years the focus has grown to Office 365, with a focus on collaboration and document management. He is a business consultant at Balestra and Principal Content Provider for "Mijn 365 Coach" that offers dutch employee video training. His main work tracks are around user adoption, training and coaching and governance. He is also not afraid to dig deeper in the technicalities with PowerShell, adaptive cards or custom formatting in lists and libraries. You can listen to him on the biweekly "Office 365 Distilled" podcast.

%d bloggers like this: