ecoslymeです。
エクセルのパワークエリ（Power Query）でデータを集約することはできたけど、毎回エクセルを開いてクエリを更新させないといけないとなると、非常に手間です。
毎日開くことが習慣のエクセルならまだしも、元データが他のエクセルブックであれば、集計用に作成したエクセルは普段開かない可能性があります。
パワーシェル（Power Shell）を用いれば、Windowsのタスクスケジューラを利用してパワークエリを利用しているエクセルブックの自動更新が可能です。
そこでこの記事では、エクセルのPower Query（パワークエリ）を自動更新させる方法をご紹介します。
めちゃくちゃ便利な機能なので、是非覚えて下さい。
タスクスケジューラでPower Query（パワークエリ）を自動更新させる方法
Power Query（パワークエリ）を使用しているエクセルブックを自動更新させる方法についてご紹介します。
大きな流れは、以下のステップになります。
- パワークエリを自動更新させる方法
①：パワーシェル（PowerShell）を使用できるように設定する
②：パワーシェルで、パワークエリを実行させる設定する
③：タスクスケジューラでパワーシェルを実行する
パワーシェルの設定ができれば、あとは難しくないと思っています。
Macでの自動更新の方法については未確認です。
①：パワーシェル（PowerShell）を使用できるように設定する
Windows10で、パワーシェル（PowerShell）を利用できるように設定しましょう。
・画面左下の虫眼鏡（検索）マークをクリック後「Power Shell」と入力しましょう。
・アプリを右クリックしてからか、出てきた画面から「管理者として実行」を選択してください
・以下のコマンドを入力（実行ポリシーの設定）
Get-ExecutionPolicy
「Restricted」となっていると思うので、これを変更します。
※「RemoteSigned」となっていれば問題ありません
・「Restricted」となっていた場合、以下のコマンドを入力（スクリプトを入力可能にさせる）
Set-ExecutionPolicy RemoteSigned
・質問に対して、「Y」で回答する。
・以下のコマンドを入力（終了）
②：パワーシェルで、パワークエリを実行させる設定する
次に、パワーシェルISE（Power Shell ISE）を用いて、スクリプトを作成します。
・画面左下の虫眼鏡（検索）マークをクリック後「Power Shell」と入力しましょう。
・「Power Shell ISE」というアプリが出てくるので、今度はこちらをクリックします。
クエリの「バックグラウンドで更新する」のチェックを外す（オフにさせる）
パワークエリを利用しているエクセルで、「バックグラウンドで更新する」をオフにさせる必要があります。
・パワークエリを利用しているエクセルで、「クエリ」タブを選択しましょう。
※パワークエリの表を一度クリックすると「クエリ」タブが選択できます
・プロパティを開きます
・「バックグラウンドで更新する」のチェックを外して、オフの状態にしましょう。
パワーシェルにスクリプトの入力
パワーシェルISEに、エクセルのパワークエリを更新させるスクリプトを入力します。
実際に実行されているかを確認するためログを確認するシートを追加します。
エクセルに新規シートとして「ログ」というシートを追加しましょう。
・PowerShell ISEの画面では、ファイルから「新規作成」をしておきましょう
Power Shell ISEには、以下のスクリプトを入力しましょう。
- Power Shell ISEに入力するスクリプト
# エクセルオブジェクト作成
$excel = New-Object -ComObject Excel.Application
#エクセルの表示有無。①$true：エクセルが画面に表示される、②$false：エクセルが画面に表示されない
$excel.Visible = $false
# エクセルファイルを開く（URLを変更して下さい）
$book = $excel.Workbooks.Open(“エクセルのフルパスを入力”)
# シート移動（「ログ」というシートを作成しておく）
$sheet = $excel.Worksheets.Item(“ログ”)
# ログ出力
$log = Get-Date -Format “yyyy/MM/dd HH:mm”
$range =$sheet.Range(“A1″)
$range.Value =”更新日時: $log”
# クエリ更新
$book.refreshall()
# 上書き保存
$book.Save()
# 閉じる
$book.Close($true)
#プロセスの削除
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)
「エクセルのフルパスを入力」の部分ですが、以下の要領でエクセルのフルパスを取得します。
該当するエクセルを「Shift + 右クリック」から、「パスのコピー(A)」を選択します。
例えば、「TEST」というフォルダがデスクトップにあり、その中に「TEST.xlsx」というファイルがある場合、ファイルのフルパスは以下になります。
③：タスクスケジューラでパワーシェルを実行する
最後に、上で作成したパワーシェルのスクリプトをタスクスケジューラで実行させましょう。
・Windowsのタスクスケジューラを起動させてください。
※同様に虫眼鏡の部分で「タスク スケジューラ」と入力してみましょう
・右クリックから、「タスクの作成」を選択
・以下の内容を入力してみましょう。
・全般（構成）：Windows10
・トリガー：自動更新させたい時間を設定
・操作：以下参照
・全般
・トリガー（新規作成から）
- タスクスケジューラ（操作タブ）で設定する内容
| プログラム/スクリプト | %SystemRoot%\System32\WindowsPowerShell\v1.0\powershell.exe |
|---|---|
| 引数の追加 | -Command “ps1ファイルのフルパス“ |
| 開始 | フォルダのURL ＊「””」（ダブルクオーテーションマーク）でくくらないこと |
「プログラム/スクリプト」は、基本的に初期設定ではどのPCも同じだと思います。（変更する必要なし）
実際に私が操作したときに、「引数の追加」と「開始」の設定の部分でつまずいてしまったので、具体的な例を上げて説明します。
- パワークエリを利用しているエクセルの場所が以下のフォルダにある場合
・フォルダの場所：デスクトップ
・フォルダの名前：TEST
・エクセルの名前：TEST.xlsx
・パワーシェルのファイル名：クエリ更新.ps1
・引数
-Command “D:\UserDATA\〇〇〇〇〇（ユーザー名）\Desktop\TEST\クエリ更新.ps1”
・開始
D:\UserDATA\〇〇〇〇〇（ユーザー名）\Desktop\TEST
以上で設定は完了です。
以降、PCが起動していれば、自動でパワークエリの更新を行ってくれるようになります。
最後に
いかがでしたでしょうか。
Windowsの複数の機能を利用することで、毎日の作業となってしまう更新作業を自動化させることができました。
一度設定してしまえば、あとは特にすることはありません。
基本的に元データの場所が変わったりしない限り、安定して自動更新をすることができます。
PCは常に起動させておき、朝2〜6時のどこかで実行させれば、通常の業務にも支障をきたさないと思います。
また、元データを更新した時にすぐに反映させてたいというのであれば、タスクスケジューラを右クリックから実行させればすぐにパワークエリのエクセルデータが更新されます。
エクセルの機能とWindowsの機能を便利に使いこなしましょう！
