Run the SharePoint Column Inventory

Prev Next

This document describes how to run the SharePoint column inventory before deploying the eSHARE classification columns. The inventory is read-only: it lists every site, library and column in the tenant, and flags existing columns that look like classification columns, so you know what the deployment will find. The following steps will be reviewed:

PREREQUISITES:

The person performing these steps should be a SharePoint Administrator using the computer and certificate set up in Create eSHARE Classify SPO Columns Entra ID App Registration.

Save the Inventory Script

  1. Go to Inventory Script at the end of this article, expand the script block and select the copy button in the top-right corner of the code.

  2. In PowerShell 7, save what you copied as Get-SPOColumnInventory.ps1:

Get-Clipboard -Raw | Set-Content -Path C:\eSHARE-Classification\Get-SPOColumnInventory.ps1 -Encoding utf8
  1. Confirm the script saved correctly. This shows the script's description:

Get-Help C:\eSHARE-Classification\Get-SPOColumnInventory.ps1

Run the Inventory

  1. Open PowerShell 7 and run the inventory for SharePoint sites and OneDrive. To scan SharePoint sites only, for example when doing sites first, leave out -IncludeOneDrive and run it again with it before the OneDrive rollout:

Set-Location C:\eSHARE-Classification
Import-Module PnP.PowerShell
.\Get-SPOColumnInventory.ps1 -ConfigFile .\Contoso\contoso.tenant.json -IncludeOneDrive
  1. Wait for the run to finish. The same command works in Commercial, GCC and GCC High tenants; the script reads the cloud from the configuration file. As a guide, about 500 sites take around 10 minutes. The script slows down and retries automatically if SharePoint throttles it.

  2. Open the report: C:\eSHARE-Classification\Contoso\SPO-Inventories\SPO-Inventory-contoso-[date]-[time]\SPO-Column-Inventory.xlsx.

Review the Inventory

The report has one sheet per result:

Sheet

What it shows

Summary

Counts of sites, lists and columns, and the options used

Look-alikes

Columns whose name contains sensitiv, classif, attribute, label, marking, eshare or guid, or a space (_x0020_). Microsoft's built-in columns are left out.

Name conflicts

Display names used with more than one internal name, and the reverse

Managed properties

The tenant's custom search properties and what they are mapped to

All columns

Every column found, on every site and library

Errors

Sites that couldn't be read, e.g. locked or read-only sites. The deployment skips these too.

  1. On the 'Look-alikes' sheet, look for columns with the internal names DataSensitivity, DataClassification, DataAttributes, eSHARE_GUID or DataSensitivityId that were created on a single library. The deployment replaces these with the standard site column and keeps their values.

  2. Look for columns with a classification display name but a different internal name, e.g. a Data Sensitivity column stored as eShareLabel. eSHARE doesn't write to these, and the deployment doesn't add the standard column to a library that already has one. Note them and contact eSHARE support if you find any.

  3. On the 'Managed properties' sheet, note any property mapped to eSHARE_GUID. This is needed later for the Purview DLP rule.

  4. Check the 'Errors' sheet and decide whether any locked site needs the columns. If it does, unlock it before the deployment.

Note:

In a large tenant, most look-alikes are ordinary columns whose names contain a space. To narrow the list, run the inventory again with -Pattern 'sensitiv|classif|eshare|guid'.

Inventory Script

  1. Copy the script below with the copy button and save it as C:\eSHARE-Classification\Get-SPOColumnInventory.ps1. It is read-only and changes nothing in the tenant.

Expand this block and copy the Get-SPOColumnInventory.ps1 script below

<#
.SYNOPSIS
    Tenant-wide inventory of SharePoint columns and search managed properties, with a look-alike report
    for classification-style columns (e.g. "Data Sensitivity", "Data Classification", "Data Attributes", "eSHARE_GUID", "Data Sensitivity Id").
    Works against any Microsoft 365 tenant, including US Government clouds.

.DESCRIPTION
    1. Microsoft Graph (app-only, certificate): enumerates every site and subsite, every site column,
       and every column on every visible list/library.
    2. PnP PowerShell (app-only with the same certificate, or signed in as an admin as a fallback):
       exports the tenant-level managed properties and their crawled-property mappings.
    3. Joins the two: for each column, the crawled property and managed property it is mapped to, if any.
    4. Flags look-alikes (name pattern match) and naming conflicts (one display name used with several
       internal names, or the reverse).

    Read-only. Nothing in the tenant is changed.

    Crawled properties cannot be exported in SharePoint Online, so only crawled properties that are
    mapped to a managed property are known. MappedCrawledProperty shows those actual names from the
    tenant search schema; no crawled property names are guessed or derived.

