I haven't had any luck finding a solution for my flow design searching the forums (or Google/YouTube either). Simply, I need a flow to refresh my Excel file and email a copy of it. I'd like to set this up on a scheduled refresh.
Flow: Go to SharePoint > Open Excel file > Refresh > Save/Close > email a copy to list of employee's
You could set up a recurrence so the flow triggers on a schedule. I would use the SQL Server Connector (doc. here: SQL Server - Connectors | Microsoft Docs) which has the Get Rows action. Now, if you Excel already has the data from the SQL Server within, then you could skip this step.
I think the Excel would be automatically updated, so I don't think you would have to open it and refresh it unless you need to update the information contained inside (if I'm wrong, please let know!) You could use the Copy File action if you need a copy of your Excel, but if not, you can proceed by sending email.
I would suggest perhaps using the Get File by Path and then using the Send Email (V2) and adding the dynamic content obtain by the fist action as the attachment. Inside the "to" field, you add the list of employees.
Let me know if that helped you get an idea!
Thank you for your reply Rodrigo!
The data i am pulling from SQL is Open Order transactional data that updates daily through the night. New orders are created each day, and some orders are invoiced (removed from the table). I have Power Query steps built into the Excel file to segment by region, clean up columns as well as add custom columns etc. Therefore, the Excel file needs to be refreshed pulling in the entire table, replacing all rows with new.
Currently, I use Power Update on a virtual machine to perform this task. But i find this tool very error prone, and I don't get specific reasons for the failure. I use Power Automate (and PAD) to perform other tasks and like how you know exactly where a failure happens (and generally, only need to fix it once). Which is why i want to move away from Power Update. With Excel and PA both being Microsoft products, I thought they'd easily work together for my scenario.
Is there a way for PA to refresh an existing Excel file?
Check out the new Process Advisor community forum board!
Check out new user group experience and if you are a leader please create your group
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks