I have been struggling to this for weeks, and I have looked everywhere yet can't find a similar case to mine.
Here is my problem:
I have an Excel spreadsheet with data formatted as a table that comes every day to our Email, with the same structure just the values are different, and I need to move this data to SharePoint and keep updating daily when this file comes.
Now I have set up a Flow that when new email comes it move it to a folder in SharePoint, then I need to get rows from it to update a SharePoint list that has the same columns as the Excel spreadsheet, but who I can tell the flow a table name and file name while I don't have the file there yet !
I'm sure there is a solution to that and hope someone will help with that.
I have a similiar workload that I do daily, outside of getting the file from an email every day - I grab it from an FTP. You likely need three different Flows to perform this task, which would be:
For us, we perform all of this on a recurring schedule. The "Download" Flow runs nightly at 10:00PM (a few hours after our client publishes the file every night). Then, the "Purge" Flow happens at midnight, with the "Upload" Flow happening 15 mintues later. The Purge flow runs in about 30 seconds, and and the Upload flow runs in about 60 seconds.
This combination works great for us, and has for months. You can certainly build all of this functionality into a single Flow, but I like having the ability of knowing right away if/when each step of the process fails. For one reason or another, we do have failures every so often but this allows us to remedy those failures right away.
I've added screenshots of our Purge and Upload Flows, which hopefully may help. It sounds like your email that comes in daily may have spreadsheets that are named differently, and possibly have different table names, but the process should still pretty much be the same, you just need to add the logic to figure out the names, and/or add a step to the dowload Flow that renames the file/table names before uploading the Excel file to the document library.
You may be able to get the table and spreadsheet to merge nightly, but again, we've found that purging the list daily is the best way to insure that we truly have reliable data daily. The merge functionality isn't reliable enough for us. The Excel connector is very troublesome, and in a perfect world we wouldn't have to use it - and I'm hoping that we eventually move this process to a REST API operation. But for now, this works for us.
Hope this helps!
First of all, Thank you for taking the time and looking through this.
My problem is with the Excel part in the flow, as in your workflow in the filed of 'List rows present in a table', what did fill in the filed of 'File' and 'Table'?
Logically there is no file yet in the folder!
My file and table name never change, so it is easy for me to just set those statically. As I said in my first post, if yours is always named differently, you can just add the logic to the "download" flow to rename the file and/or table before it uploads the file to the document library, so it is always the same name.
Hi @ELGHAZI009 ,
Thanks for feedback.
I am afraid that your scenario that using the Excel file as a dynamic value for the Excel Online actions could not be achieved currently.
There is a similar request on Allow for dynamic file value for Excel online (Business), please vote the idea at here:
PGs will take hot ideas into consideration first in the future releases.
Thanks for your understanding.
Keep up to date with current events and community announcements in the Power Automate community.
A great place where you can stay up to date with community calls and interact with the speakers.
Check out the latest Community Blog from the community!