Client Side Object Model (CSOM) CSV Document Library File PnP PowerShell PowerShell SharePoint SharePoint Online 

SharePoint Online: PowerShell to List All Files in a Document Library

Salaudeen Rajack 17 Comments , , , , , , , ,

Requirement: List All Documents in a SharePoint Online Document Library using PowerShell.

SharePoint Online PowerShell to get All Files from a Document Library

SharePoint Online: PowerShell to Get List of Files in Document Library

There may be times when you need to get a list of all the documents in a SharePoint Online document library. This can be helpful if you need to create a report of all the files in a library, just want an overview of the amount of data stored in your library, or if you need to move or delete all/some files in the library. In this post, we will be going over how to retrieve a list of all documents in a SharePoint Online document library. You can then export your results into a CSV format so that they are easier to work on as the report contains data points such as file extension, date modified, author, etc.

You can also use the “Export to Excel” feature to get a list of all files and folders from a SharePoint Online document library! SharePoint Online: Get Document Library Inventory using PowerShell

Here is the SharePoint Online PowerShell to list all documents: 

#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-AllFilesFromFolder([Microsoft.SharePoint.Client.Folder]$Folder)
{
    #Get All Files of the Folder
    $Ctx =  $Folder.Context
    $Ctx.load($Folder.files)
    $Ctx.ExecuteQuery()
 
    #Get all files in Folder
    ForEach ($File in $Folder.files)
    {
        #Get the File Name or do something
        Write-host -f Green $File.Name
    }
         
    #Recursively Call the function to get files of all folders
    $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-AllFilesFromFolder -Folder $SubFolder
    }
}
 
#powershell list all documents in sharepoint online library
Function Get-SPODocumentLibraryFiles()
{
    param
    (
        [Parameter(Mandatory=$true)] [string] $SiteURL,
        [Parameter(Mandatory=$true)] [string] $LibraryName,
        [Parameter(Mandatory=$true)] [System.Management.Automation.PSCredential] $Credential
    )
    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 Library and Its Root Folder
        $Library=$Ctx.web.Lists.GetByTitle($LibraryName)
        $Ctx.Load($Library)
        $Ctx.Load($Library.RootFolder)
        $Ctx.ExecuteQuery()
 
        #Call the function to get Files of the Root Folder
        Get-AllFilesFromFolder -Folder $Library.RootFolder 
     }
    Catch {
        write-host -f Red "Error:" $_.Exception.Message
    }
}
#Config Parameters
$SiteURL= "https://crescent.sharepoint.com/Sites/marketing"
$LibraryName="Documents"

#Get Credentials to connect
$Cred = Get-Credential
 
#Call the function to Get All Files from a document library
Get-SPODocumentLibraryFiles -SiteURL $SiteURL -LibraryName $LibraryName -Credential $Cred

This PowerShell gets a list of files in a SharePoint Online document library. While the above script works fine for smaller libraries with < 5000 items in it, It gives the “The attempted operation is prohibited because it exceeds the list view threshold.” error on larger libraries. So here is the PowerShell script to batch process list items to mitigate the threshold exceeded issue.

PowerShell to List All Documents in SharePoint Online

Here is the PowerShell for SharePoint Online to get a list of files in the document library: 

#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"
     
#Config Parameters
$SiteURL= "https://crescent.sharepoint.com/sites/marketing"
$LibraryName = "Shared Documents"
$CSVPath = "C:\Temp\DocumentsInventory.csv"
 
