こんにちは、おかちゃんせんせいです!
これまで業務効率化する上で、作業を自動化する方法の一つとしてWebスクレイピングの記事を書いたことがありました。
【業務効率化】Windows版Excel VBAでChromeを自動操作して、Webスクレイピングする方法(初期設定編)この時はExcel VBAを選択しましたが、自動化する方法として紹介した方法のうち、前回紹介していない自動化ツール『Power Automate』を今試験運用しています。
まだ初心者でほとんど理解できていないことも多いですが、初心者なりに試行錯誤してチャレンジしてみて、なかなかドンピシャな内容の記事がなかったので、記事にまとめておくことにしました。
※なお、今回の記事については、実機がMacでPower Automate Desktopがインストールできないため、クラウド上で実行するPower Automateだけを使用した方法になります。
(追記:2024年04月22日)
ご質問いただいた内容に回答するために、全体的に内容を更新いたしました。
目次
今考えている構想をPower Automateで実現できるかどうか?
現在、管理しているデータのうち特定の日付データが実行日になったら、ある処理を自動処理するフローを構想しています。
ある処理とは、たとえば条件を満たしたらメッセージを送ったり、別サービスに処理を流したり、請求書を発行したり。
とにかく、他のアプリと連携してデータを定期的にチェックして、条件次第で自動反映させる処理を考えているのです。
その上で、日付のジャッジが重要になるわけですが、、、
Power AutomateとExcelの開発元は同じMicrosoftなのに、調べていくうちにとても厄介なことが判明。
それが、取得する日付データの書式が両者で違う、という最も厄介な展開です。
書式が違うだけで異なるデータと認識する
Excelを操作していてよくありがちなのが、見た目同じ値に見えても、数式では異なるデータとして認識されているケースです。
よくあるのが書式が『数値』の認識でいるけど、Excelでは『文字列』として認識されてしまっているパターンです。
【解決】Excel VBAで書式設定の変更をしたのに反映されない件(特に、数値→文字列)あと、時々あるのが『日付』としてデータに取り込んだのに、なぜか5桁の数値として取り込まれてしまっているパターンです。
これは、Excelでは日付はシリアル値で表現され、表示形式が『日付』のように見せているためです。
この現象が、なんとPower AutomateとExcel間で起きてしまうのです・・・。
1899年12月30日を「0」とした連番のこと(Power Automateの場合)
自作で作ったプログラムならいざ知らず、両方とも天下のMicrosoftのアプリなのにー!!
——と嘆いても仕方ないので、どちらかの書式に合わせて認識させてあげるしかありません。
Step0:Excel側で表データをテーブル化しておく
事前準備として、2つのことを実施します。
まずはExcelデータはOneDriveに保存しておくこと。
そして、もう一つは、Excel側で表データをテーブル化しておくことです。
表を作成したら表の一部のセルを選択し、[挿入] – [テーブル]を選択します。
もし過不足があれば、範囲を修正します。
表がテーブル形式になっていることを確認します。
そして、テーブル名がデフォルトでは「テーブル1」になっているため、任意の名前に変更しておきます。
今回は「参照用テーブル」としておきます。
テーブル名は「転記用テーブル」としておきます。
上記設定をしたら、今度はPower Automate側の設定になります。
Excelの表全データを取得するには、
表内に存在する行を一覧表示
というアクションを追加します。
ここで一つ注意なのが、
OneDriveが個人用なのか、Business用なのかで選択するアクションが異なります。
今回はBusiness用のOneDriveに保存した場合のPower Automateの設定方法について共有いたします。
※個人用の場合は、「場所」と「ドキュメント ライブラリ」がありません。
- 新しいアクションを追加する。
- 「表内に」で検索して、「表内に存在する行を一覧表示」というアクションを選択する。
このとき、Excel Online (Business)になっているアクションを選びます。 - 場所:OneDrive for Business、ドキュメント ライブラリ:OneDrive、ファイル:表データのあるファイルを選択、テーブル:任意の名前に変更したテーブル名
Step1:Power Automate側の処理実行日の日時データを取得する
まず最初に行うのが、Power Automateで処理が実行された日付のデータを取得することです。
- 新しいアクションを追加する。
- 「組み込み」→「日時」→「タイム ゾーンの変換」というアクションを選択する。
- 名前:utcNow()、変換元のタイムゾーン:協定世界時、変換先のタイムゾーン:大阪、札幌、東京、書式設定文字列:世界共通の並べ替え可能な日時パターンを入力する。
ポイントは、基準時間を式utcNow()で表現することです。
utcNow()関数は世界標準時間を取得するため、処理を実行したい場所に応じて変換先のタイムゾーンは変更する必要があります。
Step2:取得した日時データを加工する
次に、取得した時刻を0000-00-00(年-月-日)のデータに加工するために、substringで必要な部分だけを抽出します。
- 新しいアクションを追加する。
- 「組み込み」→「変数」→「変数を初期化する」というアクションを選択する。
- 名前:日付変数、値:substring(variables(‘日付変数’),0,10) を入力する。
なぜこの処理をしたいのかというと、あくまで判断基準にしたいのは日付であって、時刻データは必要ないからです。
utcNow()で取得するデータは、例えば “2022-08-25T12:34:56.7890000” のように時・分・秒だけでなくミリ秒まで表示された文字列データになります。
なので、左から数えて10文字分の文字列だけ抽出するために、substring(body(‘タイム_ゾーンの変換’),0,10) と入力することで、この部分 “2022-08-25” のみ取得したデータを変数に設定します。
補足として、body(‘タイム_ゾーンの変換’)の部分は手入力してもいいですが、画面上で選択することで表示させることもできます。
Step3:繰り返し処理するアクションを追加する
次に、繰り返し処理をするアクションを追加します。
- 新しいアクションを追加する。
- 「組み込み」→「コントロール」→「それぞれに適用する」というアクションを選択する。
- 「表内に存在する一覧表示」で取得した動的なコンテンツvalueを選択する。
Step4:Excelで取り込んだ日付データの書式を変更する
- 新しいアクションを追加する。
- 「組み込み」→「日時」→「時間への追加」というアクションを選択する。
- 基準時間:1899/12/30、間隔:日付(※Excelで取り込んだデータ)、値:日 を入力する。
Excelから取り込むデータは、アクション『時間への追加』を追加するだけで済みます。
この設定をすることで、取り込んだ日付データはISO8601形式である “2022-08-25T00:00:00.0000000” として変換されるので、最後に “2022-08-25” の形に変換させます。
Step5:Step4のデータを加工する
- 新しいアクションを追加する。
- 「コントロール」→「条件」というアクションを選択する。
- 比較元のデータにsubstring(body(‘時間への追加’),0,10) と入力する。
Step2と同じように、形式を“2022-08-25” の形に変換させます。
そうすることで、時・分・秒を考慮しない形で、日付のみで比較することが可能になります。
Step6:日付が一致する場合に、参照用テーブルに転記済みフラグを入れる
条件分岐の「はいの場合」に「行の更新」アクションを追加します。
今回は、参照用テーブル内にあるユニーク項目「管理番号」をキー列として、転記済みフラグとして転記列には「〇」を入力します。
※個人用の場合は、「場所」と「ドキュメント ライブラリ」がありません。
- 新しいアクションを追加する。
- 「Excel Online (Business)」→「行の更新」というアクションを選択する。
- 場所:OneDrive for Business、ドキュメント ライブラリ:OneDrive、ファイル:表データのあるファイルを選択、テーブル:任意の名前に変更したテーブル名、キー列:ユニーク項目(今回は管理番号)、キー値:ユニーク項目の動的コンテンツ、転記:〇(任意)
Step7:転記用テーブルを更新する
最後に、転記用テーブルに各項目データを反映させます。
※個人用の場合は、「場所」と「ドキュメント ライブラリ」がありません。
- 新しいアクションを追加する。
- 「Excel Online (Business)」→「表に行を追加」というアクションを選択する。
- 場所:OneDrive for Business、ドキュメント ライブラリ:OneDrive、ファイル:表データのあるファイルを選択、テーブル:任意の名前に変更したテーブル名、管理番号:管理番号の動的コンテンツ、名前:名前の動的コンテンツ、日付:日付の動的コンテンツ
全体フローを見直して完成!
日付の調整さえできれば、あとは『条件』アクションで条件分岐させればOK!
下図のフローは、サンプルで作ったものです。
このフローを実行すると、参照用テーブルでは本日日付(2024-04-22)に合致する管理番号S0001の転記列に「〇」が更新されます。
また、管理番号S0001のデータが転記用テーブルに転記されます。
まだPower Automateを触り始めて1ヶ月も経っていないような初心者ですので、もしもっと簡単にできる方法(Power Automate Desktopを使わずに)がありましたら、ご教示いただけますと幸いです。
今後のPower Automateのアップデートで簡単に日付比較ができるようになるのを願っています。
最後まで読んでいただき、ありがとうございました!
一つ目は、
侍エンジニア塾
です。
VBAやJavaScript関連でネット検索したことがある方であれば、一度は見かけたことある名前かと思います。
JavaやPython、C言語、VBA、JavaScriptなど、様々なプログラミング言語をオンラインで習得するためのサービスを提供しているところなので、自分が習得したい言語がもしありましたら、無料体験レッスンを受けてみると良いかもしれません。
そこでプロ講師に相談をして、習得するプログラミング言語に対して直接質問するのがベストです。
ストアカ
です。
こちらはあえてプログラミング学習専門ではなく、もっと手軽に学ぶ機会を得るサービスをチョイスしてみました。
VBAやJavaScriptなどのワンツーマンレッスンを開催している講師が多数登録しているので、まずは出費を抑えて学習したいと考えている方にお勧めです。
以下について確認したいです。
確認① Step2:取得した日時データを加工する
確認② Step3:Excelで取り込んだ日付データの書式を変更する
確認①
変数を設定する をしたら、アクションが1つ増えて、2つになりませんか?最後の完成フローでは紫のアクションが1つです。
確認②
エクセルを張り付けるときの方法が分かりません。
是非教えてほしいです。
こあら様
ご質問いただきありがとうございます!
⇒こちらについて、ご指摘の通りでしたので再度記事を作り直しました。
⇒こちらについて記事に載せていなかったため、追加いたしました。
ご確認よろしくお願いいたします。
こんにちは!
まさに今、Excelから当日日付に合致したデータをPAで取得したいためとても勉強になります。
2点質問させてください。
Step2
「変数を設定する」という工程がありますが、全体フローを確認すると、「変数を初期化する」というアクションのようです。
「変数を初期化する」でしょうか??
Step3
「コントロール」→「それぞれに適用する」のアクションで、「表内に存在する一覧表示」で取得した動的なコンテンツ「value」を選択しましたが、次のStep4でのアクション「時間への追加」で、間隔のところにExcelで取り込んだデータが動的な値として選択できません。選択肢に出てこないです。。
方法についてぜひ教えていただけますと幸いです。
よろしくお願いします。
かな様
⇒こちらについては修正漏れだったため、「変数を初期化する」アクションが正しいです。
ご指摘いただきありがとうございました。
⇒「表内に存在する一覧表示」で取得した動的なコンテンツ「value」を「それぞれに適用する」で選択できた場合、それ以降でもExcelに表示されるはずです。
もし選択肢が表示されていない場合、動的なコンテンツとして何が表示されているのかスクショを共有いただけますでしょうか。
※このような一覧が表示されていればよいのですが、、、
おかちゃんせんせい様、ご返信ありがとうございます。
時間への追加について、スクショを共有できないのですが、表示されている動的な値は以下です。
「手動でフローをトリガーします」
緯度
経度
「Apply to each」
現在のアイテム
おかちゃんせんせいのスクショでは、間隔バーの右下部分に「動的なコンテンツの追加」があるかと思うのですが、そのような表示がない状態です。。
かな様
経度・緯度については、「表内に存在する一覧表示」でExcelのテーブルから取得できるようになっておりますでしょうか?
フローの全体像が不明な状態では的確なアドバイスができず、大変恐縮なのですが・・・
お問い合わせからフローの全体像をスクショでもし共有いただけましたら、可能な限りサポートさせていただきます。
おかちゃんせんせい様
ありがとうございます。
もう一度私の方でもフローなど整理して、お問い合わせさせていただこうと思います。
よろしくお願いいたします。