背景

LLM が浸透してきたことで、
ようやく会社で GitHub Copilot が使えるようになりました。

ってことで、Power Automate も自動生成活用方法を考えてみた

結論

Office Scripts を LLM に生成させれば、Power Automate での整形とかが楽になる

さすがに、Power Automate の JSON を生成することはできなかったけど・・

具体的なイメージは？

Trigger
なんらかのデータ取得
Run (office) Script
- LLM でここの script を作らせるだけ
- ただし、動作数の上限はあるので、稼働させ過ぎには注意
Teamsへ投稿したりして活用

Adaptive Card とは？

以下にアクセスするのが一番分かり易い。UI を Json で提議するもの。と思えば OK

あとがき

勉強会の参加者を、特定のアイコンで募集するとき用に作ろうとして、Adaptive Card を Automate で生成するのが面倒だったので、LLM に任せてみたら簡単にできたのでした。
以前までは、Column Sets を配列で生成してたんで面倒だったんですけどね

以下、サンプルとしての実装例
LLM で、Office Scripts と Adaptive Card を作ってもらう感じ

具体的な例としての、リアクション集計の実装

動作イメージ

チャットや、チャネルでのメッセージについているリアクションの集計をして、リアクションした人の名前と Emails 一覧を表示する

こんな感じで、リアクションの集計をする。
Email のところをクリックして emails list をコピーすれば、以下のようなことができる

特定のリアクションをした人だけの会議招集や、チャットなどに使える
Polls を使わずに、簡単なリアクション集計

リアクション集計実装イメージ

For a selected message

{
    "type": "AdaptiveCard",
    "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
    "version": "1.4",
    "msteams": {
        "width": "full"
    },
    "body": [
        {
            "type": "TextBlock",
            "text": "リアクション集計をします"
        },
        {
            "text": "Cancel は右上の ☒ を",
            "type": "TextBlock",
            "size": "Small",
            "horizontalAlignment": "Right",
            "weight": "Lighter",
            "color": "Accent"
        }
    ]
}

Office Scripts

interface IUser {
    UserID: string;
    DisplayName: string;
    EMail: string;
}

interface IReaction {
    reactionType: string;
    userId: string;
    userName: string;
    reactionName: string;
}

interface MergedUser {
    userId: string;
    userName: string;
    displayName: string;
    email: string;
}

// ReactionGroup に reactionName を追加
interface ReactionGroup {
    reactionType: string;
    reactionName: string;
    users: MergedUser[];
}

interface OriginalMessage {
    Message: string;
    LinkToMessage: string;
}

