A web-based tool in Microsoft 365 that enables users to quickly create surveys, quizzes, polls, and feedback forms.
There is no documented cloud action that forces the built-in Forms-to-Excel connection to synchronize; for reliable unattended processing, write each response through Power Automate to a separate Excel table—or preferably a SharePoint list—and connect Power BI directly to that destination.
Microsoft documents that updates to a Forms-linked workbook can only be triggered in Excel by the form owner or an internal user with edit permission. A Power BI refresh does not trigger that update: it reads the workbook as currently saved in SharePoint. ^1^ ^2^
Recommended architecture: Forms → SharePoint list → Power BI
A SharePoint list avoids workbook locking, Forms synchronization, and Excel table-write dependencies.
- Create one SharePoint list for each form, or a consolidated list where forms share a compatible schema.
- Add columns corresponding to the form questions, plus operational columns such as:
-
ResponseId -
SubmittedAt -
FormName
-
- In Power Automate, select Create → Automated cloud flow.
- Configure:
- Microsoft Forms — When a new response is submitted
- Microsoft Forms — Get response details
- Form ID: same form
- Response ID: Response Id from the trigger
- SharePoint — Create item
- Select the site and list.
- Map fields from Get response details.
- Submit a new test response and inspect Flow → 28-day run history.
- In Power BI Desktop, connect to the SharePoint Online list, publish the semantic model, and configure scheduled refresh. Power BI also supports creating a semantic model directly from a SharePoint list. ^3^
This is fully cloud-based and does not require a workstation.
If Excel must remain the destination
Since the attempted flow did not work, rebuild it against a separate ingestion workbook, not the workbook managed by Forms:
- Create a new
.xlsxworkbook in SharePoint. - Add column headers for the fields to capture.
- Select the range and create a named Excel table. Save and close the workbook.
- Create the flow with all three required operations:
- When a new response is submitted
- Get response details
- Excel Online (Business) — Add a row into a table
- In the Excel action, explicitly select:
- Location: the relevant SharePoint site
- Document Library: normally Documents
- File: the new ingestion workbook
- Table: the named table
- Map each table column from Get response details
- Save the flow, submit a new response, and inspect each action’s inputs and outputs in run history.
The documented pattern requires a cloud-hosted workbook with a predefined table and uses Get response details before adding the row. ^4^
For a Microsoft 365 group form, it might not appear in the Form ID list. In that case, open the form, copy its Form ID from the browser URL, and enter it as a custom value in both Forms steps. ^5^
Do not make both Forms synchronization and Power Automate write into the same response table. Microsoft documents that manually added tables, formulas, or other content can cause the Forms workbook to stop synchronizing. Keep the original workbook untouched and perform transformations in the separate ingestion workbook, Power Query, or Power BI. ^6^
Checks for the failed Power Automate flow
Use the first failed run to identify the failing stage:
- Trigger never ran: For a group form, enter the Form ID as a custom value. Also confirm the form remains in its original location. ^7^
- Get response details failed: Confirm its Response ID comes from the trigger, rather than being a fixed value.
- Table is missing from the picker: Ensure the destination is an actual named Excel table, not merely a populated worksheet range.
- Authentication or connection failure: In Power Automate, open Data → Connections and repair or recreate the Microsoft Forms, SharePoint, and Excel Online (Business) connections. ^8^
- Existing Forms workbook will not sync: If SharePoint’s document library has Require content approval for submitted items enabled, Microsoft states that Forms workbooks will not synchronize. Disable that setting only if it is compatible with the library’s governance requirements. ^9^
- Live connection is damaged: For Microsoft 365 Apps for business or Office 365 Education, use Form → Responses → More options → Sync all responses to a new workbook. This restores the Forms connection but does not provide a cloud mechanism for forcing subsequent updates. ^10^
After changing the ingestion design, point Power BI at the new list or workbook. Scheduled semantic-model refresh can then retrieve rows already written by the flow; it cannot make Forms populate the original workbook first.
References
- Check and share your form results | Microsoft Support
- Refresh a semantic model created from an Excel workbook on OneDrive or SharePoint Online
- Create a Power BI semantic model directly from a SharePoint list
- Common ways to use a form in a flow
- How to do more with forms in Power Automate
- How to ensure your form and workbook are in sync | Microsoft Support
- Troubleshoot known issues with forms in flows
- Manage connections in Power Automate
- How do I know if the data in my Excel workbook is up to date? | Microsoft Support
- How to get missing data in Forms | Microsoft Support