SharePoint Online: Bulk Update Metadata Properties from a CSV File using PowerShell

Requirement: Bulk Update Metadata of Multiple Documents from a CSV in SharePoint Online.

SharePoint Online: PowerShell to Bulk Edit Metadata Properties from a CSV File

We've a document library with a 100s of documents in several sub-folders and wanted to bulk update metadata of all those documents. While quick edit is one option, its really cumbersome to navigate through each folder and update properties of each document.

The solution we arrived is to prepare a CSV file with list of files to update and populate the CSV with required metadata and use PowerShell script to bulk edit metadata of all documents from the CSV file.

PowerShell to Get All Files from a Document Library to CSV

Let's generate document inventory from all folders and sub-folders of a given SharePoint Online document library:

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$ListName= "Branding"
$CSVOutput = "C:\Temp\DocInventory.csv"

#Connect to SharePoint Online site
Connect-PnPOnline $SiteURL -UseWebLogin
 
#Get all Files from the document library
$ListItems = Get-PnPListItem -List $ListName -PageSize 2000 -Fields "FileLeafRef" | Where {$_.FileSystemObjectType -eq "File"}

#Iterate through each item
$DocumentsList = @()
Foreach ($Item in $ListItems) 
{
    #Extract File Name and URL
    $DocumentsList += New-Object PSObject -Property ([ordered]@{
        FileName          = $Item.FieldValues.FileLeafRef
        ServerRelativeURL = $Item.FieldValues.FileRef
    })
}
#Export the results
$DocumentsList | Export-Csv -Path $CSVOutput -NoTypeInformation

This script generates a CSV file with "FileName" and "ServerRelativeURL" columns of all files from a given document library.

PowerShell to Bulk Update Metadata in SharePoint Online

Once we have the files list ready, populated the file with relevant metadata and used this script to update. This script uses "ServerRelativePath" as the key to get files and update. Here is my CSV file:

You can download the CSV from CSV to Bulk Update metadata in SharePoint Online

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$ListName= "Branding"
$CSVFile = "C:\Temp\DocMetadata.csv"

#Function to get Lookup ID from Lookup Value
Function Get-LookupID($ListName, $LookupFieldName, $LookupValue)
{
    #Get Parent Lookup List and Field from Child Lookup Field's Schema XML
    $LookupField =  Get-PnPField -List $ListName -Identity $LookupFieldName
    [Xml]$Schema = $LookupField.SchemaXml
    $ParentListID = $Schema.Field.Attributes["List"].'#text'
    $ParentField  = $Schema.field.Attributes["ShowField"].'#text'
    $ParentLookupItem  = Get-PnPListItem -List $ParentListID -Fields $ParentField | Where {$_[$ParentField] -eq $LookupValue} | Select -First 1 
    If($ParentLookupItem -ne $Null)  { Return $ParentLookupItem["ID"] }  Else  { Return $Null }
}

Try {
    #Connect to SharePoint Online site
    Connect-PnPOnline $SiteURL -UseWebLogin

    #Get the data from CSV file
    $CSVData = Import-CSV $CSVFile

    #Get fields to Update from the List - Skip Read-only, hidden fields and content type
    $ListFields = Get-PnPField -List $ListName | Where { (-Not ($_.ReadOnlyField)) -and (-Not ($_.Hidden)) -and ($_.InternalName -ne  "ContentType") }

    #Loop through each Row in the CSV file and update metadata
    ForEach($Row in $CSVData)
    {
        #Get All columns from CSV - Exclude "FileName" and "ServerRelativeURL" Columns
        $CSVFields = $Row | Get-Member -MemberType NoteProperty | Select -ExpandProperty Name | Where {$_.Name -notin ("FileName","ServerRelativeURL")}

        #Get the File to update
        $File = Get-PnPFile -Url $Row.ServerRelativeURL -AsListItem -ErrorAction SilentlyContinue
        If($File -ne $Null)
        {
            #Frame the Metadata to update
            $ItemValue = @{}

            #Map each field from CSV to target list
            Foreach($CSVField in $CSVFields)
            {
                $MappedField = $ListFields | Where {$_.InternalName -eq $CSVField}
                If($MappedField -ne $Null)
                {
                    $FieldName = $MappedField.InternalName
                    #Check if the Field value is not Null
                    If($Row.$CSVField -ne $Null)
                    {
                        #Handle Special Fields
                        $FieldType  = $MappedField.TypeAsString 
                        If($FieldType -eq "User" -or $FieldType -eq "UserMulti") #People Picker Field
                        {
                            $PeoplePickerValues = $Row.$FieldName.Split(",")
                            $ItemValue.add($FieldName,$PeoplePickerValues)
                        }
                        ElseIf($FieldType -eq "Lookup" -or $FieldType -eq "LookupMulti") #Lookup Field
                        {
                            $LookupIDs = $Row.$FieldName.Split(",") | ForEach-Object { Get-LookupID -ListName $ListName -LookupFieldName $FieldName -LookupValue $_ }                
                            $ItemValue.Add($FieldName,$LookupIDs)
                        }
                        Else
                        {
                            #Get Source Field Value and add to Hashtable
                            $ItemValue.Add($FieldName,$Row.$FieldName)
                        }
                    }
                }
            }
            #Update document properties
            Write-host "Updating Metadata of the File '$($Row.FileName)' at '$($Row.ServerRelativeURL)' with values:"
            $ItemValue | Format-Table
            Set-PnPListItem -List $ListName -Identity $File.Id -Values $ItemValue | Out-Null
        }
        Else
        {
            Write-Host "Could not find File at $($Row.ServerRelativeURL)' , Skipped!" -f Yellow
        }
    }
}
Catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}

Make sure your CSV column headers are matching the internal names (not the display names!) of the metadata columns in your document library. Also, verify the format of the metadata entered in your CSV file once. (E.g. If you supply invalid metadata, then the update would not take place - Obviously!) E.g. For MMS fields, the format should be: TermGroup|TermSet|Term.