背景
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}