.PARAMETER ConfigFile
    A <name>.tenant.json file holding TenantName, TenantId, ClientId, CertificatePath or
    CertificateThumbprint, and Cloud (see .NOTES and example.tenant.json.sample). With no -ConfigFile,
    the script looks for *.tenant.json in its own folder and in the tenant subfolders one level down
    (e.g. Contoso\): one is used automatically, several give a menu. The folder holding the
    chosen file is the TENANT FOLDER: the output defaults to it and a relative CertificatePath is resolved
    against it. Values passed on the command line override the file.

.PARAMETER TenantName
    The tenant's SharePoint name: the part before .sharepoint.com (e.g. "contoso" for
    contoso.sharepoint.com). Used to build the admin URL and the default tenant ID.

.PARAMETER ClientId
    Application (client) ID of the inventory app registration in Entra ID. Usually set in the
    tenant config file.

.PARAMETER CertificateThumbprint
    Windows only. Thumbprint of the app's certificate in the current user's certificate store
    (Cert:\CurrentUser\My). Only the thumbprint is stored; the private key stays in the store.
    Ignored when CertificatePath is set.

.PARAMETER CertificatePath
    Path to the app's certificate as a .pfx file (private key included). Works on Windows, macOS and
    Linux, and is required on macOS and Linux. "~/" means your home folder; other relative paths are
    resolved against the tenant folder. Keep the .pfx OUTSIDE the synced SharePoint folder (it holds
    the app's private key).

.PARAMETER CertificatePassword
    Password of the .pfx file, as a SecureString. If omitted, the script prompts for it (press Enter
    for a .pfx without a password). Never stored in the config file.

.PARAMETER Cloud
    Commercial (default), GCC, GCCHigh or DoD. Sets the Graph endpoint, SharePoint domain and sign-in
    environment. GCC uses the commercial endpoints. GCCHigh and DoD have not been tested.

.PARAMETER TenantId
    Optional. Tenant GUID or initial domain. Defaults to <TenantName>.onmicrosoft.com
    (.onmicrosoft.us for GCCHigh/DoD). Pass it explicitly if the initial domain differs.

.PARAMETER AdminUrl
    Optional. Overrides the admin URL built from TenantName (and can be used instead of TenantName).

.PARAMETER OutputFolder
    Parent folder for the run output. Each run creates SPO-Inventory-<tenant>-<yyyyMMdd-HHmm> inside it.
    Default: SPO-Inventories in the tenant folder (next to the script when no config file is used).

.PARAMETER Pattern
    Case-insensitive regular expression matched against internal and display names to fill the
    Look-alikes sheet. Default: 'sensitiv|classif|attribute|label|marking|eshare|guid|_x0020_'.
    Example: -Pattern 'confidential|retention' to look for other column families.

.PARAMETER IncludeBuiltIn
    Keep Microsoft built-in columns (internal names starting with "_", and a few others such as WSGUID
    and VideoRenditionLabel) on the Look-alikes sheet. They are always on the All columns sheet.

.PARAMETER IncludeOneDrive
    Also scan OneDrive (personal, -my) sites. Off by default; it can add a lot of sites and time.

.PARAMETER IncludeHiddenColumns
    Include hidden system columns. Off by default.

.PARAMETER IncludeHiddenLists
    Include hidden system lists and libraries. Off by default.

.PARAMETER SkipManagedProperties
    Skip the PnP step that exports managed properties and their crawled-property mappings. The Managed
    properties sheet is then empty and the Mapped* columns are blank.

.PARAMETER InteractiveManagedProperties
    Export the managed properties by signing in as an admin instead of app-only. By default the script
    first tries app-only with the same certificate (needs SharePoint Sites.FullControl.All, application)
    and only falls back to an interactive sign-in if that fails. The interactive sign-in needs an ACTIVE
    SharePoint Administrator role in the tenant and the delegated SharePoint AllSites.FullControl permission.

.OUTPUTS
    <OutputFolder>\SPO-Inventory-<tenant>-<yyyyMMdd-HHmm>\SPO-Column-Inventory.xlsx, with one worksheet per result:
      Summary              run details and counts
      Look-alikes          columns whose internal or display name matches -Pattern, excluding Microsoft
                           built-in columns (including the "Sensitivity" label column) unless -IncludeBuiltIn
      Name conflicts       display names with more than one internal name, and the reverse
      Managed properties   tenant managed properties and their mapped crawled properties
      All columns          every column found (site columns and list/library columns)
      Errors               sites or lists that could not be read (only if any)
    Uses the ImportExcel module (installed automatically for the current user if missing); Excel itself
    is not required.

.EXAMPLE
    .\Get-SPOColumnInventory.ps1
    Uses the single *.tenant.json file in the script folder or its tenant subfolders (or offers a menu).

.EXAMPLE
    .\Get-SPOColumnInventory.ps1 -ConfigFile .\Contoso\contoso.tenant.json

.EXAMPLE
    .\Get-SPOColumnInventory.ps1 -TenantName contoso -ClientId <app-id> -CertificateThumbprint <thumbprint>

.EXAMPLE
    .\Get-SPOColumnInventory.ps1 -TenantName contoso -Cloud GCCHigh -ClientId <app-id> -CertificateThumbprint <thumbprint>

.NOTES
    Requires PowerShell 7.4+, PnP.PowerShell, Microsoft.Graph.Authentication, ImportExcel.
    Runs on Windows, macOS and Linux; on macOS and Linux use CertificatePath (.pfx), because
    thumbprints need the Windows certificate store.
    Each tenant needs its own app registration: Graph Sites.Read.All (application), SharePoint
    Sites.FullControl.All (application) for the app-only search-schema export, and optionally SharePoint
    AllSites.FullControl (delegated) for the interactive fallback; certificate uploaded, admin consent
    granted. See the guide section
    "Tenant-wide inventory of columns, crawled properties and managed properties".

    Tenant config file (<name>.tenant.json, in the tenant's subfolder). Command-line values override it:
      {
        "TenantName": "contoso",
        "TenantId": "00000000-0000-0000-0000-000000000000",
        "ClientId": "00000000-0000-0000-0000-000000000000",
        "CertificatePath": "~/SPO-Certs/contoso-inventory.pfx",
        "CertificateThumbprint": "",
        "Cloud": "Commercial"
      }
    Use CertificatePath (any OS) or CertificateThumbprint (Windows). Cloud and TenantId are optional;
    AdminUrl can also be set. The file holds no secrets: the .pfx password is prompted for at run time,
    and with a thumbprint the private key stays in the Windows certificate store.
#>

[CmdletBinding()]
param(
    [string]$ConfigFile,                                           # optional; a <name>.tenant.json file (see .NOTES)
    [string]$TenantName,                                           # e.g. contoso (from contoso.sharepoint.com)
    [string]$ClientId,
    [string]$CertificateThumbprint,                                # Windows only
    [string]$CertificatePath,                                      # .pfx file; any OS
    [SecureString]$CertificatePassword,
    [ValidateSet('Commercial', 'GCC', 'GCCHigh', 'DoD')]
    [string]$Cloud = 'Commercial',
    [string]$TenantId,                                             # optional; defaults to <TenantName>.onmicrosoft.com/.us
    [string]$AdminUrl,                                             # optional; overrides the URL built from TenantName
    [string]$OutputFolder,                                         # parent folder; default <tenant folder>\SPO-Inventories
    [string]$Pattern = 'sensitiv|classif|attribute|label|marking|eshare|guid|_x0020_',   # regex, case-insensitive
    [switch]$IncludeBuiltIn,                                       # keep Microsoft built-in columns on the Look-alikes sheet
    [switch]$IncludeOneDrive,                                      # include personal (-my) sites
    [switch]$IncludeHiddenColumns,                                 # include hidden system columns
    [switch]$IncludeHiddenLists,                                   # include hidden system lists
    [switch]$SkipManagedProperties,                                # skip the PnP step
    [switch]$InteractiveManagedProperties                          # skip the app-only attempt; sign in as an admin instead
)

$ErrorActionPreference = 'Stop'

# ---------- 0a. Tenant config file ----------
# If the connection values aren't all on the command line, read them from a <name>.tenant.json file.
# Folder layout: scripts at the root, one subfolder per tenant holding its config, certificate and output.
# With no -ConfigFile: *.tenant.json in the script folder or one level down; one is used, several give a menu.
$haveCliValues = $ClientId -and ($CertificateThumbprint -or $CertificatePath) -and ($TenantName -or $AdminUrl)
if (-not $ConfigFile -and -not $haveCliValues) {
    $found = @(Get-ChildItem -Path $PSScriptRoot -Filter '*.tenant.json' -File -Recurse -Depth 1 -ErrorAction SilentlyContinue | Sort-Object FullName)
    if ($found.Count -eq 1) {
        $ConfigFile = $found[0].FullName
    } elseif ($found.Count -gt 1) {
        Write-Host "Tenant config files found:" -ForegroundColor Cyan
        for ($k = 0; $k -lt $found.Count; $k++) { Write-Host ("  [{0}] {1}" -f ($k + 1), [System.IO.Path]::GetRelativePath($PSScriptRoot, $found[$k].FullName)) }
        $pick = Read-Host "Select a tenant (1-$($found.Count))"
        if ($pick -notmatch '^\d+$' -or [int]$pick -lt 1 -or [int]$pick -gt $found.Count) { throw "Invalid selection '$pick'." }
        $ConfigFile = $found[[int]$pick - 1].FullName
    }
}
$TenantFolder = $PSScriptRoot
if ($ConfigFile) {
    $hit = @($ConfigFile, (Join-Path $PSScriptRoot $ConfigFile)) | Where-Object { Test-Path -LiteralPath $_ -PathType Leaf } | Select-Object -First 1
    if (-not $hit) { throw "Config file not found: $ConfigFile" }
    $ConfigFile   = (Resolve-Path -LiteralPath $hit).Path
    $TenantFolder = Split-Path -Parent $ConfigFile
    Write-Host "Using tenant config: $ConfigFile" -ForegroundColor Cyan
    Write-Host "Tenant folder:       $TenantFolder" -ForegroundColor Gray
    $cfg = Get-Content -LiteralPath $ConfigFile -Raw | ConvertFrom-Json
    # Values given on the command line win over the file.
    foreach ($key in 'TenantName', 'TenantId', 'ClientId', 'CertificateThumbprint', 'CertificatePath', 'Cloud', 'AdminUrl') {
        if (-not $PSBoundParameters.ContainsKey($key) -and $cfg.PSObject.Properties[$key] -and $cfg.$key) {
            Set-Variable -Name $key -Value ([string]$cfg.$key)
        }
    }
}
if (-not $ClientId) { throw "ClientId is missing. Pass -ClientId or add it to a <name>.tenant.json file." }
if (-not $OutputFolder) { $OutputFolder = Join-Path $TenantFolder 'SPO-Inventories' }

# Certificate: a .pfx file (any OS) or a thumbprint in the Windows certificate store (Windows only).
$graphCert = $null
if ($CertificatePath) {
    if ($CertificatePath -match '^~[\\/]') { $CertificatePath = Join-Path $HOME $CertificatePath.Substring(2) }
    if (-not [System.IO.Path]::IsPathRooted($CertificatePath)) { $CertificatePath = Join-Path $TenantFolder $CertificatePath }
    if (-not (Test-Path -LiteralPath $CertificatePath -PathType Leaf)) { throw "Certificate file not found: $CertificatePath" }
    if (-not $CertificatePassword) {
        $CertificatePassword = Read-Host "Password for certificate $(Split-Path $CertificatePath -Leaf) (press Enter if none)" -AsSecureString
    }
    $graphCert = [System.Security.Cryptography.X509Certificates.X509Certificate2]::new($CertificatePath, $CertificatePassword)
} elseif ($CertificateThumbprint) {
    if (-not $IsWindows) { throw "CertificateThumbprint works only on Windows (certificate store). On macOS or Linux, set CertificatePath to a .pfx file." }
} else {
    throw "No certificate. Set CertificatePath (.pfx, any OS) or CertificateThumbprint (Windows) in the tenant config, or pass it on the command line."
}

# ---------- 0b. Tenant and cloud settings ----------
$cloudSettings = @{
    Commercial = @{ Graph = 'https://graph.microsoft.com';     MgEnv = 'Global';   PnPEnv = 'Production';       SpoDomain = 'sharepoint.com';    InitialDomain = 'onmicrosoft.com' }
    GCC        = @{ Graph = 'https://graph.microsoft.com';     MgEnv = 'Global';   PnPEnv = 'Production';       SpoDomain = 'sharepoint.com';    InitialDomain = 'onmicrosoft.com' }
    GCCHigh    = @{ Graph = 'https://graph.microsoft.us';      MgEnv = 'USGov';    PnPEnv = 'USGovernmentHigh'; SpoDomain = 'sharepoint.us';     InitialDomain = 'onmicrosoft.us' }
    DoD        = @{ Graph = 'https://dod-graph.microsoft.us';  MgEnv = 'USGovDoD'; PnPEnv = 'USGovernmentDoD';  SpoDomain = 'sharepoint-mil.us'; InitialDomain = 'onmicrosoft.us' }
}[$Cloud]

if (-not $TenantName -and $AdminUrl) {
    $TenantName = ([uri]$AdminUrl).Host.Split('.')[0] -replace '-admin$', ''
}
if (-not $TenantName) { throw "Provide -TenantName (e.g. 'contoso' for contoso.sharepoint.com) or -AdminUrl." }
if (-not $AdminUrl)  { $AdminUrl = "https://$TenantName-admin.$($cloudSettings.SpoDomain)" }
if (-not $TenantId)  { $TenantId = "$TenantName.$($cloudSettings.InitialDomain)" }
$TenantUrl = "https://$TenantName.$($cloudSettings.SpoDomain)"
$graphV1   = "$($cloudSettings.Graph)/v1.0"

Write-Host "Tenant: $TenantUrl  (cloud: $Cloud, tenant ID: $TenantId)" -ForegroundColor Cyan

$OutputFolder = Join-Path (Resolve-Path -LiteralPath (New-Item -ItemType Directory -Path $OutputFolder -Force).FullName) `
    ("SPO-Inventory-$TenantName-" + (Get-Date -Format 'yyyyMMdd-HHmm'))
New-Item -ItemType Directory -Path $OutputFolder -Force | Out-Null
$errors = [System.Collections.Generic.List[object]]::new()

# ---------- Graph helpers ----------
function Invoke-Graph([string]$Uri) {
    for ($attempt = 1; $attempt -le 6; $attempt++) {
        try {
            return Invoke-MgGraphRequest -Method GET -Uri $Uri -OutputType PSObject
        } catch {
            $status = $_.Exception.Response.StatusCode.value__
            if ($status -in 429, 503, 504 -and $attempt -lt 6) {
                $wait = [math]::Min(60, [math]::Pow(2, $attempt))
                Write-Warning "Throttled ($status). Waiting $wait s..."
                Start-Sleep -Seconds $wait
            } else { throw }
        }
    }
}

function Get-GraphAll([string]$Uri) {
    $items = [System.Collections.Generic.List[object]]::new()
    while ($Uri) {
        $page = Invoke-Graph $Uri
        if ($page.value) { $items.AddRange([object[]]$page.value) }
        $Uri = $page.'@odata.nextLink'
    }
    return $items
}

$columnTypes = 'text','choice','number','dateTime','boolean','lookup','personOrGroup','term',
               'calculated','currency','hyperlinkOrPicture','geolocation','thumbnail','contentApprovalStatus'
function Get-ColumnType($col) {
    foreach ($t in $columnTypes) { if ($col.PSObject.Properties[$t] -and $null -ne $col.$t) { return $t } }
    return 'other'
}

function New-ColumnRow($site, $listName, $listUrl, $listTemplate, $scope, $col, $siteColumnIds) {
    [pscustomobject]@{
        SiteUrl                 = $site.webUrl
        SiteName                = $site.displayName
        Scope                   = $scope                       # SiteColumn | ListColumn
        List                    = $listName
        ListTemplate            = $listTemplate
        ListUrl                 = $listUrl
        DisplayName             = $col.displayName
        InternalName            = $col.name
        ColumnId                = $col.id
        Type                    = Get-ColumnType $col
        Group                   = $col.columnGroup
        IsFromSiteColumn        = if ($scope -eq 'ListColumn') { $siteColumnIds.Contains($col.id) } else { $null }
        Hidden                  = $col.hidden
        ReadOnly                = $col.readOnly
        MappedCrawledProperty   = $null                        # crawled property actually used by a managed-property mapping
        MappedManagedProperties = $null                        # filled in after the PnP step
    }
}

# ---------- 1. Graph: sites, site columns, list columns ----------
Write-Host "Connecting to Microsoft Graph (app-only)..." -ForegroundColor Cyan
if ($graphCert) {
    Connect-MgGraph -ClientId $ClientId -TenantId $TenantId -Certificate $graphCert -Environment $cloudSettings.MgEnv -NoWelcome
} else {
    Connect-MgGraph -ClientId $ClientId -TenantId $TenantId -CertificateThumbprint $CertificateThumbprint -Environment $cloudSettings.MgEnv -NoWelcome
}

Write-Host "Enumerating sites..." -ForegroundColor Cyan
$rootSites = Get-GraphAll "$graphV1/sites/getAllSites?`$select=id,webUrl,displayName&`$top=999"
if (-not $IncludeOneDrive) { $rootSites = $rootSites | Where-Object { $_.webUrl -notmatch '-my\.sharepoint' } }
Write-Host "  $($rootSites.Count) site collections found." -ForegroundColor Gray

$queue   = [System.Collections.Generic.Queue[object]]::new()
$visited = [System.Collections.Generic.HashSet[string]]::new()
$rootSites | ForEach-Object { $queue.Enqueue($_) }
$rows = [System.Collections.Generic.List[object]]::new()
$n = 0

while ($queue.Count -gt 0) {
    $site = $queue.Dequeue()
    if (-not $visited.Add($site.id)) { continue }
    $n++
    Write-Host ("[{0}] {1}" -f $n, $site.webUrl)
    $base = "$graphV1/sites/$($site.id)"

    try {
        # subsites
        Get-GraphAll "$base/sites?`$select=id,webUrl,displayName" | ForEach-Object { $queue.Enqueue($_) }

        # site columns
        $siteCols = Get-GraphAll "$base/columns"
        $siteColumnIds = [System.Collections.Generic.HashSet[string]]::new()
        foreach ($c in $siteCols) {
            [void]$siteColumnIds.Add($c.id)
            if ($IncludeHiddenColumns -or -not $c.hidden) {
                $rows.Add((New-ColumnRow $site $null $null $null 'SiteColumn' $c $siteColumnIds))
            }
        }

        # lists and libraries
        $lists = Get-GraphAll "$base/lists?`$select=id,displayName,webUrl,list"
        foreach ($l in $lists) {
            if (-not $IncludeHiddenLists -and $l.list.hidden) { continue }
            try {
                foreach ($c in (Get-GraphAll "$base/lists/$($l.id)/columns")) {
                    if ($IncludeHiddenColumns -or -not $c.hidden) {
                        $rows.Add((New-ColumnRow $site $l.displayName $l.webUrl $l.list.template 'ListColumn' $c $siteColumnIds))
                    }
                }
            } catch {
                $errors.Add([pscustomobject]@{ SiteUrl = $site.webUrl; List = $l.displayName; Error = $_.Exception.Message })
            }
        }
    } catch {
        $errors.Add([pscustomobject]@{ SiteUrl = $site.webUrl; List = $null; Error = $_.Exception.Message })
    }
}
Write-Host "  $n sites scanned, $($rows.Count) columns collected." -ForegroundColor Gray