function main(
    workbook: ExcelScript.Workbook,
    reactionsString: string = '[{"reactionType":"🚢","userId":"userid-0001","reactionName":"Ship"}]',
    usersString: string = '[{"UserID":"userid-0001","DisplayName":"たかにぃ","EMail":"hoge@hoge.onmicrosoft.com"}]',
    originalMessageJSON: string = '{ "Message": "Default quoted message", "LinkToMessage": "https://teams.microsoft.com/l/message/DEFAULT" }'
): string {
    // Reaction に対応する画像 URL のマッピング
    const reactionImageMap: { [key: string]: string } = {
        "yes": "https://statics.teams.cdn.office.net/evergreen-assets/personal-expressions/v2/assets/emoticons/yes/default/20_f.png",
        "like": "https://statics.teams.cdn.office.net/evergreen-assets/personal-expressions/v2/assets/emoticons/yes/default/20_f.png",  // like > yes となった過去の経緯により
        "heart": "https://statics.teams.cdn.office.net/evergreen-assets/personal-expressions/v2/assets/emoticons/heart/default/20_f.png",
        "laugh": "https://statics.teams.cdn.office.net/evergreen-assets/personal-expressions/v2/assets/emoticons/laugh/default/20_f.png",
        "surprised": "https://statics.teams.cdn.office.net/evergreen-assets/personal-expressions/v2/assets/emoticons/surprised/default/20_f.png",
        "sad": "https://statics.teams.cdn.office.net/evergreen-assets/personal-expressions/v2/assets/emoticons/sad/default/20_f.png",
        "angryface": "https://statics.teams.cdn.office.net/evergreen-assets/personal-expressions/v2/assets/emoticons/angryface/default/20_f.png"
    };

    let parsedUsers: IUser[];
    try {
        parsedUsers = JSON.parse(usersString);
    } catch (error) {
        console.log("ユーザー情報のJSONパースに失敗しました", error);
        return "";
    }
    const userDict: { [key: string]: IUser } = {};
    parsedUsers.forEach((user: IUser) => {
        userDict[user.UserID.toLowerCase()] = user;
    });

    let reactions: IReaction[];
    try {
        reactions = JSON.parse(reactionsString);
    } catch (error) {
        console.log("反応情報のJSONパースに失敗しました", error);
        return "";
    }

    // reactionName をキーにグループ化する
    const grouped: { [key: string]: { userId: string; userName: string; reactionType: string }[] } =
        reactions.reduce((acc, reaction: IReaction) => {
            const key: string = reaction.reactionName;
            if (!acc[key]) {
                acc[key] = [];
            }
            acc[key].push({
                userId: reaction.userId ? reaction.userId.toLowerCase() : "",
                userName: reaction.userName,
                reactionType: reaction.reactionType
            });
            return acc;
        }, {} as { [key: string]: { userId: string; userName: string; reactionType: string }[] });
    // ReactionGroup は、表示時は従来通り「reactionType / reactionName (件数)」として出す

    const result: ReactionGroup[] = Object.entries(grouped)
        .sort((a, b) => b[1].length - a[1].length)
        .map(([reactionName, reactionsArray]) => {
            const first = reactionsArray[0];
            const mergedUsers: MergedUser[] = reactionsArray.map((r) => {
                const info: IUser | undefined = userDict[r.userId];
                return {
                    userId: r.userId,
                    userName: r.userName,
                    displayName: info ? info.DisplayName : "",
                    email: info ? info.EMail : ""
                };
            });
            return { reactionType: first.reactionType, reactionName, users: mergedUsers };
        });
    console.log(result);

    // Adaptive Card の JSON 構築
    let card: Record<string, unknown> = {
        type: "AdaptiveCard",
        $schema: "http://adaptivecards.io/schemas/adaptive-card.json",
        version: "1.4",
        msteams: { width: "full" },
        body: [] as unknown[]
    };

    // 元メッセージ表示（引用）
    let originalMsg: OriginalMessage;
    try {
        originalMsg = JSON.parse(originalMessageJSON);
    } catch (error) {
        console.log("元メッセージのJSONパースに失敗しました", error);
        originalMsg = { Message: "元メッセージが取得できませんでした", LinkToMessage: "" };
    }
    card.body.push({
        type: "Container",
        style: "emphasis",
        items: [
            {
                type: "TextBlock",
                text: originalMsg.Message,
                wrap: true,
                isSubtle: true
            },
            {
                type: "TextBlock",
                text: `[元のメッセージを表示](${originalMsg.LinkToMessage})`,
                wrap: true,
                size: "Small",
                color: "Accent"
            }
        ]
    });

    // タイトルブロック
    card.body.push({
        type: "TextBlock",
        text: "Reactions Summary",
        weight: "Bolder",
        size: "Large"
    });

    // テーブルヘッダ (3列: Reaction, User Name, Email)
    card.body.push({
        type: "ColumnSet",
        columns: [
            {
                type: "Column",
                width: "stretch",
                items: [
                    {
                        type: "TextBlock",
                        text: "Reaction",
                        weight: "Bolder",
                        wrap: true
                    }
                ]
            },
            {
                type: "Column",
                width: "stretch",
                items: [
                    {
                        type: "TextBlock",
                        text: "User Name",
                        weight: "Bolder",
                        wrap: true
                    }
                ]
            },
            {
                type: "Column",
                width: "stretch",
                items: [
                    {
                        type: "TextBlock",
                        text: "Email",
                        weight: "Bolder",
                        wrap: true
                    }
                ]
            }
        ]
    });

    // グループごとの行データ作成部分
    result.forEach((group: ReactionGroup, idx: number) => {
        // Reaction 列: 画像＋テキスト（reactionType / reactionName (件数)）
        const lowerType: string = group.reactionType.toLowerCase();
        let reactionColumnItems: Record<string, unknown>[] = [];
        const reactionText = `${group.reactionType} / ${group.reactionName} (${group.users.length})`;
        if (reactionImageMap[lowerType]) {
            reactionColumnItems.push({
                type: "Image",
                url: reactionImageMap[lowerType]
            });
            reactionColumnItems.push({
                type: "TextBlock",
                text: reactionText,
                wrap: true
            });
        } else {
            reactionColumnItems.push({
                type: "TextBlock",
                text: reactionText,
                wrap: true
            });
        }
        // ユーザー情報（DisplayName）を改行区切りでまとめる
        const userNames: string = group.users.map(u => u.displayName).join("\n");
        // メールアドレスはコピー用のデータとして取得しておく
        const userEmails: string = group.users.map(u => u.email).join("\n");

        // 行は3列とする：Reaction / User Name / Email(コピー用ボタン)
        card.body.push({
            type: "ColumnSet",
            columns: [
                {
                    type: "Column",
                    width: "stretch",
                    items: reactionColumnItems
                },
                {
                    type: "Column",
                    width: "stretch",
                    items: [
                        {
                            type: "TextBlock",
                            text: userNames,
                            wrap: true,
                            size: "Small"
                        }
                    ]
                },
                {
                    type: "Column",
                    width: "stretch",
                    items: [
                        {
                            type: "ActionSet",
                            actions: [
                                {
                                    type: "Action.ShowCard",
                                    title: "ShowEmails",
                                    card: {
                                        type: "AdaptiveCard",
                                        body: [
                                            {
                                                type: "Input.Text",
                                                id: `copyEmails_${idx}`,  // ユニークなIDにするためにインデックスを付与
                                                value: userEmails,
                                                isMultiline: true,
                                                style: "text"
                                            }
                                        ],
                                        $schema: "http://adaptivecards.io/schemas/adaptive-card.json",
                                        version: "1.4"
                                    }
                                }
                            ]
                        }
                    ]
                }
            ]
        });
    });

    const adaptiveCardJson: string = JSON.stringify(card, null, 2);
    console.log(adaptiveCardJson);
    return adaptiveCardJson;
}

