How to Refresh Power Query in SharePoint Excel Without Opening It
Question details
The user wants to automatically refresh Power Query data in an Excel workbook stored on SharePoint without having to open the file manually.
- Product
- Microsoft Excel, SharePoint, Power Automate
- Device & OS
- not provided
- Scenario
- A new source row is added to the data, requiring an automatic update of the Power Query results.
- Observed behavior
- Currently, the user has to open the Excel file manually to trigger a Power Query refresh, and is looking for an automated background solution.
Ensure you have the necessary permissions to edit the Excel file on SharePoint and access to Power Automate or PowerShell to set up automated scripts.
Use Power Automate and an Office Script
Ideal if Power Automate is already adding new rows and your data source is within the same Excel file.
This method uses an Office Script to trigger the 'RefreshAll' command via Power Automate. Note that Excel for the web currently does not support refreshing Power Query connections that rely on external data sources.
Open the Excel file in your web browser, navigate to the 'Automate' tab on the ribbon, create a new script, and add the code: workbook.refreshAllDataConnections();
Log into Power Automate and create an automated cloud flow that triggers whenever a new row is added to your source data.
Add the 'Run script' action under the Excel Online (Business) connector to your flow. Select your SharePoint site, document library, the specific Excel file, and the Office Script you just created.
Use PowerShell to Automate Excel
Best for workbooks that rely on external data sources where Excel for the Web cannot refresh the connections.
Try WPS Office for Seamless Data Management
While complex background automation with SharePoint requires specific Microsoft tools, WPS Office provides a free, lightweight, and highly compatible alternative for everyday spreadsheet tasks. Enjoy a familiar interface and robust data processing tools without the hefty subscription fees.
- 1. Download WPS Office: Visit the official WPS website and download the free installer for your operating system.
- 2. Install the software: Run the downloaded installer and follow the on-screen instructions to set up WPS Office on your device.
- 3. Open your spreadsheets: Launch WPS Spreadsheet and open your existing Excel files to continue working seamlessly with high format compatibility.
Frequently Asked Questions
Why does my Office Script RefreshAll fail for external data?
Excel for the Web currently does not support refreshing Power Query connections that pull data from external sources. The RefreshAll method via Office Scripts only works if the data source is contained entirely within the same workbook.
Can I use Power Automate to refresh Excel desktop?
Power Automate cloud flows primarily interact with Excel Online. To automate the desktop version of Excel for external data refreshes, you would need to use Power Automate Desktop (RPA) or a scheduled PowerShell script on a machine where Excel is installed.
Does the SharePoint file need to be synced locally for the PowerShell method?
Yes, typically the PowerShell COM object method requires the SharePoint document library to be synced to your local machine or server via OneDrive, as it relies on the local Excel desktop application to open the file and perform the refresh.
