SharePoint Online：将文档库元数据导出到 Excel

要求：将 SharePoint Online 文档库元数据导出到 CSV 文件。

使用 PowerShell 将文档库元数据导出到 CSV：

您是否需要将 SharePoint Online 文档库元数据导出到 Excel？也许您需要创建报告或在另一个应用程序中使用数据。没问题！您可以轻松地将元数据从 SharePoint Online 中的任何文档库导出到 Excel。在本文中，我们将向您展示如何操作。

要将文档库的元数据导出到 Excel，您可以使用任何 SharePoint Online 库上的“导出到 Excel”按钮。但是，此功能不会为您提供版本数、文件大小等详细信息。以下是 PowerShell CSOM 脚本，用于提取特定文档库下所有文档的元数据并将其导出到 Excel：

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
   
#Function to get all files of a folder
Function Get-FilesFromFolder([Microsoft.SharePoint.Client.Folder]$Folder)
{
    Write-host -f Yellow "Processing Folder:"$Folder.ServerRelativeUrl

    #Get All Files of the Folder
    $Ctx.load($Folder.files)
    $Ctx.ExecuteQuery()
    
    $DataCollection = @()
    ForEach ($File in $Folder.files)
    {
        #Get Metadata associated with the File
        $ListItem = $File.ListItemAllFields
        $ctx.Load($ListItem)
        $ctx.Load($File.Author)
        $ctx.Load($File.ModifiedBy)
        $Ctx.ExecuteQuery()

        Write-host -f Green "Extracting Metadata from:"$File.Name
        $Data = New-Object PSObject
        $Data | Add-Member NoteProperty Name($File.Name)
        $Data | Add-Member NoteProperty Status($ListItem["Status"])
        $Data | Add-Member NoteProperty CreatedBy($File.Author.Title)
        $Data | Add-Member NoteProperty Size($File.Length/1KB)
        $Data | Add-Member NoteProperty Versions($File.MajorVersion)
        $Data | Add-Member NoteProperty CreatedOn($File.TimeCreated)
        $Data | Add-Member NoteProperty ModifiedBy($File.ModifiedBy.Title) #LoginName
        $Data | Add-Member NoteProperty LastModifiedOn($File.TimeLastModified)
        $Data | Add-Member NoteProperty URL($SiteURL+$File.ServerRelativeUrl)         
        $DataCollection += $Data
    }
    #Export the metadata to CSV
    $DataCollection | Export-Csv $ReportOutput -Append -NoTypeInformation
        
    #Recursively Call the function to get files of all folders and SubFolders
    $Ctx.load($Folder.Folders)
    $Ctx.ExecuteQuery()

    #Exclude "Forms" system folder and iterate through each folder
    ForEach($SubFolder in $Folder.Folders | Where {$_.Name -ne "Forms"})
    {
        Get-FilesFromFolder -Folder $SubFolder
    }
}

Function Extract-SPODocLibraryMetaData()
{
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $LibraryName
    )
    Try {
        #Setup Credentials to connect
        $Cred = Get-Credential
        $Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)
    
        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Cred

        #Get the Library and Its Root Folder
        $Library=$Ctx.web.Lists.GetByTitle($LibraryName)
        $Ctx.Load($Library)
        $Ctx.Load($Library.RootFolder)
        $Ctx.ExecuteQuery()

        #Delete the Output Report, if exists
        If(Test-Path $ReportOutput) {  Remove-Item $ReportOutput }

        #Call the function to get Files of the Root Folder
        Get-FilesFromFolder -Folder $Library.RootFolder

     }
    Catch {
        write-host -f Red "Error Getting Metadata from Library!" $_.Exception.Message
    }
}
#Config Parameters
$SiteURL= "https://crescent.sharepoint.com"
$LibraryName="Project Docs"
$ReportOutput ="C:\Temp\DocMetadata.csv"

#Call the function to get list items from folder
Extract-SPODocLibraryMetaData -SiteURL $SiteURL -LibraryName $LibraryName 

此适用于 SharePoint Online 的 PowerShell 脚本获取文档库中的文件列表，并将所有文档元数据导出到 CSV 文件中。

如果需要从 SharePoint Online 网站集中获取所有文档，请使用：SharePoint Online：使用 PowerShell 获取网站集中的所有文档清单

使用 PnP PowerShell 将文档清单导出为 CSV

同样，您可以使用 PnP PowerShell 从 SharePoint Online 导出文档清单：

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/marketing"
$ListName= "Documents"
$ReportOutput = "C:\Temp\DocInventory.csv"

#Connect to SharePoint Online site
Connect-PnPOnline $SiteURL -Interactive

#Array to store results
$Results = @()

#Get all Items from the document library with given fields
$ListItems = Get-PnPListItem -List $ListName -PageSize 500 -Fields "FileLeafRef", "SMTotalFileStreamSize", "FileDirRef","Author","Created","File_x0020_Type"

#Iterate through each item
Foreach ($Item in $ListItems) 
{
    #Filter Files only
    If (($Item.FileSystemObjectType) -eq "File")
    {
        $Results += New-Object PSObject -Property ([ordered]@{
            FileName          = $Item["FileLeafRef"]
            FileType          = $Item["File_x0020_Type"]            
            RootFolder        = $Item["FileDirRef"]
            RelativeURL       = $Item["FileRef"]
            FileSize          = ($Item["SMTotalFileStreamSize"]) 
            CreatedBy         = $Item["Author"].Email
            CreatedOn         = $Item["Created"]
        })
    }
}

#Export the results
$Results
$Results | Export-Csv -Path $ReportOutput -NoTypeInformation

总之，可以将文档库中的元数据导出到 SharePoint Online 中的 Excel，以方便在 SharePoint 环境之外使用元数据。通过在 Excel 中对数据进行排序、筛选和分析的功能，用户可以更深入地了解他们在 SharePoint 中管理的内容，并使用此信息做出更明智的决策。