Unanswered

Clone SharePoint list to Excel table and keep updated?

(0) Share

Report

Posted on by RodM

193

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?

Categories:

Building flows

I have the same question (0)

All responses (10)

Answers (1)

MarconettiMarco 3,812 Super User 2024 Season 1 on at

Like (1)

Report

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

Was this reply helpful? Yes No
RodM 193 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
RodM 193 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
MarconettiMarco 3,812 Super User 2024 Season 1 on at

Like (1)

Report

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

Was this reply helpful? Yes No
RodM 193 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
RodM 193 on at

Like (0)

Report

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

Was this reply helpful? Yes No
MarconettiMarco 3,812 Super User 2024 Season 1 on at

Like (0)

Report

Hello @RodM ,
please share the Update a row action in edit mode.

BR,
Marco

Was this reply helpful? Yes No
RodM 193 on at

Like (0)

Report

@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.

Was this reply helpful? Yes No
Verified answer

RodM 193 on at

Like (1)

Report
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.

Was this reply helpful? Yes No
MarconettiMarco 3,812 Super User 2024 Season 1 on at

Like (0)

Report

@RodM ,
glad to see that the issue is solved!!!

Was this reply helpful? Yes No