How to Create a Power Automate Flow for Daily Microsoft Forms Totals
Question details
Set up a scheduled Power Automate flow to calculate daily submission totals from Microsoft Forms, grouped by department and callback requests, and distribute a summary via email.
- Product
- Microsoft Power Automate, Microsoft Forms
- Device & OS
- not provided
- Scenario
- Automating daily reporting of Microsoft Forms submissions, including department breakdowns and tracking specific responses like callback requests.
- Observed behavior
- The goal is to successfully generate and send an automated daily email summarizing the filtered form responses in a formatted HTML table.
Ensure you have an active Microsoft Power Automate license and that your Microsoft Forms responses are being saved to a reachable data source, such as SharePoint, Dataverse, or an Excel Online spreadsheet.
Build a Scheduled Cloud Flow with Data Filtering
Create an automated daily schedule that retrieves, filters, and summarizes your form data into an email report.
To compile a daily summary, you need a flow that runs on a timer, retrieves the existing responses, and filters them strictly by the current date before performing counting logic.
Log in to Power Automate, select 'Create' from the left menu, and choose 'Scheduled cloud flow'. Name your flow and set the recurrence to run once a day at your preferred reporting time.
Add an action to fetch the data where your Microsoft Forms responses are stored. Use 'Get items' for a SharePoint list or 'List rows present in a table' if responses are synced to an Excel Online file.
Add a 'Filter array' action to isolate responses submitted on the current date. Compare the submission date column to the current date using the UTC-aware expression `formatDateTime(utcNow(), 'yyyy-MM-dd')`.
Use additional 'Filter array' actions on the daily records to isolate specific departments. Create another filter where the 'Callback' field is equal to 'Yes'. Use the `length()` expression in a 'Compose' action to count the items in these filtered arrays.
Add the 'Create HTML table' action to neatly structure your counted variables. Finally, add the 'Send an email (V2)' action, mapping your HTML table to the email body, and send it to the relevant stakeholders.
Analyze Form Data Offline with WPS Office
While Power Automate handles automated workflows online, you can easily download your Microsoft Forms responses as an Excel file and perform advanced data analysis for free using WPS Spreadsheet. WPS Office offers a lightweight, highly compatible alternative to Microsoft Office.
- 1. Export form data: Go to Microsoft Forms, open your form's 'Responses' tab, and click 'Open in Excel' to download the raw data.
- 2. Open with WPS Spreadsheet: Launch WPS Office and open the downloaded .xlsx file to view your submission records.
- 3. Summarize with Pivot Tables: Select your data range, click on the 'Insert' tab, and choose 'PivotTable' to easily group responses by department and count callback requests offline.
Frequently Asked Questions
Can I trigger this flow every time a new response is submitted instead of daily?
Yes. Instead of a 'Scheduled cloud flow', you can create an 'Automated cloud flow' using the 'When a new response is submitted' trigger. However, this method is designed to send an email per individual response rather than aggregating a daily summary.
Why is my Power Automate date filter missing some daily responses?
This usually occurs due to time zone differences. Because Power Automate operates in UTC by default, responses submitted late in the day might be recorded as the next day in UTC. Make sure you use the 'Convert time zone' action or adjust your filter query to match your local time zone's midnight-to-midnight boundary.
How do I count the length of a filtered array in Power Automate?
You can use the `length()` expression in a 'Compose' action or directly inside a variable. For example, typing `length(body('Filter_array'))` into the expression builder will return the total integer count of items that matched your department or callback criteria.
