Forum Discussion
Excel Does Not Sync Until Opened
I created a form linked to an Excel spreadsheet in SharePoint as well as a Power Automate approval flow triggered on submission. In addition to sending out confirmation/approval emails, the flow also updates the associated Excel spreadsheet with the approval/denial.
The flow was working fine until last week but since then has failed 2 consecutive times. The failures appear to be related to the flow unable to find the automatically generated submission ID in the Excel spreadsheet.
When I open the linked spreadsheet, the submission is not immediately present at first but the file does sync at that point and the entry appears. When re-triggered after the sync, the flow completes successfully.
Not sure if it is related, but MS form/linked spreadsheets have been updated by Microsoft recently and I was prompted to go through an update process for synced Excel spreadsheets. I completed the update with this specific form last week after the flow failed for the first time.
TLDR - my form only syncs with the spreadsheet once opened.
Any ideas how to get MS Forms to update the spreadsheet in real-time as opposed to when it is opened?
3 Replies
Thanks for your reply, really appreciate it. Was hoping there might have been another way round this, given the amount of flows, and logic I now need to amend/rewrite to just get things back working as they were....☹️.
TBrockTROCdid you ever get an answer/solution to this issue as I have the same issue since the form update
TIBN79... sort of.
Turns out it was an intentional change from Microsoft - here is a blog post about how the the linked Excel document has to be manually opened to sync:
https://techcommunity.microsoft.com/blog/microsoftformsblog/update-to-the-new-solution-for-syncing-forms-responses-to-excel/4229844/
Since this was an intentional change from Microsoft, I am not optimistic that it will be "fixed" in the sense of returning to real-time syncing instead of having to manually open the Excel file. The workaround would be to use Power Automate to update the Excel file or a SharePoint list with the MS Form submission data.
Continue using the Excel
The issue with using Power Automate to update the Excel spreadsheet, you will end up with duplicated submissions because the flow would update the spreadsheet and the spreadsheet would automatically sync when opened.
To avoid this, you can go to the form and go through the process of syncing the form to a new Excel spreadsheet. You can then use Power Automate (add row in Excel) to update the original spreadsheet with new submission data without being concerned that opening the file will sync to MS Forms and create duplicate entries.
In this scenario, the new spreadsheet the MS Form is now synced to is essentially a dummy and not used for anything functionally - it just exists and is synced whenever opened. The old spreadsheet (containing historical data) will continue to be updated via Power Automate.
Create and Update a SharePoint List
Alternatively, create a list in SharePoint (or MS Lists) that matches the data collected in your MS Form / the Excel spreadsheet - I believe you can even create the list from uploading an example spreadsheet as well as bulk load historical data.
Use Power Automate to create a new item in the list and populate it with the details from the Forms submission - this way you aren't using the Excel spreadsheet in a real-time way.
In this scenario, the SharePoint is the primary data location and the Excel spreadsheet just kind of exists as a backup and syncs with Forms - the data should match all the original data collected in Forms but would not represent any changes made by Power Automate in the SharePoint list after the submission (i.e. marking an item as approved).
Sucks but hope this helps.
