Find missing metadata in SharePoint using PowerShell

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!

What are we trying to do?

If you mark columns with required values, 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:  

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 if the (required) field empty
  • Grab some information –> For example a document name, who created that document, etc…

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

The script

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

#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