How to delete rows automatically

Dale Murphy

Community;

I manage a process that injects a fairly high volume of rows into a sheet at least weekly. These rows are meant to be viewed by a sales team, annotated, acted on, and then archived. After about a month they lose all value (key data points are moved into our ERP or POS backend, depending on outcomes).

But the sheets quickly overfill, despite moving as many as possible from the active sheet to a secondary and tertiary set of sheets.

I need to automate deletion of the oldest rows. Any ideas?

dm

Jason P

Hi @Dale Murphy

To my knowledge no way to automate deletion of a row. A more longwinded way could be to use the new Clear Cell Value in Automations.

Set up a check box column - this represents all process in that row "annotated, acted on" etc have been actioned.

Set up a date column - example "Completion Date" then set up an automation to record a date in the cell when the check box is ticked.

These are your triggers;

Set up a new Clear Cell value automation, Trigger will be "When a date is reached", set it to run once every (X) days or weeks, select the Date field option then from the drop down select "Completion Date" column, pick the time to run the automation - default is 2PM. Condition (1) will be when check box is ticked

For the Clear Cell Value actions you'll now need to set a Clear Cell value for all of your cells in each column - hope that makes sense - you may end up with several clear cell value actions but the end result should be a cleared row actioned every (X) days/weeks.

I've not tested it so interested if you do give it a crack and it pulls off. 👍️

Andrée Starå

Hi @Dale Murphy & @Jason P

I hope you're well and safe!

Unfortunately, It will not work with the clear cell action because the row(s) would still be active and count against the limit.

Here's a possible workaround or workarounds

• Is the Premium app, Data Shuttle an option?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Dale Murphy

@Jason P yours is an interesting idea, albeit a bit brute-force! (The sheet is 29 columns wide. That "Clear" workflow only allows you to clear 20 columns in a given workflow)

@Andrée Starå is correct though, clearing the cells still leaves a row in the sheet which counts against the 500,000 cell limit. (I tested it with my dataset.)

@Andrée Starå, right now Shuttle is not available to me, but I think this situation makes a case for it (I have several similar workflows that could be fully automated.)

Regards,

dm

Grant Lagimodiere

To automatically delete rows in an "archive/trash bin" sheet I do this. I use a standard "move" automation template to move the rows into a newly created "trash bin" sheet. Using Data Shuttle I do this:

1. Create an Upload workflow.
2. The source file will be a blank .xls or .csv with the identical structure as your regular data import file - but blanked out, no data.
3. Configure the workflow for complete replace of the target sheet (the "delete bin" sheet).
4. Schedule the new delete bin workflow frequency greater than the frequency of your data import. For example, if you upload your data every hour, then empty the trash bin every 15 or 30 minutes. I do this to reduce the chance that I could "overflow" the trash bin and halt the automation.

Dale Murphy

@Grant Lagimodiere If I had the use of Data Shuttle, your approach would be nice and clean.

dm

ker9

@Dale Murphy

Below is a manual workaround I use to get a notification when a sheet is getting full.

In the Sheet Summary of the “trash” sheet, I have 3 fields, Total Columns, Total Rows and Total Cells (which is Total Columns*Total Rows). On the first row of the sheet, I add an INFO row with specific data that I can reference in automation and the Total Cells (=[Total Cells]#) in another cell. (I color that row orange.)

This allows me to setup automation that runs Every Day at a specific time and sends me a notice when the Total Cells is >350000. I manually delete all the rows because we don’t need them, but I don’t have to think about it until I get notified. 

On my notification row, I’m using the cells that are in the sheet – I didn’t create any extra columns:

I know its not automated, but perhaps it will help you.

It can take a while to delete so I usually open it in a new window and let it do its thing while I move on.

Some folks want to keep everything - in that case I make a copy of my Move sheet for posterity and then delete the data.

Dale Murphy

@ker9 That is cool. I have workflows that move the contents of my primary archive sheet to a secondary (and sometimes tertiary) sheet. Then I watch for a SS error message from those sheets, which alerts me to clean up the data channel. A more deliberate message as you have constructed would be better, and could be directed to anyone.

Thanks,

dm