Clear a Sheet on a Schedule

Brian_Richardson

Hello lovely community, I need some help figuring out the best mechanism for clearing a sheet on a schedule. It really should be easier than this!

I have tried the following methods, and outlined the limitations and issues below. I would LOVE to get some suggestions for other methods!

Problem: I need to fully clear (delete ~8000 rows of) a sheet full of resource data, once a month on a specific date. I need to delete the rows to make room for the next 8000 to be loaded, not just clear cells. Additionally I need to clear another sheet of ~8000 rows where they meet certain criteria.

Solutions tried:

1) Move rows automation + Data Shuttle: on a schedule, the Move Rows automation shifts all rows, or filtered rows, to another "clearinghouse" sheet. Data Shuttle clears the "clearinghouse" sheet daily by importing a blank Google Sheet and Deleting all non-matching rows. This works pretty well and is quick, and is my current solution. Drawbacks: automations fail if the clearinghouse sheet hasn't been cleared yet. Automations also cannot be manually triggered as Run Now...not sure if it's a size thing, but they fail. Using two sheets, automations, and Data Shuttle is a relatively complicated setup that fails at various points if everything isn't perfect.

2) Just Data Shuttle: Data Shuttle works well to clear an entire sheet on a schedule, by importing a blank Google Sheet and selecting Delete rows that don't match. Drawbacks: didn't work for me, because the Data Shuttle scheduling is at most weekly, and I needed to run this only once a month....that isn't an option.

3) Data Mesh: cannot delete rows, and I need to delete them, not just clear them

4) Bridge: I tried to set up Bridge to do this but wasn't able to get it to work well. The main steps were: 1. Read 300 rows of the sheet 2. Extract the row ID into an array for the 300 rows 3. Use the Delete Rows Smartsheet Integration step. For row ID, provided the 300 id array. And this works! (around 300 is the "limit" for number of id's that the URL will accept). Drawbacks: I couldn't figure out a good method to "loop back" and do the next 300 rows. When I tried to connect the bottom of the workflow back to the top, using a conditional junction to identify when the workflow should stop, it simply wouldn't work...it never looped back. I was able to use Child Workflow to call itself again, but this resulted in a pile of nested loops and started taking a long time to run, as 8000 / 300 meant the "child" had to be called 26+ times before the whole thing would finish.

David Tutwiler

The cleanest way I can think of would be to do this through the API, although that would require a bit of API knowledge from someone on your team. I wouldn't think it would be terribly difficult though.

Essentially it would be a 3 step process.

1 - Start a loop that checks the current day until the correct day is met (this assumes that this happens on the same day of the month every month. If it is something different then it would need to be coded to look for that trigger)

2 - Have the API go to your sheet and create a list object to put all of the rows on the sheet into a list

3 - Call the DeleteRows function in the Smartsheet API and provide it with the list of rows you just read in from the current sheet

Then loop it to look for the next day.

Brian_Richardson

@David Tutwiler hey Overachiever team mate :-)

That's what I was doing with Bridge...the issue wasn't the scheduling, it was that the mass DeleteRow can only do about 300 rows at a time, so I need to figure out how to make Bridge loop until all 8000 rows are deleted, 300 at a time.

David Tutwiler

Bummer. I wonder if that is a limitation of the bridge connector or the delete rows function itself. I'll try to scrounge through the API documentation this week and run some tests in a test sheet.

David Tutwiler

Unfortunately, I've hit a dead end. There is no reference of a 500 row limit in the API documentation, and the error code I get back is not in their error code log. Even so, I get hit with an error every time I try to delete even 501 rows at a time. Must be some built-in restriction in their backend. I don't know what to do except potentially schedule a Pro Desk session for Bridge and see if they can build you some kind of custom JavaScript that would do it.

Sorry I don't have a better answer than that

Brian_Richardson

Thanks for checking on it.... it appears to be a limitation with the way the DeleteRows method is implemented. Instead of being able to provide a body with the row numbers in an array, instead they have you provide a list of row numbers in the HTTP URL itself....and above roughly 300 numbers the URL gets too long for the backend to parse and it throws an error.

As far as looping through them, though, Jscript was my next thought. Basically read the sheet, use Jscript to chunk up the row numbers into groups of 300ish, then push all those chunked arrays to a Call Child Workflow step which would run DeleteRow for each group. I think that would work pretty well so I'm going to try it out and I'll post if it works.

I also noticed the 500 record limit isn't documented as you said. Smartsheet engineering told me that there's a 500 record limit for any bulk API call like AddRow where you pass a body to the endpoint. That's why I chunk arrays in Javascript into groups of 500 to add to sheets, as outlined in my post here:

https://community.smartsheet.com/discussion/97621/use-javascript-in-bridge-to-efficiently-import-data-from-an-api