SharePoint Online: Export Document Library Metadata to Excel

Requirement: Export SharePoint Online Document Library Metadata to a CSV file.

Export Document Library Metadata to CSV using PowerShell:

To export a document library’s metadata into Excel, You can use: “Export to Excel” button on any SharePoint Online library. However, this functionality doesn’t give you details such as: Number of Versions, Filesize, etc. Here is the PowerShell CSOM script to extract the metadata of all documents under a specific document library and export them to Excel.

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\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 

This PowerShell script for SharePoint Online gets list of files in document library and export all documents metadata into a CSV file.

export sharepoint online document metadata to CSV

If you need to get all documents from a SharePoint Online site collection, use: SharePoint Online: Get All Documents Inventory in a Site Collection using PowerShell

Export Documents Inventory to CSV using PnP PowerShell:

Similarly, you can use PnP PowerShell to Export documents inventory from 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 -Credential (Get-Credential)

#Array to store results
$Results = @()

#Get all Items from the document library with given fields
$ListItems = Get-PnPListItem -List $ListName -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

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

4 thoughts on “SharePoint Online: Export Document Library Metadata to Excel

  • September 7, 2021 at 10:35 PM

    I know this is two years ago, but…

    We are using Veeam Backup for M365, and it has the issue where it is able to back up the metadata, but it is not able to restore the metadata correctly. They say this is a limitation of Microsoft’s API’s.

    If one were to use your code above, is there a complimentary piece of code that lets you restore the metadata? Because a backup is good, but being able to restore is better.

    Thanks,

    Reply
  • January 16, 2019 at 7:07 PM

    when I run under my Sharepoint Online I get the following error

    PS C:365itclient> C:UsersthiagoDocuments_thiagoSPO-Export Document Library Metadata to Excel.ps1
    Processing Folder: /Current Clients
    Extracting Metadata from: T&B – MEDIA LIST.xlsx
    Error Getting Metadata from Library! Exception calling “ExecuteQuery” with “0” argument(s): “User cannot be found.”

    Reply
    • February 18, 2019 at 8:47 PM

      That’s because: User in “Created By” or “Modified By” of the Document is either deleted or disabled from Active Directory (So they become orphans in SharePoint!) To mitigate, Remove Line#20 and 21 ($File.Author, $File.ModifiedBy) and use: $Item[“Author”].Email, $Item[“Editor”].Email

      Reply

Leave a Reply