Power Automateで定型メールから情報をExcelへ書き出したい

2022/10/092022/12/25

そういうニーズってありますよね。今回はBillboard Live OSAKAのメールマガジンでいつも教えてくれる新規公園情報を、自動的にSPO(SharePointOnline)に置いたExcelファイルへ書き出すようなフローを作ってみたいと思います。

あらかじめ、Outlookにこのメルマガの移動先として専用のフォルダを作っておきます。Outlookに新着メールが届いたら、自動的に作った専用フォルダへ振り分けさせます。以下のような感じで差出人と件名を実際に届いたメールを参考に指定するとよいでしょう。

自動化したクラウドフローから始める

Power Automateでは決まったフォルダにメールが届いたことをきっかけにフローを実行することにします。届いたら動くを実現するために、作成時に選ぶのは「自動化したクラウドフロー」です。

適当なフローのタイトルをつけ、Outlook365の「新しいメールが届いたとき(V3)」を選択して「作成」へ進みます。

どのフォルダにメールが届いたらフローが動き出すかは、最初から表示されている「フォルダ」で指定します。あらかじめOutlookで専用の受信フォルダを用意していましたので、候補として表れています。これを選択します。

とりあえず届いたメールの中身を確認しておきたいので、「作成」のコネクタを使って、入力を「新しいメールが届いたとき(V3)」の「本文」を指定しておきます。

設定ができたら一度保存して「テスト」を手動で実行してやります。

最初に選択した「自動化したクラウドフロー」では、手動に設定すると待機状態になります。

メールの本文に従ってスクレイビングを仕込んでいくのですが、そう都合よく用意したフォルダに自動振り分けされるメールは届かないと思いますので、Outlook側でフォルダ振り分け条件の「差出人」に関する条件をとりあえず消して、Billboardからすでに届いているメールを自分から自分に転送してやります。

専用フォルダに届いたメールに反応してテストが実行されました。「作成」には本文が表示されています。

てっきりTEXTメールだと思っていましたが、HTMLメールのようでした。本文にはHTMLのタグがたくさんついています。そこで、Content Conversionにある「Htmlからテキスト（プレビュー）」を使ってみます。

コンテンツをHTMLを入力するモードに変えてから、挿入する値として「本文」を選択します。

先ほど一度手動テストを実行しているので、今度は「自動」から先ほど成功したテストを選択します。こうしておくとわざわざOutlookで再度転送をしてサンプルのメールを受信する必要がなくなります。

HTMLのメール本文がすっきりしたプレーンテキストコンテンツに変換されました。

メール本文からどこを取り出せばよいか考える

メール本文をもう一度読み返してみましょう。「■新規公演決定■」の文字があって、その下に罫線がひかれています。次の行から【】に囲まれた公演タイトルがあり、空の行が１行。そこから先は【公演日】【1stステージ】【2ndステージ】【予約開始】が続きます。必ずこの文字列の次に内容が含まれているので、そこを抜き出せばよさそうです。

続いて「・・・・・」で罫線が引かれ、次の行にはURLが現れます。さらに空の行があり、「—–」の罫線で一つの公演情報が終了し、次の行からは別の公演情報が記載されているのがわかります。

公演情報の最後には、太い罫線が引かれ、ここで終わっています。これをキーワードにして処理を終わらせればよさそうですね。

ループを回す前に空欄の行をなくす

前述のように、■新規公演決定■の行から取り込みを開始するわけですが、メールに何が書いてあるかを判定するには、一行ずつ中身を見て条件に当てはまるかをより分けてやらないといけません。

それにはループで回せばよいのですが、メール本文は一塊の長い文字列です。この文字列を１行ずつ配列にしてやりましょう。行の最後はそのままでは目に見えませんが、改行文字が書かれています。

Power Automateで改行文字を簡単に表すには、ちょっと変な感じがしますが、「変数の初期化する」を使って、「改行」のような適当な変数名を付け、種類は「文字列」、値のところに、ただ１行改行を行ってやります。目に見えませんが、値の箱がちょっと縦に広がった状態で、改行文字がこの変数に入ったことになります。

