How to add Microsoft Forms responses to database

Add Microsoft Forms responses to databases using power automate will help any person/organization to do advanced data analytics on the responses.

We can add responses to list or table using Power Automate in two different ways and create your survey using Microsoft Form as a first step.

Method 1:

• After creating the survey using Microsoft Form, create a new ‘Automated Flow’ type flow and select the ‘When a new response is submitted’ as trigger event. Select the newly created Microsoft form from the Form Id dropdown.
• Add a new action ‘Get Response details’ and use the ‘Response Id’ from trigger body as input for this action.
• Use add a new row (Dataverse) or create item (SharePoint) or any other action to capture the response from ‘Get Response Details’ action.

This flow creates the response(s) to the data source whenever response is submitted for the form. This method is not efficient for large number of response since the flow creates same number instances and end up in throttle issue.

Method 2 :

Create a new Instant Cloud flow or Scheduled Cloud Flow and Initialize the required variables such as Form Id, User Id and Tenant ID.

• Find the ‘FormId’ query string parameter from the Microsoft Form (Open the Microsoft form in design mode) and set the value to FormId variable.
• Using the Get my profile (V2) action, set the User Id value to ‘User Id’ variable.
• Get the Tenant Id from the Azure Active Directory.

Add ‘Send HTTP Request to SharePoint’ action as a next step, configure the action as mentioned below.

// Site Address
https://forms.office.com/

//Method
GET method

//Uri
/formapi/api/[tenant id]/users/[user id]/light/[form id]

Use the ‘rowCount’ value from the ‘FormInfo’ action output in the compose action and ‘rowCount’ is give the total number of responses.

// Use this below expression in compose action
body('FormInfo')?['rowCount']

By default, Microsoft Form provides option to export and download all the responses in excel format. As a next step, Add ‘Send HTTP Request to SharePoint’ action to get this excel using REST API method.

// Site Address
https://forms.office.com/

//Method
GET method

//Uri
/formapi/DownloadExcelFile.ashx?formid=[FormId]&timezoneOffset=-330&__TimezoneId=Asia%2FCalcutta&minResponseId=1&maxResponseId=[rowCount]

Excel file content is available in the body value of this REST API response and using this ‘body’, we can create a new excel file in the desired location like Onedrive/SharePoint.

As a next step, Use this excel in ‘List rows present in table’ to get all the rows and create all the rows in the SharePoint list / Dataverse table. Refer to our previous posts to work with Excel Online (Business).

Please post your queries in the comments section. Happy Building 🙂