Forum Discussion

Copper Contributor

Apr 28, 2023

Solved

SharePoint storage report in Microsoft 365

Hi, We have reached the allowed SharePoint storage for our tenant. Currently we have two options, pay for additional storage (this is NOT the preferred option) or to remove some unused data. In ...

Feb 20, 2024

NicolasKheirallah Thanks for your script, it works; however, there are some modifications that need to be done to make it working under PowerShell 7.4 and using two factor authentication:

#Set Variables
$SiteURL = "https://your_tenant.sharepoint.com/sites/your_team_site"
$LibraryName = "Documents"
$ReportOutput = "C:\Temp\getAllFiles.csv"

#Please note that on my case, the modules: 
#Microsoft.Online.SharePoint.Powershell and
#Microsoft.Online.SharePoint.Powershell didn't get loaded
#automatically, so have to manually import them

#This doesn't seems to work with Powershell 7.4 because it seems
#that this module is not yet compatible. You will get some warnings
#Import-Module Microsoft.Online.SharePoint.Powershell

#This will work with Powershell 7.4, I guess it is using version
#5.1 for the import
Import-Module Microsoft.Online.SharePoint.Powershell -UseWindowsPowerShell

Import-Module PnP.PowerShell

#Connect to SharePoint Online site. Please note that since I'm
#using two factor authentication, Get-Credential won't work, so,
#I used the "Connect-PnPOnline" with the "-Interactive" option,
#then a Window will popup
Connect-PnPOnline -Url $SiteURL -Interactive

$FileData = @()
#Iterate through all files
$ItemCounter = 0 

$ListItems = Get-PnPListItem -List $LibraryName -PageSize 500 -Fields Author, Editor, Created, File_x0020_Type, File_x0020_Size, Versions

Foreach ($Item in $ListItems)
{
  $FileData += New-Object PSObject -Property ([ordered]@{
    Name              = $Item["FileLeafRef"]
    Type              = $Item.FileSystemObjectType
    FileType          = $Item["File_x0020_Type"]
    FileSize =  [Math]::Round(($Item.FieldValues.File_x0020_Size/1KB),2)
    RelativeURL       = $Item["FileRef"]
    CreatedByEmail    = $Item["Author"].Email
    CreatedOn         = $Item["Created"]
    Modified          = $Item["Modified"]
    ModifiedByEmail   = $Item["Editor"].Email
  })
}

$FileData | Export-Csv -Path $ReportOutput -NoTypeInformation -Delimiter ";"

Thanks for the script.

Best regards

Josef

Josef76

Copper Contributor

Feb 20, 2024

NicolasKheirallah Thanks for your script, it works; however, there are some modifications that need to be done to make it working under PowerShell 7.4 and using two factor authentication:

#Set Variables
$SiteURL = "https://your_tenant.sharepoint.com/sites/your_team_site"
$LibraryName = "Documents"
$ReportOutput = "C:\Temp\getAllFiles.csv"

#Please note that on my case, the modules: 
#Microsoft.Online.SharePoint.Powershell and
#Microsoft.Online.SharePoint.Powershell didn't get loaded
#automatically, so have to manually import them

#This doesn't seems to work with Powershell 7.4 because it seems
#that this module is not yet compatible. You will get some warnings
#Import-Module Microsoft.Online.SharePoint.Powershell

#This will work with Powershell 7.4, I guess it is using version
#5.1 for the import
Import-Module Microsoft.Online.SharePoint.Powershell -UseWindowsPowerShell

Import-Module PnP.PowerShell

#Connect to SharePoint Online site. Please note that since I'm
#using two factor authentication, Get-Credential won't work, so,
#I used the "Connect-PnPOnline" with the "-Interactive" option,
#then a Window will popup
Connect-PnPOnline -Url $SiteURL -Interactive

$FileData = @()
#Iterate through all files
$ItemCounter = 0 

$ListItems = Get-PnPListItem -List $LibraryName -PageSize 500 -Fields Author, Editor, Created, File_x0020_Type, File_x0020_Size, Versions

Foreach ($Item in $ListItems)
{
  $FileData += New-Object PSObject -Property ([ordered]@{
    Name              = $Item["FileLeafRef"]
    Type              = $Item.FileSystemObjectType
    FileType          = $Item["File_x0020_Type"]
    FileSize =  [Math]::Round(($Item.FieldValues.File_x0020_Size/1KB),2)
    RelativeURL       = $Item["FileRef"]
    CreatedByEmail    = $Item["Author"].Email
    CreatedOn         = $Item["Created"]
    Modified          = $Item["Modified"]
    ModifiedByEmail   = $Item["Editor"].Email
  })
}

$FileData | Export-Csv -Path $ReportOutput -NoTypeInformation -Delimiter ";"

Thanks for the script.

Best regards

Josef

jmcarthur

Copper Contributor

Oct 09, 2024

Josef76 I made an additional change that will likely help others. Rather than using a basic list/array, I implemented a Generic List. Every time you += to an array, it copies the entire thing. So, in the case where a SP site has tens of thousands of files, this will consume a massive amount of memory. See this example for adding elements to the list. All other operations are the same as a regular array.

$FileData = New-Object System.Collections.Generic.List[PSCustomObject]
Foreach ($Item in $ListItems)
{
    $newObj = New-Object PSObject -Property ([ordered]@{
            Name            = $Item["FileLeafRef"]
            Type            = $Item.FileSystemObjectType
            FileType        = $Item["File_x0020_Type"]
            FileSize        = [Math]::Round(($Item.FieldValues.File_x0020_Size / 1KB), 2)
            RelativeURL     = $Item["FileRef"]
            CreatedByEmail  = $Item["Author"].Email
            CreatedOn       = $Item["Created"]
            Modified        = $Item["Modified"]
            ModifiedByEmail = $Item["Editor"].Email
        })
    $FileData.Add($newObj) # <------ This is the key operation
}