Boxファイル監視をPower AutomateでExcelに書込み

自分が現在勤務してる環境は、なぜかPower AutomateからBoxコネクタを使って接続しようとしても、Box側から接続を拒否されるオカシナ制限が掛けられてる為、Boxでの様々なファイルの更新状況はメールで通知を受け取るしか無いという酷い有様です（このBoxも同じく契約してるにも関わらず）。ということで、逆の発想で、Box側からのOutgoing WebhookをPower Automate側で受け取れば同じような事が実現出来ると考え、構築しました。

これが実現出来る事により、様々なメンバーのファイルへのアクションを全て取得でき、「あのファイルが消えた。どこ行った？」であったり、「誰がいつ更新をしたのか？」といった事が追跡出来るようになります。今回は、Box⇒Power Automate⇒Teams通知⇒Excel Onlineに書き込みまでを実現しています。

図：Teams上で記録されていく

今回使用するサービス

• Box API
• Microsoft Power Automate
• Microsoft Teams
• Microsoft Excel Online (Business)

事前準備

Box側でのWebhookを準備

Box Developer Consoleにログインし、新しいプロジェクトを作成しWebhook v1を使えるようにします。今回は、boxdomainという名前で作っています。

1. アプリの新規作成をクリックする
2. カスタムアプリを選択。ダイアログでは、ユーザ認証（OAuth2.0）を選択します。
3. クライアントIDとクライアントシークレットを控えておきます。
4. リダイレクトURLは自分の持ってるウェブサイトなどを指定します。
5. アプリケーションスコープでは、Webhookを管理にチェックを入れます。
6. Webhookのタブをクリックします。
7. Webhook v1の新規Webhookの作成をクリックします。
8. 名前、説明を入れて、無効化可能にチェック。
9. イベントのトリガーではどのイベントをWebhookで飛ばすか？を選びます（よく使うUploaded, Moved, Copied, Deletedあたりにチェックを入れます）

とりあえずはココまで準備。エンドポイントURL、コールバックパラメータはPower Automate側である程度作ってから出ないと入力が出来ないので、空のままにしておきます。

※但し、Box Webhook v1の場合、Boxユーザのメアドが含まれていないので、その場合はBox APIのWebhook v2を利用する必要があります。Power Automate側でメアドでフィルタしたい場合は工夫が必要になります。

OAuth2.0認証

作っただけではこのプロジェクトは利用することが出来ません。一旦何かのアプリ等でOAuth2.0認証を実行してあげる事で、プロジェクトを自身のBoxアカウントに追加出来るようになるので、この作業が大きな壁になります。ここでは過去のエントリーでも紹介したVBAからBox APIを叩いてみるで紹介した、Access VBAのファイルを使って認証を実行します。Access Token等取るのが目的ではないので、認証を実行したら廃棄して問題ありません。

1. ダウンロードしたAccessファイルのVBA編集画面に入る
2. OAuth認証用とあるルーチンの上部に、控えておいたクライアントID、クライアントシークレット、リダイレクトURLを追記します。
3. 社内でプロキシを使ってる場合は、プロキシURLも追記します。
4. boxAuthorizationを実行すると、IE11が起動し、Boxの認証画面が出るのでログインして認証を完了させます。

これで、プロジェクトが使えるようになりました。

図：Boxへのログイン画面が出てきます。

アプリの追加

OAuth2.0認証を実行すると、自分のBoxアカウントのマイアプリケーションに、作ったプロジェクトが表示されるようになります。クリックして、詳細が出てくるので、「追加」のボタンをクリックすれば、準備完了です。

図：作ったプロジェクトが出てくる

図：追加をクリックして有効化する

Power Automateでフロー作成

続いて、BoxのWebhookを受け取る側を作成します。以下のようなフローを作成することになります。

図：シンプルに見えて結構難易度高めです。

本来使うBoxコネクタの場合

本来は、Power AutomateにはBoxのコネクタがあるので、自動化したクラウドフローのトリガーとして、「ファイルが作成された時」というトリガー等を使って、そこからメールやTeams、Excel Onlineへの書き込みをさせるのが定石です。他にも更新された時コネクタがあったりしますが、ちょっと取れるプロパティが少ない・・・

Box Webhook v1のほうが細かく取れ、さらに言えばBox Webhook v2ならばかなり詳細に取れるので、この処理では物足りないかもしれません。簡単にログインで連結出来て、Webhookの場合よりも手数が少なくて楽なのですが。

図：ただちょっと取れる情報が少ない・・・

インスタントフローの作成

HTTP要求の受信時

ログイン後、以下の手順でまずは受け口を作ります。これがIncoming Webhookとなります。

1. マイフローに入り、新規作成にて「インスタントフロー」を選択します。
2. フロー名を適当に入れて、トリガーはHTTP要求の受信時を選択します。
3. 要求本文の JSON スキーマには、以下のような仮コードを入れます。今回GETで受信させるのでこのJSONスキーマは無意味ですが、無いとNGなので入れておきます。
4. 詳細オプションを表示するをクリック
5. methodはGETを選択します。

図：GETで受信でもJSONスキーマが必要

