Get nested folders, files count, folder size, and more in SPO Document Libraries using PowerShell PnP

Isn’t it nice to have some reports, inventory, or any visual about a SharePoint site? So we know what’s going on, at a high level at least.

Today’s post will be about running SharePoint PowerShell PnP to extract info on a SharePoint site, and we’ll go fetch those nested folders! But that’s not all, we’ll also get the number of files in each folder, and the folder size! 😋

Want another bonus? We’ll go through 1 document library, then through all document libraries in your site. Which means 2 PowerShell scripts! 🤠

🔸 Most of my scripts are also available for everyone on Github

 

Report on ONE particular document library

OK, let’s set the scene.

  • One Site Collection
  • One document library
  • Multiple folders (with nested ones)
  • Multiple files (maybe?)
  • And we don’t know about the size…

So here we go.
ℹ️ Change the tenant name and site to your own

#Connect to SPO
Connect-PnPOnline -Url "https://<TENANT-NAME>.sharepoint.com/sites/<YOUR-SITE>"

#My list target 
$myList = "/Shared Documents"

#Store the results
$results = @()

foreach ($list in $myList) {
    $allItems = Get-PnPListItem -List $list -Fields "FileLeafRef", "SMTotalFileStreamSize", "FileDirRef", "FolderChildCount", "ItemChildCount"
    
    foreach ($item in $allItems) {
        
        #Narrow down to folder type only
        if (($item.FileSystemObjectType) -eq "Folder") {
            $results += New-Object psobject -Property @{
                FileType          = $item.FileSystemObjectType 
                RootFolder        = $item["FileDirRef"]
                LibraryName       = $list 
                FolderName        = $item["FileLeafRef"]
                FullPath          = $item["FileRef"]
                FolderSizeInMB    = ($item["SMTotalFileStreamSize"] / 1MB).ToString("N")
                NbOfNestedFolders = $item["FolderChildCount"]
                NbOfFiles         = $item["ItemChildCount"]
            }
        }
    }
}
#Export the results
$results | Export-Csv -Path "C:\Users\$env:USERNAME\Desktop\NestedFoldersForONEdoclib.csv" -NoTypeInformation

So what are we doing here?

  • First, as usual, we connect to our SharePoint site using the Connect-PnPOnline cmdlet,
  • We target our document library to report on ($myList),
  • And we create an array to store our results in ($results)

Then we need to create 2x foreach (nested) loops:

  • In the first one, we loop through each list item, declare the fields we are interested in, and we store the whole lot in a variable called $allItems,
  • In the second one, we narrow down by selecting only the “Folder” type using the IF statement, create a New-Object for our values, and do a bit of customisation with the SMTotalFileStreamSize to format the output in MB.

Finally, we export the $results into a csv file.

The export should look like this:

If we look at the first row, in the Shared Documents library, we have a folder called “Folder 1“, which has 3x nested folders & 1 file inside.

 

We can check this out in 2 ways: In SharePoint Online (you’ll have to trust me 😉)

But ALSO in the csv file itself!
Look at the rows 4, 5, and 8? They contain the full path of those 3 sub-folders… So they were really here when I run the script 🙂

 

 

Report on MULTIPLE document libraries

Now let’s have a look at how we can extend our scenario, and report on multiple document libraries within the site.
This part will be shorter as the script will not change too much, so I’m not gonna bore you with a remake of the above 😉

ℹ️ Again, change the tenant name and site to your own

#Connect to SPO
Connect-PnPOnline -Url "https://<TENANT-NAME>.sharepoint.com/sites/<YOUR-SITE>"

#Target multiple lists 
$allLists = Get-PnPList | Where-Object {$_.BaseTemplate -eq 101}

#Store the results
$results = @()

foreach ($row in $allLists) {
    $allItems = Get-PnPListItem -List $row.Title -Fields "FileLeafRef", "SMTotalFileStreamSize", "FileDirRef", "FolderChildCount", "ItemChildCount"
    
    foreach ($item in $allItems) {
        if (($item.FileSystemObjectType) -eq "Folder") {
            $results += New-Object psobject -Property @{
                FileType          = $item.FileSystemObjectType 
                RootFolder        = $item["FileDirRef"] 
                LibraryName       = $row.Title
                FolderName        = $item["FileLeafRef"]
                FullPath          = $item["FileRef"]
                FolderSizeInMB    = ($item["SMTotalFileStreamSize"] / 1MB).ToString("N")
                NbOfNestedFolders = $item["FolderChildCount"]
                NbOfFiles         = $item["ItemChildCount"]
            }
        }
    }
}
#Export the results
$results | Export-Csv -Path "C:\Users\$env:USERNAME\Desktop\NestedFoldersForMULTIPLEdoclibs.csv" -NoTypeInformation

