Formsからの投稿でOneDriveのExcel書き込みとカレンダー登録
現在、社内向けにMicrosoft365のPower Automate（Desktopではないほう）の利用に関しておよび、Microsoft365版Excel上級研修（改の研修用資材を作成しています。Power AutomateはIFTTTやZapierのようなタスクランナーサービスですが、これらとの大きな違いが、Microsoft365製品との強力な連携機能と関数が使えるといった独自の機能があります。
今回はMicrosoft Formsから投稿されたら、OneDrive上のExcel Onlineに書き込み（内容によって書き込み先シートを分ける）、Outlookカレンダーに作成期限閾値を1週間として登録といったものを研修サンプルで作っています。この作り方についてのエントリーをここに残しておきます。
今回利用するサービス
いつものSharePoint上ではなく、Microsoft365付属のOneDrive Business上にExcelファイルを作成し、ここをタスク管理台帳として利用する予定です。また、前回の記事同様、Teamsチャンネルへの通知も一応含めております。
事前準備
フォームをまずは作成
前回同様、まずはフォームを作成します。但し今回は特定の値を持って書き込み先シートを分けているので、その為の選択肢を用意します。ネットで「わざわざPAでExcel書き込みを装備しなくても標準でFormsで書き込みできる」等という書き込みがありましたが
では、なぜPAで行わせているか？
- Formの送信項目にない内容（今回の作成期日や処理ステータスといった内容）の書き込み
- Formsの送信内容によって書き込み処理を分岐が必要なケースがある
といった装備をする必要があるため。後から切り替える位なら最初からこれを標準でつくるのは「Google FormのGASで書き込み」を知っていたら既定路線です。ということで、今回は以下のようなスタイルで作成しています。
- 社員番号、社員名、会社名、メアドの入力欄
- 作成する証明書の選択（その他で記載に無いものを依頼も可としている）
- 作成に当っての特記事項を備考欄に書いてもらう
図：作成する証明書の選択で条件分岐をさせます
OneDriveにExcelファイルを作成する
OneDrive Business上に前回同様にExcelファイルを作成します。今回は3つの証明書依頼用シートと「その他の場合」用のシートの計4つを作成し、各シートの入力欄は「テーブル化」して起きます（分かりやすいテーブル名を記述しておきましょう）。
その他のシートだけは、どんな証明書を作成するか？といった列を別途追加しております。また、テーブル化しておかないとPower Automateからレコードの追加が出来ません。
なお、ファイルの情報漏えいを防ぐ為に、共有⇒「このリンクを利用できる対象ユーザ」は「特定のユーザ」として、予めこの画面で閲覧可能なメンバーを追加しておきましょう。閲覧のみや「ダウンロードの可否」等も設定可能です。
図：4つのシートを用意しておく
タスク登録専用カレンダーを用意
Excel Onlineにタスクを登録する際に「作成期限」を7日で設定した日付も書き込みはしますが、当たり前ですがリマインダーも掛からなければ、表を見ただけでは「タスクの集中具合」などは一意に確認出来ません。そこで、専用のカレンダーを用意し、Power Automateで書き込み時にリマインダー通知も入れるので、タスクの未実行を防ぐ事が可能です。以下の手順でOutlookのカレンダーに新規に作成します。他のメンバーがアクセス出来るように閲覧許可も同時に追加しておきましょう。
- WebのOutlookを開き、カレンダーを開きます。
- 「予定表を追加」をクリックし、空白の予定表を作成するをクリックする。
- カレンダー名やアイコン、色を設定し、リストに追加では今回は「個人用の予定表」としました。
- 個人用のカレンダーに作成されたら、そのカレンダー名の「…」をクリックする
- 共有とアクセス許可をクリックし、閲覧できるメンバーのアドレスを追加しておきます。編集許可をするかどうかはお好みで
図：タスク管理用の専用カレンダーを用意
Power Automateで追加機能を装備する
新規フローを作成する
新規にフローを作成します。左サイドのマイフロー⇒新しいフローをクリック⇒自動化したクラウドフローをクリックします。最初のトリガーは、Formsで検索すると出てくる「新しい応答が送信されるとき」を選び適当にフロー名を設定します。
図：最初のトリガーはこれだけ
フォームの指定と値の取得
前項の作成後にはまだトリガーのフローのみで他に何もありません。まずは以下の手順でフォームの指定と値を取得できるようにします。
- 新しい応答が送信される時では、フォームIDをクリックし、前述で作成したフォームが出てくるので選択します。
- 新しいステップをクリックする
- 「応答」と検索して、「応答の詳細を取得する」アクションを選ぶ
- フォームIDは１．と同じものを指定。応答IDをクリックすると、動的なコンテンツダイアログが出てくるので、その中にある応答IDをクリックして選ぶ
これでフォームの指定と値の取得が出来るようになりました。
図：この２つはワンセットで必要です
現在時刻の取得とタイムゾーン変換
今回は前回同様にタイムスタンプとしてPower Automate側で作ってあげます。しかし、そのまま構築すると見慣れた日付の形式ではない上に、時間帯がUTCとなっている為、日本時間ではありません。ここは、２つのコネクタを利用し、タイムゾーンの変更と日付の形式を整えてあげます。このデータをExcelに追記時に合わせて利用します。２つのコネクタは「日付」で検索すると出てくると思います。
- コネクタとして「現在の時刻」を追加します。（時刻で検索すると出てくる）
- 次にコネクタとして「タイムゾーンの変換」を追加します。（日時で検索すると出てくる）
- タイムゾーンの変換では、基準時間に1.のコネクタを指定します。
- 書式設定文字列は「yyyy/MM/dd HH:mm」を入力してカスタム形式として登録します。MMとHHが大文字の理由は、MMでないと分になってしまうという理由と、hhだと12時間表記になってしまうのでHHで24時間表記にする為です。
- 変換元のタイムゾーンはUTC（協定世界時）を指定します。
- 変換先のタイムゾーンはUTC+9:00 大阪、札幌、東京を指定します。
図：タイムゾーン変換が必要です
作成期日等を作成する
さらに、受付日たる現在時刻の値に7日を加えた日付を変数に追加する形で「作成期日」を格納します。
- コネクタとして「変数の初期化」を新規に追加します。（変数で検索すると出てくる）
- 名前は作成期日とし、種類は文字列を指定
- 値は特定の値ではなく、addDays関数にて動的なコンテンツの追加⇒前項で作った「タイムゾーンの変換」を指定して、以下のような数式を構築します。
1addDays(body('タイム_ゾーンの変換'),7) - また、同じ用に条件分岐用にも同じ変数の初期化を追加して、Formの証明書の種類の応答を格納しておくと良いでしょう。
図：７日後の日付をここで生成しておく
条件分岐を設定する
今回はその他を含めて４パターン分岐が必要になり、それぞれでExcelに書き込みを装備するといったやり方を取っています。ただスマートに行うならばテーブル名と選択肢の名称を一致させておけば、前項の条件分岐用の変数をExcelのテーブルで指定すれば、今回の条件分岐自体が不要です。ただ今回はこの「条件分岐で処理を分ける」のを学習する為に、冗長な処理を実装しています。
- 新しいコネクタとして「スイッチ」を追加します（条件で検索すると出てきます）
- スイッチのオンには前項で作っておいた「Formの証明書の種類の応答を格納」を入れておいた変数をしておきます。
- ケース１～３までを作成し、それぞれの次の値と等しいには、選択肢で選ばれた証明書名を入れておきます。
- その他は2.以外になるので、既定というスイッチ内に処理を書き込みます。
- それぞれのケースにコネクタとしてExcel Online(Business)の表に行を追加を追加しておく
図：スイッチを使って条件分岐
Excelに追記処理
前項で追加しておいたExcel Onlineで表に行を追加ですが、今回はSharePointではなくOneDrive Businessなのでちょっと注意が必要です。以下は既定のケースの処理ですが、「作成書類名」の列がありますが、他も殆ど同じです。
- 場所はOneDrive Businessを選択。ドキュメントライブラリはOneDriveを選択します。
- ファイルは予め作っておいたExcel Onlineのファイルを指定します。
- 既定なので今回は「その他」を書き込み先のテーブルとして指定しました。
- 受付日欄は予め作っておいた変換後の時間を指定、作成期日も予め作っておいた7日後の日付を演算させた変数を指定します。
- ステータスはデフォルトで「未着手」の文字
- その他はFormの応答の中から適切なものを列に当てはめておきます。
図：書き込むレコードをここで定義する
自動応答メールを構築
Forms標準の自動応答だと簡素すぎるので、メール送信のフローを追加してカスタマイズされたメールで送信者に自動応答メールを送ることができます。
- 新しいステップをクリックする
- メールの送信で検索して、「メールの送信V2」を追加します。
- 宛先をクリックし、動的なコンテンツダイアログにて、応答の詳細を取得するから、「Responder’s Email」を選びます（但しコレは匿名送信時には使えないので、その場合は、メアド入力欄を設けて入れてもらう）
- 件名、本文を構築。動的なコンテンツダイアログからExcelに追記した内容などのパーツから構築する
最後に保存をクリックするとフローが保存されます。これでForms送信時に各種アクションが順番に発火してくれるようになります。
図：署名やらなにやら細かく構築出来るのでオススメ
Teamsチャンネルに通知チャットを送る
続いて、メールではなく自身が参加してる特定のチャンネルに送信された通知を自動で書き込みするようにしてみます。
- 新しいステップをクリックする
- Teamsで検索して、「チャットまたはチャネルでメッセージを投稿する」を選択する（「メッセージを投稿するV3」ではなくなった模様）
- チームをクリックして、自分の参加してるチームを選択する
- チャネルではそのチーム内にあるチャネルを指定します。
- メッセージでは、Formsの投稿内容を利用できます。応答の詳細を取得するの項目から部品だけ取り出して、本文を構成します。
これで、Forms送信されると、チャネルに通知が飛んできて確認することが可能になります。
図：Teams投稿のフローでメッセージを投稿
カレンダーに登録する
最後にOutlookのカレンダーに今回のタスク依頼を登録します。開始日付は送信日、作成期日は7日後の日付を指定し、リマインダーもセットします。
- 新しいコネクタとして「イベントの作成（V4）」を追加する（イベントで検索すると出てきます）
- 予定表IDは予め作っておいたOutlookのカレンダーを選択します
- 開始日付は変換後の時間、終了時刻は作成期日の７日後の日付を指定した変数を指定します。
- タイムゾーンは+9:00のosaka, tokyoを指定する
- 本文はメール同様適当に作り込む
- リマインダーをオンにするを「オン」を指定する
とりあえずオプションはこれだけ指定しました。色々細かいオプション指定があるので、繰り返し登録なども可能になっていますので、使い所が結構多そうです。また、アラートを出すだけで良ければ、カレンダーではなくPlannerのコネクタを利用してタスク登録をしておくというのも選択肢の一つになるかと思います。
図：細かなオプション設定があります
注意事項
OneDriveのExcelファイルをTeamsに表示したい
Power AutomateはOneDriveやSharepoint上のExcelファイルに対して自動化処理を実行出来ます。しかし、OneDrive上に先に作ってしまったExcelファイルはそのままでは、Teamsのタブのファイル一覧には当然出てきません。この場合以下の手順でファイルを移動する事で、Teams上のファイル一覧に表示させることが出来るようになります。
- Teamsのチャンネルを開き、ファイルタブを開く
- OneDriveへのショートカットの追加をクリックする
- OneDrive Businessを開くとルート直下にチャンネル名のフォルダが出来ている
- 既に作ってしまってるExcelファイルを3.のフォルダへ移動する
- Teamsのファイルタブの一覧に表示されるようになる
- ただし、ファイルのIDが変わるので、Power AutomateでExcelのフローを作ってる場合にはファイルへのアクセスを修正する必要があります。
図：Teamsからショートカットを追加
図：OneDriveにフォルダが出来た
Teamsに編集画面としてFormを追加したい
前述の方法だと個人所有のフォームについては、Teamsに追加時には「フォーム投稿画面」として追加されます。よって、社内で申請書やアプリの１つして表示する分には問題ありませんが、プロジェクトチームとして編集画面としてTeamsに追加したくても、追加時に「共同作業」の項目が出てきません。
この場合、どうしたらTeamsに編集画面としてのFormsを追加できるか？ですが、以下の手順で変更が必要になります。
- Formsのサイトを開く
- 対象のフォームの右下にある「…」をクリックして、「グループに移動」をクリック
- 追加するTeamsのチャンネルグループを選択して、移動をクリックする（Formがチーム所有に変わります）
- Teamsの対象チャンネルでFormsを追加する
- 追加するFormを選んだら、下のプルダウンの中に出てくる「共同作業」を選択する
- これで、編集画面としてTeamsのタブにFormsが追加されます。
但し、この処理を行うとPower AutomateのFormsのフローにてフォームIDにはリストに出てこなくなってしまいます（よって、FormのIDを直打ちして指定する必要があります）。
図：グループに移動しなければならない
図：Teamsに追加時の選択画面
図：編集画面として追加できた
チーム所有のフォームについて
Form送信からのPower Automateによる自動化処理についてですが、いくつかの注意事項があります。
- 新しい応答が送信される時のコネクタに出てくるのは「自分がオーナーで所有してる」フォームについてのみです。
- 故にTeams上などでチームのチャンネルに追加した場合には、一覧には出てきません。
- またこの時、フォームの選択画面にはフォーム名とIDが出てくるのですが、同じ名前のフォームをいくつも作ってしまうと、混乱し失敗を招く原因になるので、フォームの名称は一意のわかるものにしましょう。
図：トマトという名のチーム所有のフォームは出てこなかった
では、Teams上にチーム所有としてしまったフォームについて、Power Automateで処理をしたいとなった場合にどうしたら良いのか？一応以下の手順でできなくはないのですが、本来は部門アカウントを用意し、その部門アカウントでフォームを作って所有者にしておくべきだと思います。
- Formsの画面を出し、対象のフォームを直接開き、編集画面を出す
- URLの中に「id=」の部分があるので、このIDの値をコピーしておく
- 「新しい応答が送信されるとき」の選択肢には出てこないので、「＋マークのカスタム項目の追加」をクリック
- 2.の値をダイアログに入力して、OKをクリック
- 「応答の詳細を取得する」のコネクタに於いても、3.と同様に作業をして、IDを直接入力する
- これで、以降の応答IDに紐付けられた内容を取得する事が可能になります
ただし、上記の手順の通りちょっと煩雑な上に、フォーム名等が表示されていないので、別の担当に引き継ぐ時等に留意しておかなければならない為、スマートじゃない方法になってしまいます。
一応この手法でやってみたところ、フローボットではきちんと動作しています。
図：ID直指定が必要になってしまう
