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)"
}