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

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

# 概要
1. [最終の Lists をまず作る](#最終の-lists-をまず作る)<br>![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/45bc6645-bbad-06f4-226a-a8994a12bca5.png)

1. [得点テーブルを作る](#得点テーブルを作る)<br>![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/4ef8300e-e9b4-4535-a10f-100058fcb734.png)

1. [Automate で Forms の回答があった際に、値を設定すれば完了](#automate-で-forms-の回答があった際に値を設定すれば完了)<br>![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/dd853b4b-0c02-a895-4ce4-81638abc427a.png)

# 詳細
## 最終の Lists をまず作る
特に苦労することは無く、以下な感じで

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

合計の計算は以下で。
単純に、lookupValue を加算してるのみ
```json: 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])"
}
```

https://learn.microsoft.com/ja-jp/sharepoint/dev/declarative-customization/formatting-advanced

## 得点テーブルを作る
まぁ、こっちも簡単に

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

## Automate で Forms の回答があった際に、値を設定すれば完了
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/477c203c-5040-c72b-6f3b-14864cdb6270.png)

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

### Create Item の Item　例
Filter_array で取得した ID を、Lookup の Id に設定する
Item 名は、リストをSort/Filter すればすぐわかるはず
```json: 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 でコピペして貼り付ければ再現は簡単かな、と

```json: 
{"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":{}}}
```

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

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

https://learn.microsoft.com/ja-jp/sharepoint/dev/declarative-customization/formatting-advanced

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

https://learn.microsoft.com/ja-jp/sharepoint/dev/sp-add-ins/working-with-lists-and-list-items-with-rest

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

https://qiita.com/baku2san/items/7d78b84d666a2b6d4588