cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chrisggates
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. 

 

chrisggates_0-1609942803199.pngchrisggates_1-1609942856530.png

chrisggates_2-1609943025128.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Pstork1
Dual Super User III
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.

image.png

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.

image.png

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.

image.png

I tested it and it does work.



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

View solution in original post

3 REPLIES 3
Pstork1
Dual Super User III
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 I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
chrisggates
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. 

chrisggates_0-1609948109433.png

 

 

Pstork1
Dual Super User III
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.

image.png

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.

image.png

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.

image.png

I tested it and it does work.



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

View solution in original post

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (70,932)