Export SharePoint Online File & Folder Storage Usage Report Using PowerShell

SharePoint’s storage can be quite complex to manage. Once you set storage limits for your SharePoint site, it’s essential to monitor how much space each file and folder is using. Without regular monitoring, you risk running out of storage, which can force your SharePoint site into read-only mode if large files or folders consume too much space. Therefore, implementing a strategy to track file and folder storage consumption is crucial.

However, manually navigating through each site to find the storage consumed by files and folders can be challenging. In this blog, we will show you various methods to report on file/folder consumption in SharePoint Online.

To see how storage is used by files and folders on your SharePoint site and to understand what’s consuming your storage space, you can use the below methods:

SharePoint Admin Center: Login to the SharePoint admin center -> Active sites -> Select the respective site. Go to Site settings -> under Site Collection Administration, select Storage Metrics. This shows a detailed view of folders, associated files, and their storage quota details such as total size, percentage of site quota details etc. However, this method requires checking each file/folder metrics across all sites, which can be time-consuming and complex.

Microsoft 365 Admin Center: In the M365 admin center, go to Reports -> Usage and select SharePoint. In the Site usage tab, select ‘Storage’. This shows the amount of storage used by SharePoint sites. However, these reports do not provide storage information used by files/folder in each site and are limited to 180 days.

PowerShell: You can use the Get-PnPFolderStoragemetric cmdlet to view file/folder sizes storage metrics in SharePoint Online. It will reveal the true storage usage, the date and time the folder was last modified, and the number of files inside the folder. However, there is no direct cmdlet available for files.

To address this gap, our custom PowerShell script offers a solution by exporting file/folder storage usage report into a CSV file.

Download Script: SPOFileFolderStorageConsumptionReport.ps1

1. Retrieves file/folder consumption details for all document libraries in a site.
2. Exports file/folder consumption details for a list of sites.
3. Exports the ‘SPO file storage consumption report’ and ‘SPO folder storage consumption report’ into a CSV file.
4. File/folder size can be exported in preferred units such as MB, KB, B, and GB.
5. Automatically installs the PnP PowerShell module (if not installed already) upon your confirmation.
6. The script can be executed with an MFA-enabled account too.
7. The script is scheduler friendly.
8. The script uses modern authentication to connect SharePoint Online.
9. It can be executed with certificate-based authentication (CBA) too.

The script analyzes and exports two reports for file and folder storage consumption separately such as ‘SPO file storage consumption report’ and ‘SPO folder storage consumption report’.

The script exports this report with the following attributes:

• Site
• Library Name
• URL
• File Name
• File Size
• Created By
• Created Date
• Last Modified By
• Last Modified Date

The script exports this report with the following attributes:

• Site
• Library Name
• Folder Name
• Location
• Total Size
• Direct Folders Size
• Direct Files Size
• Total Size
• Percentage By Parent Library
• Percentage By Overall Site

1. Download the script.
2. Start the Windows PowerShell.
3. Select any of the methods provided to execute the script.

Method 1: You can run the script with MFA and non-MFA accounts.

This script exports the document library size including files/folders in SharePoint Online. If you are not sure about the number of document libraries you have, the script to export a list of all document libraries in SharePoint Online may be helpful.

Method 2: Run the script using Certificate-based authentication.

You also have the option to run the script using certificate-based authentication. When you want to run the script unattended, you can choose this method. To use certificates, you must register an app in MS Entra which helps you connect to SPO using certificate. Depending on your requirements, you can create a self-signed certificate.

The script is scheduler friendly. You can schedule the script using the Task scheduler or Azure Automation.

The script includes predefined filters designed to meet your specific needs. Here are a few scenarios where it excels:

• Get storage consumption for a list of sites in SharePoint Online (Input CSV)
• Retrieve file/folder storage usage with custom size units

By default, the script generates a report for the file/folder consumption of the site you give as input. You can also generate a report for a list of site collections which enables a more targeted analysis. To do this, you can input the site collection details via a CSV file and generate a report for the associated subsites.

Use the –ImportCsv parameter to pass the CSV file and retrieve reports for expired anyone links in SharePoint Online. For example,

The file must follow the format below: Site names separated by a new line with “SiteUrl” as header. Not including this header will result in errors when running the script.

The resulting report will give total document library size and associated file/folder consumption details for the list of sites.

Important:

• While running the script, you’ll need to authorize for each site regardless of whether MFA is enabled. For example, if you have 25 sites, you will need to authorize access for each of the 25 sites individually.
• Additionally, if you’re not an owner of certain sites, you might encounter an “Access is denied” or “Unauthorized operation” error and won’t be able to retrieve the storage consumption data.
• To avoid these issues, use certificate-based authentication, which lets you gather data from all sites without repeated authorization prompts or access errors.

Efficient data management often requires understanding the storage impact of files in different units. By specifying the -Unit parameter, you can format the file/folder storage consumption report in your desired unit (B, KB, MB, GB), making it easier to manage and analyze storage usage and to find the largest files in SharePoint. By default, the script exports the result with values in ‘MB’.

This example exports the report with file/folder size and total size in the unit “GB”.

Note: Beyond just files and folders, it’s equally important to monitor storage utilization for SPO list items in each site. To get a complete view of your site storage usage, consider exporting SharePoint Online list item count report using PowerShell.

Keeping track of file/folder storage usage in SharePoint is crucial for managing space effectively and ensuring compliance. While PowerShell scripts can help, they often lack the depth needed to provide clear insights into file and folder storage.

That’s where AdminDroid comes in! AdminDroid’s SharePoint Online auditing tool simplifies the process, offering detailed reports on files and folders. With its comprehensive insights, you can easily monitor site storage consumption and manage your SharePoint environment more efficiently. Here is a glimpse of what the tool provides:

SharePoint Storage Reports

• All site collections with storage information
• Daily site storage consumption
• Sites over warning quota
• OneDrive storage over time

File Management Activities

• Uploaded files
• Uploaded files to document libraries
• Modified files

Folder Activities

• Created Folders
• Modified Folders

Document Library Reports

• All SharePoint document libraries
• Hidden document libraries
• Inactive document libraries

Moreover, the SharePoint Online dashboard provided by AdminDroid gives you an engaging, clear view of your SharePoint such as storage usage, inactive sites, external sharing enabled sites, etc. It simplifies SharePoint Online management, making tasks easier and boosting security.

With over 1800 reports and more than 30 dashboards, AdminDroid covers Exchange Online, SharePoint Online, Microsoft Teams, OneDrive for Business, and more. Start your journey towards optimized Microsoft 365 management with AdminDroid. Try our 15-day free trial and experience the difference for yourself! Download AdminDroid today and elevate your SharePoint monitoring and management.

We hope this blog has been helpful in guiding you through the PowerShell script to find the storage consumption of files and folders in SPO. Additionally, it’s important to report on file version history as the storage used for version history also counts toward your overall SharePoint storage quota. Thanks for reading! To further enhance SharePoint Online security, follow best practices for sharing files and folders and create SPO alerts on file/folder changes in SharePoint Online. If you have any questions or need further assistance, please leave a comment below.