# ---------- 2. PnP: tenant managed properties ----------
$mpRows = @()
$crawledToMp = @{}
if (-not $SkipManagedProperties) {
    # A failure here must not lose the column inventory already collected: record it and carry on.
    # 1st choice: app-only with the same certificate (needs SharePoint Sites.FullControl.All, application).
    # Fallback: interactive sign-in (needs an ACTIVE SharePoint Administrator and delegated AllSites.FullControl).
    $mps = $null
    $mpErrors = @()
    if (-not $InteractiveManagedProperties) {
        Write-Host "Reading the tenant search schema with PnP (app-only, certificate)..." -ForegroundColor Cyan
        try {
            $pnpCert = if ($CertificatePath) { @{ CertificatePath = $CertificatePath; CertificatePassword = $CertificatePassword } } else { @{ Thumbprint = $CertificateThumbprint } }
            Connect-PnPOnline -Url $AdminUrl -ClientId $ClientId -Tenant $TenantId -AzureEnvironment $cloudSettings.PnPEnv @pnpCert
            $mps = @(Get-PnPSearchConfiguration -Scope Subscription -OutputFormat ManagedPropertyMappings)
        } catch {
            $mpErrors += "App-only: $($_.Exception.Message)"
            Write-Warning "App-only search-schema export failed ($($_.Exception.Message)). The app likely lacks SharePoint Sites.FullControl.All (application). Falling back to an interactive sign-in."
        }
    }
    if ($null -eq $mps) {
        Write-Host "Connecting to SharePoint admin with PnP (sign in as an active SharePoint Administrator)..." -ForegroundColor Cyan
        try {
            Connect-PnPOnline -Url $AdminUrl -ClientId $ClientId -Tenant $TenantId -AzureEnvironment $cloudSettings.PnPEnv -Interactive
            $mps = @(Get-PnPSearchConfiguration -Scope Subscription -OutputFormat ManagedPropertyMappings)
        } catch {
            $mpErrors += "Interactive: $($_.Exception.Message)"
            Write-Warning "Interactive search-schema export failed: $($_.Exception.Message)"
            Write-Warning "Usual causes: the signed-in account is not an ACTIVE SharePoint Administrator in this tenant (activate it in PIM), or the app lacks the delegated SharePoint AllSites.FullControl permission."
        }
    }
    if ($null -eq $mps) {
        $mps = @()
        Write-Warning "Managed properties were not exported. The column inventory is still written."
        $errors.Add([pscustomobject]@{ SiteUrl = $AdminUrl; List = 'Managed properties (Get-PnPSearchConfiguration)'; Error = ($mpErrors -join ' | ') })
    }
    $mpRows = foreach ($mp in $mps) {
        foreach ($cp in @($mp.Mappings)) {
            if ($cp) {
                if (-not $crawledToMp.ContainsKey($cp)) { $crawledToMp[$cp] = [System.Collections.Generic.List[string]]::new() }
                $crawledToMp[$cp].Add($mp.Name)
            }
        }
        [pscustomobject]@{
            ManagedProperty   = $mp.Name
            Type              = $mp.Type
            Aliases           = (@($mp.Aliases) -join '; ')
            CrawledProperties = (@($mp.Mappings) -join '; ')
        }
    }
    Write-Host "  $(@($mpRows).Count) tenant managed properties found." -ForegroundColor Gray
}

