Issue while populating data in excel sheet using powershell

Question

I am having below code in powershell which I am using to populate the data in excel

    # Apply the formula in column O for all rows
    $lastRowO = $sheet1.UsedRange.Rows.Count
    for ($rowO = 2; $rowO -le $lastRowO; $rowO++) {
        $cellO = $sheet1.Cells.Item($rowO, 15)
        $formulaO = '=IF($N2<>"",XLOOKUP($N2,BACKUP!$C:$C,BACKUP!$B:$B),"")'
        $cellO.Formula = $formulaO

        #$sheet1.range("O$rowO:O$rowO").formula = '=IF($N2<>"",XLOOKUP($N2,BACKUP!$C:$C,BACKUP!$B:$B),"")'
    }

when I am executing the code, it is working fine. if I go to sheet I am able to see the formula in cell but it is not populating the data.

If I manually click on the cell with the same pasted formula, it is populating the data.

Please let me know what is the issue.

Below is the entire code

"Start time is: $((Get-Date).ToString())"

$excelFilePath = "C:\VLAN.xlsx"

# Create an instance of the Excel application
$excel = New-Object -ComObject Excel.Application


try {
    # Open the workbook
    $workbook = $excel.Workbooks.Open($excelFilePath)
    
    # Check if the "USER" sheet exists
    $sheetExists = $false
    foreach ($sheet in $workbook.Sheets) {
        if ($sheet.Name -eq "USER") {
            $userSheet = $sheet
            $sheetExists = $true
            break
        }
    }

    if (-not $sheetExists) {
        throw "Sheet 'USER' not found in the workbook."
    }

    # Add a new sheet named "Sheet1"
    $sheet1 = $workbook.Sheets.Add()
    $sheet1.Name = "Sheet1"

    # Copy data from "USER" sheet to "Sheet1"
    $userSheet.UsedRange.Copy()
    $sheet1.Range("A1").PasteSpecial(-4163) # -4163 is the Excel constant for xlPasteAll

    # Define the headers to be added starting from column N
    $headers = @("BckpSG-VLanID", "Bckpnetwork-label", "BckpSubnet", "BckpNetmask", "BckpGateway", "BckpNetwork")

    # Set the headers in Sheet1 starting from column N (14th column)
    for ($i = 0; $i -lt $headers.Length; $i++) {
        $sheet1.Cells.Item(1, 14 + $i).Value2 = $headers[$i]
    }

    # Apply the formula in column N for all rows
    $lastRow = $sheet1.UsedRange.Rows.Count
    for ($row = 2; $row -le $lastRow; $row++) {
        $cellN = $sheet1.Cells.Item($row, 14)
        $formulaN = '=IFERROR(IFS(MID($C' + $row + ',1,1)="2",XLOOKUP(NUMBERVALUE(MID($C' + $row + ',2,3)),BACKUP!$C:$C,BACKUP!$C:$C),MID($C' + $row + ',1,1)="3",XLOOKUP($C' + $row + '+300,BACKUP!$C:$C,BACKUP!$C:$C)),"")'
        $cellN.Formula = $formulaN
    }
    

    # Apply the formula in column O for all rows
    $lastRowO = $sheet1.UsedRange.Rows.Count
    for ($rowO = 2; $rowO -le $lastRowO; $rowO++) {
        #$cellO = $sheet1.Cells.Item($rowO, 15)
        #$formulaO = '=IF($N2<>"",XLOOKUP($N2,BACKUP!$C:$C,BACKUP!$B:$B),"")'
        #$cellO.Formula = $formulaO

        $sheet1.range("O$rowO:O$rowO").formula = '=IF($N2<>"",XLOOKUP($N2,BACKUP!$C:$C,BACKUP!$B:$B),"")'
    }

    # Force Excel to calculate all formulas in the sheet
    $sheet1.Calculate()


    # Save and close the workbook
    $workbook.Save()
    $workbook.Close()

    Write-Output "Sheet 'Sheet1' has been created with the 'USER' data, headers added, and formulas applied."
} catch {
    Write-Error $_.Exception.Message
} finally {
    # Quit the Excel application
    $excel.Quit()

    # Release the COM objects
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($sheet1) | Out-Null
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($userSheet) | Out-Null
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) | Out-Null
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null

    # Suppress the final output
    Remove-Variable -Name sheet1, userSheet, workbook, excel
}

Answer 1

Excel doesn't know that your cells need to be recalculated. Inside your loops, right after you set the formula, mark the cell as "dirty" and when you recalculate the sheet it should update the data.

    for ($row = 2; $row -le $lastRow; $row++) {
        $cellN = $sheet1.Cells.Item($row, 14)
        $formulaN = '=IFERROR(IFS(MID($C' + $row + ',1,1)="2",XLOOKUP(NUMBERVALUE(MID($C' + $row + ',2,3)),BACKUP!$C:$C,BACKUP!$C:$C),MID($C' + $row + ',1,1)="3",XLOOKUP($C' + $row + '+300,BACKUP!$C:$C,BACKUP!$C:$C)),"")'
        $cellN.Formula = $formulaN
        $cellN.Dirty()
    }


    # Apply the formula in column O for all rows
    $lastRowO = $sheet1.UsedRange.Rows.Count
    for ($rowO = 2; $rowO -le $lastRowO; $rowO++) {
        #$cellO = $sheet1.Cells.Item($rowO, 15)
        #$formulaO = '=IF($N2<>"",XLOOKUP($N2,BACKUP!$C:$C,BACKUP!$B:$B),"")'
        #$cellO.Formula = $formulaO

        $sheet1.range("O$rowO:O$rowO").formula = '=IF($N2<>"",XLOOKUP($N2,BACKUP!$C:$C,BACKUP!$B:$B),"")'
        $sheet1.range("O$rowO:O$rowO").Dirty()
    }

    # Force Excel to calculate all formulas in the sheet
    $sheet1.Calculate()