Power Queryの操作をpowershellで自動化する

Power Queryはデータの取得・加工を行うことができるExcelアドインツールで、 Excel2016以降のExcelであれば標準搭載されています.

普段から利用している人も多いのではないでしょうか.

さてPower QueryはGUIベースでも十分使いやすい代物ですが, 自動化を行うことでより強力なツールとなります.

以下では, powershellを使ったpower queryの自動化について紹介します.

power queryをpowershellから実行する

以下はファイルシステム内の複数のExcelデータを一つの表にまとめるpowershellスクリプトになります.

####################################################################################
# Power Queryを利用して複数Excel表データを結合するスクリプト
####################################################################################

# 設定 #############################################################################

# Excelのパスとシート名を記述
$DATA_SOURCE_CONFIG = @(
        @{"path"="<PATH_TO_XLSX_FILE1>"; "sheetName"="<SHEETNAME>"},
        @{"path"="<PATH_TO_XLSX_FILE2>"; "sheetName"="<SHEETNAME>"}
        # etc...
    )

# Excelの出力先
$OUTPUT = "$($PSScriptRoot)\report.xlsx"

# 設定ここまで ######################################################################
function mFormulaBuilder {
    param (
        $dataSourceList
    )

    $dataSourceObjList = $dataSourceList | % { "Excel.Workbook(File.Contents(""$($_.path)""), true){[Name=""$($_.sheetName)""]}[Data]" }

    return "Table.Combine({$($dataSourceObjList -join ",")})"
}

function main {
    
    $excel = New-Object -ComObject Excel.Application
    $book = $excel.Workbooks.Add()

    # クエリの登録
    $QUERY_NAME = "query1"
    $mFormulaString = mFormulaBuilder($DATA_SOURCE_CONFIG)
    $book.Queries.Add($QUERY_NAME, $mFormulaString)

    $sheet = $book.Worksheets.Item(1)
    # Sheet1のA1にQueryTableを作成
    $qt = $sheet.QueryTables.Add(
        # 接続文字列
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=`$Workbook`$;Location=${QUERY_NAME};Extended Properties=""""",
        # 表範囲
        $sheet.Range('$A$1')
    )
    # sql文
    $qt.CommandText = "SELECT * FROM [${QUERY_NAME}]"
    # sql文実行
    $qt.Refresh($false)

    # ワークブックを保存する
    $book.SaveAs($OUTPUT)

    $excel.Quit()
    $book = $null
    $excel = $null
    
    [GC]::Collect()
}

main

powershell(>= 6.0)であれば, 以下のコマンドを実行することで複数のExcelファイルのデータを1つにまとめたExcelファイルが出力されます.

pwsh ./bind_excel_data.ps1

One drive上のExcelファイルへのアクセス

前節では, ファイルシステム管理下のExcelファイルデータのアクセスをおこないました.

以下の記事のよう法人向けOne driveはHTTPSプロトコルによるファイルアクセスに対応しています.

Power Query から https 経由で SharePoint の Excel ブックを開く

前節のスクリプトファイルに対し以下のようにFile.Contents →Web.Contentsと修正を加えることで, (法人向け)One drive上の複数Excelファイルを統合できるようになります.

修正前

function mFormulaBuilder {
    param (
        $dataSourceList
    )

    $dataSourceObjList = $dataSourceList | % { "Excel.Workbook(File.Contents(""$($_.path)""), true){[Name=""$($_.sheetName)""]}[Data]" }

    return "Table.Combine({$($dataSourceObjList -join ",")})"
}

修正後

function mFormulaBuilder {
    param (
        $dataSourceList
    )

    $dataSourceObjList = $dataSourceList | % { "Excel.Workbook(Web.Contents(""$($_.path)""), true){[Name=""$($_.sheetName)""]}[Data]" }

    return "Table.Combine({$($dataSourceObjList -join ",")})"
}

参考リンク

• Microsoft Power Query for Excel の概要

• Power Query から https 経由で SharePoint の Excel ブックを開く