背景

一定時刻周期でアラームが必要な作業があった。

とはいえ、完全に周期ではない・・
ということで、リストで設定した時刻で通知してくれるようにしてみた。

概要

元となるリスト

とりあえず、以下を用意

列名	型	目的
アラーム	Datetime	通知時刻設定用
HasDone	bool	通知した際に

実際には以下|

フローイメージ

詳説

ループで、アイテムあるまで実行

とりあえず以下の条件で

上限60回
アイテムが取得出来なくなるまで。

フィルタークエリーを使って、次のアラーム行を取得

Filter Query, Order By を利用して、次のアイテムを取得

日時にフィルタークエリーを適用する際は、UTC ってところに注意

Boolでフィルタークエリーも躓きポイント

再利用

Sharepoint のサイトや、リストIDなんかは、一か所にまとめたいのでアクションのinputs から取得しておく

IDを調べて設定することも可能だが、ここでは動的コンテンツで選択したものを、取得するようにしている

取得結果がある場合のみ継続させる判断

ループ条件と一緒。
アイテムがあれば、そこまで待たせて通知をして、ループ継続

対象時刻まで待たせて通知するだけ

フィルタークエリーと同様、待たせるのも UTC なので注意。

通知した後は、アイテムを通知済みに設定するのも忘れずに

コード例

ループにまとまってるので、そこをクリップボードコピーしたもの

code sample

{"id":"4442d8ad-7fb7-4c49-ae7e-b35a2c3bd717","brandColor":"#486991","connectionReferences":{"shared_teams":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_teams/connections/shared-teams-864099d5-54a7-4002-93b6-565ecf481128"}},"shared_sharepointonline":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-15e7a58d-6555-418d-a955-a6b286a76a7b"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzQ4Njk5MSIvPg0KIDxnIGZpbGw9IiNmZmYiPg0KICA8cGF0aCBkPSJNMTEuNDI5IDIwLjMxaDEyLjU3MXYxLjE0M2gtMTMuNzE0di0zLjU0M2wtMS40ODYgMS40ODYtLjgtLjggMi44NTctMi44NTcgMi44NTcgMi44NTctLjguOC0xLjQ4Ni0xLjQ4NnoiLz4NCiAgPHBhdGggZD0iTTIyLjg1NyAyMS4zNjh2LTkuODM2aC04di0uOTg0aDkuMTQzdjEwLjgyIi8+DQogIDxwYXRoIGQ9Im05LjE0MyAxMC41NDhoMy40Mjl2My40MjloLTMuNDI5eiIvPg0KIDwvZz4NCjwvc3ZnPg0K","isTrigger":false,"operationName":"Do_until","operationDefinition":{"type":"Until","expression":"@equals(length(outputs('Get_items')?['body/value']), 0)","limit":{"count":60,"timeout":"PT1H"},"actions":{"Get_items":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_sharepointonline","operationId":"GetItems","apiId":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline"},"parameters":{"dataset":"https://shimakuma-my.sharepoint.com/personal/shima_kuma_shimakuma_onmicrosoft_com/","table":"53c7868d-516d-483b-9d51-979039b5fb9c","$filter":"(OData__x30a2__x30e9__x30fc__x30e0_ ge '@{body('Current_time')}') and (HasDone ne '1')","$orderby":"OData__x30a2__x30e9__x30fc__x30e0_ asc","$top":1},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{"Current_time":["Succeeded"]},"metadata":{"operationMetadataId":"d6b0fe00-a7e2-4bd8-b9dd-fca386baeb01"}},"Condition":{"type":"If","expression":{"equals":["@length(outputs('Get_items')?['body/value'])",0]},"actions":{},"runAfter":{"Scope_Get_items_Action_の再利用にinputs_の取得":["Succeeded"]},"else":{"actions":{"Post_card_in_a_chat_or_channel":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_teams","operationId":"PostCardToConversation","apiId":"/providers/Microsoft.PowerApps/apis/shared_teams"},"parameters":{"poster":"Flow bot","location":"Chat with Flow bot","body/recipient":"@triggerOutputs()['headers']['x-ms-user-email-encoded']","body/messageBody":"@outputs('Compose_Message')"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{"Compose_Message":["Succeeded"]},"metadata":{"operationMetadataId":"f724842d-f980-4b66-a1d2-06db8e7dc1ec"}},"Update_item":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_sharepointonline","operationId":"PatchItem","apiId":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline"},"parameters":{"dataset":"@outputs('Compose_dataset_as_siteURL')","table":"@outputs('Compose_table_as_ListID')","id":"@outputs('Compose_ItemID')","item":{"HasDone":true}},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{"Post_card_in_a_chat_or_channel":["Succeeded"]},"metadata":{"operationMetadataId":"52ae21af-bd85-4874-bdc4-4219e0abd39f"}},"Compose_Message":{"type":"Compose","inputs":{"type":"AdaptiveCard","$schema":"http://adaptivecards.io/schemas/adaptive-card.json","version":"1.4","msteasms":{"width":"full"},"body":[{"type":"TextBlock","text":"アラーム通知","wrap":true,"color":"Accent"},{"type":"TextBlock","text":"@{body('Convert_time_zone_WaitedTime')} になりました。","wrap":true}]},"runAfter":{"Delay_Until_2":["Succeeded"]},"metadata":{"operationMetadataId":"df8e7421-e67c-41b5-8ede-34414f8e40a7"}},"Delay_Until_2":{"type":"Wait","inputs":{"until":{"timestamp":"@{outputs('Compose_Time')}"}},"runAfter":{"Scope_取得したアイテムからのプロパティ取得":["Succeeded"]},"metadata":{"operationMetadataId":"e29377ae-edfd-4e25-a072-edf192d2251a"}},"Scope_取得したアイテムからのプロパティ取得":{"type":"Scope","actions":{"Compose_firstItem":{"type":"Compose","inputs":"@first(outputs('Get_items')?['body/value'])","runAfter":{},"metadata":{"operationMetadataId":"37b97382-728c-4109-b9cb-dc9d0e620b29"}},"Compose_ItemID":{"type":"Compose","inputs":"@outputs('Compose_firstItem')?['ID']","runAfter":{"Compose_Time":["Succeeded"]},"metadata":{"operationMetadataId":"d568543f-1492-4229-b133-fe2aa64c6dba"}},"Compose_Time":{"type":"Compose","inputs":"@outputs('Compose_firstItem')?['OData__x30a2__x30e9__x30fc__x30e0_']","runAfter":{"Compose_firstItem":["Succeeded"]},"metadata":{"operationMetadataId":"c8649cdd-6948-482b-aadf-a849045bc369"}},"Convert_time_zone_WaitedTime":{"type":"Expression","kind":"ConvertTimeZone","inputs":{"baseTime":"@{outputs('Compose_Time')}","formatString":"yyyy/MM/dd HH:mm:ss","sourceTimeZone":"UTC","destinationTimeZone":"Tokyo Standard Time"},"runAfter":{"Compose_ItemID":["Succeeded"]},"metadata":{"operationMetadataId":"0aceb4e8-1e4a-4a40-998d-c76105d0f454"}}},"runAfter":{},"metadata":{"operationMetadataId":"26292102-122d-4f85-8974-a7207e9f3ebc"}}}},"description":"true: 空っぽ / false: 取得出来たので処理が必要","metadata":{"operationMetadataId":"ea6c9876-cf19-4e33-a841-47d6ac2ba55d"}},"Current_time":{"type":"Expression","kind":"CurrentTime","inputs":{},"runAfter":{},"metadata":{"operationMetadataId":"b45cb409-a33e-433b-a084-dd99748fb6fc"}},"Scope_Get_items_Action_の再利用にinputs_の取得":{"type":"Scope","actions":{"Compose_trigger_inputs":{"type":"Compose","inputs":"@actions('Get_items').inputs","runAfter":{},"metadata":{"operationMetadataId":"7c0f8b6e-ba59-44b5-8a7d-7880fd4cd1a5"}},"Compose_dataset_as_siteURL":{"type":"Compose","inputs":"@actions('Get_items').inputs.parameters.dataset","runAfter":{"Compose_trigger_inputs":["Succeeded"]},"metadata":{"operationMetadataId":"70cefa0f-d5ff-4a3b-9654-6ab733c2992a"}},"Compose_table_as_ListID":{"type":"Compose","inputs":"@actions('Get_items').inputs.parameters.table","runAfter":{"Compose_dataset_as_siteURL":["Succeeded"]},"metadata":{"operationMetadataId":"a658888b-0648-4db3-b768-e47d6191249b"}}},"runAfter":{"Get_items":["Succeeded"]},"metadata":{"operationMetadataId":"03cb4717-0fe7-4a14-9287-cc313680f096"}}},"runAfter":{},"metadata":{"operationMetadataId":"0ce4db6e-87f9-4510-9825-a34f3de64872"}}}

あとがき

あとはこれを会社で復元すればいいだけ・・