How to Fix Excel SharePoint Query Refresh Failing After First Time
Question details
Users are experiencing a failure when refreshing an Excel Power Query connected to a SharePoint List, where it works the first time but fails on subsequent attempts.
- Product
- Microsoft Excel, SharePoint, PowerApps
- Device & OS
- not provided
- Scenario
- Refreshing Excel data that is connected to a SharePoint List via PowerApps.
- Observed behavior
- The query refreshes successfully on the initial attempt but fails for multiple users on subsequent refreshes, often displaying random or script errors.
Ensure you have the necessary administrative or owner access to the connected SharePoint List and verify that you are logged into Excel with the correct Microsoft organizational account.
Verify SharePoint List Permissions and Credentials
Check that all users have the correct access rights and that the data-source credentials haven't expired, which is the most common cause for multi-user refresh failures.
When an Excel file is shared among multiple users, the initial creator's credentials might be cached for the first refresh, but subsequent refreshes by other users will fail if they lack proper permissions or if the privacy settings restrict data access.
Navigate to your SharePoint List in the browser. Click on 'Settings' (the gear icon), select 'List settings', and go to 'Permissions for this list' to ensure all affected users have at least Read access to the complete list.
Open the affected Excel workbook, navigate to the 'Data' tab on the ribbon, click 'Get Data', and select 'Data Source Settings'.
Select the SharePoint source from the list, click 'Clear Permissions', and then prompt users to refresh the query. They will be asked to sign in again using their current Microsoft organizational account.
In the 'Data Source Settings' menu, select 'Edit Permissions' and ensure the Privacy Level is set to 'Organizational' for both the Excel workbook and the SharePoint List to prevent firewall conflicts.
Isolate the Query from PowerApps Integration
Test the query independently of PowerApps to determine if the integration is causing the file to lock or the refresh to fail.
Collect Exact Errors and Review Audit Logs
Gather specific technical details from the failing refresh to identify underlying schema changes or list lock conflicts.
Discover WPS Office for Your Spreadsheet and Data Needs
While complex integrations involving PowerApps and SharePoint Power Query are exclusive to the Microsoft ecosystem, WPS Office provides a free, lightweight, and highly compatible alternative for your everyday spreadsheet tasks. It supports standard Excel formats seamlessly and offers an intuitive interface without the burden of expensive subscriptions.
- 1. Download and Install: Visit the official WPS Office website to download the free suite and install it on your device.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing .xlsx data files directly.
- 3. Manage Data Easily: Utilize built-in sorting, filtering, and pivot tables to manage your data effortlessly without complex setup requirements.
Frequently Asked Questions
Why does my Excel Power Query refresh work only the first time?
This commonly occurs due to caching issues, expired background credentials, or file locks applied to the data source after the initial data pull, especially when integrated with external tools like PowerApps.
How do I fix script errors when refreshing SharePoint data in Excel?
Script errors typically point to credential conflicts or changes in the SharePoint List schema. Verifying that all users have adequate read permissions and clearing the data source settings in Excel via 'Data' > 'Get Data' > 'Data Source Settings' usually resolves the issue.
Can I use Microsoft Power Query features in WPS Office?
WPS Office offers robust data filtering, sorting, and pivot table capabilities for everyday use. However, advanced Microsoft-specific features like Power Query (M-code) and direct PowerApps integrations are exclusive to the Microsoft environment.
