Microsoft forms responses are not syncing automatically to onedrive/sharepoint

I have many PowerBi reports connected to form responses in excel and now my dashboards will not automatically refresh because form responses don't sync without opening the responses workbook. I cannot understand how MS thought this was a sensible decision. I sure hope they are making this an urgent priority! Next step is to bombard MS support.

Hello,

I manage over 400+ Microsoft Forms, and stopped synching after Jan 13, 2025. The associated Power Automate flows, Power BI queries, and dashboards data is not updating unless the linked Excel files are manually opened. Manually opening 400 files just to trigger the sync is not practical.

To address this in my environment, I created a PowerShell script that loops through a specified directory containing the Form-linked Excel files. The script programmatically opens each file to force the data sync, then closes the file. This process is automated via Task Scheduler and runs once daily.

This is a temporary workaround until Microsoft resolves the New Sync bugs.

Below is the script — you can modify the folder path to match your setup.

In my case, the process took about 55 minutes to open and close all files. Your runtime may vary depending on the number of form response files you have.

Hope this helps, and feel free to reach out if you have any questions.

Thanks,

Rodrigo Pareno - Systems Analyst

# MS Form and Excel Data Refresh 02/27/2025

# Prerequisites - Install OneDrive on the server or computer and ensure the SharePoint folder is showing. Create a task in Task Scheduler to run the PowerShell script daily or every 3 hours.

# Start OneDrive and ensure it is running

$OneDriveProcess = Get-Process -Name "OneDrive" -ErrorAction SilentlyContinue

if (-not $OneDriveProcess) {

***Start-Process "C:\Program Files\Microsoft OneDrive\OneDrive.exe"*** 

***Start-Sleep -Seconds 10 # Allow time for OneDrive to start*** 

***Write-Host "OneDrive launched."*** 

} else {

***Write-Host "OneDrive is already running."*** 

}

# Set path to the folder containing the Excel files

$folderPath = "C:\Users\JohnWick\OneDrive - Data\Documents\FormResponses"

# Get all Excel files from the folder and subfolders, excluding specific folders

$excelFiles = Get-ChildItem -Path $folderPath -Recurse -Filter *.xlsx | Where-Object {

***$filePath = $\_.DirectoryName*** 

***$filePath -notlike "$folderPath\Folder1\*" -and*** 

***$filePath -notlike "$folderPath\Folder2\*" -and*** 

***$filePath -notlike "$folderPath\Folder3\*" -and*** 

***$filePath -notlike "$folderPath\Folder4\*"*** 

}

# Function to open and close Excel files

function Open-And-CloseExcelFiles {

***param(*** 

    ***[array]$files*** 

***)*** 

***# Start Excel application*** 

***$excel = New-Object -ComObject Excel.Application*** 

***$excel.Visible = $true*** 

***foreach ($file in $files) {*** 

    ***try {*** 

        ***# Open Excel file*** 

        ***$workbook = $excel.Workbooks.Open($file.FullName)*** 

        ***Write-Host "Opened file: $($file.FullName)"*** 

    ***} catch {*** 

        ***Write-Host "Error opening file: $($file.FullName). Skipping this file."*** 

        ***continue # Skip this file and move to the next*** 

    ***}*** 

    ***# Wait for 10 seconds (optional, depending on sync needs)*** 

    ***Start-Sleep -Seconds 10*** 

    ***try {*** 

        ***# Close the Excel file without saving changes*** 

        ***$workbook.Close($false) # False means don't save changes*** 

        ***Write-Host "Closed file: $($file.FullName)"*** 

    ***} catch {*** 

        ***Write-Host "Error closing file: $($file.FullName)"*** 

    ***}*** 

***}*** 

***# Quit Excel application*** 

***$excel.Quit()*** 

}

# Process all files and stop after the last file

Open-And-CloseExcelFiles -files $excelFiles

Write-Host "All files processed. Exiting the script."

Great job!

HI..

Do the same thing. All my form created through sharepoint. However, still not auto update. Any other ways to overcome this?

Any screenshots of the error?