背景

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

イメージ

こんな感じのスレッドがあって

こういう一覧にする感じ

概要

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

ってことで

チャネルメッセージを一覧表示する
親スレッドのみを取得して Reactions 分析

で、まぁ、なんかないかなと考えた結果が、以下

対象となる、Message ID を保存した Lists を用意
1. Lists へは、新しいチャネルメッセージが追加されたときで更新
この Lists の Message ID を使って
メッセージ詳細を取得するで Reactions を取得して
解析結果を Adaptive Card でまとめて返信

フロー

はこんな感じ

Card データ格納配列の初期化

この部分で初期化して、以降のデータはスレッドのリアクションを解析して追加していく

初期化内容

[
  {
    "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 の取得

昔なら、チャネルメッセージを一覧表示するで遡って取得が可能だったんですが、今はアクセス権の問題で管理者承認が必要。
ってことで、新しいチャネルメッセージが追加されたときで登録した Message ID を利用することにした。

対象Messageのリアクション取得と解析⇒データ生成

方針は以下

reactionType で、フィルターして、その個数を計数
行単位の Card-ColumnSet を作成して、Array に追加

reactionType の一覧が以下

reactionType Name	Icon
like
heart
laugh
surprised
sad
angry

Teams Icon の URL は以下で

動的コンテンツで Reactions を拾うと、バグってることもあるので注意

下図のように body/temp になってしまうので、 body/reactions へ修正必要

Card 合成と投稿

ここまできたら、合成して投稿するだけ。実際には Reply で、かな

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

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

フロー全体のクリップボードコピペ

いいね分析 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

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

背景