Power Automateのスケジュール済みクラウドフローを使う

Power Automateにはタスクランナーな「自動化したクラウドフロー」の他に、HTTPアクセス等をトリガーに発動する「インスタントクラウドフロー」の他に、日付や時刻を元に自動的に発火する「スケジュール済みクラウドフロー」があります。これは、Google Apps Scriptならお馴染みの「スクリプトトリガー」、LinuxサーバならばCron、Windowsならば「タスクスケジューラ」のようなもの。

１度セットしたら後は、指定の間隔や期日をもって自動的に動きそして完了するので、業務の無い夜中に発動させる等で自動処理をするといったように、使い所が結構多いです。今回はこのスケジュール済みクラウドフローを使って、OneDriveのExcel Onlineを操作し、学んでいきたいと思います。

1 今回利用するサービス
2 事前準備と基礎知識
- 2.1 OneDriveにExcelファイルを準備
- 2.2 スケジュール済みクラウドフローの基礎知識
3 ケース別フローの作り方
4 今回のフローのサンプル
- 4.1 フローサンプルダウンロード
- 4.2 フローのインポート方法
5 関連リンク

今回利用するサービス

過去にBox Webhookで用いてる「インスタントクラウドフロー」や、タスクランナーな「自動化したクラウドフロー」は以下のエントリーで紹介しています。

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

Formsからの投稿でOneDriveのExcel書き込みとカレンダー登録

事前準備と基礎知識

今回考えた使い所は、深夜にOneDrive上のExcelファイルに対して色々と行う処理を、自動で行わせる事。主に以下の3本。

特定の列のデータをクリアする
Excelのシート上のデータを全クリアする
締切日付を読み取り、アラート通知をTeamsに自動で送信する

OneDriveにExcelファイルを準備

Power AutomateからExcelを操作する場合、基本的にはExcelのテーブルに対して行うことになるので、しっかりと事前準備が必要です。

OneDrive Businessを開く
新規⇒Excelブックにて、Book.xlsxを作成する
Sheet1に以下の図のようなデータを作成する
データ範囲を選択し、挿入⇒テーブルを選び、先頭行をテーブルの見出しとして使用するにチェックを入れる
テーブルデザインタブを開き、左上のテーブル名を「member」にする

このファイルに対して、Power Automateで処理をさせます。

図：テーブル化しておく必要があります。

スケジュール済みクラウドフローの基礎知識

Power Automateを開き、マイフローにて新しいフローから、スケジュール済みクラウドフローを作るわけですが、細かいスケジュール条件を作れるわけではなく、繰り返し間隔にて

毎月、毎日、毎時、毎分、毎秒で時間を指定する
毎週にて「日〜土までの所定の曜日」を指定して実行する

の２パターンになります。この時以下のようなことに注意を払う必要があります。

Power Automateは１日に実行できるタスクには上限があるため、毎秒等の場合リミットに到達してしまう可能性があります。
上記のようなパターンしかないため、祝日には実行しないといったような事は、フローの中で独自に実装が必要です。
平日に1時間毎に実行といったような組み合わせは作れません。平日判定を独自に実装が必要です。

また、初期の作成後の作業として、必ずタイムゾーンの指定が必要です。これを行わないと「UTC」の時間で実行されてしまいます。作成後で以下の作業を行います。

フローの「Recurrence」を開く
詳細オプションを表示するをクリックする
タイムゾーンをUTC+09:00の大阪、札幌、東京を指定する
保存をクリックする

この時、指定の時刻を複数指定する事が出来ます（また分の指定も可能です）。

図：毎週だけちょっと指定が異なる

図：タイムゾーンの変更

ケース別フローの作り方

Power AutomateにてExcel Onlineを操作する際に自分の場合よく利用するのが「データのクリア」と「行を読み取ってアラート通知」の２つ。今回はシンプルにこの2つのパターンについて構築してみたいと思います。

Excelの特定列のクリア

出勤表等に於いて、毎日どこに出勤しているのか？といったようなケースの場合、従業員に毎朝場所を記入してもらうケースがあります。この時、深夜0時に出勤場所の列の値だけを消したい（レコードを削除したい訳じゃない）。といった場合に利用します。基本毎晩作動してはクリアし、翌日はまた書き込む。そうでなければ、前の日の出勤場所が出てきてしまうのでこのフローは使い所が多いと思います。

OneDrive Businessに対象のExcelを作成し、範囲はテーブル化しておく（今回はmembers.xlsxとテーブル名はsheet1のmembersとしました）
スケジュール済みクラウドフローを追加（間隔は1日単位）
Recurrenceのフローについて、詳細オプションを開き、タイムゾーンを設定、深夜0時作動なので設定時刻は0として指定。
次に、Excel Online Businessのフローの「表内に存在する行を一覧表示」を追加する
4.について、OneDrive Businessの1.のファイルとテーブル名を指定する
次に、Excel Online Businessのフローの「行の更新」を追加する（自動的にApply to eachのフローに括られます）
5.と同様にファイルとテーブル名を指定する
行を特定できる列をキー列に指定する（今回はID列）⇒ ユニークな連番である必要があります。
キーの値では、動的な値をクリック⇒IDの列を選択する
今回は勤務場所をクリアしたいだけなので、勤務場所列のボックスにて、式を追加をクリック⇒expressionに「''」を記述する（シングルコーテーション2個）
保存をクリックし、手動でテストして列の値が消えたら成功

