PowerQuery 更新の自動化・定期実行【PowerShell編】

パワークエリのクエリを定期実行・定期更新する方法を紹介します。

ドノ

ここではWindowsのPowerShellとタスクスケジューラを利用した方法を紹介します。

PowerQueryの設定・準備

まずは自動更新用のパワークエリの準備と設定を行います。

自動実行したいクエリの作成

自動更新用のクエリを作成してください。ここでは例として「クエリA」という名前のクエリを作成しています。

定期更新用の設定

PowerShellからクエリを更新する場合にエラーが発生しないように「バックグラウンドで更新する」をオフにします。

対象のクエリを右クリックして「プロパティ」を選択します。

設定画面の「バックグラウンドで更新する」のチェックを外して「OK」を選択します。

ファイルを保存してPowerQuery側の設定は完了です。

PowerShellの設定・準備

次にPowerShellの準備と設定を行います。

実行ポリシーの確認と変更

PowerShellの実行ポリシーが「RemoteSigned」になっていることを確認します。なっていない場合は変更します。

STEP

PowerShellの起動

Windowsの検索窓で「PowerShell」を入力して、表示される「PowerShell」を右クリックして管理者として実行します。

STEP

実行ポリシーの確認

「Get-ExecutionPolicy」を入力し、Enterキーを押す。

「RemoteSigned」になっていればOKです。「Restricted」であれば下記に従ってポリシーを変更してください。

STEP

実行ポリシーの変更

「Set-ExecutionPolicy RemoteSigned」を入力し、Enterキーを押します。実行ポリシーの変更について確認されますので問題なければ「Y」を入力してEnterキーを押します。

再度「Get-ExecutionPolicy」を入力して、実行ポリシーが「RemoteSigned」になっていればOKです。

クエリ更新用のPowerShellファイルを作成

次にクエリを実行するPowerShellファイルを作成します。

テキストエディタ（メモ帳など）に下記のスクリプトを記載して保存します。このときファイル拡張子は「.ps1」としてください。

"ファイルパス"は環境に合わせて自由に設定変更してください

# 設定項目
$FilePath = "ファイルパス"  # 自由に変更してください

# Excelアプリケーションを起動
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false        # Excelを非表示で実行（表示したい場合はTrueに変更）

# 対象のExcelファイルを開く
$Workbook = $Excel.Workbooks.Open($FilePath)

# 全てのクエリをリフレッシュ
$Workbook.RefreshAll()

# 更新が完了するまで待機
do {
    Start-Sleep -Seconds 1
} while ($Excel.CalculationState -ne 0 -or $Excel.RefreshingData)

# ファイルを保存して閉じる
$Workbook.Save()
$Workbook.Close()

# Excelアプリケーションを終了
$Excel.Quit()

# COMオブジェクトを解放
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($Excel) | Out-Null
Remove-Variable Excel

ここでは、下図のように「UpdatePowerQuery.ps1」という名前でC:\test\UpdatePowerQuery.ps1に保存しています。このファイルの保存場所をタスクスケジューラで使用します。