JSONの解析

GETなのでJSONは受け取れないのですが、次のステップとしてJSONの解析を追加します。設定は以下の通りです。

1. スキーマには以下のようなものを作り込んでおきます。param1やparam2などの名称はBox Webhook側のコールバックパラメータ名と合わせておく必要があるので、控えておきます。
2. コンテンツをクリックして、動的コンテンツの式をクリック
3. 数式として、以下のような数式を入力し、OKをクリックします（クエリという形でコンテンツに入力されます）

JSONスキーマとしては以下のようなものを書き込む

図：この数式でGETパラメータを解析します

Teamsに通知を投稿

ここまで来たら、GETパラメータからBoxのWebhook通知内容を詳細に取り出せるので、Teamsコネクタのメッセージを投稿するV3をステップで追加し、以下のように設定します。

1. 投稿先チーム名を選択します。
2. チーム内にあるチャネル名を選択します。
3. メッセージ本文では、動的コンテンツを利用し、JSONの解析の内容を差し込む形で本文を構築します。

図：Teamsに定型文で投稿してくれるようになります。

現在時刻の取得とタイムゾーン変換

BoxのWebhookには日付の内容が含まれていないので、タイムスタンプとしてPower Automate側で作ってあげる必要があります。しかし、そのまま構築すると見慣れた日付の形式ではない上に、時間帯がUTCとなっている為、日本時間ではありません。ここは、２つのコネクタを利用し、タイムゾーンの変更と日付の形式を整えてあげます。このデータをExcelに追記時に合わせて利用します。２つのコネクタは「日付」で検索すると出てくると思います。

1. コネクタとして「現在の時刻」を追加します。（時刻で検索すると出てくる）
2. 次にコネクタとして「タイムゾーンの変換」を追加します。（日時で検索すると出てくる）
3. タイムゾーンの変換では、基準時間に1.のコネクタを指定します。
4. 書式設定文字列は「yyyy/MM/dd HH:mm」を入力してカスタム形式として登録します。MMとHHが大文字の理由は、MMでないと分になってしまうという理由と、hhだと12時間表記になってしまうのでHHで24時間表記にする為です。
5. 変換元のタイムゾーンはUTC（協定世界時）を指定します。
6. 変換先のタイムゾーンはUTC+9:00 大阪、札幌、東京を指定します。

図：タイムゾーン変換が必要です

Excelに追記

TeamsのチャンネルにファイルとしてExcelを一個追加し、ID, ファイル名, アクション, 日時, 編集者としてタイトルを決めて、範囲をテーブル化しておきます。テーブルでないとPAからは書き込みが出来ないので、注意が必要です。

1. Power Automateに最後のステップとして、Excel Onlineの表に行を追加を追加
2. 場所として、アップしたTeamsのチームを選びます。
3. アップ先指定としてはドキュメントライブラリになるので、ドキュメントを選びます。
4. ファイルをクリックすると、アップしたファイルが出てくるので、それを選択します。
5. テーブルでは、そのエクセル内のテーブル名が出てくるので、それを選択します。
6. 自動的にテーブルのタイトル列を判定してくれるので、ID～編集者までの項目を埋めていきます。
7. それぞれクリックしたら、動的コンテンツとして、param1～4を割り当てます。
8. 日時だけは、前項のタイムゾーンの変換を利用するので、変換後の時間を割り当てます。

図：これで表に自動的にレコードとして追加されます

Box Webhookに追記して保存

最後に保存をクリックするとフローは完成。この時、HTTP要求の受信時フローの「HTTP GETのURL」が出現するので、これをコピーしておきます。このURLとparam1～4の為の設定をBox Webhook側に追記します。

1. エンドポイントURLにHTTP GETのURLを記入します。
2. コールバックパラメータを追加をクリック
3. 方法はGETを指定します。
4. パラメータ名はparam1とします
5. パラメータ値をクリックすると、何を割り当てるか出てくるので、Power Automate側で割り当てたものと合致するものを選択。今回param1はファイル名なので、「#item_name#」を選びました。
6. 同じようにparam2～4をそれぞれ合致するものを選んで作ります。
7. 最後にWebhookの保存をクリックすると保存されて、その時点からWebhookがPower Automate側に飛ぶようになり、タスクランナーが走り始めます。

図：最後のパラメータ指定はとっても重要

ログを自動削除する

さて、この状態のまま運用すると、SharePoint上のExcelにはBox Webhookで設定したイベントトリガーの分 × 人数分のログがどんどん溜まっていきます。しかし、Excelはデータベースではないので行には上限があります（1048576行まで）。また、いつまでも古いログを残しておく意味もないので、自動的にこれらのうち指定経過月数を超えたものについては自動で削除してくれたほうがありがたいです。

ということで、スケジュール済みクラウドフローを使って、今回「現在の日付から6ヶ月を経過したレコードを自動削除」するフローを作りました。そのためにUTC列を用意して、UTCの書き込み日付を入れるようにテーブルを拡張しておきました。フローの作り方は以下の通りです。

1. スケジュール済みクラウドフローを新規作成する
2. 設定時刻を0にセット（深夜0時に発火します）、間隔は1日でセット（これで毎日発火します）
3. 現在の時刻コネクタを日時のコネクタから追加します。
4. 過去の時間の取得コネクタを日時のコネクタから追加します。
5. 4.に於いて、基準時間は3.をセット。間隔を6に、時間単位は「ヶ月」を指定（これで、6ヶ月前の日付を取得出来ます）
6. 変数初期化を一個追加。4.の算出時間を文字列型として格納します。（pastdayと命名）
7. 作成コネクタをデータ操作のコネクタから追加。
8. 入力値に於いて、以下の式を使い、6.の日付データからシリアル値を算出します。
9. 8.を格納する為の変数初期化コネクタをpastmanとして追加します（整数型）。
10. Excelの「表内に存在する行を一覧表示コネクタ」を追加し、SharePointにアップされてる前項Excelとテーブル名を指定します。
11. ExcelテーブルのUTC列の日付を格納する為の一時変数初期化コネクタを追加。tempSerialと命名（整数型）
12. 変数設定コネクタを一個追加。tempSerialに対して以下の数式でテーブルのUTC列の値からシリアル値を算出して格納します（自動的にApply to eachのループコネクタで括られます）。
13. 12のコネクタにつなげる形で、コントロールコネクタから「条件コネクタ」を追加します。
14. 条件式は tempSerialの値がpastmanの値以下という条件になるように構築します。
15. 「はい」の側のフローに、Excelの「行の削除コネクタ」を追加します。
16. 15.に対して、アップロードしたSharePoint上のExcelファイルとテーブル名を指定し、キー列とキー値はIDを指定します（どれでも良いのですが）

これで、毎日指定時刻にフローが自動発火し、現在から見て6ヶ月前の日付よりも前の日付よりも前の日付をUTC列に持つレコードが自動削除されていきます。ポイントは、日付の比較をする時に「シリアル値」に変換してから比較する事で面倒な日付の比較を楽にしてる点です。

動作時間はそれほど早くは無いので、6ヶ月ではなくもうちょっと短い間隔でも良いのではないかと思います。

※UTC列の値が空だとエラーとなるので注意。

※表内に存在する行を一覧表示のコネクタは１度に256行までしか取得出来ないようなので、全件処理の場合は更に工夫が必要です。

図：フローの全体像

図：シリアル値の計算を出力コネクタでやらせてます

図：シリアル値でUTC日付6ヶ月前日付を比較

図：行削除コネクタで1行ずつ削除

Excelファイルの閲覧権限

今回、Teamsのチャンネルにアップロードしたログ記録用のExcelファイルですが、プライベートルームの場合は問題ないかと思いますが、そうではない場合閲覧権限が非常に広範囲に公開がデフォルトになっています。また、プライベートルームであっても、自分以外に編集してもらいたくは無いので、制限を掛けたいのですが、Teams上からは出来ません。そこで、以下の手順でチーム内の自分以外のメンバーは閲覧権限になるように、SharePointを使って変更をしておきます。

1. Teamsの該当チャネルのファイルタブを開く
2. アップロードしておいたファイルが出てくるので、「…」をクリックして、SharePointで開くをクリック
3. SharePoint上で同じファイルが表示されます。「︙」をクリックして、アクセス許可の管理をクリック
4. サイドバーが出てきてココでも管理出来るのですが、ちょっとやりにくいので、一番したにある「詳細設定」をクリック
5. SharePoint Onlineの権限管理画面が出ます。所有者以外の権限は「閲覧権限」にしたいので、対象をチェック
6. ユーザ権限の編集をクリック
7. 権限の編集のチェックを外して、閲覧にチェックを入れます。OKをクリック
8. これでファイルの編集は自分以外は出来なくなりました。

図：細かな権限管理はSharePoint上で行う

関連リンク

• HTTPトリガーでクエリパラメータを利用するには
• Power Automate で扱う時刻を日本時間に変換したい
• Power BI から HTTP リクエストをリンクで送って Flow で受け取る方法
• boxのアップロード/ダウンロードイベント発生時にWebhookを飛ばす
• BoxのWebhookをセキュアに受け取るまでの（短い）道のり。
• HTTP request trigger - MS Flow
• Can I get the query parameters of HTTP request?
• Problem with "HTTP request is received" action flow
• Get the query parameters from HTTP Request trigger
• Google Apps ScriptでBox Webhookを受取りkintoneへ流してみた
• Power AutomateでExcelのクエリを行う際に、Excelの列名で気をつけること
• Power Automateで実行日からExcelのシリアル値を求める
• CONVERTING EXCEL DATE TIME SERIAL VALUES WITH POWER AUTOMATE
• Power Automate 「Apply to each(コントロール)」アクション
• Power Automate から SharePoint リストアイテムを取得して件数確認
• Power Automate / Logic Appsで複雑なフィルター条件設定
• Delete Excel Rows X Amount of Days Old
• Box のフォルダー内のファイルが作成または変更されたときに Slack の通知を受け取る
• Do untilを使い、ExcelやSharePointリストのデータ取得数制限を回避して全件取得する