Clear a sheet using Bridge

Brian_Richardson

Hi all, I have developed a Clear Sheet solution using Bridge. Using Bridge allows you to incorporate a sheet clear into a Bridge workflow, rather than trying to align timing between workflows and other sheet clearing methods like automations and Data Shuttle.

Once setup, you simply call this Clear Sheet workflow using the Run Child Workflow step, in any number of your workflows.

Here's the setup, it does involve some custom Javascript, but I have the code below for copy/paste. You shouldn't have to customize the code, just paste it.

Overview

This workflow is composed of three workflows. We will set them up in reverse order.

1. Workflow that calls the Clear Sheet workflow
2. Clear Sheet workflow
3. Delete Rows child workflow

How it works

Once setup, you simply add a Run Child Workflow step to your workflows that calls the Clear Sheet workflow, tells it what sheet to clear, and tells it how many times to run ( number of runs = expected number of rows to clear / 5000).

The Clear Sheet workflow gets the sheet, extracts the row IDs for the entire sheet, gets the number of rows (so it can check to see if it's done), then chunks the rows into groups of 250. If there's actually rows to delete, it then sends each group of 250 row IDs to the Delete Rows workflow. Otherwise it stops. Note that it has to chunk into 250 because the delete items batch call can only handle 250-300 rows at a time.

The Delete Rows workflow then runs once for each group of 250 rows that the Clear Sheet workflow sends to it, and deletes them as a batch.

Step 1: Setup the Delete Rows Child Workflow

1. Create a new workflow in Bridge called Delete Rows Child
2. With the Delete Rows Child workflow open, click Integrations...Smartsheet and drag the Delete Row step into your workflow.
3. In the Delete Row step settings enter {{runtime.entities.sheetID}} for Sheet, and {{runtime.data}} for Row ID
4. Save and return to the home page

Step 2: Setup the Javascript

1. In Bridge home screen, select the Integrations page
2. Click Javascript (at the bottom)
3. In the Extension-Javascript popup window, click Scripts
4. Click Add to Scripts
5. In Script Name, enter "Chunk Rows for Deletion"
6. In Script Body, copy and paste the following code and Save

const chunkArray = (array) => {

 const arrays = [];

 let i,

  j,

  temparray,

  chunk = 250; // Sets the value to number of items per array.

 for (i = 0, j = array.length; i < j; i += chunk) {

  temparray = array.slice(i, i + chunk);

  arrays.push(temparray);

 }

 return arrays;

}

return chunkArray(rowNumbers)

Step 3: Create the Clear Sheet workflow

1. Create a new workflow in Bridge called Clear Sheet
2. Add Integrations...Smartsheet...Get Sheet. In the Sheet settings put {{runtime.entities.sheetID}}. In the Filters, Results Per Page setting enter 5000.
3. After Get Sheet step, add Utilities...Array Management...Extract Field From Array. In the Array setting put {{states.startstate.smartsheet.get_sheet.sheet.rows}} . In the Key setting put id
4. After Extract Field From Array step, add Utilities...Array Management...Size/Length. In the Data setting put {{states.startstate.arraymgmt.extract_from_array.results}}
5. After Size/Length step, add Utilities...JavaScript...Run Script. In the Script Name setting put Chunk Rows for Deletion. In the Script Parameters Key #1 put rowNumbers. In the Script Parameters Value #1 put {{states.startstate.arraymgmt.extract_from_array.results}}
6. After the Javascript step, add Utilities...Conditional Junctions...Equals. When asked for a Group Name, enter Is Sheet Empty. The junction will be added with two open State boxes, one for success and one for failure.
7. Drag a New State item onto the success box and name it Sheet Empty.
8. Drag Run Child Workflow onto the failure box. In the Name setting put Delete Rows. In the Child workflow name setting select the Delete Rows Child workflow that you created in Step 1. In the Number of Runs setting put {{states.startstate.javascript.run_script.result}}. In the Child entity values Key #1 put sheetID. In Child entity values Value #1 put {{runtime.entities.sheetID}}

Step 4: Insert Clear Sheet into your workflow

1. Now, find a workflow that you want to insert the Clear Sheet step intotep 4: Insert Clear Sheet into your workflowcoNld be existing, or you can create a new one. Note that you cannot directly schedule the Clear Sheet workflow, as it needs a sheetID "sent to it" from another workflow. For my example, I'll use a "Get Resource Management Users" workflow, that clears the user sheet first using this new Clear Sheet process.
2. Y.u need to do a little guesswork now. Each time the Clear Sheet workflow runs it will clear 5000 rows. So you need to divide your expected sheet size to clear by 5000 to get the number of runs that you'll need. Of course, you can be safe and always run it 4 times, as 20,000 is the maximum rows limit. But if your sheet is always smaller than the maximum, you may wish to save time and only run Clear Sheet once, or twice.
3. Open the workflow that you want to have clear a sheet.
4. Drag Run Child Workflow to the point that you wish to clear a sheet. In this example, I clear the sheet first. In the Name settings put Clear Sheet. In the Child Workflow Name setting select Clear Sheet workflow that you created in Step 3. In Number of Runs setting put the number of runs (1-4) that you want, based on the size of the sheet (size of sheet / 5000). In Child Entity Values Key #1 put sheetID. In Child Entity Values Key #2 put the sheet ID number that you want to clear out.
5. Note for troubleshooting: sometimes multiple runs of the Clear Sheet workflow will fail, especially if you have lots of other workflows running at the same time. You can reduce errors by calling the Clear Sheet 2-4 separate times instead of setting number of runs to 2, 3, or 4. For example, if you need to clear 20,000 rows and are having errors, try inserting 4 Child Workflow steps in sequential order, with 1 run each.

Conclusion

Whew! That's it. It seems like a lot to setup, but once setup this Clear Sheet process can be called from any workflow, for any sheet, of any size.

asarwary

@Brian_Richardson

Hi Brian,

I'm trying to implement your solution. I also need a way of clearing my archives sheets. I need help on step 4 if you could further explain please. Is the steps the same for some of them that you mentioned earlier? For the HTTP call I'm I suppose to put the sheets URL? I'm I doing the same steps for the Run Script part also? I just need more clarification for step 4 and what you configured for each one.

Thank you!

Danielle Wilson

@Brian_Richardson Thank you so much for sharing this solution!

prime_nathaniel

@Brian_Richardson haha switched to my Prime account so I haven't been getting notifications. I have something extremely similar here at Prime ;)

I have also reported to the engineering team that the delete API needs to be v2'd to post body not URL parameter due to server URI limitations.

We used to do 500 rows at a time but that started failing like crazy during Q1 this year. Had to move it back down to 250 like in your example. Apparently, this URL length restriction has been increased on the servers as it no longer fails. That said, if they do plan to increase sheet limits to anything over 20k they NEED to increase API's to handle commensurate sizes of data.

Ex if sheets go to 100,000 rows, that's a 5x and 2500 row ids in URL is pretty bad parsing for the server vs parsing a body package.

Brian_Richardson

"I have also reported to the engineering team that the delete API needs to be v2'd to post body not URL parameter due to server URI limitations."

YESSSSSSSS

Esp as you said with size increases. I don't know why this one endpoint works differently but it drives me crazy.

However, there's an alternative that I have been meaning to share. I got this from the Technical Sales Engineer group at the last Engage. Those guys are amazing. Anyway:

You can avoid having to multi-call a Delete Rows and run Javascript with a "simple trick". If you shift all rows of the sheet under a Parent, then delete the Parent row, it will delete all the children in one step. I'm doing this successfully in one of my workflows. I used two methods:

Method 1: This is the cleanest and easiest way but you end up with data all "childrened" and sometimes that's not tenable. Method: Delete parent and all children with a single delete step, add parent back in, Add Rows with the Parent rows defined as the parent location. Repeat.

Method 2: Insert a parent row and update all existing rows to be children of the parent. A little more involved as you have to run an update row for every row first, using some Javascript to create arrays of 500 row updates. Then delete the parent. Then add the new data. Repeat.