网站搜索

SharePoint Online：使用 PowerShell 查找超过最大 URL 长度限制的所有文件

存储在深层文件夹、名称较长的子文件夹结构中的 Microsoft Office Excel 文件在超过 218 个字符的最大文件 URL 长度时通常会导致错误，如我的另一篇文章 SharePoint Online：Excel 文件中 218 个字符的 URL 长度限制。因此，我们希望主动扫描整个 SharePoint Online 环境以查找具有长 URL 的 Excel 文件。

PowerShell 获取所有超出最大字符限制 URL 的 Excel 文件

此 PowerShell 脚本扫描给定网站集中所有文档库中的 Excel 文件，检查是否有任何文件 URL 长度超过给定限制，并将输出报告导出为 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

对于包含 < 5000 个项目的列表和库，此脚本可以正常工作。让我们在 PnP PowerShell 中重新编码以处理更大的文档库。

PnP PowerShell 用于审核 SharePoint Online 网站集的长 URL

此 PnP PowerShell 脚本扫描给定网站集的长文件和文件夹，并将其结果导出到 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

为整个 SharePoint Online 租户获取此库存怎么样？

这次，我们扫描整个租户中所有网站集中的所有文件，以查找具有长文件 URL 的所有文件。

在执行此脚本之前，请确保您对租户中的所有网站集具有管理员权限。否则，您将收到访问被拒绝“远程服务器返回错误：(403) 禁止”错误！如何将网站集管理员添加到 SharePoint Online 租户中的所有网站？

#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 $_ }
}

请注意，虽然 Microsoft 文档说它是 218 个字符，但我发现最多 259 个字符就可以了！

相关文章