@anugrahandi & Power Automate Community,
I wanted to provide an update and lessons experienced on this type of flow and an absolute answer for a variety of questions that surfaced in this thread.
Scenario:
I download an excel file weekly that needs to be processed and pushed into a "leaderboard" on our sharepoint site. It involves reading rows of a spreadsheet and writing them to a SharePoint list as a refresh (i.e., deleting the entire list and then updating it with new information)
Problem:
The Excel Create table and/or most functions do not permit a dynamic link to a file. Meaning, I cannot use the ID from "When a file is created in SharePoint" and tell Excel to create or update a table. It MUST retain its unique ID. That includes "overwriting" the existing file.
Solution:
Create a static file and update the file with the contents or body of the new file. In other words, DO NOT REPLACE the file, simply update the contents INSIDE the file. Now, this poses a few idiosyncrasies which I will describe below to get it setup properly. BOTH files must be .xlsx.
Setup:
- Create a permanent storage file. Also, create a table that will cover the MAXIMUM columns and rows you'd ever need. For me, it was 300 as I have 193 employees today. Go ahead and create the table and call it Table1. Do this in native Excel.
- Create the flow
- The new file w/ the contents; choose Connector "When a new file is created.." - drop into a separate folder so you can "watch" for new files. This will start the flow
- You can ignore the get items--apply to each delete --> this is simply deleting the old items in the target SharePoint list
- Select "Update File" - choose the permanent .xlsx file THEN choose the File Content from the new file via "When a new file is created" connector
- Choose Create Table pointed at the permanent file. Select the range and name it Table1 -- the same as the target/permanent file
- List Rows Present in Table Connector - this is to get the total number of rows. Here is the tricky part--it wants a table name. If you create one in advance, PA will allow you to save the flow, otherwise it will throw an error complaining it does not exist. See (1) above.
- Use "Apply to Each" to do what you need--you'll see attached.
- Delay & Delete
- Create Table "locks" the file for a period of time. Delay for 10 minutes allows PA to release the file and permit its deletion.
