Add row to Excel when a SharePoint item is restored?

[JManningJPS]

Hi all,

I need help with creating a flow that adds a row to an Excel spreadsheet when deleted items in a SharePoint list are restored from the recycle bin. 

My organization uses Microsoft Forms for users to manually enter monthly scorecard data. I have created flows that move the form responses to a SharePoint list, but I also need to keep a record outside SharePoint of all changes made to the list. I created flows that add a row to an Excel Online spreadsheet when list items are created, modified, or deleted. However, there is currently no trigger available for "When a deleted item is restored". Restoring does not trigger the created/modified flow for some reason.

How can I solve this problem? I thought of two ways, but I can't figure out how to make them work.

1. Export the version history to a file every hour/day/etc.
   • I have seen posts on how to do this with PowerShell scripts, but I don't have access to SharePoint cmdlets. Using an API call with the "Send an HTTP request" connector might work. However, it only takes a single item ID, and I need the history of all items.
   • Is it possible to get all of the list IDs and loop through them, adding a row for each one? If so, how would I do that?
2. Periodically check the list to see if it contains any IDs that were previously deleted, then add a row for those items in the spreadsheet.
   • I have created a column in the spreadsheet named "IsDeleted". When an item is deleted, a flow inserts a new row with "IsDeleted" = 1.
   • Is it possible to get the unique item IDs from the spreadsheet where "IsDeleted" = 1 and check that against the current list of items? If so, how would I do that?

Can anyone suggest a solution to this problem? I would greatly appreciate it! Thank you very much!

[ScottShearer · Accepted solution]

@JManningJPS 

You may be able to get where you want to go, but it is likely to be a bit complicated.  I would like to suggest an alternate approach.  You might consider the following:

1. Remove your users ability to delete items by using a custom permission level (break inheritance on the list.....)
2. Add a yes/no column to the list and call the column "Delete"
3. Exclude items where Delete is checked from all views available to users

Another avenue would be to have a Flow delete the items once Delete is checked.  Items could only be restored by you (since your connection is used to delete) or an SCA.

While this does not directly answer your question, it is a workable solution that can be implemented in just a few minutes.

[JManningJPS]

@ScottShearer ,

Thank you very much for your suggestions. I think the first one might work for our purposes. I will take it to my team next week and see what they think. I greatly appreciate your help.

[mmangiante]

You may be able to get where you want to go, but it is likely to be a bit complicated.  I would like to suggest an alternate approach.  You might consider the following:

1. Remove your users ability to delete items by using a custom permission level (break inheritance on the list.....)
2. Add a yes/no column to the list and call the column "Delete"
3. Exclude items where Delete is checked from all views available to users

Another avenue would be to have a Flow delete the items once Delete is checked.  Items could only be restored by you (since your connection is used to delete) or an SCA.

While this does not directly answer your question, it is a workable solution that can be implemented in just a few minutes.

---

Hello,

I know that this thread is old but I tried the approach to enable custom permission level to hide the delete button and then created a link in every list item to a flow that would cancel that item, but iwth that permisison the flow doesn't start; if I disable the custom permission (so there is again the delete button), the flow starts and works.

--

Regards

[ScottShearer]

@mmangiante 

I believe that I suggested adding a checkbox to the list - when the box is checked the Flow will fire because it runs in the context of the Flow author and not the user.  If you added a button to call the Flow directly, then the behavior that you have described is expected/as designed.

[mmangiante]

Hello @ScottShearer 

thanks for your clarification: do you think is it possible to set the permission to hide the delete button and then however try to start with a button a flow that takes the list item and then delete it with the delete action of sharepoint connector?

To do this, I simply formatted a column to have a button to that start a flow that takes the selected item trigger and then takes the various columns info to make some elaboration and then delete it; I have to hide the delete button on the ribbon otherwise some people can use it and I can't do my elaboration.

Marco

[ScottShearer]

@mmangiante 

Simply hiding the delete button isn't a great idea.  There are a number of ways a user can work around hiding the button.  A custom permission level is the only way that you can be sure that users can't delete an item.