PowerAutomate: リストで設定した複数の時刻に通知をさせる

# 背景

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

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


# 概要

## 元となるリスト

- とりあえず、以下を用意

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

実際には以下|

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/089ea712-c31d-6435-2a87-1a500e44cf42.png)

## フローイメージ

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/24825929-2531-af2c-0478-8db1e1dc640d.png)


# 詳説

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/78659db4-c947-9d1c-65ad-61ac32276558.png)

とりあえず以下の条件で
- 上限60回
- アイテムが取得出来なくなるまで。 

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

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



![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/131a430d-ae24-01b9-8ed9-294ce4d78d5f.png)


:::note warn

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

:::

https://qiita.com/baku2san/items/99a41b1aae9903541818

:::note warn

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

:::

https://qiita.com/baku2san/items/1ed05b6ba9c25a1857ac

## 再利用 

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/f4a42389-c3e4-54e8-f24c-404115994032.png)

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

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

https://qiita.com/baku2san/items/6fa63d15198c2a20f61c

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/c49e8d78-122c-79f4-2ca9-e97124250f44.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/77803ddc-65ce-0b26-f56e-4d07752ddd15.png)

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

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

# コード例

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

```json: 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"}}}
```

</details></summary>

# あとがき

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