Updating CSV Spreadsheet when items are added, modified or deleted in a Sharepoint list

[RustyRapier]

Hi all,

I use a Sharepoint to store geological data in lists. The company also uses Studio RM, which is a 3D modelling software package. In order to digest the data, Studio RM requires the data in CSV format.

We have a drilling data that comes into the Sharepoint lists daily. So I wanted to know what is the best way to automate this in powerautomate? my thinking is due to the data coming into the lists and possibility for the data to be modified once in the lists, i did not want to create a recurrence flow, but one triggered by when an item is created, modified or deleted in Sharepoint i wanted the spreadsheet or table to replicate the list but not the other way around.

What i have tried is the following:

So what currently happens is table updates with new information and modifications on existing items in the table but how do i get it to delete an item that has been deleted from the Sharepoint list?

[ekarim2020]

For the deleted case, you will need a new flow which triggers on when a SharePoint item is deleted, something like:

Ellis

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

[RustyRapier]

Thank you soo much Ellis!

One more question for you @ekarim2020 would the filter array be the same as per the screenshots above?

[ekarim2020]

You most likely don't need to use the Filter Array action.  I'll use the following SharePoint List and Excel file as an example:

You can instead use the Filter Query in the "List rows present in a table" for both flows. In the query below I am selecting (searching) for rows in Excel that have the same Title as the SharePoint item that was just created or modified:

Note that the SharePoint Title is enclosed in 'single quotes'. This flow would look similar to this:

The flow for deleting a row is going to be a little different. This is because the information returned to the flow by a SharePoint delete action is different from the information returned by a Create or Update action. For example, when I deleted the item with Title ID201 SharePoint returns the following information in the response body:

The response body tells us that the item that was deleted had a SharePoint ID=4, it had a "Name" and "FileNameWithExtenstion" properties = ID201 along with who and when it was deleted. We can use this information to find the same row in Excel that was just deleted in SharePoint. In my case above, the SharePoint Name (and FileNameWithExtension) seems to store the item Title and value (ID201) - the key and value pair - of the item that was just deleted.

In the When an item is deleted trigger, I can add a Filter Query to select the row that was just deleted. Note that the SharePoint Name property is enclosed in 'single quotes' below:

The List rows present in a table isn't actually needed to delete a row. To delete a row I just need to specify the Key column (named Title in the Excel file) and the value of the row item to delete (which we know is stored in the SharePoint property Name, in the response body):

SharePoint list before deleting item with Title = ID201:

The Excel file before deleting row with Title = ID201

After item with Title = ID201 is deleted from SharePoint.  The flow then deletes the row with Title = ID20 from the Excel file:

Your Excel and SharePoint list will be different to my demo, but the concepts will be the same when it comes to deleting a row.

Hope this helps.

Ellis

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