# ---------- 3. Join ----------
# A managed property can be mapped to the column's ows_ crawled property, or to the raw crawled property
# created from a Word custom document property with the same name as the column (e.g. eSHARE_GUID).
# Record which one actually matched, using the name as it appears in the search schema.
$crawledNames = @{}
foreach ($k in $crawledToMp.Keys) { $crawledNames[$k] = $k }
foreach ($r in $rows) {
    $hits = @(); $matched = @()
    foreach ($cp in @("ows_$($r.InternalName)", $r.InternalName)) {   # look up only; the name shown comes from the schema
        if ($crawledToMp.ContainsKey($cp)) {
            $hits    += $crawledToMp[$cp]
            $matched += $crawledNames[$cp]
        }
    }
    $r.MappedCrawledProperty   = ($matched | Select-Object -Unique) -join '; '
    $r.MappedManagedProperties = ($hits    | Select-Object -Unique) -join '; '
}

# ---------- 4. Look-alikes and conflicts ----------
# Microsoft built-in columns are left out of the look-alike report (they stay in columns.csv):
# internal names starting with "_" (including the "Sensitivity" label column, _DisplayName) plus a
# few known system columns.
$keepBuiltIn    = @()
$builtInNames   = @('TagEventDate', 'Created_x0020_Date', 'VideoRenditionLabel', 'WSGUID', 'WSEventSourceGUID')
function Test-BuiltIn($r) {
    if ($r.InternalName -in $keepBuiltIn) { return $false }
    return ($r.InternalName -like '_*') -or ($r.InternalName -in $builtInNames)
}
$lookalikes = $rows | Where-Object {
    ($_.InternalName -match $Pattern -or $_.DisplayName -match $Pattern) -and
    ($IncludeBuiltIn -or -not (Test-BuiltIn $_))
}

