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!

 

 

 

Leave a Reply

%d bloggers like this: