We know that Microsoft forms is very easy to design and collect responses. But it becomes very handy when you include Microsoft forms connector with flow and save responses in a SharePoint List.
Power Automate has trigger “When a new response is submitted,” and one action, “Get response details“. This action pulls in the details of the form response as dynamic content that you can use in your flow.
Use Case– When user submit form responses then responses will be recorded into a Sharepoint List.
Microsoft Form-
SharePoint List-
List Schema-
Flow Steps:
- Go to your Power Automate Studio and click on Create then choose “Automated Cloud Flow”. Give your flow Name and choose trigger- When a new response is submitted.
- Then add “Get response details” action as shown below-
- Now you will initialize two variables. one is string type and another one is array type. String type variable will be used for attachment link & Array type variable will be used to store computer accessories choices.
- To store form multiple choices in your SharePoint choice column, you will create an array for computer accessories choices.Split(replace(replace(replace(outputs(‘Get_response_details’)?’body/rc9bce6a0275e4d789bd1098d569868d5′],'[‘,”),’]’,”),'”‘,”),’,’)
- And then configure Append to array variable- VarMultipleChoices in such format that will be accepted by SharePoint Choice column.
- After that add Create item action and configure as following.
- Select Employee Name, Employee Number, Employee Email & Employee Address dynamic content from Get response details.
- Here you need to map choices via switch to input entire array icon. Then pass your array variable- VarMultipleChoices in computer accessories required column.
- This will create line item in your SharePoint list ‘Working From Home’. But still you need to attach document and link in your SharePoint list. To do so first you will parse Doctor Prescription attachment then you will get attachment name.
- Note: if you do not know schema then simply add {} in schema.
- As Microsoft form attachments are uploaded on Onedrive. You will fetch attachment content via action-Get file content using path. but this action requires file path. You need to construct file path dynamically via passing file name.
- File path : /Apps/Microsoft Forms/Working From Home Accessories request form/Question/
- For adding form attachment you will “Add attachment” action. In this action, you will pass file name and file content. After that use Set Variable action and set VarLink value as AbsoluteUri from add attachment action.
- You must pass ID value from Create item action.
- Now you have attached Doctor prescription on SharePoint list and you have attachment link in your variable Var link. To update SharePoint list column Document link (Column type: Hyperlink), you will use Send HTTP request action as you can not update hyperlink display name via SharePoint Update item action.
- Headers: {
“Content-Type”: “application/json;odata=verbose”,
“X-HTTP-Method”: “MERGE“,
“IF-MATCH”: “*”
} - Body: {“__metadata”: {“type”:”SP.Data.WorkingFromHomeListItem”},
“DocumentLink”: {“Description”: “Click here”,”Url”: “@{variables(‘VarLink’)}”}
}
- Now save your flow. Go to Microsoft form link and submit one response. Once Flow completed successfully, You will see-Choice columns, Document link in your SharePoint list as shown below.
In this post you have understand how to store Microsoft form response in your SharePoint list. In my next post, I will add few more functionalities like Approval action and notification.
Thank You!!!
Gopenly
