Clearing Old Responses in Bulk from both the Form and the underlying Excel Workbook

[stamperadam]

I have a Form I would like to delete old responses from in bulk. Ideally I'd like to set a date range, say everything older than 6 months ago, and delete ALL those responses.

Here's a little background on why I'd like to do this. The form in question is stored in a SharePoint group and accordingly the underlying Excel workbook that the responses feed exists in the Documents for that same group. The responses table in this workbook is connected to a Microsoft Power Automate Flow and the List Rows action in that Flow keeps hitting Action Limits (resulting in throttling). I'm concerned that the large number of rows/responses in the workbook's table may be the cause and that is why I would like to delete older responses.

Accordingly, I would want to delete these responses from both the form itself and the underlying Excel book. I know you can delete individual responses, but that doesn't delete the response in the Excel workbook and deleting individual responses is WAY to time consuming with the number of responses I am looking to delete (over 1,000). I could just delete the older rows/responses from the underlying Excel table, but I've had cases where that caused a sync issue between the Form and the Excel Workbook.

Any ideas or solutions to this would be very helpful. Thank you.

[#2]

@stamperadam you might have already done this but make sure in your List rows present in a table action that you have gone into the action settings, turned the pagination toggle on and set the threshold to 2000. This will overcome the default 256 row limit for the action.

Secondly, at my company we always save Forms responses to a list in SharePoint via a flow in Power Automate. This acts as a backup, is useful for audit reasons and means that if there is any problem with the form  -  Forms can be very flaky - we still have all the responses.

However, with Power Automate there is also a way to delete all the form responses and the entries in the underlying spreasheet. The flow for this is shown below. I've trigered it manually but you equally have it run on a recurrence schedule, e.g. every month.

Rob
Los Gallardos
Microsoft Power Automate Community Super User

[#3]

@RobElliott 

Thank you Rob!

I'll look into Pagination, I forget about that setting. Thanks for the suggestion.

I've thought about creating a Flow for saving responses to a MS List, but I already backup the Form's associated excel document at the end of each month and that's more than enough for this data set. I like keeping things in Excel when possible, just a personal preference really. I've always been able to get a lot out of Excel and I know if the data is in an Excel workbook it's pretty future proof (more likely Microsoft would kill Lists than Excel). I'm also a big keep it simple kind of guy and find Flows to be a little unreliable at times, so whenever I can avoid another Flow I prefer that. Again, this is all more personal preference really.

I think I will definitely try you Flow suggestion for deleting Form responses though! I didn't know an HTTPRequest could be used to delete both the response and the associated Excel row. This is very helpful!

Thank you again Rob!