Why is my Forms Spreadsheet not syncing until I open it?

Question

We created a simple MS Form about 2 months ago and it stores the Responses is a spreadsheet in Sharepoint. We then created a Power BI report using the Web as a source and mapping to the Form's Spreadsheet on Sharepoint. We do not have a Flow involved. The Power BI data suddenly stopped updating when refreshed. It would not recognize new Form Responses until we go into the Form and Open the Spreadsheet. Then it would magically work. I tried to Disconnect and Sync to New Workbook in the Form, but that did not fix the issue. I don't want to change the Form link since everyone in the company uses it. How do I fix this?

Answer 1

Hi Darren,

Thanks for posting in the community. We are happy to help you.

Currently, based on the way forms were created, there are two ways for forms to sync data to Excel workbooks (sync responses in real-time Or sync responses only when opening the workbook in Excel for Web). Per your description, it appears that your form uses the second method. To check your form type, I would like to know what the Excel icon of your form looks like.

You can go to Forms for the web (https://forms.office.com/), open the form, click the Responses tab, and check the Excel icon. Does the Excel icon look like the first icon or the second shown in my screenshots?

We look forward to your response. Thanks for your cooperation.

Sincerely,

George | Microsoft Community Moderator

Answer 2

Mine looks like this. How can I change it to sync in real time?

Answer 3

Hi Darren,

Thanks for posting back.

Here is a thread where the member asked a similar question. He was using the same type of form. I suggest you view the workarounds in my reply.

How do I force Forms and Excel to sync before my Flow runs?

If you don't want to rebuild the form or move it to a group, then building a Power Automate flow (which takes data directly from the form and adds it to an Excel workbook) is a better solution.

Sincerely,

George | Microsoft Community Moderator

Answer 4

I can't move the Form to a Group as that violates security policy. I can't create the Form from Excel as that creates a new Form link. Our whole company has the one link and I can't change it or people won't the right Form. The only option i have is to use Power Automate flow to sync data. How do I do that? Can I also trigger the an entry in the Form to update the Power BI report? Do I use the Refresh a Dataset? How do I do this? Is there an example of all the Flow steps needed?

Answer 5

Hi Darren,

Thanks for posting back.

Here is an example.

1. Create a new workbook to store the responses.
2. Open the original workbook and copy the entire table, paste it into the new workbook
3. Create a Power Automate flow

a. Go to https://make.powerautomate.com/, sign into your account, click "My flows" on the left sidebar, New flow, Automated cloud flow, choose the trigger "When a new response is submitted".

b. In the trigger "When a new response is submitted", click the down arrow and choose your form.

c. Add the action "Get response details", choose the form in Form Id and select "Response Id".

d. Add the action "Add a row into a table", select the relevant group/location, library, select the new workbook and its table, in Advanced parameters, add the relevant parameters from "Get response details".

Also, you can view the YouTube videos to build such a flow.

https://www.youtube.com/results?search_query=Power+Automate+Submit+MicroSoft+Forms+Responses+to+Excel+Online

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.

We look forward to your response. Thanks for your cooperation.

Sincerely, 

George | Microsoft Community Moderator