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
    }
}