Microsoft Forms to Excel-Automatic Synchronization Issue

PowerBI Admin 0 Reputation points
2026-11-19T00:37:28.9033333+00:00

Hi Microsoft Support,

We have multiple Microsoft Group Forms linked to Excel workbooks stored in SharePoint.

The issue is that new Forms responses do not appear in the linked Excel workbooks until someone manually opens each workbook in Excel Online and waits for synchronisation.

We have also developed Power BI reports that pull data from these Excel workbooks. Despite configuring scheduled refreshes in Power BI, the latest responses are not reflected in the reports unless we first open the Excel workbooks online and allow them to synchronise.

To address this, we developed a Python script to automatically open the workbooks, but it requires a computer to be running and occasional maintenance.

We also attempted to use Power Automate with the When a new response is submitted trigger and Add a row into a table action, but this did not work either.

Is there a supported, reliable, cloud-based solution to automatically synchronise Microsoft Forms responses with their existing Excel workbooks in the background, without manually opening them or relying on a local computer?

We would appreciate your guidance.

Kind regards

Microsoft 365 and Office | Microsoft Forms | For business
0 comments No comments

1 answer

Sort by: Most helpful
  1. AI answer

    2026-11-19T00:38:26.8533333+00:00

    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^

    A SharePoint list avoids workbook locking, Forms synchronization, and Excel table-write dependencies.

    1. Create one SharePoint list for each form, or a consolidated list where forms share a compatible schema.
    2. Add columns corresponding to the form questions, plus operational columns such as:
      • ResponseId
      • SubmittedAt
      • FormName
    3. In Power Automate, select Create → Automated cloud flow.
    4. 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.
    5. Submit a new test response and inspect Flow → 28-day run history.
    6. 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:

    1. Create a new .xlsx workbook in SharePoint.
    2. Add column headers for the fields to capture.
    3. Select the range and create a named Excel table. Save and close the workbook.
    4. Create the flow with all three required operations:
      1. When a new response is submitted
      2. Get response details
      3. Excel Online (Business) — Add a row into a table
    5. 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
    6. 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

    1. Check and share your form results | Microsoft Support
    2. Refresh a semantic model created from an Excel workbook on OneDrive or SharePoint Online
    3. Create a Power BI semantic model directly from a SharePoint list
    4. Common ways to use a form in a flow
    5. How to do more with forms in Power Automate
    6. How to ensure your form and workbook are in sync | Microsoft Support
    7. Troubleshoot known issues with forms in flows
    8. Manage connections in Power Automate
    9. How do I know if the data in my Excel workbook is up to date? | Microsoft Support
    10. How to get missing data in Forms | Microsoft Support
    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.