Update metadata on SharePoint documents and folders

You have SharePoint managed metadata terms used in document libraries and you decide to update the label of a term. You can easily do so in the term store and wait for the change to take effect. All documents will display the new term label after the label change is propagated from the term store to the sites.
But what if you want to update a specific term, to a different term that already exists in the store? If you only have a small number of documents and folders that need to be updated, you can simply do the update manually. But if you need to update a large number of items, unfortunately, this is a scenario that will require some automation.
The following script will do the job for you.

Summary

  • PnP PowerShell for all the interactions with SharePoint
  • Uses search to find all the documents/folders
  • Checks the current value of the list item, in the case search index is not updated
  • Handle single or multi values
  • Performs a system update: Modified and Modified by are not updated
  • Generates a CSV file with the items parsed

The script currently connects to a specific site and assumes that all items retrieved from the search belong to the site as this was the scenario tested. If you need to connect to multiple sites, you may need to adjust the script slightly to handle that.

Requirements

  • PnP PowerShell is available on the machine running the script
  • A SharePoint search managed property mapped to the custom field associated with the term set (for example, RefinableString01)

Script to update metadata

$siteUrl = "https://contoso.sharepoint.com/sites/Home"
# ampersand: &
$oldTerm = "My old term label" -replace '&', '&'
$newTerm = "My new term label" -replace '&', '&'
$listColumn = "MyCustomField"
$termPath = "My Term Group|My Term Set|"
$Query = "* RefinableString01=""$oldTerm"""
$LogFile = "C:\users\$env:USERNAME\Desktop\UpdatedItems.csv"
# ---------------------------------
Connect-PnPOnline -Url $siteUrl -UseWebLogin
$SearchResults = Submit-PnPSearchQuery -Query $query -All -TrimDuplicates $false -SelectProperties ListItemID, ContentTypeId 
# $SearchResults
$results = @()
foreach ($ResultRow in $SearchResults.ResultRows) {
    
    $itemId = $ResultRow["ListItemID"]
    $library = $ResultRow["ParentLink"].Split("/")[5] # quick way to get library from path
    $contentTypeId = $ResultRow["ContentTypeId"]
    $path = $ResultRow["Path"]
    $parentLink = $ResultRow["ParentLink"]
    $type = ""
    Write-Host "Path: $path"
    if ($contentTypeId -like '0x0101*') {
        Write-Host "Document" -ForegroundColor Yellow
        $type = 'Document'     
    }
    if ($contentTypeId -like '0x0120*') {
        Write-Host "Folder" -ForegroundColor Yellow
        $type = 'Folder'
    }
    # Get list item
    $listItem = Get-PnPListItem -List $library -Id $itemId -Fields $listColumn
    if ($null -ne $listItem[$listColumn]) {
        
        # Generate new value for the field
        $termsWithPath = $null
        if ($listItem[$listColumn].Count -gt 1) {
            # check current value, in case search index is not updated
            if ($listItem[$listColumn].Label -contains $oldTerm) {
                # If multi-value, create an array of terms, and replace the old term by the new one
                $termsWithPath = @()
                foreach ($term in $listItem[$listColumn]) {
                    if ($term.Label -eq $oldTerm) {
                        $termsWithPath += $termPath + $newTerm
                    }
                    else {
                        $termsWithPath += $termPath + $term.Label
                    }
                }
            }
            else {
                Write-Host "Skipped: multi-value field does not contain term" -ForegroundColor Red
            }
        }
        else {
            # If single value, replace term
            # check current value, in case search index is not updated
            if ($listItem[$listColumn].Label -eq $oldTerm) {
                $termsWithPath = $termPath + $newTerm
            }
            else {
                Write-Host "Skipped: single-value field does not match term" -ForegroundColor Red
            }
        }
        
        if ($null -ne $termsWithPath) {
            # Update list item
            $termsWithPath
            Set-PnPListItem -List $library -Identity $itemId -SystemUpdate -Values @{"$listColumn" = $termsWithPath }
        }
    } 
    else { 
        Write-Host "Skipped: field is empty" -ForegroundColor Yellow
    }
    Write-Host "-------------" -ForegroundColor Yellow
    #Creating object to export in .csv file
    $results += [pscustomobject][ordered] @{
        Library    = $library
        ItemId     = $itemId 
        Type       = $type
        ParentLink = $parentLink
        Path       = $path
    }
    # break
}
$results | Export-Csv -Path $LogFile -NoTypeInformation

Leave a Reply

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