$conflicts = @()
$conflicts += $rows | Group-Object DisplayName | Where-Object { ($_.Group.InternalName | Select-Object -Unique).Count -gt 1 } |
    ForEach-Object { [pscustomobject]@{ Kind = 'SameDisplayName_DifferentInternalNames'; Name = $_.Name
                     Variants = (($_.Group.InternalName | Select-Object -Unique) -join '; '); Occurrences = $_.Count } }
$conflicts += $rows | Group-Object InternalName | Where-Object { ($_.Group.DisplayName | Select-Object -Unique).Count -gt 1 } |
    ForEach-Object { [pscustomobject]@{ Kind = 'SameInternalName_DifferentDisplayNames'; Name = $_.Name
                     Variants = (($_.Group.DisplayName | Select-Object -Unique) -join '; '); Occurrences = $_.Count } }

# ---------- 5. Output: one Excel workbook ----------
if (-not (Get-Module -ListAvailable -Name ImportExcel)) {
    Write-Host "Installing the ImportExcel module (current user)..." -ForegroundColor Cyan
    Install-Module ImportExcel -Scope CurrentUser -Force
}
Import-Module ImportExcel

$xlsx = Join-Path $OutputFolder 'SPO-Column-Inventory.xlsx'
if (Test-Path $xlsx) { Remove-Item $xlsx }