#Get Credentials to connect
$Cred = Get-Credential
 
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 and List
    $Web = $Ctx.Web
    $Ctx.Load($Web)
    $List = $Web.Lists.GetByTitle($LibraryName)
    $Ctx.Load($List)
    $Ctx.ExecuteQuery()

    #Prepare the 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>"
 
    $Counter=1
    $DataCollection = @()
    #Batch Process items
    Do {
        #powershell sharepoint online list all documents
        $ListItems = $List.GetItems($Query) 
        $Ctx.Load($ListItems)
        $Ctx.ExecuteQuery()
        $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition

        #Iterate through each document in the library
        ForEach($ListItem in $ListItems| Where {$_.FileSystemObjectType -eq "File"})
        {
            #Display a Progress bar
            Write-Progress -Activity "Scanning Files in the Library" -Status "Collection file's Inventory '$($Item.FieldValues.FileRef)' ($Counter of $($List.ItemCount))" -PercentComplete (($Counter / $List.ItemCount) * 100)

            #Collect data        
            $DataCollection += New-Object PSObject -Property ([Ordered] @{
                FileName    = $ListItem.FieldValues["FileLeafRef"]
                RelativeURL = $ListItem.FieldValues["FileRef"]
                CreatedBy   = $ListItem.FieldValues["Created_x0020_By"]
                CreatedOn   = $ListItem.FieldValues["Created"]
                ModifiedBy  = $ListItem.FieldValues["Modified_x0020_By"]
                ModifiedOn  = $ListItem.FieldValues["Modified"]
                FileSize    = $ListItem.FieldValues["File_x0020_Size"]
            })
            $Counter++
        }
    }While($Query.ListItemCollectionPosition -ne $Null)

    #Export Documents data to CSV
    $DataCollection | Export-Csv -Path $CSVPath -Force -NoTypeInformation
    Write-host -f Green "Documents Data Exported to CSV!"
}
Catch {
    write-host -f Red "Error:" $_.Exception.Message
}

This PowerShell lists all files and exports the data to a CSV report!

PnP PowerShell for SharePoint Online to List All Documents Inventory

Use the PnP PowerShell cmdlet Get-PnPListItem to get all documents in a given document library, with each file’s file name and server relative path.

#Set Variables
$SiteURL= "https://crescent.sharepoint.com/sites/Marketing"
$ListName="Documents"
 
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive

#Get All Files from the document library - In batches of 500
$ListItems = Get-PnPListItem -List $ListName -PageSize 500 | Where {$_.FileSystemObjectType -eq "File"}
 
#Loop through all documents
[email protected]()
ForEach($Item in $ListItems)
{
    #Collect Documents Data
    $DocumentsData += New-Object PSObject -Property @{
    FileName = $Item.FieldValues['FileLeafRef']
    FileURL = $Item.FieldValues['FileRef']
    }
}
#sharepoint online get all files in document library powershell
$DocumentsData

Get All Documents Inventory from a SharePoint Online Document Library and Export to a CSV

Let’s add some progress bar to make it interactive and get all documents inventory list from a SharePoint Online document library and export it to a CSV file:

#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/mexico"
$ListName= "Documents"
$ReportOutput = "C:\Temp\mex-DocInventory.csv"
$Pagesize = 500
  
#Connect to SharePoint Online site
Connect-PnPOnline $SiteURL -Interactive

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

#Array to store results
$Results = @()
  
#Get all Documents from the document library
$List  = Get-PnPList -Identity $ListName
$global:counter = 0;
$ListItems = Get-PnPListItem -List $ListName -PageSize $Pagesize -Fields Author, Editor, Created, File_x0020_Type -ScriptBlock `
        { Param($items) $global:counter += $items.Count; Write-Progress -PercentComplete ($global:Counter / ($List.ItemCount) * 100) -Activity `
             "Getting Documents from Library '$($List.Title)'" -Status "Getting Documents data $global:Counter of $($List.ItemCount)";} | Where {$_.FileSystemObjectType -eq "File"}
 
$ItemCounter = 0
#Iterate through each item
Foreach ($Item in $ListItems)
{
        $Results += New-Object PSObject -Property ([ordered]@{
            Name              = $Item["FileLeafRef"]
            Type              = $Item.FileSystemObjectType
            FileType          = $Item["File_x0020_Type"]
            RelativeURL       = $Item["FileRef"]
            CreatedByEmail    = $Item["Author"].Email
            CreatedOn         = $Item["Created"]
            Modified          = $Item["Modified"]
            ModifiedByEmail   = $Item["Editor"].Email
        })
    $ItemCounter++
    Write-Progress -PercentComplete ($ItemCounter / ($List.ItemCount) * 100) -Activity "Exporting data from Documents $ItemCounter of $($List.ItemCount)" -Status "Exporting Data from Document '$($Item['FileLeafRef'])"         
}
 
#Export the results to CSV
$Results | Export-Csv -Path $ReportOutput -NoTypeInformation
  
Write-host "Document Library Inventory Exported to CSV Successfully!"

As the number of files in a Document Library grows, it becomes increasingly difficult to keep an inventory of them. To address this challenge, you can use PowerShell to retrieve a list of all files in a Document Library. In this guide, we showed you how to use PowerShell to get a list of files in a Document Library in SharePoint Online.

