cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RustyRapier
New Member

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

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:

RustyRapier_0-1633215303519.png

RustyRapier_1-1633215356629.png

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?

 

3 REPLIES 3
ekarim2020
Super User
Super User

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

ekarim2020_0-1633251913948.png


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
New Member

Thank you soo much Ellis!

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

ekarim2020
Super User
Super User

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:

2021-10-03_22-52-55.jpg

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:

ekarim2020_0-1633295733501.png

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

2021-10-03_23-25-42.jpg

 

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:

ekarim2020_7-1633296595518.png

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:

ekarim2020_1-1633295754895.png

 

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):

ekarim2020_2-1633300784740.png

 

SharePoint list before deleting item with Title = ID201:

ekarim2020_8-1633298038536.png

 

The Excel file before deleting row with Title = ID201

ekarim2020_2-1633296072738.png

 

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

2021-10-03_22-26-23.jpg

 

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.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (1,482)