'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Update an Excel File in SharePoint with Power Automate Desktop
Question details
The user needs to run an on-premises SQL query and automatically write the results to an existing Excel workbook stored in a SharePoint library using Power Automate Desktop.
- Product
- Microsoft Excel, Power Automate Desktop
- Device & OS
- not provided
- Scenario
- Automating data export from an on-premises SQL server to a SharePoint-hosted Excel file for centralized data tracking and reporting.
- Observed behavior
- Power Automate Desktop natively handles local Excel files, but requires different approaches or community workarounds to successfully target and update SharePoint-hosted Excel files.
Ensure you have the necessary permissions to access the target SharePoint document library and that an On-Premises Data Gateway is configured if you need to query local SQL databases.
Use Power Automate Cloud Flows (Recommended)
Since Power Automate Desktop is optimized for local automation, the most reliable way to update SharePoint-hosted workbooks is by using a Cloud Flow with the Excel Online (Business) connector.
Cloud flows natively integrate with SharePoint and Excel Online, making them much better suited for querying on-premises data (via a gateway) and writing it directly to a SharePoint document without relying on desktop UI automation.
Log into Power Automate in your web browser and create a new Automated or Scheduled Cloud Flow.
Add the 'SQL Server' action, select 'Get rows' or 'Execute a SQL query', and connect to your on-premises database using your pre-configured data gateway.
Add the 'Add a row into a table' action under the 'Excel Online (Business)' connector.
Select your SharePoint Site, Document Library, the specific Excel file, and the Table name where the SQL data should be inserted. Map the SQL columns to the Excel table columns.
Sync SharePoint to a Local Drive for Desktop Automation
If you must strictly use Power Automate Desktop (PAD), you can sync the SharePoint library to your local machine so PAD can treat it as a local Excel file.
Consult the Power Automate Community for Custom Scripts
For highly specific scenarios involving direct API calls from PAD to SharePoint, the Microsoft Power Automate Community offers custom scripts and workarounds.
Easily Edit and Manage Spreadsheets with WPS Office
If you are managing complex data exports or simply looking for a cost-effective, high-performance suite for your spreadsheet tasks, WPS Office is a perfect choice. It provides powerful data processing capabilities with a familiar interface, helping you work seamlessly outside of automated flows.
- 1. Download and Install: Visit the official WPS website to download WPS Office Free and complete the quick installation process.
- 2. Open Your Workbooks: Launch WPS Spreadsheets and open any existing Excel files you exported from your SQL databases.
- 3. Analyze and Edit: Use the advanced formulas, pivot tables, and charting tools to analyze your data effortlessly.
Frequently Asked Questions
Can Power Automate Desktop edit Excel files on SharePoint without syncing?
Power Automate Desktop is primarily designed for local automation. To directly interact with an Excel file on SharePoint without syncing it locally via OneDrive, it is highly recommended to use Power Automate Cloud flows utilizing the Excel Online (Business) connector.
Why does my flow fail to find the Excel table in SharePoint?
For Power Automate to write or read data dynamically from an Excel file in SharePoint, the target range must be formatted as an official Table. You can do this by opening the workbook, highlighting your data, and pressing Ctrl+T before running your flow.
How do I export SQL server data to Excel using Power Automate?
You can connect to your on-premises SQL server using an On-Premises Data Gateway. Use the 'Get rows' action in a Cloud flow to pull the data, then use a 'Apply to each' loop with the 'Add a row into a table' Excel action to write the SQL results into your SharePoint workbook.
