Forms の回答を Lists で得点計算。回答毎の得点は Lookup List で管理

背景

仕事柄 Power Automate から離れてたけど、Qiita の質問でサクッと出来そうな質問があったので作ってみた

概要

1. 最終の Lists をまず作る
   

2. 得点テーブルを作る
   

3. Automate で Forms の回答があった際に、値を設定すれば完了
   

詳細

最終の Lists をまず作る

特に苦労することは無く、以下な感じで

列名	型	概要
質問A/B	Lookup	計算用の値
合計	計算	合計値を表示
RawA/B	Text	念の為、回答結果を格納する

合計の計算は以下で。
単純に、lookupValue を加算してるのみ

sum

{
   "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
   "elmType": "div",
   "txtContent": "=Number([$_x8cea__x554f_A.lookupValue])+Number([$_x8cea__x554f_B.lookupValue])"
}

得点テーブルを作る

まぁ、こっちも簡単に

列名	型	概要
タイトル	ext	回答。完全に一致させてるが、Automate での Filter 次第
値	number	計算に使用する値を設定

Automate で Forms の回答があった際に、値を設定すれば完了

簡単な説明としては

1. Forms から Trigger
2. Forms の詳細を取得して
3. 得点テーブルのアイテムを取得して
4. 回答毎に、一致するアイテムを取得して、アイテム追加時の Id を取得出来るようにしておく
5. ListName(Id)を格納。ここは別途取得しておく
6. で、アイテム追加するだけ

Create Item の Item　例

Filter_array で取得した ID を、Lookup の Id に設定する
Item 名は、リストをSort/Filter すればすぐわかるはず

Create Item の Item　例

{
  "Title": @{outputs('Get_response_details')?['body/responder']},
  "RawA": @{outputs('Get_response_details')?['body/rfc413ef37dbe42e9aa74088c06825d15']},
  "RawB": @{outputs('Get_response_details')?['body/r9fe5defea7a141c1999db756af5e3ddb']},
  "OData__x8cea__x554f_A": {
    "Id": @{first(outputs('Filter_array_A')?['Body'])?['ID']}
  },
  "OData__x8cea__x554f_B": {
    "Id": @{first(outputs('Filter_array_B')?['Body'])?['ID']}
  }
}

automate の json コピペ例

Edit でコピペして貼り付ければ再現は簡単かな、と

{"id":"30360bcb-8d0a-4f7d-af69-5b706669b1c0","brandColor":"#8C3900","connectionReferences":{"shared_sharepointonline":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-15e7a58d-6555-418d-a955-a6b286a76a7b"}},"shared_microsoftforms":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_microsoftforms/connections/shared-microsoftform-febc4b35-8faf-4ab6-9d6f-98211a3de4bb"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Scope_結果を元に_List_Item_を作る","operationDefinition":{"type":"Scope","actions":{"Get_response_details":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_microsoftforms","operationId":"GetFormResponseById","apiId":"/providers/Microsoft.PowerApps/apis/shared_microsoftforms"},"parameters":{"form_id":"5yeNf3C-7UuD1Ze-mrfnESHR_FsoAN5IhfS8_RTMEAVUNjFMTEM2QkhSQlBWWElYRjk0VklEVFM4RC4u","response_id":"@triggerOutputs()?['body/resourceData/responseId']"},"authentication":"@parameters('$authentication')"},"runAfter":{},"metadata":{"operationMetadataId":"fcd41a61-8bb4-48c5-8b18-93466e90acf3"}},"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/TestForTeams","table":"9739cbeb-e0ce-495c-994a-e192d5a7e09b"},"authentication":"@parameters('$authentication')"},"runAfter":{"Get_response_details":["Succeeded"]},"metadata":{"operationMetadataId":"fe4ad5aa-e78e-4c59-90eb-eff34caf5fa6"}},"Filter_array_A":{"type":"Query","inputs":{"from":"@outputs('Get_items')?['body/value']","where":"@equals(item()?['Title'], outputs('Get_response_details')?['body/rfc413ef37dbe42e9aa74088c06825d15'])"},"runAfter":{"Get_items":["Succeeded"]},"metadata":{"operationMetadataId":"e6171928-a847-4256-8830-31cccdc04a07"}},"Filter_array_B":{"type":"Query","inputs":{"from":"@outputs('Get_items')?['body/value']","where":"@equals(item()?['Title'], outputs('Get_response_details')?['body/r9fe5defea7a141c1999db756af5e3ddb'])"},"runAfter":{"Get_items":["Succeeded"]},"metadata":{"operationMetadataId":"8ee9c18f-8741-47d6-8b5d-e400cbe2bd1f"}},"Compose":{"type":"Compose","inputs":"93484ca0-382a-481f-ac93-91a7941ac5c2","runAfter":{"Filter_array_A":["Succeeded"],"Filter_array_B":["Succeeded"]},"metadata":{"operationMetadataId":"c13cea91-9c23-4866-83d8-5175dff10fd8"}},"Create_item":{"type":"OpenApiConnection","inputs":{"host":{"connectionName":"shared_sharepointonline","operationId":"PostItem","apiId":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline"},"parameters":{"dataset":"https://shimakuma.sharepoint.com/sites/TestForTeams","table":"@outputs('Compose')","item":{"Title":"@outputs('Get_response_details')?['body/responder']","RawA":"@outputs('Get_response_details')?['body/rfc413ef37dbe42e9aa74088c06825d15']","RawB":"@outputs('Get_response_details')?['body/r9fe5defea7a141c1999db756af5e3ddb']","OData__x8cea__x554f_A":{"Id":"@first(outputs('Filter_array_A')?['Body'])?['ID']"},"OData__x8cea__x554f_B":{"Id":"@first(outputs('Filter_array_B')?['Body'])?['ID']"}}},"authentication":"@parameters('$authentication')"},"runAfter":{"Compose":["Succeeded"]},"metadata":{"operationMetadataId":"dc46208c-606d-4b3c-975c-fd022e342d54"}}},"runAfter":{}}}

あとがき

久し振りにやると、Lists の書式設定を忘れてて困りますな

あとは、情報取得系の RestAPI の使い方も、か

この辺が自分的に役立った