Creating and Configuring Microsoft Forms

Step 1: Creating a Microsoft Form

Instructions:

1. Log in to Microsoft Forms: Go to Microsoft Forms and log in with your Microsoft account.

2. Create a New Form:

   • Click on "New Form".
   • Add a title and a description for your form.

3. Add Questions:

   • Click on "Add New".
   • Choose the type of question to add (e.g., Choice, Text, Rating, Date).
   • Populate the necessary fields for each question.

4. Customize:

   • Use the ellipsis (three dots) next to each question to provide additional settings like required questions or multiple answers.
   • Customize the theme by clicking on the "Theme" button on top.

5. Share:

   • Click on the "Share" button.
   • Copy the link or use other provided options like email or QR code to distribute your form.

Step 2: Collecting Responses into an Excel Spreadsheet

Instructions:

1. Open Microsoft Forms Responses:

   • After users submit their responses, go to your form.
   • Click on the “Responses” tab.

2. Export Responses to Excel:

   • Click on the "Open in Excel" button.
   • An Excel file (.xlsx) will be downloaded containing all responses collected so far.

Step 3: Automating the Process Using Microsoft Power Automate

Instructions:

1. Log in to Microsoft Power Automate: Go to Microsoft Power Automate and log in with your Microsoft account.

2. Create a New Flow:

   • Click on "Create" -> "Automated cloud flow".
   • Give your flow a name and select the "When a new response is submitted" trigger under Microsoft Forms.

3. Configure the Flow:

   • Trigger: Choose your form from the list.
   • Action: Click on "New step", search for "Get response details", and select it.
     • Form ID: Select your form.
     • Response ID: Choose the ID from the trigger.

4. Add Excel Connector:

   • Add New Step: Search for "Add a row into a table" under Excel Online (Business).
   • Choose Location, Document Library, File and Table in your Excel workbook where responses will be placed.

5. Map Form Responses to Excel Columns:

   • Map each field from your form to the corresponding column in your Excel table. This is where you define how each response is placed into your spreadsheet.

6. Save the Flow:

   • Save your flow and test it by submitting a new response to your form. Check if the new entry appears in your Excel table.

Example Workflow in Power Automate

1. Trigger: When a new response is submitted
2. Action: Get response details
   • Form ID: {Your Form ID}
   • Response ID: Response ID from Trigger
3. Action: Add a row into a table
   • Location: {OneDrive or SharePoint}
   • Document Library: {Document Library Name}
   • File: {File Name}
   • Table: {Table Name}
   • Field Mappings: {Map each response field to corresponding Excel column}

Notes:

• Make sure your Excel file is properly formatted and resides in OneDrive or SharePoint.
• Ensure that the table in Excel is created and headers are correctly set to match response fields.

By following these steps, you can automate the process of collecting responses from Microsoft Forms and saving them into an Excel spreadsheet. This will help streamline data collection and make it easier to analyze responses.

Power Automate: Automate Microsoft Forms Responses to Excel

Introduction to Power Automate Interface

Power Automate (formerly known as Microsoft Flow) is a service that helps you create automated workflows between your favorite apps and services to synchronize files, get notifications, collect data, and more. In this section, we will automate the process of collecting responses from Microsoft Forms and placing them into an Excel spreadsheet based on predefined conditions.

Step-by-Step Implementation

Step 1: Create a New Flow

1. Log in to Power Automate: Go to Power Automate and log in with your Microsoft credentials.

2. Create a New Automated Flow:

   • Click on "Create" on the left panel.
   • Select "Automated cloud flow".
   • Provide a name for your flow.
   • Choose the trigger "When a new response is submitted" for Microsoft Forms.

Step 2: Configure the Trigger

1. Select Form:

   • Choose the form you want to get responses from using the dropdown list.

2. Get Response Details:

   • After selecting the form, add a new action.
   • Search for "Microsoft Forms" and select "Get response details".
   • Specify the Form ID and Response ID (can be selected from the dynamic content).

Step 3: Add Conditions to Filter Responses

1. Add a Condition:
   • Click on "New step" and choose "Condition" from the actions.
   • Define your condition logic, for example, if a specific field in the form response is equal to a certain value.