メール本文を見ると、何も書かれていない行がたくさんあります。ループを回す際に行数が多いと無駄が多いですし、空欄行を関数に渡すとエラーなってしまうので、先に空欄行を消してやります。

空欄の行というのは、改行が２回以上連続しているということなので、「作成」を使って以下のようにあらわせます。２つの改行を１つの改行に置き換えています。

replace(body('Html_からテキスト'),concat(variables('改行'),variables('改行')),variables('改行'))

１回改行を置き換えただけではまだ残っているかもしれないので、もう一段階改行の数を減らしてやります。「作成」の名前を変更しました。ついでに一番外側にtrim関数を付けました。これはテキストの前後の余分な空白を取り除いてくれます。これも配列にしたときに無駄な空行を発生させないためです。

trim(replace(outputs('空行置き換え1'),concat(variables('改行'),variables('改行')),variables('改行')))

テキストを配列に変換する

メールを配列化するためにはSplit関数を使います。以前に用意した「改行」を区切りに指定します。

split(outputs('空行置き換え2'),variables('改行'))

テストをしてみると結果は以下の通り。メール本文の一行ずつが配列になっているのがわかります。これでループに渡して一行ずつぐるぐる回すことができます。

あとはひたすら地道なスクレイビング

あとはひたすらメール本文とにらめっこして、その特徴に従って条件を付けていきます。以下のような作戦で行こうと思います。

「■新規公演決定■」が現れたら開始フラグを立てる
開始フラグが立っている間に現れた「━━━━━━」区切りがあれば区切りカウントアップ。
区切りカウントが２を超えていたら処理しない
「【」が含まれていれば、タイトルフラグ立てる
「】」が含まれていれば、タイトルフラグをおろす
タイトルフラグが立っていれば、タイトル変数に追加する。前後の空白は取り除く
[公演日]　から始まれば公演日変数に設定。前後の空白は取り除く
[1stステージ]から始まればステージ１変数に設定、前後の空白は取り除く
[2ndステージ]から始まればステージ2j変数に設定、前後の空白は取り除く
[予約開始]から始まれば予約開始フラグを立てる
「‥‥‥‥‥‥‥‥‥」だったら予約開始フラグをおろす
予約開始フラグが立っていれば、予約開始変数へ追加
httpから始まっていたらURL変数に設定
「————————————————————-」だったら各変数の値をExcelの行へ追加する処理

変数類はこのように用意しました。

ループ全体像はこんな感じ。

ループの中身はこんな感じ。開始フラグが立ったあとは「開始フラグがtrueの場合の処理」の中で動きます。開始フラグが立っている間に現れる区切り線は２つ目が新規公演情報の終わりなので、それより後で誤処理されないようにカウントが２になったら処理が走らないようにAND条件にしています。

各項目は単純に条件に従ってループで渡された各行の値を変数にセットしているだけです。公演日の部分だけ抜き出してみるとこんな感じ。substring関数を使って[公演日]の５文字より後ろの部分だけを公演日変数にセットしています。trim関数で余分な空白を省いています。

trim(substring(items('Apply_to_each'),5))

スコープでくくってあるところ（タイトルと公演日）は複数行にわたっているため、「【」が含まれていたらフラグを立て、「】」が含まれていればフラグを下ろします。フラグが立っている間は変数に文字を「追加」しています。

最後に各公演どうしの区切り線が現れたらExcel表に集めた変数の文字列を行追加しています。

Excel表への行追加はこんな感じです。変数に集めてさえいればシンプルです。

行追加が終わったら変数類のクリアも忘れずに。文字変数には値として空文字を設定できないので、関数モードで「null」を設定してやります。

最後に

本当はなるべくループをつかわず、変数もselectコネクタなどを使って１公演をJSON形式でまとめたりするほうがよかったかもしれません。ループの中で条件をいくつも使っているだけの、ちょっとイケてない処理ですが、とりあえず作ることができました。実行すると１件のメールから以下のように複数のアーティストの公演情報がExcelに無事に書き出されました。