decopilot.ps1

<#
.SYNOPSIS
    Converts Excel cells containing =COPILOT() formulas into their calculated results or standard formulas using ImportExcel.
    Compatible with Linux (PWSH).
.DESCRIPTION
    This script utilizes the ImportExcel module (EPPlus) to process Excel files without needing an Excel installation.
    It recursively searches for Excel files, examines cells with formulas starting with "=COPILOT(", and:
    - If the cached value starts with "=", converts it to a live Formula.
    - Otherwise, converts it to a static Value (freezing the result).
.PARAMETER Path
    The path to a specific Excel file or a directory containing Excel files.
.EXAMPLE
    .\Convert-CopilotFormulas.ps1 -Path "./finance_docs"
#>
param (
    [Parameter(Mandatory=$true, Position=0)]
    [string]$Path
)
# Dependency Check
if (-not (Get-Module -ListAvailable ImportExcel)) {
    Write-Warning "The 'ImportExcel' module is required but not found."
    $confirm = Read-Host "Do you want to install it now? (y/n)"
    if ($confirm -eq 'y') {
        Install-Module ImportExcel -Scope CurrentUser -Force
    }
    else {
        Write-Error "Script cannot run without ImportExcel module."
        exit 1
    }
}
Import-Module ImportExcel
function Process-ExcelFile {
    param ($FilePath)
    Write-Host "Processing: $FilePath" -ForegroundColor Cyan
    
    try {
        $pkg = Open-ExcelPackage -Path $FilePath
    }
    catch {
        Write-Error "Failed to open package: $_"
        return
    }
    $modified = $false
    foreach ($ws in $pkg.Workbook.Worksheets) {
        Write-Host "  Scanning sheet: $($ws.Name)" -NoNewline
        
        # EPPlus Optimization: Iterate only cells with formulas if possible, 
        # but EPPlus doesn't expose a simple "GetFormulas" collection easily without iteration 
        # or LINQ on the Cells collection.
        # We will iterate the dimension.
        
        if ($null -eq $ws.Dimension) {
            Write-Host " -> Empty (No Dimension)." -ForegroundColor DarkGray
            continue
        }
        
        Write-Host " [Range: $($ws.Dimension.Address)]" -NoNewline
        # Efficiently get cells in the used range
        # Note: Accessing $ws.Cells without indexer might return the whole sheet range definition in some versions
        # Accessing with Dimension Address ensures we get the iterator for used cells
        $usedRange = $ws.Cells[$ws.Dimension.Address]
        
        # Filter for cells with formulas
        # We check Formula property. Note that for some ranges, getting Formula might be slow, but essential.
        $cellsWithFormulas = $usedRange | Where-Object { -not [string]::IsNullOrEmpty($_.Formula) }
        
        if (-not $cellsWithFormulas) {
             Write-Host " -> No formulas found in range." -ForegroundColor DarkGray
             continue
        }
        foreach ($cell in $cellsWithFormulas) {
            if ($cell.Formula -match "^COMPLEX\(") { # Debug check, remove later? No, stick to logic
            }
            
            # Case insensitive check for COPILOT
            if ($cell.Formula -match "^=COPILOT\(") {
                $formulasFound++
                $currentVal = $cell.Value # This gets the CACHED value from the file
                
                if ($null -ne $currentVal) {
                    $valString = $currentVal.ToString()
                    
                    if ($valString.StartsWith("=", [System.StringComparison]::OrdinalIgnoreCase)) {
                        # Case A: Generated Formula
                        Write-Host "`n    Converting $($cell.Address) to Formula: $valString" -ForegroundColor Green
                        $cell.Formula = $valString
                        $modified = $true
                    }
                    else {
                        # Case B: Static Content
                        Write-Host "`n    Freezing $($cell.Address) to Value: '$valString'" -ForegroundColor Yellow
                        $cell.Value = $currentVal 
                        # $cell.Formula = $null # Redundant: Setting Value clears Formula in EPPlus and this line actually wipes the Value!
                        $modified = $true
                    }
                }
                else {
                    Write-Warning "`n    Cell $($cell.Address) has COPILOT formula but NO cached value. Skipping."
                }
            }
            elseif ($cell.Formula -match "COPILOT\(") {
                # Case C: Medial (Embedded) COPILOT
                $formulasFound++
                $currentVal = $cell.Value
                
                if ($null -ne $currentVal) {
                    $valString = $currentVal.ToString()
                    Write-Host "`n    Freezing Embedded COPILOT at $($cell.Address) to Value: '$valString'" -ForegroundColor Cyan
                    $cell.Value = $currentVal
                    $modified = $true
                }
                else {
                    Write-Warning "`n    Cell $($cell.Address) has embedded COPILOT but NO cached value. Skipping."
                }
            }
        }
        
        if ($formulasFound -eq 0) {
            Write-Host " -> No COPILOT formulas." -ForegroundColor DarkGray
        } else {
             Write-Host "`n    Processed $formulasFound formulas."
        }
    }
    if ($modified) {
        Write-Host "  Saving changes..." -ForegroundColor Magenta
        Close-ExcelPackage -ExcelPackage $pkg
    }
    else {
        Write-Host "  No changes made."
        $pkg.Dispose()
    }
}
# Main Execution
$targetPath = Resolve-Path $Path
if (Test-Path $targetPath -PathType Container) {
    $files = Get-ChildItem -Path $targetPath -Include *.xlsx, *.xlsm -Recurse
}
else {
    $files = @(Get-Item $targetPath)
}
if ($files.Count -eq 0) {
    Write-Warning "No Excel files found at '$Path'."
    exit
}
foreach ($file in $files) {
    Process-ExcelFile -FilePath $file.FullName
}
Write-Host "Done."