How to Transform Microsoft Forms Responses into Interactive Dashboards with Bold BI

Introduction

Organizations commonly use Microsoft Forms to collect employee information, customer feedback, survey responses, event registrations, and various operational data. As the number of responses grows, manually reviewing records in Forms or Excel becomes inefficient and makes it difficult to identify trends, monitor key metrics, or gain timely insights. To effectively analyze the collected data, organizations need an automated process that centralizes responses and makes them readily available for reporting and analytics.
 
This knowledge base demonstrates how to automate Microsoft Forms responses using Microsoft Power Automate and store them in an Excel file hosted on OneDrive, creating a reliable and continuously updated data source for analysis.

Once the form responses are automatically stored in Excel, Bold BI can be used to transform the raw data into meaningful visual insights. Using the Microsoft OneDrive Connector, Bold BI connects directly to the Excel file stored in OneDrive and enables users to create interactive dashboards, reports, and KPIs based on the collected responses.

By combining Microsoft Forms, Power Automate, OneDrive, and Bold BI, organizations can establish an end-to-end solution for automated data collection, dashboarding, and analytics, helping stakeholders monitor trends, track performance, and make informed decisions with up-to-date data.

Steps to Automate Microsoft Forms Responses

Follow the steps below to automatically capture Microsoft Forms responses and store them in an Excel file on OneDrive using Microsoft Power Automate.

1. Create a Microsoft Form

• Create a Microsoft Form with the desired questions. For example, a form named Employee Form with five sample questions.

2. Set Up Excel File

• Create a new Excel file named EmployeeForm to store the responses.
• Add five columns corresponding to the questions in the form.
• Select all header columns, click on Insert, then Table. Ensure to select ‘My table has headers’ and click ‘OK’.
• Save the Excel file in OneDrive.

3. Use Power Automate to Create a Flow

• Log in to your Microsoft 365 account and navigate to Power Automate by clicking the grid icon.
  

  

• Click the Create icon on the left side and select Automated cloud flow.
  

  

• In the pop-up, enable When a new response is submitted to create the flow.
  

  

4. Configure the Flow

• Click the When a new response is submitted option.
  
  
• Select the form you created (e.g., Employee Form) under Form ID.
  
  
• Click on Add an action and search for Microsoft Forms, then select Get response details.
  
  

• Choose the created Excel file EmployeeForm and select ResponseID.
  
  
• Search for and select Excel Online (Business).
  
  
• Click See more and choose Add a row into a table.
  
  

5. Set Up Excel Table Action

• In the Add a row into a table action:
  • Select OneDrive for Business in the Location.
  • Choose OneDrive in Document Library.
  • Upload the created EmployeeForm Excel file.
  • Select the appropriate table.
    
    
• In the advanced parameters, map the columns from the Excel file to the corresponding columns in the Microsoft Forms queries to store responses respectively
  
  
  .

6. Save and Test the Flow

• Save the flow.
  
  
• Submit responses through the Microsoft Form. The responses should automatically update in the Excel file.
  
  

Connecting Form Response Excel to Bold BI

• Use the Microsoft OneDrive connector to connect the Excel file stored in OneDrive.
  
  
• The data will be fetched and displayed in the data preview. Configure the refresh settings to update the data at desired intervals (e.g., every five minutes).
  
  

Note: The entries automated in the Excel file will be refreshed and fetched in the dashboard through refresh scheduling Manage Data sources

Conclusion

By automating Microsoft Forms responses with Microsoft Power Automate and storing them in Excel on OneDrive, organizations can eliminate manual data entry and maintain a centralized, continuously updated data repository. With Bold BI, this data can be transformed into interactive dashboards and reports through the Microsoft OneDrive Connector, enabling users to visualize response trends, monitor key metrics, and make informed decisions using near real-time insights.

Additional References

• Microsoft Power Automate Documentation
• Bold BI Learning - Microsoft OneDrive Connector
• Bold BI Documentation - Manage Data Sources