function Add-Sheet($Items, [string]$Sheet, [string]$Table, [string[]]$Props) {
    $data = @($Items)
    if ($data.Count -eq 0) { $data = @([pscustomobject]@{ Result = 'None found' }) }
    elseif ($Props) { $data = $data | Select-Object $Props }
    $data | Export-Excel -Path $xlsx -WorksheetName $Sheet -TableName $Table -TableStyle Medium2 `
        -AutoSize -MaxAutoSizeRows 500 -FreezeTopRow
}

$colProps = 'SiteName','SiteUrl','Scope','List','ListTemplate','DisplayName','InternalName','ColumnId','Type','Group',
            'IsFromSiteColumn','Hidden','ReadOnly','MappedCrawledProperty','MappedManagedProperties','ListUrl'

$summary = @(
    [pscustomobject]@{ Item = 'Tenant';                  Value = $TenantUrl }
    [pscustomobject]@{ Item = 'Cloud';                   Value = $Cloud }
    [pscustomobject]@{ Item = 'Run';                     Value = (Get-Date -Format 'yyyy-MM-dd HH:mm') }
    [pscustomobject]@{ Item = 'Sites scanned';           Value = $n }
    [pscustomobject]@{ Item = 'Columns';                 Value = $rows.Count }
    [pscustomobject]@{ Item = 'Look-alikes';             Value = @($lookalikes).Count }
    [pscustomobject]@{ Item = 'Name conflicts';          Value = @($conflicts).Count }
    [pscustomobject]@{ Item = 'Tenant managed properties'; Value = @($mpRows).Count }
    [pscustomobject]@{ Item = 'Errors';                  Value = $errors.Count }
    [pscustomobject]@{ Item = 'Look-alike pattern';      Value = $Pattern }
    [pscustomobject]@{ Item = 'Built-in columns in look-alikes'; Value = $(if ($IncludeBuiltIn) { 'Included (-IncludeBuiltIn)' } else { 'Excluded' }) }
    [pscustomobject]@{ Item = 'OneDrive sites';          Value = $(if ($IncludeOneDrive) { 'Included' } else { 'Excluded' }) }
    [pscustomobject]@{ Item = 'Note';                    Value = 'MappedCrawledProperty and MappedManagedProperties come from the tenant search schema (managed-property mappings). Crawled properties that are not mapped cannot be exported, so they are not listed; check the Search Schema page for those.' }
)

Add-Sheet $summary    'Summary'            'Summary'
Add-Sheet $lookalikes 'Look-alikes'        'Lookalikes'        $colProps
Add-Sheet $conflicts  'Name conflicts'     'NameConflicts'
Add-Sheet $mpRows     'Managed properties' 'ManagedProperties'
Add-Sheet $rows       'All columns'        'AllColumns'        $colProps
if ($errors.Count) { Add-Sheet $errors 'Errors' 'Errors' }

Write-Host ""
Write-Host "Done. Output in $OutputFolder" -ForegroundColor Green
Write-Host ("  Columns: {0}   Look-alikes: {1}   Name conflicts: {2}   Managed properties: {3}   Errors: {4}" -f `
    $rows.Count, @($lookalikes).Count, @($conflicts).Count, @($mpRows).Count, $errors.Count)
Write-Host ""
Write-Host "Look-alikes:" -ForegroundColor Cyan
$lookalikes | Select-Object SiteName, List, DisplayName, InternalName, Type, MappedCrawledProperty, MappedManagedProperties | Format-Table -AutoSize