Private/Get-SharePointExcelDocument.ps1
|
<#
.SYNOPSIS Downloads an Excel file from a SharePoint Online document library to a specified local directory. .DESCRIPTION The Get-SharePointExcelDocument function connects to a SharePoint Online site using secure credentials (username and encrypted passcode), then downloads the specified Excel file to a provided local folder path. It ensures the local path exists before downloading and logs the status of the operation. .PARAMETER SiteUrl The full URL of the SharePoint site (e.g., https://company.sharepoint.com/sites/YourSite). .PARAMETER SharePointFileServerRelativeUrl The server-relative path to the Excel file in SharePoint (e.g., Shared Documents\Licensing\Report.xlsx). .PARAMETER LocalDownloadPath The local folder path where the file will be saved. .PARAMETER Username The username (usually a service account) used for authentication to SharePoint. .PARAMETER Passcode The encrypted passcode (converted using ConvertTo-SecurePasscode) associated with the Username. .EXAMPLE Get-SharePointExcelDocument -SiteUrl "https://company.sharepoint.com/sites/ITCMR" ` -SharePointFileServerRelativeUrl "Shared Documents\Licensing\MonthlyReport.xlsx" ` -LocalDownloadPath "C:\Temp" ` -Username "citrix@test.com" ` -Passcode $pass Downloads the specified Excel report from SharePoint into the local folder C:\Temp. .NOTES - Uses `PnP.PowerShell` to connect to SharePoint. - Requires `ConvertFrom-SecurePasscode` helper function to decrypt credentials. #> function Get-SharePointExcelDocument { [CmdletBinding()] param ( [Parameter(Mandatory)] [string]$SiteUrl, [Parameter(Mandatory)] [string]$SharePointFileServerRelativeUrl, [Parameter(Mandatory)] [string]$LocalDownloadPath, [Parameter()] [string]$Username, [Parameter()] [string]$Passcode ) try { if ($PSBoundParameters.ContainsKey('Username') -and $PSBoundParameters.ContainsKey('Passcode')) { # Decrypt passcode into SecureString $decryptedPassword = ConvertFrom-SecurePasscode -Passcode $Passcode -Username $Username $securePassword = ConvertTo-SecureString $decryptedPassword -AsPlainText -Force $credential = New-Object System.Management.Automation.PSCredential($Username, $securePassword) Connect-PnPOnline -Url $SiteUrl -Credentials $credential -WarningAction SilentlyContinue } else { throw "Username and Passcode are required." } $filename = Split-Path -Leaf $SharePointFileServerRelativeUrl # Ensure download path exists if (-not (Test-Path $LocalDownloadPath)) { New-Item -Path $LocalDownloadPath -ItemType Directory -Force | Out-Null } Get-PnPFile -Url $SharePointFileServerRelativeUrl -Path $LocalDownloadPath -Filename $filename -AsFile -Force Write-LogEntry -Message "Downloaded $filename to $LocalDownloadPath" } catch { Write-LogEntry -Message "Failed to retrieve Excel file from SharePoint: $_" } finally { Disconnect-PnPOnline -ErrorAction SilentlyContinue } } |