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 to 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 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 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 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

Send items to second-stage SharePoint recycle bin

SharePoint recycle bin

When a document or folder is deleted in SharePoint, it goes to the first-stage (site) recycle bin. After some time, it is moved to the second-stage recycle bin. And it stays there for another period before being completely deleted.
If you need to delete a very large number of documents in SharePoint, you may consider sending the items straight to the second-level recycle bin. This will prevent “flooding” the first-stage recycle bin with items, which would make the recycle bin hard to use for manually restoring deleted items.

This can be easily done with a few lines of PnP PowerShell.

First we need to delete the item and send it to the first-level recycle bin. We then query the recycle bin and retrieve the item with a DirName and Title that match the item previously deleted.

# Use Get-PnPListItem to retrieve the item(s) to delete
Remove-PnPListItem -List $DocLib -Identity $Item["ID"] -Recycle -Force
$path = $Item["FileRef"].substring(1) # remove first '/' from path as recycle bin items don't start with '/'
$path = $path.substring(0, $path.LastIndexOf('/')) # remove folder name and last '/'
$deletedItem = Get-PnPRecycleBinItem -FirstStage -RowLimit 1 | Where-Object { $_.DirName -Eq $path -and $_.Title -Eq $Item["FileLeafRef"]}
Move-PnpRecycleBinItem -Identity "$($deletedItem.Id)" -Force

Sample use case: Send all empty folders and files to second-stage recycle bin

The following script is an extension of the script that Veronique Lengelle published on her blog. The original script was to find empty folders and documents on a site. I have extended it to send all empty folders and files to the second-stage recycle bin. Finally, we move that item to the second-level recycle bin.

#Connect to SPO
Connect-PnPOnline -Url https://contoso.sharepoint.com/sites/Test -UseWebLogin
#Store in variable all the document libraries in the site
$DocLibrary = Get-PnPList | Where-Object { $_.BaseTemplate -eq 101 } 
$LogFile = "C:\users\$env:USERNAME\Desktop\SPOEmptyFoldersAndDocuments.csv"
$results = @()
foreach ($DocLib in $DocLibrary) {
    #Get list of all folders and documents in the document library
    $AllItems = Get-PnPListItem -PageSize 1000 -List $DocLib -Fields "SMTotalFileStreamSize", "Author", "ID"
    
    #Loop through each files/folders in the document library for folder size = 0
    foreach ($Item in $AllItems) {
        if ((([uint64]$Item["SMTotalFileStreamSize"]) -eq 0)) {
            Write-Host "Empty folder/file:" $Item["FileLeafRef"] -ForegroundColor Yellow
                
            #Creating object to export in .csv file
            $results += [pscustomobject][ordered] @{
                CreatedDate      = [DateTime]$Item["Created_x0020_Date"]
                FileName         = $Item["FileLeafRef"] 
                CreatedBy        = $Item.FieldValues.Author.LookupValue
                FilePath         = $Item["FileRef"]
                SizeInMB         = ($Item["SMTotalFileStreamSize"] / 1MB).ToString("N")
                LastModifiedBy   = $Item.FieldValues.Editor.LookupValue
                EditorEmail      = $Item.FieldValues.Editor.Email
                LastModifiedDate = [DateTime]$Item["Modified"]
            }
            #Remove item - send to first-level recycle bin
            Remove-PnPListItem -List $DocLib -Identity $Item["ID"] -Recycle -Force
            #Generate relative path in the same format as used by the recycle bin. Example: sites/Test/Shared Documents
            $path = $Item["FileRef"].substring(1) # remove first '/' from path as recycle bin items don't start with '/'
            $path = $path.substring(0, $path.LastIndexOf('/')) # remove folder name and last '/'
            #Get previously deleted item from first stage recycle bin using path and title
            $deletedItem = Get-PnPRecycleBinItem -FirstStage -RowLimit 1 | Where-Object { $_.DirName -Eq $path -and $_.Title -Eq $Item["FileLeafRef"]}
            #Move item to second-stage recycle bin
            Move-PnpRecycleBinItem -Identity "$($deletedItem.Id)" -Force
            
            Invoke-PnPQuery
        }#end of IF statement
    }
}
$results | Export-Csv -Path $LogFile -NoTypeInformation