Clone SharePoint list to Excel table and keep updated?

[RodM]

I'm attempting to create an automated flow where every time a SP list item is created or modified it updates an Excel table to match. Meaning when a new item is added, it also adds a row to the Excel table. If the SP list item is modified, the Excel table row is also modified. And if the SP list item is deleted, the Excel row is also deleted.

I've been able to successfully create the part of adding a row to a spreadsheet table, but cannot get the update/delete a row part to work. Can anyone assist? In my original attempt I was attempting to use 2 flows to accomplish this task. Should this be one flow to complete both actions?

[MarconettiMarco]

Hello @RodM ,

it's possible to perform the actions in 1 flow.

When an item is changed, you should use the excel action "List rows present in a table" and use the Filter Query field to search for the key. Then you'll be able to update the found excel row.

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

BR,

Marco

[RodM]

Thanks, I was able to do some more research on YouTube and found almost the full solution (did not cover deletion):
Add & Update Excel Table Row from SharePoint List | Power Automate Tutorial - YouTube

This is what I have so far which is working well to add a row or update a row. Is anyone able to provide more info on how I can tweak this to delete a table row when the corresponding SP list item is deleted?

[RodM]

Actually, upon some more testing, when I attempt to update SP list items, it adds another row. I guess I'm back to square one.

For additional context, I'm starting with an Excel sheet which has 6000 rows and counting. After doing more research I'm finding something about a possible pagination threshold.

[MarconettiMarco]

Hello @RodM ,

you should trigger another flow.

Please refer to the following article:

https://sharepains.com/2018/05/02/when-a-file-an-item-deleted-triggers/amp/

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

BR,

Marco

[RodM]

So this would require a completely separate flow? Thanks. I'll try that. 

Any ideas why the current flow is adding rows rather than updating the existing rows that have the matching key column? Please let me know where I'm going wrong. Thanks.

[RodM]

Was there anyone else able to help with this solution? The original question is still unresolved.

[MarconettiMarco]

Hello @RodM ,

please share the Update a row action in edit mode.

BR,

Marco

[RodM]

@MarconettiMarco , thanks for helping. Here is my update a row:

Since this is basically a clone of my add a row action, I think its more probable the issue is with an earlier step. Please see those below:

Let me know if you've found where I went wrong here.

[RodM · Accepted solution]

I was able to find a solution with some more research which now works correctly to both add and update existing Excel table rows. I removed the Filter array step and filtered the query directly within the List rows present in a table step.

The function here entered in the Condition step also changed:

empty(outputs('List_rows_present_in_a_table')?['body/value'])

Lastly, I nested the Update a row action within the Apply to each control.

[MarconettiMarco]

@RodM ,

glad to see that the issue is solved!!!