Before a I spin my wheels in a mud rut, can someone validate my conclusions, please?
1. Once a day an Excel workbook is uploaded to a SharePoint Library
2. I need to trigger a flow that 'Get items' from that workbook
3. Flow needs to copy the data from the workbook to a corresponding SharePoint List
My Flow Solution Starts with:
1. Create a 'Automated Cloud Flow'
2. Trigger = 'When a file is created in a folder'
3. Select Site and Library
4. Which action comes next?: Get Items; Get file metadata; Get file properties
This is more like Power Automate topic rather Power apps but here it is :
After You set the trigger and the file exist in Library You should use "List rows present in a table" than from SharePoint flow use "Create item" and Map Excel to SharePoint List.
The only challenge you will face if one of the Excel Table columns will store Dates.
Power Automate don't like them for some reason and You will need to create variable what will extract each invidual date column and than use expression to convert them to string (initialize variable" for each column with date you will need to do that call them varDate1 and if you have more change it to varDate2...
Than add an “Apply to each” action using the “value” output from the Excel “List rows present in a table” action.
And Add “Set variable” actions for each of your date columns and set the value to the Excel date columns.
Now in "Create item" You will be able easy map Your Data Columns by using below Expression remember to change varDate1 to variable You want to map
Also keep in mind Dates can't have Time on them that will brake things out as well if data you passing have dates modifie above formula to
Hope it helps
I go it to this point. It was working fine when I only had one item in the SP List.
I deleted that item. I added three new items. Now that flow is looking for 'ID' 1, but it has been deleted.
The first 'ID' now available is 5. How to I update the flow to correct this issue?
SharePoint Library: Excel Spreadsheet
The Issue You have is that you using Update Item action and that will look for match ID You need to use Create item Action.
If you looking for flow what will recognise if an item exist than update then you using update item but than you need to add condition example if ID exist and match ID in excel Update the item if no Create new item 🙂
An Excel workbook with the same name is uploaded to the SP Library once per week.
Don't I need a unique ID in the Excel workbook (in this case a column called 'ID'), and the Update function in Automate in order to update data already in the SharePoint List?
This seems to be working.
Do you see any fatal flaws? Do I add a action to the 'No' side where the condition is false that creates a new item in the SP List?
You should create an action if the record does not exist in the List and then create it. I think I don't know what Your main objective is, but if you don't want this flow to do anything when the condition is not met, just leave it as it is.
I just had a thought; There will be no direct edit of the workbook.
The workbook will be updated by being overwritten with a new copy the workbook with the same name once per month.
Will this action trigger this 'When an item or file is modified' flow?
Or, would the above be considered 'create' and I need to use a different trigger?
When an item or file is modified will be efficient enough to do what you looking for.