図：1行ずつ値をクリアする必要がある

Excelの特定行の削除

Excel Onlineの操作で次によく利用するのが、例えばステータス欄を見て「完了」となっているデータについて、テーブルから削除するといったような処理です。こちらはレコードを削除してしまう処理なので、事前にバックアップ等をしておくと良いでしょう。主にタスク管理等で使うシーンがあるのではないかと思います。

OneDrive Businessに対象のExcelを作成し、範囲はテーブル化しておく（今回はmembers.xlsxとテーブル名はsheet2のsinseiとしました）
スケジュール済みクラウドフローを追加（間隔は1日単位）
Recurrenceのフローについて、詳細オプションを開き、タイムゾーンを設定、深夜0時作動なので設定時刻は0として指定。
次に、Excel Online Businessのフローの「表内に存在する行を一覧表示」を追加する
4.について、OneDrive Businessの1.のファイルとテーブル名を指定する
次にコントロールフローから「Apply to each」を追加する。以前の手順から出力を選択では、valueを選択する
続けて、アクションの追加にて、コントロールフローから「条件」を追加する
条件は、「ステータス」が次の値に等しいにて「完了」を指定します
「いいえ」の場合は特にアクションを追加しません。
「はい」の場合は、アクションを追加にて、Excel Online Businessのフローの「行の削除」を追加する
5.と同様にファイルとテーブル名を指定する
行を特定できる列をキー列に指定する（今回はID列）⇒ ユニークな連番である必要があります。
キーの値では、動的な値をクリック⇒IDの列を選択する
保存をクリックし、手動でテストして特定の行が削除されたら成功

図：ステータス欄を見て削除を実行

図：条件判定させながら削除をするフロー

Excelの日付列を見てTeamsに期限切れ通知

本来タスク管理はPlannerを使うほうが自動でリマインドもしてくれるので良いのですが、Plannerは視認性が悪く一覧表形式でも無い為、他との連携をする点では使い勝手が非常によろしく無い。という事で、Excel Onlineに記述したほうが結果的に楽というケースは多いです。しかし、Excelの場合は当然、タスク期限が来てもリマインドしてくれるわけじゃない。

という事で、Power Automateが発動した時点で期限の値を超えてるレコードを集めて、Teamsに通知するものを作りたいと思います。

OneDrive Businessに対象のExcelを作成し、範囲はテーブル化しておく（今回はmembers.xlsxとテーブル名はsheet3のtaskとしました）
スケジュール済みクラウドフローを追加（間隔は1日単位）
Recurrenceのフローについて、詳細オプションを開き、タイムゾーンを設定、深夜0時作動なので設定時刻は0として指定。

変数の初期化フローを追加して、値欄に関数にて以下のような関数の「今日の日付のシリアル値」を算出して格納します（変数の種類は整数を指定）

add(div(sub(ticks(startOfDay(convertFromUtc(utcNow(),'Tokyo Standard Time'))),ticks(startOfDay(convertFromUtc('1899-12-31T00:00:00Z','Tokyo Standard Time')))),864000000000),1)

1	add(div(sub(ticks(startOfDay(convertFromUtc(utcNow(),'Tokyo Standard Time'))),ticks(startOfDay(convertFromUtc('1899-12-31T00:00:00Z','Tokyo Standard Time')))),864000000000),1)

変数の初期化フローを追加して、変数の種類文字列を指定します（値は無し）：期限超えという名称を付けています
次に、Excel Online Businessのフローの「表内に存在する行を一覧表示」を追加する
4.について、OneDrive Businessの1.のファイルとテーブル名を指定する。詳細オプションを開き、DateTime形式は「Serial Number」を指定する
次に、Excel Online Businessのフローの「行の取得」を追加する（自動的にApply to eachのフローに括られます）。詳細オプションを開き、DateTime形式は「Serial Number」を指定する
5.と同様にファイルとテーブル名を指定する
行を特定できる列をキー列に指定する（今回はID列）⇒ ユニークな連番である必要があります。
キーの値では、動的な値をクリック⇒IDの列を選択する
コントロールフローから「条件」を追加します
左側の値の選択には以下のような条件式を数式で追加します

int(outputs('行の取得')?['body/期限'])

1

int(outputs('行の取得')?['body/期限'])
次の値未満を指定し、右側の値の選択には4.の今日の日付のシリアル値の変数を指定します
「いいえ」の場合は特に何もしません
「はい」の場合は、変数の文字列変数に追加フローを追加し、変数は5.の期限超えという変数を指定
変数には、「タスク名」に加えて、以下の数式で変換を掛けた期限の値をつなげて格納します。

formatDateTime( addSeconds('1899/12/30T00:00:00', int(string(mul(float( outputs('行の取得')?['body/期限'] ), 86400.0)))) , 'yyyy年MM月dd日')

1
2
3

formatDateTime(
addSeconds('1899/12/30T00:00:00', int(string(mul(float( outputs('行の取得')?['body/期限'] ), 86400.0))))
, 'yyyy年MM月dd日')

Excelの期限の値は、行の取得にて日付はシリアル値で取得されるので、この値と今日の日付のシリアル値とを比較して日付の比較を行わせています。
最後に16.に集められた期限切れの項目一覧をTeamsの特定のChannelに対して、本文に16.の変数を指定して、送信すれば、Teamsに期限切れ一覧がまとめて送信されます。（今回はフローボットで通知送信させています）