Actions

{"nodeId":"Scope-copy","serializedValue":{"type":"Scope","actions":{"Reactions_取得（Emoticon）":{"type":"Scope","actions":{"ChannelMessage？":{"type":"If","expression":{"and":[{"not":{"equals":["@triggerBody()?['teamsFlowRunContext']?['channelData']?['team']","@null"]}}]},"actions":{"Get_message_details_InChannel":{"type":"OpenApiConnection","inputs":{"parameters":{"messageId":"@triggerBody()?['teamsFlowRunContext']?['messagePayload']?['id']","threadType":"channel","body/recipient/groupId":"@triggerOutputs()?['body/teamsFlowRunContext/channelData/team/aadGroupId']","body/recipient/channelId":"@triggerOutputs()?['body/teamsFlowRunContext/channelData/channel/id']","body/recipient/parentMessageId":"@triggerOutputs()?['body/teamsFlowRunContext/messagePayload/replyToId']"},"host":{"apiId":"/providers/Microsoft.PowerApps/apis/shared_teams","connection":"shared_teams","operationId":"GetMessageDetails"}},"metadata":{"operationMetadataId":"06210aad-f59f-4c39-aae8-8ab53571ea91"}},"Compose":{"type":"Compose","inputs":"@outputs('Get_message_details_InChannel')?['body/reactions']","runAfter":{"Get_message_details_InChannel":["Succeeded"]},"metadata":{"operationMetadataId":"52790c17-d868-4fd1-987e-34c1e138584a"}},"Set_variable":{"type":"SetVariable","inputs":{"name":"Reactions","value":"@outputs('Get_message_details_InChannel')?['body/reactions']"},"runAfter":{"Compose":["Succeeded"]},"metadata":{"operationMetadataId":"284935d6-bd58-44c4-9b4c-07827cad73b8"}},"Set_variable_Message":{"type":"SetVariable","inputs":{"name":"Message","value":{"Message":"@if(greater(length(outputs('Get_message_details_InChannel')?['body/body/plainTextContent']),100),\r\nsubstring(outputs('Get_message_details_InChannel')?['body/body/plainTextContent'], 0,10100),outputs('Get_message_details_InChannel')?['body/body/plainTextContent'])","LinkToMessage":"@outputs('Get_message_details_InChannel')?['body/messageLink']"}},"runAfter":{"Set_variable":["Succeeded"]}}},"else":{"actions":{"Get_message_details_InChat":{"type":"OpenApiConnection","inputs":{"parameters":{"messageId":"@triggerBody()?['teamsFlowRunContext']?['messagePayload']?['id']","threadType":"groupchat","body/recipient":"@triggerOutputs()?['body/teamsFlowRunContext/conversation/id']"},"host":{"apiId":"/providers/Microsoft.PowerApps/apis/shared_teams","connection":"shared_teams","operationId":"GetMessageDetails"}},"metadata":{"operationMetadataId":"9dd45887-faf7-4325-8992-b04901a414e3"}},"Set_variable_2":{"type":"SetVariable","inputs":{"name":"Reactions","value":"@outputs('Get_message_details_InChat')?['body/reactions']"},"runAfter":{"Get_message_details_InChat":["Succeeded"]},"metadata":{"operationMetadataId":"ea211d03-6b6a-4620-b691-f35055cdaebe"}},"Set_variable_Message_in_Chat":{"type":"SetVariable","inputs":{"name":"Message","value":{"Message":"@if(greater(length(outputs('Get_message_details_InChat')?['body/body/plainTextContent']),100),\r\nsubstring(outputs('Get_message_details_InChat')?['body/body/plainTextContent'], 0,10100),outputs('Get_message_details_InChat')?['body/body/plainTextContent'])","LinkToMessage":"@outputs('Get_message_details_InChat')?['body/messageLink']"}},"runAfter":{"Set_variable_2":["Succeeded"]}}}},"metadata":{"operationMetadataId":"4004a979-97c8-45c6-9fb0-2d3788cff941"}}},"runAfter":{"Initialize_Users":["SUCCEEDED"]},"metadata":{"operationMetadataId":"cd7790be-db53-4b1b-a50b-f9c043d30fc3"}},"結果投稿":{"type":"Scope","actions":{"Condition":{"type":"If","expression":{"and":[{"not":{"equals":["@triggerBody()?['teamsFlowRunContext']?['channelData']['Team']","@null"]}}]},"actions":{"Reply_with_adaptive_card_in_a_channel":{"type":"OpenApiConnection","inputs":{"parameters":{"poster":"Flow bot","location":"Channel","body/parentMessageId":"@outputs('replyToId')","body/recipient/groupId":"@outputs('Get_message_details_InChannel')?['body/teamId']","body/recipient/channelId":"@outputs('Get_message_details_InChannel')?['body/channelId']","body/messageBody":"@outputs('Run_script')?['body/result']"},"host":{"apiId":"/providers/Microsoft.PowerApps/apis/shared_teams","connection":"shared_teams","operationId":"ReplyWithCardToConversation"}},"runAfter":{"replyToId":["Succeeded"]},"runtimeConfiguration":{"staticResult":{"staticResultOptions":"Disabled","name":"Reply_with_adaptive_card_in_a_channel0"}},"metadata":{"operationMetadataId":"8600d900-094f-4cc9-9294-aa5f59c17860"}},"replyToId":{"type":"Compose","description":"null: 親なので、Id, !null: 親Id","inputs":"@if(empty(outputs('Get_message_details_InChannel')?['body/replyToId']), outputs('Get_message_details_InChannel')?['body/Id'], outputs('Get_message_details_InChannel')?['body/replyToId'])","metadata":{"operationMetadataId":"085386cc-4f7d-4f4b-9866-61383cb5da3d"}}},"else":{"actions":{"Post_adaptive_card_in_a_chat_or_channel":{"type":"OpenApiConnection","inputs":{"parameters":{"poster":"Flow bot","location":"Group chat","body/recipient":"@outputs('Get_message_details_InChat')?['body/conversationId']","body/messageBody":"@outputs('Run_script')?['body/result']"},"host":{"apiId":"/providers/Microsoft.PowerApps/apis/shared_teams","connection":"shared_teams","operationId":"PostCardToConversation"}},"runtimeConfiguration":{"staticResult":{"staticResultOptions":"Disabled","name":"Post_adaptive_card_in_a_chat_or_channel0"}},"metadata":{"operationMetadataId":"f1fa9c6b-da04-4149-9896-8870b0f57b05"}}}},"metadata":{"operationMetadataId":"cc1202d5-e154-4abe-b279-982378592833"}}},"runAfter":{"Reactions_Analysis":["Succeeded"]},"metadata":{"operationMetadataId":"fcfd03b0-ae6e-4948-9bba-032adc98a8c9"}},"Reactions_Analysis":{"type":"Scope","actions":{"Select_ReactionsValues":{"type":"Select","inputs":{"from":"@variables('Reactions')","select":{"reactionType":"@item()?['reactionType']","userId":"@item()?['user/user/id']","reactionName":"@item()?['displayName']"}}},"Select_UserIDs":{"type":"Select","inputs":{"from":"@variables('Reactions')","select":"@item()?['user/user/id']"}},"For_each":{"type":"Foreach","foreach":"@outputs('Select_UserIDs')['body']","actions":{"Get_user_profile_(V2)":{"type":"OpenApiConnection","inputs":{"parameters":{"id":"@item()","$select":"displayName, mail"},"host":{"apiId":"/providers/Microsoft.PowerApps/apis/shared_office365users","connection":"shared_office365users","operationId":"UserProfile_V2"}}},"Append_to_array_variable":{"type":"AppendToArrayVariable","inputs":{"name":"Users","value":{"UserID":"@item()","DisplayName":"@outputs('Get_user_profile_(V2)')?['body/displayName']","EMail":"@outputs('Get_user_profile_(V2)')?['body/mail']"}},"runAfter":{"Get_user_profile_(V2)":["Succeeded"]}}},"runAfter":{"Select_UserIDs":["Succeeded"]}},"Compose_results":{"type":"Compose","inputs":"@variables('Users')","runAfter":{"For_each":["Succeeded"]}},"Run_script":{"type":"OpenApiConnection","inputs":{"parameters":{"source":"me","drive":"b!QSv_CUULHECjjx05iSyK255pBaQe5FlKprvq9VYwcdpPYNdo1e42Qa5Y147BwZtO","file":"01LEKUELAKOLXHO6LZH5EKLPFL4MC4J6A6","scriptId":"ms-officescript%3A%2F%2Fonedrive_business_itemlink%2F01LEKUELFQ54OHLZDOBNEIA665CL3KGMHD","ScriptParameters/reactionsString":"@string(body('Select_ReactionsValues'))","ScriptParameters/usersString":"@variables('Users')","ScriptParameters/originalMessageJSON":"@variables('Message')"},"host":{"apiId":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness","connection":"shared_excelonlinebusiness","operationId":"RunScriptProd"}},"runAfter":{"Select_ReactionsValues":["Succeeded"],"Compose_results":["Succeeded"]},"metadata":{"01U75VYIQNP6GOQZUV6VE3YORLAODZIEXC":"/DummyForOfficeScripts.xlsx","tableId":null,"01LEKUELAKOLXHO6LZH5EKLPFL4MC4J6A6":"/DummyForOfficeScript.xlsx"}}},"runAfter":{"Reactions_取得（Emoticon）":["Succeeded"]}},"Initialize_Users":{"type":"InitializeVariable","inputs":{"variables":[{"name":"Users","type":"array"}]},"runAfter":{"Initialize_Message":["SUCCEEDED"]}},"Initialize_Message":{"type":"InitializeVariable","inputs":{"variables":[{"name":"Message","type":"object"}]},"runAfter":{"Initialize_Reactions":["SUCCEEDED"]}},"Initialize_Reactions":{"type":"InitializeVariable","inputs":{"variables":[{"name":"Reactions","type":"array"}]},"metadata":{"operationMetadataId":"d196fb6a-df4e-41cf-a341-82c39403f450"}}},"runAfter":{},"metadata":{"operationMetadataId":"f3810333-05bf-4a43-a6e2-8323a7efcd24"}},"allConnectionData":{"Get_message_details_InChannel":{"connectionReference":{"api":{"id":"/providers/Microsoft.PowerApps/apis/shared_teams"},"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_teams/connections/shared-teams-c9efbf63-e5a2-4102-a864-e913ec0c8ef8"},"connectionName":"shared-teams-c9efbf63-e5a2-4102-a864-e913ec0c8ef8"},"referenceKey":"shared_teams"},"Get_message_details_InChat":{"connectionReference":{"api":{"id":"/providers/Microsoft.PowerApps/apis/shared_teams"},"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_teams/connections/shared-teams-c9efbf63-e5a2-4102-a864-e913ec0c8ef8"},"connectionName":"shared-teams-c9efbf63-e5a2-4102-a864-e913ec0c8ef8"},"referenceKey":"shared_teams"},"Reply_with_adaptive_card_in_a_channel":{"connectionReference":{"api":{"id":"/providers/Microsoft.PowerApps/apis/shared_teams"},"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_teams/connections/shared-teams-c9efbf63-e5a2-4102-a864-e913ec0c8ef8"},"connectionName":"shared-teams-c9efbf63-e5a2-4102-a864-e913ec0c8ef8"},"referenceKey":"shared_teams"},"Post_adaptive_card_in_a_chat_or_channel":{"connectionReference":{"api":{"id":"/providers/Microsoft.PowerApps/apis/shared_teams"},"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_teams/connections/shared-teams-c9efbf63-e5a2-4102-a864-e913ec0c8ef8"},"connectionName":"shared-teams-c9efbf63-e5a2-4102-a864-e913ec0c8ef8"},"referenceKey":"shared_teams"},"Get_user_profile_(V2)":{"connectionReference":{"api":{"id":"/providers/Microsoft.PowerApps/apis/shared_office365users"},"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_office365users/connections/shared-office365user-8adbcca0-19c0-4a73-8fdb-225b07b9092b"},"connectionName":"shared-office365user-8adbcca0-19c0-4a73-8fdb-225b07b9092b"},"referenceKey":"shared_office365users"},"Run_script":{"connectionReference":{"api":{"id":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness"},"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_excelonlinebusiness/connections/shared-excelonlinebu-499d4373-d0fb-4e25-9c76-65a0ff2c5a73"},"connectionName":"shared-excelonlinebu-499d4373-d0fb-4e25-9c76-65a0ff2c5a73"},"referenceKey":"shared_excelonlinebusiness"}},"staticResults":{"Reply_with_adaptive_card_in_a_channel":{"status":"Succeeded","outputs":{"statusCode":"OK"}},"Post_adaptive_card_in_a_chat_or_channel":{"status":"Succeeded","outputs":{"statusCode":"OK"}}},"isScopeNode":true,"mslaNode":true}