PowerShellでExcelのデータ接続を更新し、JSON出力する

Excelには外部データ接続（Power Queryなど）を使って最新データを取り込む機能があります。 この記事では PowerShell 5.x を使って、次の処理を自動化します👇

• Excelのデータ接続をすべて更新
• 「日付」と「ステータス」で複合フィルタ
• フィルタ後のデータを JSON 出力（非連続セルにも対応）

💻 スクリプト全文

# Requires -Version 5.1
param(
    [Parameter(Mandatory=$true)]
    [string]$ExcelPath,
    [Parameter(Mandatory=$false)]
    [string]$OutputJson = "$(Split-Path $ExcelPath -Parent)\filtered.json",
    [int]$DaysBack = 30,
    [string]$StatusFilter = "Active"
)

$xlCellTypeVisible = 12
$xlAnd = 1

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false

try {
    $wb = $excel.Workbooks.Open($ExcelPath)
    $wb.RefreshAll()
    while (-not $excel.CalculateUntilAsyncQueriesDone()) { Start-Sleep -Milliseconds 500 }

    $ws = $wb.Worksheets.Item(1)
    $table = $ws.ListObjects.Item(1)

    $dateCol = ($table.ListColumns | Where-Object { $_.Name -eq "Date" }).Index
    $statusCol = ($table.ListColumns | Where-Object { $_.Name -eq "Status" }).Index
    $cutoff = (Get-Date).AddDays(-$DaysBack)

    try { $table.AutoFilter.ShowAllData() } catch {}
    $ws.Range($table.Name).AutoFilter($dateCol, ">=" + $cutoff.ToOADate(), $xlAnd)
    $ws.Range($table.Name).AutoFilter($statusCol, $StatusFilter, $xlAnd)

    $visibleCells = $table.DataBodyRange.SpecialCells($xlCellTypeVisible)
    $headers = $table.ListColumns | ForEach-Object { $_.Name }
    $rows = @()

    foreach ($area in @($visibleCells.Areas)) {
        $data = $area.Value2
        if ($null -eq $data) { continue }

        if ($data -is [object[,]]) {
            for ($r = 1; $r -le $data.GetLength(0); $r++) {
                $obj = @{}
                for ($c = 1; $c -le $headers.Count; $c++) { $obj[$headers[$c-1]] = $data[$r,$c] }
                $rows += [PSCustomObject]$obj
            }
        } else {
            $obj = @{}
            for ($c = 1; $c -le $headers.Count; $c++) { $obj[$headers[$c-1]] = $data[$c] }
            $rows += [PSCustomObject]$obj
        }
    }

    if ($rows.Count -gt 0) {
        $rows | ConvertTo-Json -Depth 5 | Out-File $OutputJson -Encoding UTF8
        Write-Host "✅ Exported: $OutputJson"
    }
}
finally {
    $wb.Close($false)
    $excel.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
}

⚙️ 実行例

powershell -ExecutionPolicy Bypass -File .\Export-FilteredExcelToJson.ps1 `
    -ExcelPath "C:\Data\Report.xlsx" `
    -DaysBack 30 `
    -StatusFilter "Active"

🧠 ポイント