if (response_field == "specific value") { // Add actions based on your condition }

Step 4: Add an Action to Store Data in Excel

1. Select Excel Online (Business):

   • Add a new step.
   • Search for "Excel" and select "Excel Online (Business)".

2. Add a Row into a Table:

   • Choose the action "Add a row into a table".
   • Select the location (OneDrive, SharePoint, etc.).
   • Navigate to the file where you want to store the responses.
   • Select the Table within the Excel file where the data needs to go.
   • Map Form responses to the corresponding columns in the Excel table.

// Example: "TableID": "Your Table Name", "Column1": "Response detail 1", "Column2": "Response detail 2", // Map remaining responses

Step 5: Save and Test Your Flow

1. Save the Flow:

   • Click on "Save" at the bottom right.

2. Test Your Flow:

   • Use the "Test" button to run the flow using existing or new submissions.
   • Check your Excel file to ensure the responses are correctly added based on your predefined conditions.

Summary

By following these steps, you have successfully created a Power Automate flow that automates the collection of Microsoft Forms responses and places them into an Excel spreadsheet with predefined conditions. This implementation directly addresses the automation of data handling, making your workflow efficient and streamlined.

Building the Flow: Connecting Forms to Excel

Overview

This section focuses on automating the process of collecting responses from Microsoft Forms and placing them into an Excel spreadsheet using Power Automate.

Steps

Step 1: Create an Excel Spreadsheet

Before creating the flow, ensure you have an Excel spreadsheet ready with a table to store the form responses.

1. Open Excel and create a new workbook.
2. Create a table (e.g., FormResponses) with columns corresponding to the form questions.
3. Save this Excel file to OneDrive or SharePoint.

Step 2: Build the Flow in Power Automate

1. Navigate to Power Automate: Open Power Automate and sign in with your credentials.

2. Create a New Flow: Click on Create and then choose Automated flow.

3. Set Up Automated Flow Trigger:

   • Choose the trigger "When a new response is submitted" from Microsoft Forms.
   • Select the form you want to connect to your Excel file from the dropdown.

4. Get Response Details:

   • Add a new action and select "Get response details" from Microsoft Forms.
   • Configure it by setting Form Id (choose the form) and Response Id (from the previous step's output).

5. Add Row to Excel:

   • Add a new action and search for "Add a row into a table" from Excel Online (Business).
   • Configure the action:
     • Location: OneDrive or SharePoint.
     • Document Library: Where your Excel file is saved.
     • File: Browse to the Excel file.
     • Table: Select the table you created (e.g., FormResponses).
     • Columns: Map each form question to the respective column in the Excel table.

Sample Expression for Mapping Responses

Here's an example of how to map form responses to Excel columns:

{
  "Field 1": @{outputs('Get_response_details')?['body/Question1']},
  "Field 2": @{outputs('Get_response_details')?['body/Question2']},
  "Field 3": @{outputs('Get_response_details')?['body/Question3']}
}

Example Setup in Power Automate

1. Create a new automated cloud flow.
2. Set the trigger to Microsoft Forms, "When a new response is submitted".
3. Add the action "Get response details" and configure it.
4. Add the action "Add a row into a table" and fill in the required fields.

Example Flow Diagram

Trigger: When a new response is submitted (Microsoft Forms)
    -> Action: Get response details (Microsoft Forms)
        -> Action: Add a row into a table (Excel Online)
            - Location: OneDrive for Business
            - Document Library: OneDrive
            - File: YourWorkbook.xlsx
            - Table: FormResponses
            - Columns: Map form questions to table columns

Conclusion

Your flow is now set up to automatically collect responses from Microsoft Forms and place them into an Excel spreadsheet. This automation enhances efficiency by streamlining data collection into predefined Excel tables. You can monitor the flow execution in Power Automate to ensure it runs as expected.

Incorporating If-Then Statements in Excel

Introduction

This section will guide you on how to incorporate If-Then statements in Excel to automate the process of evaluating responses collected from Microsoft Forms and placing them into an Excel spreadsheet with predefined conditions. Considering that you've already covered creating forms, configuring them, and setting up the flow with Power Automate, we will focus specifically on using If-Then logic within Excel.

Example Scenario

Suppose you have a questionnaire that collects responses about customer satisfaction, and based on the responses, you want to categorize the feedback into different columns: "Positive", "Negative", and "Neutral".

Using IF Function in Excel

You can use the IF function in Excel to achieve this. Below is a practical example:

Source Data in Excel

Assume the collected responses are placed in column A ("Responses").

IF-Then Logic in Excel

1. Add new columns for categorization:

   • In columns B, C, and D, create headers "Positive", "Neutral", and "Negative".

2. Apply IF formula:

   • In cell B2 (first row under "Positive"), apply the following formula:

   =IF(OR(A2="Very Satisfied", A2="Satisfied"), "Positive", "")

   • In cell C2 (first row under "Neutral"), apply the following formula:

   =IF(A2="Neutral", "Neutral", "")

   • In cell D2 (first row under "Negative"), apply the following formula:

   =IF(OR(A2="Dissatisfied", A2="Very Dissatisfied"), "Negative", "")

3. Drag the formula down:

   • Select cells B2, C2, and D2, and drag the fill handle down to apply the formulas to all rows corresponding to your response data in column A.

Resulting Spreadsheet

By applying the above formulas, you have automated the categorization of responses into predefined conditions.

Conclusion

This guide provides you with an exact and practical implementation of using If-Then statements in Excel to automatically evaluate and categorize responses from Microsoft Forms. You can further enhance the complexity as per your requirements by using nested IF functions, AND, OR, or other logical functions provided by Excel.

Automation and Troubleshooting Tips for Beginners

Part 5: Collecting Responses from Microsoft Forms and Placing Them into an Excel Spreadsheet with Predefined Conditions

Automation Implementation

1. Trigger Flow on Form Submission:

   • Ensure your Microsoft Form triggers the flow upon new submissions.

2. Initialize Variables:

   • Initialize any variables required for processing responses. This is typically done in the "Initialize Variable" step in Power Automate.

   Step: Initialize Variable
   Name: SubmittedResponse
   Type: Object

3. Retrieve Form Response Details:

   • Use the "Get response details" action to fetch detailed responses from the form submission.

   Step: Get response details
   Form ID: YourFormID
   Response ID: TriggerOutput_ResponseID

4. Apply Predefined Conditions:

   • Use condition controls to apply any predefined checks or transformations needed for your data before inserting it into the Excel sheet. This leverages 'Condition' action in Power Automate.

   Step: Condition
   Expression: SubmittedResponse['Question1'] equals 'ConditionValue'

   • Under the "if true" branch, define steps for inserting into Excel, and specify any transformation logic if required.

5. Update Excel Spreadsheet:

   • Use the "Add a row into a table" action to insert the form response into the specified cells of the Excel sheet.

   Step: Add a row into a table
   Location: OneDrive for Business (or other storage services)
   File: Path to Excel file
   Table: Table name
   Values:
       Column1: SubmittedResponse['Question1Response']
       Column2: SubmittedResponse['Question2Response']

Troubleshooting Tips

1. Ensure Connectivity:

   • Verify that Power Automate has access to both Microsoft Forms and the Excel file stored in OneDrive or SharePoint.

2. Check for Proper Permissions:

   • Ensure the Flow creator or executor has sufficient permissions to modify and access the respective Microsoft Form and Excel file.

3. Validate Condition Logic:

   • Use built-in debugging and "Compose" actions to output intermediate values and ensure your conditions are being evaluated correctly.

   Step: Compose
   Input: SubmittedResponse

4. Track Errors:

   • Add error-handling steps. Use the "Configure run after" feature to handle errors gracefully and send notifications or log errors appropriately.

   Step: Send an email
   Call: Office 365 Outlook - Send an email (v2)
   To: YourEmail@domain.com
   Subject: Error Notification
   Body: "An error occurred: Error Details"

5. Monitor Flow Runs:

   • Regularly check the run history of your Power Automate flow to diagnose any issues and ensure that responses are being processed as expected.

---

By following this guide, you should be able to automate the task of collecting Microsoft Forms responses and storing them into an Excel sheet while applying predefined conditions, as well as troubleshoot common issues effectively.