Find Missing Metadata in SharePoint Online using PowerShell PnP

Did you know that fields in SharePoint Online set as “required” are not so… required anymore? It may be a pain for some, but they are now used as ‘guidance’ with a yellow background color instead and other signs like the red dot in the View. It means that users can upload documents without filling the required values and leave the site! 😕

This is not ideal if you use this metadata for your Search is it? So let’s get right into it!

ℹ️  If you prefer to watch a video, fear not! I made one just right there on my YouTube Channel 📽

 

What are we trying to do?

If you mark columns with values required, it’s surely because it might be important somehow. So it can be frustrating when users leave those fields blank! Other than educating your users, it’s difficult at this stage.

So we want to get all the documents (i.e.: names and more) which are missing values in required columns. Originally, it looks like that:

Missing Metadata

All this yellow is not looking good! 😅

 

The Logic

Let’s take a minute to think through how to build this script.
After connecting to the site, we want to:

  • Loop through the SharePoint libraries
  • Look at the documents (items)
  • Inspect each required field (let’s scope that now!)
  • Do ‘something’ is the (required) field empty
    • Grab some information (i.e.: document name, who created that document, etc…)

It’s important break down the steps when building a script. Trust me, it helps a lot! 😉

 

The Script

Here it is my friends!

#Connect to SPO
Connect-PnPOnline -Url "https://<TENANT-NAME>.sharepoint.com/sites/<YOUR-SITE>" -Credentials <YOUR-CREDS>

#variables
$allLibs = Get-PnPList 
$results = @()

foreach($lib in $allLibs){
    $allDocs = Get-PnPListItem -List $lib
    
    foreach($doc in $allDocs){
        $allRequiredFields = Get-PnPField -List $lib | Where-Object {$_.Required -eq $true}  
        
        foreach($field in $allRequiredFields){
            if ($null -eq $doc.FieldValues["$($field.InternalName)"]) {
                
                $results += [pscustomobject]@{
                    FileName = $doc.FieldValues.FileLeafRef
                    CreatedBy = $doc.FieldValues.Author.LookupValue
                    MissingMetadata = $field.Title
                    FileLocation = $lib.Title
                }
            }
        }
    }    
}
$results

Here, we’re only looping through 1 site. But remember that if your site contain 1000’s of libraries with missing values, it may take a while, and impact performance.

 

Thanks for reading!

 

 

One Response to “Find Missing Metadata in SharePoint Online using PowerShell PnP

  • ivanvazovv82
    4 days ago

    Hi, I found an old article in your blog about creating folders in SharePoint online. It’s this one – https://veronicageek.com/microsoft-365/sharepoint-online/create-a-folder-structure-in-sharepoint-online-using-powershell-pnp-from-file-shares/2019/02/#comments or “Create a folder structure in SharePoint Online using PowerShell PnP (from file shares)”. I simply wanted to say that the script there won’t work since Join-Path uses “\” but we need “/” for SharePoint. Substituting “$root = (Join-Path $root $_)” with “$root = $root + ‘\’ + $_” and “$root = Join-Path $topLevelRoot $_” with “$root = $topLevelRoot + ‘\’ + $_” works great. I lost more than an hour today to figure out why the script wasn’t working so I wanted to save someone else the time and writing here seems the only way to contact you.

Leave a Reply

%d bloggers like this: