SharePoint Online: busque todos los archivos que superen la limitación de longitud máxima de URL mediante PowerShell

Los archivos de Microsoft Office Excel almacenados en carpetas profundas y estructuras de subcarpetas con nombres largos a menudo generan un error cuando exceden la longitud máxima de la URL del archivo de 218 caracteres, como en mi otro artículo SharePoint Online: limitación de la longitud de la URL de 218 caracteres en archivos de Excel. . Por eso, queríamos escanear de forma proactiva todo el entorno de SharePoint Online en busca de archivos de Excel con URL largas.

PowerShell para obtener todos los archivos de Excel que superen la URL del límite máximo de caracteres

Este script de PowerShell escanea archivos de Excel en todas las bibliotecas de documentos en una colección de sitios determinada, verifica si la longitud de la URL de algún archivo excede el límite dado y exporta el informe de salida a un formato CSV.

Import-Module Microsoft.Online.SharePoint.PowerShell -DisableNameChecking
 
#Function to scan all files with long file names in a site
Function Scan-SPOLongFilePath($SiteURL)
{   
    Try {
        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)
  
        #Get the web from given URL and its subsites
        $Web = $Ctx.web
        $Ctx.Load($Web)
        $Ctx.Load($Web.Lists)
        $Ctx.Load($web.Webs)
        $Ctx.executeQuery()
          
        #Arry to Skip System Lists and Libraries
        $SystemLists = @("Converted Forms", "Master Page Gallery", "Customized Reports", "Form Templates", "List Template Gallery", "Theme Gallery",
                            "Reporting Templates", "Solution Gallery", "Style Library", "Web Part Gallery","Site Assets", "wfpub", "Site Pages", "Images")
      
        Write-host -f Yellow "Processing Site: $SiteURL"
  
        #Prepare the CAML query
        $Query = New-Object Microsoft.SharePoint.Client.CamlQuery
        $Query.ViewXml = "@
        <View Scope='RecursiveAll'>
            <Query>
                <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
            </Query>
            <RowLimit Paged='TRUE'>2000</RowLimit>
        </View>"

        #Filter Document Libraries to Scan 
        $Lists = $Web.Lists | Where {$_.BaseType -eq "DocumentLibrary" -and $_.Hidden -eq $false -and $SystemLists -notcontains $_.Title}
        
        #Loop through each document library
        Foreach ($List in $Lists)
        {
            $Counter=1
            #Batch Process List items 
            Do {
                $ListItems = $List.GetItems($Query)
                $Ctx.Load($ListItems)
                $Ctx.ExecuteQuery() 
                $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
             
                If($ListItems.count -gt 0)
                {
                    Write-host -f Cyan "`t Processing Document Library: '$($List.Title)', Auditing $($ListItems.Count) Item(s)"
  
                    $DocumentInventory = @()
                    #Iterate through each file and get data
                    Foreach($Item in $ListItems | Where {$_.FileSystemObjectType -eq "File"})
                    {
                        #Display a Progress bar
                        Write-Progress -Activity "Scanning Files in the Library" -Status "Testing if the file has long URL '$($Item.FieldValues.FileRef)' ($Counter of $($List.ItemCount))" -PercentComplete (($Counter / $List.ItemCount) * 100) 

                        $File = $Item.File
                        $Ctx.Load($File)
                        $Ctx.ExecuteQuery()
 
                        #calculate the Absolute encoded URL of the File
                        If($Web.ServerRelativeUrl -eq "/")
                        {
                            $AbsoluteURL=  $("{0}{1}" -f $Web.Url, $ListItem.FieldValues["FileRef"])
                        }
                        else
                        {
                            $AbsoluteURL=  $("{0}{1}" -f $Web.Url.Replace($Web.ServerRelativeUrl,''), $Item.FieldValues["FileRef"])
                        }
                        $AbsoluteURL = [uri]::EscapeUriString($AbsoluteURL)
 
                        If($AbsoluteURL.length -gt $MaxUrlLength)
                        {
                            Write-host "`t`tFound a Long File URL at '$AbsoluteURL'" -f Green
                            #Collect document data
                            $DocumentData = New-Object PSObject
                            $DocumentData | Add-Member NoteProperty SiteURL($SiteURL)
                            $DocumentData | Add-Member NoteProperty DocLibraryName($List.Title)
                            $DocumentData | Add-Member NoteProperty FileName($File.Name)
                            $DocumentData | Add-Member NoteProperty FileURL($AbsoluteURL)
                            $DocumentData | Add-Member NoteProperty CreatedBy($Item["Author"].Email)
                            $DocumentData | Add-Member NoteProperty CreatedOn($File.TimeCreated)
                            $DocumentData | Add-Member NoteProperty ModifiedBy($Item["Editor"].Email)
                            $DocumentData | Add-Member NoteProperty LastModifiedOn($File.TimeLastModified)
                            $DocumentData | Add-Member NoteProperty Size-KB([math]::Round($File.Length/1KB))
 
                            #Add the result to an Array
                            $DocumentInventory += $DocumentData
                        }
                        $Counter++
                    }
                }
            }While($Query.ListItemCollectionPosition -ne $Null)
                
            #Export the result to CSV file
            $DocumentInventory | Export-CSV $ReportOutput -NoTypeInformation -Append
        }
        #Iterate through all subsites of the current site
        ForEach ($Subweb in $Web.Webs)
        {
            #Call the function recursively
            Scan-SPOLongFilePath($Subweb.url)
        }
    }
    Catch {
        write-host -f Red "Error Scaning Document Library Inventory!" $_.Exception.Message
    }
}

#Set Parameters
$SiteURL= "https://crescent.sharepoint.com/sites/Marketing"
$ReportOutput="C:\temp\LongFileNames.csv"
$FileExtension = "xlsx"
$MaxUrlLength = 259
 
#Get Credentials to connect
$Cred = Get-Credential
 
#Delete the Output Report if exists
If (Test-Path $ReportOutput) { Remove-Item $ReportOutput }
 
#Call the function 
Scan-SPOLongFilePath $SiteURL

Para listas y bibliotecas con <5000 elementos, este script funciona bien. Recodificamos en PnP PowerShell para manejar bibliotecas de documentos más grandes.

PnP PowerShell para auditar la colección de sitios de SharePoint Online en busca de URL largas

Este script PnP PowerShell escanea la colección de sitios determinada en busca de archivos y carpetas largos y exporta sus hallazgos a un archivo CSV.

#Parameters
$SiteURL = "https://crescent.SharePoint.com/sites/Marketing"
$MaxUrlLength = 218
$CSVPath = "C:\Temp\LongURLInventory.csv"
$global:LongURLInventory = @()
$Pagesize = 2000 
 
#Function to scan and collect long files
Function Get-PnPLongURLInventory
{
    [cmdletbinding()]
    param([parameter(Mandatory = $true, ValueFromPipeline = $true)] $Web)
 
    Write-host "Scanning Files with Long URL in Site '$($Web.URL)'" -f Yellow
    If($Web.ServerRelativeUrl -eq "/")
    {
        $TenantURL= $Web.Url
    }
    Else
    {
        $TenantURL= $Web.Url.Replace($Web.ServerRelativeUrl,'')
    }
    
    #Get All Large Lists from the Web - Exclude Hidden and certain lists
    $ExcludedLists = @("Form Templates", "Preservation Hold Library","Site Assets", "Pages", "Site Pages", "Images",
                            "Site Collection Documents", "Site Collection Images","Style Library") 
                             
    #Get All Document Libraries from the Web
    $Lists= Get-PnPProperty -ClientObject $Web -Property Lists
    $Lists | Where-Object {$_.BaseType -eq "DocumentLibrary" -and $_.Hidden -eq $false -and $_.Title -notin $ExcludedLists -and $_.ItemCount -gt 0} -PipelineVariable List | ForEach-Object {
        #Get Items from List   
        $global:counter = 0;
        $ListItems = Get-PnPListItem -List $_ -PageSize $Pagesize -Fields Author, Created, File_x0020_Type -ScriptBlock { Param($items) $global:counter += $items.Count; Write-Progress -PercentComplete ($global:Counter / ($_.ItemCount) * 100) -Activity "Getting List Items of '$($_.Title)'" -Status "Processing Items $global:Counter to $($_.ItemCount)";}
        $LongListItems = $ListItems | Where { ([uri]::EscapeUriString($_.FieldValues.FileRef).Length + $TenantURL.Length ) -gt $MaxUrlLength }
        Write-Progress -Activity "Completed Retrieving Items from List $($List.Title)" -Completed
                
        If($LongListItems.count -gt 0)
        {
            #Get Root folder of the List
            $Folder = Get-PnPProperty -ClientObject $_ -Property RootFolder
            Write-host "`tFound '$($LongListItems.count)' Items with Long URLs at '$($Folder.ServerRelativeURL)'" -f Green

            #Iterate through each long url item and collect data           
            ForEach($ListItem in $LongListItems)
            {
                #Calculate Encoded Full URL of the File
                $AbsoluteURL =  "$TenantURL$($ListItem.FieldValues.FileRef)"
                $EncodedURL = [uri]::EscapeUriString($AbsoluteURL)
 
                    #Collect document data
                    $global:LongURLInventory += New-Object PSObject -Property ([ordered]@{
                        SiteName  = $Web.Title
                        SiteURL  = $Web.URL
                        LibraryName = $List.Title
                        LibraryURL = $Folder.ServerRelativeURL
                        ItemName = $ListItem.FieldValues.FileLeafRef
                        Type = $ListItem.FileSystemObjectType
                        FileType = $ListItem.FieldValues.File_x0020_Type
                        AbsoluteURL = $AbsoluteURL
                        EncodedURL = $EncodedURL
                        UrlLength = $EncodedURL.Length                      
                        CreatedBy = $ListItem.FieldValues.Author.LookupValue
                        CreatedByEmail  = $ListItem.FieldValues.Author.Email
                        CreatedAt = $ListItem.FieldValues.Created
                        ModifiedBy = $ListItem.FieldValues.Editor.LookupValue
                        ModifiedByEmail = $ListItem.FieldValues.Editor.Email
                        ModifiedAt = $ListItem.FieldValues.Modified                        
                    })
                }
            }
        }
}

#Connect to Site collection
Connect-PnPOnline -Url $SiteURL -Interactive
  
#Call the Function for all Webs
Get-PnPSubWeb -Recurse -IncludeRootWeb | ForEach-Object { Get-PnPLongURLInventory $_ }
 
#Export Documents Inventory to CSV
$Global:LongURLInventory | Export-Csv $CSVPath -NoTypeInformation
Write-host "Report has been Exported to '$CSVPath'"  -f Magenta

¿Qué tal obtener este inventario para todo el inquilino de SharePoint Online?

Esta vez, analizaremos todos los archivos de todas las colecciones de sitios de todo el inquilino en busca de todos los archivos con URL de archivo largas.

#Parameters
$Domain =  "crescent" #Domain Name in SharePoint Online. E.g. https://Crescent.SharePoint.com
$MaxUrlLength = 218
$CSVFile = "C:\Temp\LongURLInventory.csv"
$Pagesize = 2000
 
#Frame Tenant URL and Tenant Admin URL
$TenantURL = "https://$Domain.SharePoint.com"
$TenantAdminURL = "https://$Domain-Admin.SharePoint.com"
   
#Function to scan and collect long files
Function Get-PnPLongURLInventory
{
    [cmdletbinding()]
    param([parameter(Mandatory = $true, ValueFromPipeline = $true)] $Web)
  
    Write-host "Scanning Files with Long URL in Site '$($Web.URL)'" -f Yellow
 
    #Get All Large Lists from the Web - Exclude Hidden and certain lists
    $ExcludedLists = @("Form Templates", "Preservation Hold Library","Site Assets", "Pages", "Site Pages", "Images",
                            "Site Collection Documents", "Site Collection Images","Style Library") 
                              
    #Get All Document Libraries from the Web
    $Lists= Get-PnPProperty -ClientObject $Web -Property Lists
    $Lists | Where-Object {$_.BaseType -eq "DocumentLibrary" -and $_.Hidden -eq $false -and $_.Title -notin $ExcludedLists -and $_.ItemCount -gt 0} -PipelineVariable List | ForEach-Object {
        #Get Items from List   
        $global:counter = 0;
        $LongURLInventory = @()
        $ListItems = Get-PnPListItem -List $_ -PageSize $Pagesize -Fields Author, Created, File_x0020_Type -ScriptBlock { Param($items) $global:counter += $items.Count; Write-Progress -PercentComplete ($global:Counter / ($_.ItemCount) * 100) -Activity "Getting List Items of '$($_.Title)'" -Status "Processing Items $global:Counter to $($_.ItemCount)";}
        $LongListItems = $ListItems | Where { ([uri]::EscapeUriString($_.FieldValues.FileRef).Length + $TenantURL.Length ) -gt $MaxUrlLength }                
        If($LongListItems.count -gt 0)
        {
            #Get Root folder of the List
            $Folder = Get-PnPProperty -ClientObject $_ -Property RootFolder
            Write-host "`tFound '$($LongListItems.count)' Item(s) with Long URLs at '$($Folder.ServerRelativeURL)'" -f Green
 
            #Iterate through each long url item and collect data           
            ForEach($ListItem in $LongListItems)
            {
                #Calculate Encoded Full URL of the File
                $AbsoluteURL =  "$TenantURL$($ListItem.FieldValues.FileRef)"
                $EncodedURL = [uri]::EscapeUriString($AbsoluteURL)
  
                    #Collect document data
                    $LongURLInventory += New-Object PSObject -Property ([ordered]@{
                        SiteName  = $Web.Title
                        SiteURL  = $Web.URL
                        LibraryName = $List.Title
                        LibraryURL = $Folder.ServerRelativeURL
                        ItemName = $ListItem.FieldValues.FileLeafRef
                        Type = $ListItem.FileSystemObjectType
                        FileType = $ListItem.FieldValues.File_x0020_Type
                        AbsoluteURL = $AbsoluteURL
                        EncodedURL = $EncodedURL
                        UrlLength = $EncodedURL.Length                      
                        CreatedBy = $ListItem.FieldValues.Author.LookupValue
                        CreatedByEmail  = $ListItem.FieldValues.Author.Email
                        CreatedAt = $ListItem.FieldValues.Created
                        ModifiedBy = $ListItem.FieldValues.Editor.LookupValue
                        ModifiedByEmail = $ListItem.FieldValues.Editor.Email
                        ModifiedAt = $ListItem.FieldValues.Modified                        
                    })
                }
                #Export Documents Inventory to CSV
                $LongURLInventory | Export-Csv $CSVFile -NoTypeInformation -Append
            }
            Write-Progress -Activity "Completed Exporting Long URL Items from List $($_.Title)" -Completed
        }
}
 
#Connect to Admin Center
Connect-PnPOnline -Url $TenantAdminURL -Interactive
 
#Delete the Output report file if exists
If (Test-Path $CSVFile) { Remove-Item $CSVFile }
 
#Get All Site collections - Filter BOT and MySite Host
$Sites = Get-PnPTenantSite -Filter "Url -like '$TenantURL'"
   
#Iterate through all site collections
$Sites | ForEach-Object {
    #Connect to each site collection
    Connect-PnPOnline -Url $_.URL -Interactive

    #Call the Function for Webs
    Get-PnPSubWeb -Recurse -IncludeRootWeb | ForEach-Object { Get-PnPLongURLInventory $_ }
}

Tenga en cuenta que, aunque la documentación de Microsoft dice que tiene 218 caracteres, ¡descubrí que hasta 259 funciona bien!