cancel
Showing results for
Did you mean:
Regular Visitor

## Setting up a recurrence flow that happens once each week that will delete all rows in a spreadsheet that are 7 days old.

I am needing to reset a spreadsheet (delete every row that is from the previous week) every week to only have submissions from a MS Form for the current week. I have it set to trigger with the recurrence trigger, but I cannot figure out how to make a condition based on the Completion Time column in my spreadsheet. I know this is possible with the expression functions, but I'm very new to the expressions so any help would be greatly appreciated. My screenshots below are what I have done so far.

1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User III

Ok, here's the flow to do what you have requested.

1) Use a recurrence trigger set to run once a week.  Mine will run every Saturday.  List the Rows currently in the table.

2) Here's the first tricky part.  Excel will provide the dates as a decimal number of days after 1/1/1900.  So you need to create a number that represents 7 days ago. and filter the List of Rows based on that number.  To calculate the number use this formula. Ticks is the number of milliseconds after 1/1/1000. So divide that by 864,000,000,000 to get the number of days since then and subtract 693,593 days, which is the number of days between 1/1/1000 and 1/1/1900.  This is all to get the number of days represented by 7 days ago.

``Add(Div(Ticks(AddDays(UTCnow(),-7)),864000000000),-693593)``

Now use that to Filter the list of Rows using that date and parse the result so you have access to each filtered row.

3) Finally use a Loop through the filtered results and delete each row using a Key,  In my example I'm assuming ID is unique.  This works because you are looping through the filtered list, not through the table rows themselves.  So when you delete a Row the row is removed from the table, but not the Filtered list.

I tested it and it does work.

-------------------------------------------------------------------------
If you like my response, please give it a Thumbs Up.
3 REPLIES 3
Dual Super User III

Assuming you have a value in the Excel sheet for each row that represents the timestamp this is relatively straightforward.  The biggest issue is that you can't delete rows in Excel from inside an Apply to Each loop.  The problem is that if you delete the row that way you'll short-circuit the logic controlling the loop.  The standard workaround is to use a do until loop to loop through the records and delete the ones that need to be deleted.

-------------------------------------------------------------------------
If you like my response, please give it a Thumbs Up.
Regular Visitor

Can you help with the expressions needed for the condition to accurately identify the correct rows to delete? The Excel sheet does have a value for each row that represents the timestamp.

Dual Super User III

Ok, here's the flow to do what you have requested.

1) Use a recurrence trigger set to run once a week.  Mine will run every Saturday.  List the Rows currently in the table.

2) Here's the first tricky part.  Excel will provide the dates as a decimal number of days after 1/1/1900.  So you need to create a number that represents 7 days ago. and filter the List of Rows based on that number.  To calculate the number use this formula. Ticks is the number of milliseconds after 1/1/1000. So divide that by 864,000,000,000 to get the number of days since then and subtract 693,593 days, which is the number of days between 1/1/1000 and 1/1/1900.  This is all to get the number of days represented by 7 days ago.

``Add(Div(Ticks(AddDays(UTCnow(),-7)),864000000000),-693593)``

Now use that to Filter the list of Rows using that date and parse the result so you have access to each filtered row.

3) Finally use a Loop through the filtered results and delete each row using a Key,  In my example I'm assuming ID is unique.  This works because you are looping through the filtered list, not through the table rows themselves.  So when you delete a Row the row is removed from the table, but not the Filtered list.

I tested it and it does work.

-------------------------------------------------------------------------
If you like my response, please give it a Thumbs Up.

Announcements

#### Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors
Users online (1,277)