What’s changed?

Only a couple of things have changed in this script:

  • We use the Get-PnPList to retrieve all the libraries, and store in a variable called $allLists,
  • The -List parameter is now referring to $row.Title,
  • And the LibraryName is also $row.Title

The rest is unchanged.

Output anyone? Sure!

 

That’s it, thanks for reading!

 

 

 

5 Responses to “Get nested folders, files count, folder size, and more in SPO Document Libraries using PowerShell PnP

  • ken fowles
    3 weeks ago

    disregard all of that.
    I got past it with some tweaks, but now im getting this:

    Get-PnPListItem : The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.
    At line:9 char:17
    + … $allItems = Get-PnPListItem -List $list -Fields “FileLeafRef”, “SMTot …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : WriteError: (:) [Get-PnPListItem], ServerException
    + FullyQualifiedErrorId : EXCEPTION,SharePointPnP.PowerShell.Commands.Lists.GetListItem

    Do you know what restriction is causing this?

  • ken fowles
    3 weeks ago

    I need this info desperately, but it is erroring out for me.

    Connect-PnPOnline : Exception has been thrown by the target of an invocation.
    At line:2 char:1
    + Connect-PnPOnline -Url “https://landmarksims.sharepoint.com/G%20Drive …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [Connect-PnPOnline], TargetInvocationException
    + FullyQualifiedErrorId : System.Reflection.TargetInvocationException,SharePointPnP.PowerShell.Commands.Base.ConnectOnline

    I dont know what path to tell it, If I do tenant.sharepoint.com it errors out, if I do sites/ site name it errors out
    I tried the literal url from the browser, same thing
    I have been looking for this but am having trouble logging into it.

    • Veronique Lengelle
      3 weeks ago

      You only need to connect to your target site collection, like https://tenant-name.sharepoint.com/sites/mySiteCollection
      Can you try connecting to any site collection with the Connect-PnPOnline cmdlet? Is it working? Also make sure you have the permission to access this target site.

      • ken fowles
        3 weeks ago

        so if i just try the connect-pnponline -url https://myspot.sharepoint.com/sites/shared
        I get this error.

        PS C:\WINDOWS\system32> Connect-PnPOnline -url https://myspot.sharepoint.com/sites/shared
        Connect-PnPOnline : Exception has been thrown by the target of an invocation.
        At line:1 char:1
        + Connect-PnPOnline -url https://myspot.sharepoint.com/sites/shar
        + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo : NotSpecified: (:) [Connect-PnPOnline], TargetInvocationException
        + FullyQualifiedErrorId : System.Reflection.TargetInvocationException,SharePointPnP.PowerShell.Commands.Base.ConnectOnline

        I can connect to my sharepoint through connect-SPOService but I cannot seem to connect with any other method. Obviously your script doesnt work with that.
        I tried this as well:

        [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SharePoint.Client”)

        [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SharePoint.Client.Runtime”)

        $weburl = “https://myspot.sharepoint.com”
        $ctx = New-Object Microsoft.sharepoint.Client.ClientContext($weburl)
        $username = ‘kenneth.f@myspot.com’
        $password = Read-Host -Prompt “Password for $username” -AsSecureString
        $ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password)
        $web = $ctx.Web
        $ctx.Load($web);
        $ctx.ExecuteQuery();

        That gives me an error like this:
        Exception calling “ExecuteQuery” with “0” argument(s): “Cannot contact web site ‘https://myspot.sharepoint.com/’ or the web site does not support SharePoint Online
        credentials. The response status code is ‘Unauthorized’. The response headers are ‘X-SharePointHealthScore=4, X-MSDAVEXT_Error=917656;
        Access+denied.+Before+opening+files+in+this+location%2c+you+must+first+browse+to+the+web+site+and+select+the+option+to+login+automatically

        So if you have any idea why I cant run any scripts the way, literally every person is telling me to do it, i would love to get past this.

Leave a Reply

%d bloggers like this: