Export Managed Metadata value of “TaxonomyFieldMulti” type with PnP PowerShell

I was tasked to create a report containing the list of items (with columns including Managed Metadata) for all document libraries in all SharePoint Online (SPO) sites with STS#3 template type in the tenant that are tied to specific Content Types.

It’s straight forward when it comes to exporting columns (or fields) that are non Managed Metadata type. However, if especially the Managed Metadata column has multiselect properties (a.k.a. of TaxonomyFieldMulti type),then your CSV file may end up with a value of System.Object[] (or a different object type) inside your CSV file (see screenshot below). 

This can be frustrating to look at, especially when you have hundreds or thousands of rows of data which may have multiple columns that contain this type of information. Since our “Department” Managed Metadata field can have multiple values, we are dealing with collections, and we need to parse them accordingly using a couple of options. With the help of this post I was able to resolve my issue so that my CSV file captured all field values cleanly as seen below:

Below is the PnP PowerShell script code that reads all relevant sites/libraries and later export to a csv file, save it to a local folder and if you want upload, it to a reporting library inside a SPO site.

$siteCSVWillBeSaved = "https://[TenantName].sharepoint.com/sites/[site alias]"
$clientID = 'Your Azure AD registered app client id'
$tenantName = "[TenantName].onmicrosoft.com"
$thumbPrint = 'Your ThumbPrint'

$startTime = Get-Date
$dateStr = $startTime.ToString("yyyyMMdd")
$ReportOutput = "D:\Code\MyReports\spo_file_metadata_$($dateStr).csv"

#get all site collections with no M365 Group template 
$allSiteCollections = Get-PnPTenantSite | Where-Object { $_.Template -eq "STS#3" }

$Results = @()

#loop through all site collections with STS#3 template type
ForEach ($site in $allSiteCollections) {
 Connect-PnPOnline -Url $site.Url -ClientId $clientID -Tenant $tenantName -Thumbprint $thumbPrint
 #Looking for all document libraries in a given SPO site
 $DocLibrary = Get-PnPList | Where-Object { $_.BaseTemplate -eq 101 } 
 foreach ($DocLib in $DocLibrary) {
    #Get libraries that are tied to these two CTs (Document Set Dept and Document Dept)
    $contentType = Get-PnPContentType -List $DocLib | Where-Object { ($_.Name -eq "Document Set Dept") -or ($_.Name -eq "Document Dept") }

    #If the library exists
    if ($null -ne $contentType) {
        #Get the particular library 
        $List = Get-PnPList -Identity $DocLib

        #Get Items from the document library
        #$ListItems = Get-PnPListItem -List $DocLib -PageSize 500

        Write-Host "Total Number of Items in the List:"$List.ItemCount
        $ItemCounter = 0
        #Iterate through each item, hence ONLY document libraries that have items get reported 
        foreach ($Item in $ListItems) {
            #$Results += New-Object PSObject -Property ([ordered]@{
            $Results += New-Object PSObject -Property ([ordered]@{
                    Name        = $Item["FileLeafRef"]
                    Type        = $Item.FileSystemObjectType
                    FileType    = $Item["File_x0020_Type"]
                    RelativeURL = $Item["FileRef"]
                    FileSize    = $Item["SMTotalFileStreamSize"] 
                    CreatedBy   = $Item["Author"].Email 
                    CreatedOn   = $Item["Created"] 
                    #Department   = (($Item["Organization_x0020_GoA"].Label) -join ',') #option 1 Department concatenated with comma
                    Department   = (@($Item["Organization_x0020_GoA"].Label) | Out-String).Trim() #option 1 with one nice formatting
                   })
            $ItemCounter++
            Write-Progress -PercentComplete ($ItemCounter / ($List.ItemCount) * 100) -Activity "Processing Items $ItemCounter of $($List.ItemCount)" -Status "Getting Metadata from Item '$($Item['FileLeafRef'])"         
        }
	
    }
 }

}

#Export the results to CSV
$Results | Export-Csv -Path $ReportOutput -NoTypeInformation
Write-Host "Document Library Inventory Exported to CSV Successfully!"

#Connect to Report library site
Connect-PnPOnline -Url $siteCSVWillBeSaved -ClientId $clientID -Tenant $tenantName -Thumbprint $thumbPrint

#powershell pnp to upload file to CSV report library site
try {
    Add-PnPFile -Path $ReportOutput -Folder $DestinationPath -ErrorAction Stop
    Write-Host "CSV file saved to IMReoprtLibrary Successfully!"
    Add-Content -Path $IMReportTraceLog -Value "CSV file saved to IMReoprts library Successfully" 
}
catch {
    Write-Host "Error: $($_.Exception.Message)" -ForegroundColor Red
    $itemError = $($_.Exception.Message)
    $errorLineNumber = $Error[0].InvocationInfo.ScriptLineNumber
        
    Add-Content -Path $IMReportTraceLog -Value "Error raised: $($itemError) at line # $($errorLineNumber)"
    Write-Host "Error raised: $($itemError) at line # $($errorLineNumber)"
}

Advertisement