To get the inventory of the documents from all document libraries in a site collection, use: SharePoint Online: Site Documents Inventory (Library, Folder, Sub-Folder, and Files) Report using PowerShell

How do I get items from a SharePoint Online list in PowerShell?

To get all items from a list, use: $List.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
More info: PowerShell to Get All Items from a List in SharePoint Online

How do I get a list of document library in SharePoint using PowerShell?

To get all document libraries in a SharePoint Online site using PowerShell, use: Get-PnPList | Where-Object {$_.BaseType -eq “DocumentLibrary”}
More info: PowerShell to List All Document Libraries in SharePoint Online

How do I get all documents from a SharePoint Online site?

To get all documents inventory from a SharePoint Online site, You have to iterate through each document library in the site and retrieve files with PowerShell.
More info: Get All Documents Inventory from SharePoint Online Site using PowerShell

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

17 thoughts on “SharePoint Online: PowerShell to List All Files in a Document Library”

  • Hi, This works a charm and is awesome thank you. I want to get all teams sharepoint sits with something like this command:

    get-unifiedgroup -filter {ResourceProvisioningOptions -eq “Team”} | Select SharePointSiteUrl

    Then for each site found it runs your script against it?

    Particularly for the one:

    “Get All Documents Inventory from a SharePoint Online Document Library and Export to a CSV”

    Reply

    • Scripts is good for getting all inventry but failed when the are more than 1 millions rows. How to handle this situation in script

      Reply

  • Hi, This works a charm and is awesome thank you. I want to get all teams sharepoint sits with something like this command:

    get-unifiedgroup -filter {ResourceProvisioningOptions -eq “Team”} | Select SharePointSiteUrl

    Then for each site found it runs your script against it?

    Reply

  • I want to get the names of all documents with their all versions in a csv, I don’t want only version count,I want to have names of all documents.

    Reply

  • I’m getting the following error:
    Get-PnPListItem : Attempted to divide by zero.
    At line:1 char:14
    + … ListItems = Get-PnPListItem -List $ListName -PageSize $Pagesize -Fiel …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : WriteError: (:) [Get-PnPListItem], RuntimeException
    + FullyQualifiedErrorId : EXCEPTION,PnP.PowerShell.Commands.Lists.GetListItem

    Reply

  • I want to get all files created (and in some cases, modified) in the past 1 Month! How do I do that?

    Reply

    • Let’s take the last example in this post:

      #Get the past 1 Month
$Timestamp = (Get-Date).AddMonths(-1)

#Filter Files created in the past 1 Month
$Results | Where-Object {$_.CreatedOn -gt $Timestamp}

#Similarly, to get Files mocified in the past 1 Month, use:
$Results | Where-Object {$_.Modified -gt $Timestamp}
      Reply

  • ‘Get All Documents Inventory from a SharePoint Online Document Library and Export to a CSV’

    Worked like a charm!
    Thanks, you saved me a lot of time.

    Reply

  • I always get this error (it is a very large library ~200,000 items)
    Get-PnPListItem : The decryption operation failed, see inner exception.
    At C:\Users[removed]
    … ListItems = Get-PnPListItem -List $ListName -PageSize $Pagesize -Fiel …
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    CategoryInfo : WriteError: (:) [Get-PnPListItem], IOException
    FullyQualifiedErrorId : EXCEPTION,PnP.PowerShell.Commands.Lists.GetListItem

    Reply

  • I get the following two warnings when running the final script and wonder if you could please explain why and how to resolve these two messages for use when Web is removed?

    WARNING: Consider using -Interactive instead, which provides better functionality. See the documentation at https://pnp.github.io/powershell/cmdlets/connect-pnponline.html#interactive-login-for-multi-factor-authentication

    WARNING: Parameter ‘Web’ is obsolete. The -Web parameter will be removed in a future release. Use Connect-PnPOnline -Url [subweburl] instead to connect to a subweb.

    Reply

  • Error: Exception calling “ExecuteQuery” with “0” argument(s): “The sign-in name or password does not match one in the Microsoft account system.”

    Reply

  • Error: Exception calling “ExecuteQuery” with “0” argument(s): “The remote server returned an error: (502) Bad Gateway.”

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *