PowerAutomate: Teams チャネル内で、スレッド毎にアイディア投稿をした際の、いいね分析

# 背景
チャネルでアイディアを投稿共有し、Reactions で投票することになった。
スレッドにはアイディアを膨らませる為の返信が可能。
最終的に親スレッドの Reactions を解析するつもりなんだろうけど、手集計しそうだったのと、ちょうど Reactions 分析してたので、見える化をしてみた記録

## イメージ
こんな感じのスレッドがあって

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/08ecf1f3-3e6f-1435-0f41-8d77fae1e43d.png)

こういう一覧にする感じ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/e8bab5c1-7fca-0961-1000-3dbcf736d0e6.png)

# 概要
以前なら 以下のようにして、BI で出来たんですが、セキュリティ権限の見直し？で、管理者に承認をもらう必要が発生した
といっても、弊社・・簡単に承認をもらえる環境がないわけです・・

ってことで

1. [チャネル メッセージを一覧表示する](https://learn.microsoft.com/ja-jp/graph/api/channel-list-messages?view=graph-rest-beta&tabs=http) 
1. 親スレッドのみを取得して Reactions 分析

https://qiita.com/baku2san/items/f224b71b204b45d8c9cc

で、まぁ、なんかないかなと考えた結果が、以下
1. 対象となる、Message ID を保存した Lists を用意
    1. Lists へは、[新しいチャネル メッセージが追加されたとき](https://learn.microsoft.com/ja-jp/connectors/teams/#%E6%96%B0%E3%81%97%E3%81%84%E3%83%81%E3%83%A3%E3%83%8D%E3%83%AB-%E3%83%A1%E3%83%83%E3%82%BB%E3%83%BC%E3%82%B8%E3%81%8C%E8%BF%BD%E5%8A%A0%E3%81%95%E3%82%8C%E3%81%9F%E3%81%A8%E3%81%8D) で更新
1. この Lists の Message ID を使って 
1. [メッセージ詳細を取得する](https://learn.microsoft.com/ja-jp/connectors/teams/#%E3%83%A1%E3%83%83%E3%82%BB%E3%83%BC%E3%82%B8%E8%A9%B3%E7%B4%B0%E3%82%92%E5%8F%96%E5%BE%97%E3%81%99%E3%82%8B) で Reactions を取得して
1. 解析結果を Adaptive Card でまとめて返信

# フロー
はこんな感じ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/9d897d7e-7c98-18cf-2034-1d9851f84440.png)

## Card データ格納配列の初期化
この部分で初期化して、以降のデータはスレッドのリアクションを解析して追加していく
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/ee337bdf-c318-d143-ff84-c8f285089d93.png)

```json: 初期化内容
[
  {
    "type": "ColumnSet",
    "width": "stretch",
    "columns": [
      {
        "type": "Column",
        "items": [
          {
            "type": "Image",
            "url": "https://static2.sharepointonline.com/files/fabric-cdn-prod_20210930.001/assets/brand-icons/product/png/teams_32x1.png",
            "width": "20px"
          }
        ]
      },
      {
        "type": "Column",
        "items": [
          {
            "type": "Image",
            "url": "https://statics.teams.cdn.office.net/evergreen-assets/personal-expressions/v2/assets/emoticons/yes/default/20_f.png"
          }
        ]
      },
      {
        "type": "Column",
        "items": [
          {
            "type": "Image",
            "url": "https://statics.teams.cdn.office.net/evergreen-assets/personal-expressions/v2/assets/emoticons/heart/default/20_f.png"
          }
        ]
      },
      {
        "type": "Column",
        "items": [
          {
            "type": "Image",
            "url": "https://statics.teams.cdn.office.net/evergreen-assets/personal-expressions/v2/assets/emoticons/laugh/default/20_f.png"
          }
        ]
      },
      {
        "type": "Column",
        "items": [
          {
            "type": "Image",
            "url": "https://statics.teams.cdn.office.net/evergreen-assets/personal-expressions/v2/assets/emoticons/surprised/default/20_f.png"
          }
        ]
      },
      {
        "type": "Column",
        "items": [
          {
            "type": "Image",
            "url": "https://statics.teams.cdn.office.net/evergreen-assets/personal-expressions/v2/assets/emoticons/sad/default/20_f.png"
          }
        ]
      },
      {
        "type": "Column",
        "items": [
          {
            "type": "Image",
            "url": "https://statics.teams.cdn.office.net/evergreen-assets/personal-expressions/v2/assets/emoticons/angryface/default/20_f.png"
          }
        ]
      }
    ]
  }
]
```

## Message ID の取得
昔なら、[チャネル メッセージを一覧表示する](https://learn.microsoft.com/ja-jp/graph/api/channel-list-messages?view=graph-rest-beta&tabs=http)  で遡って取得が可能だったんですが、今はアクセス権の問題で管理者承認が必要。
ってことで、[新しいチャネル メッセージが追加されたとき](https://learn.microsoft.com/ja-jp/connectors/teams/#%E6%96%B0%E3%81%97%E3%81%84%E3%83%81%E3%83%A3%E3%83%8D%E3%83%AB-%E3%83%A1%E3%83%83%E3%82%BB%E3%83%BC%E3%82%B8%E3%81%8C%E8%BF%BD%E5%8A%A0%E3%81%95%E3%82%8C%E3%81%9F%E3%81%A8%E3%81%8D) で登録した Message ID を利用することにした。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/46edb125-5360-8cd5-d7f1-bdf963cb11d0.png)

## 対象Messageのリアクション取得と解析⇒データ生成
方針は以下
- reactionType で、フィルターして、その個数を計数
- 行単位の Card-ColumnSet を作成して、Array に追加

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/223d9f8b-53ef-3ed4-e85f-9c938ecffdbd.png)

reactionType の一覧が以下
|reactionType Name|Icon|
|-|-|
|like|![like](https://statics.teams.cdn.office.net/evergreen-assets/personal-expressions/v2/assets/emoticons/yes/default/40_f.png)|
|heart|![heart](https://statics.teams.cdn.office.net/evergreen-assets/personal-expressions/v2/assets/emoticons/heart/default/40_f.png)|
|laugh|![laugh](https://statics.teams.cdn.office.net/evergreen-assets/personal-expressions/v2/assets/emoticons/laugh/default/40_f.png)|
|surprised|![surprised](https://statics.teams.cdn.office.net/evergreen-assets/personal-expressions/v2/assets/emoticons/surprised/default/40_f.png)|
|sad|![sad](https://statics.teams.cdn.office.net/evergreen-assets/personal-expressions/v2/assets/emoticons/sad/default/40_f.png)|
|angry|![angry](https://statics.teams.cdn.office.net/evergreen-assets/personal-expressions/v2/assets/emoticons/angryface/default/40_f.png)|

Teams Icon の URL は以下で

https://qiita.com/baku2san/items/db24e2cd8760a6c88542

:::note warn
動的コンテンツで Reactions を拾うと、バグってることもあるので注意
- 下図のように body/temp になってしまうので、 body/reactions へ修正必要
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/cb884b0e-e2d3-db93-4045-b641d4fbdb2d.png)
:::

## Card 合成と投稿
ここまできたら、合成して投稿するだけ。実際には Reply で、かな
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/20e1c40e-421d-c32e-382f-b7bd36d344a9.png)

:::note warn
Adaptive card の Payload 上限 28k がある為、30件程度で too large エラーになると思われる。
そうなったら、以下などの回避策が必要

- 投稿分割
- Column 分割をやめるか Column 自体を減らして、計数結果をまとめる
:::

# フロー全体のクリップボードコピペ
https://qiita.com/baku2san/items/ad4f7fa744d104b23f50

```json: いいね分析 by List 
{"id":"9316b78a-79c3-4719-88e5-4d5e70ed2091","brandColor":"#8C3900","connectionReferences":{"shared_sharepointonline":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-15e7a58d-6555-418d-a955-a6b286a76a7b"}},"shared_teams":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_teams/connections/shared-teams-864099d5-54a7-4002-93b6-565ecf481128"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"いいね分析_by_List_","operationDefinition":{"type":"Scope","actions":{"Get_items":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_sharepointonline","operationId":"GetItems","apiId":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline"},"parameters":{"dataset":"https://shimakuma.sharepoint.com/sites/testforflow","table":"0a0dcba8-686b-4e32-9d63-f1c1686ac267"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{}},"Apply_to_each":{"type":"Foreach","foreach":"@outputs('Get_items')?['body/value']","actions":{"Reactions_取得＆解析":{"type":"Scope","actions":{"Filter_array_yes":{"type":"Query","inputs":{"from":"@outputs('Get_message_details_InChannel')?['body/reactions']","where":"@equals(item()?['reactionType'], 'like')"},"runAfter":{"Get_message_details_InChannel":["Succeeded"]},"metadata":{"operationMetadataId":"97c83fc7-c9be-4502-8551-dbbe1a1054ca"}},"Filter_array_heart":{"type":"Query","inputs":{"from":"@outputs('Get_message_details_InChannel')?['body/reactions']","where":"@equals(item()?['reactionType'], 'heart')"},"runAfter":{"Get_message_details_InChannel":["Succeeded"]},"metadata":{"operationMetadataId":"7c92990a-581e-4c87-9703-f588d1400ac2"}},"Filter_array_laugh":{"type":"Query","inputs":{"from":"@outputs('Get_message_details_InChannel')?['body/reactions']","where":"@equals(item()?['reactionType'], 'laugh')"},"runAfter":{"Get_message_details_InChannel":["Succeeded"]},"metadata":{"operationMetadataId":"d0818a23-fc47-44fa-b10e-48b64804b29f"}},"Filter_array_surprised":{"type":"Query","inputs":{"from":"@outputs('Get_message_details_InChannel')?['body/reactions']","where":"@equals(item()?['reactionType'], 'surprised')"},"runAfter":{"Get_message_details_InChannel":["Succeeded"]},"metadata":{"operationMetadataId":"67238927-2e93-4558-8b98-4342b9efc26d"}},"Filter_array_sad":{"type":"Query","inputs":{"from":"@outputs('Get_message_details_InChannel')?['body/reactions']","where":"@equals(item()?['reactionType'], 'sad')"},"runAfter":{"Get_message_details_InChannel":["Succeeded"]},"metadata":{"operationMetadataId":"63dfac04-af85-4fca-a4e0-e206d3ad3652"}},"Filter_array_angry":{"type":"Query","inputs":{"from":"@outputs('Get_message_details_InChannel')?['body/reactions']","where":"@equals(item()?['reactionType'], 'angry')"},"runAfter":{"Get_message_details_InChannel":["Succeeded"]},"metadata":{"operationMetadataId":"12c6cd61-4b5a-44ca-a354-94ec0db9e747"}},"Append_to_array_variable":{"type":"AppendToArrayVariable","inputs":{"name":"ColumnSets","value":{"type":"ColumnSet","columns":[{"type":"Column","width":"stretch","items":[{"type":"ColumnSet","columns":[{"type":"Column","width":"stretch","items":[{"type":"TextBlock","text":"[@{outputs('Get_message_details_InChannel')?['body/subject']}](@{outputs('Get_message_details_InChannel')?['body/messageLink']})","wrap":true}]},{"type":"Column","width":"stretch","items":[{"type":"TextBlock","text":"@{length(body('Filter_array_yes'))}","wrap":true}]},{"type":"Column","width":"stretch","items":[{"type":"TextBlock","text":"@{length(body('Filter_array_heart'))}","wrap":true}]},{"type":"Column","width":"stretch","items":[{"type":"TextBlock","text":"@{length(body('Filter_array_laugh'))}","wrap":true}]},{"type":"Column","width":"stretch","items":[{"type":"TextBlock","text":"@{length(body('Filter_array_surprised'))}","wrap":true}]},{"type":"Column","width":"stretch","items":[{"type":"TextBlock","text":"@{length(body('Filter_array_sad'))}","wrap":true}]},{"type":"Column","width":"stretch","items":[{"type":"TextBlock","text":"@{length(body('Filter_array_angry'))}","wrap":true}]}]}]}]}},"runAfter":{"Filter_array_yes":["Succeeded"],"Filter_array_heart":["Succeeded"],"Filter_array_laugh":["Succeeded"],"Filter_array_surprised":["Succeeded"],"Filter_array_sad":["Succeeded"],"Filter_array_angry":["Succeeded"]}},"Get_message_details_InChannel":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_teams","operationId":"GetMessageDetails","apiId":"/providers/Microsoft.PowerApps/apis/shared_teams"},"parameters":{"messageId":"@items('Apply_to_each')?['MessageID']","threadType":"channel","body/recipient/groupId":"a70cc9a6-85a4-4b73-974a-c005f11ca2b5","body/recipient/channelId":"19:b55252f93b12444ea97ffd5ac9a26abc@thread.tacv2"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{},"metadata":{"operationMetadataId":"06210aad-f59f-4c39-aae8-8ab53571ea91"}}},"runAfter":{},"metadata":{"operationMetadataId":"f73d3379-e546-490b-a4fc-fb92bfbf0b4a"}}},"runAfter":{"Get_items":["Succeeded"]}},"結果投稿":{"type":"Scope","actions":{"Post_adaptive_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":"{\n    \"type\": \"AdaptiveCard\",\n    \"$schema\": \"http://adaptivecards.io/schemas/adaptive-card.json\",\n    \"version\": \"1.4\",\n    \"msteams\": {\n        \"width\": \"full\"\n    },\n    \"body\": \n@{variables('ColumnSets')}\n}"},"authentication":{"type":"Raw","value":"@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"}},"runAfter":{}}},"runAfter":{"Apply_to_each":["Succeeded"]},"metadata":{"operationMetadataId":"fcfd03b0-ae6e-4948-9bba-032adc98a8c9"}}},"runAfter":{"Initialize_variable_ColumnSets":["Succeeded"]}}}
```

# あとがき
Teams の Payload 上限がやっぱり厳しい

Message Center の通知も最近HTML書式が変更されて、転送すると上限超えちゃうし、何か良い手が無いものか・・

# Keyword
how to analyze each parent message reactions