Bridge | How to Find Rows With Status & Delete Them

Sarah123

I'm trying to use Smartsheet Bridge to find all rows within a Jira Status column that are closed and have it delete those tasks (rows). I've been looking at multiple articles but am having a hard time referencing what rows match this cell and then delete.

Brian_Richardson

I'm assuming, from your question, what you're asking is more about a Smartsheet sheet with a column called Jira Status, that you're populating somehow. And you want to seek those rows and delete them on a regular or triggered basis? Bridge can definitely do that.

Couple things to check though:

1 - If you're importing the Jira info using the Jira connector, then deleting rows in Smartsheet isn't going to do anything, it's just going to reappear on the next sync.

2 - If you're using the connector, deleting rows in Smartsheet also isn't going to delete the issue from Jira.

In order to use Bridge to delete rows in Smartsheet based on criteria, I'd set it up like below. Assuming you've already created the empty workflow in Smartsheet and have connected Bridge to Smartsheet in the integrations.

I created a quick sample sheet to show the results:

In Bridge:

1) Add a Search Sheet step (Integrations...Smartsheet...Search Sheet)

2) Use the row filter to search for Jira Status = Closed (or whatever the right status is). Also check the box under Advanced Options that says "Include Sheet Data"

3) Save! Then Run the workflow to get the results. (run by clicking the log button-the two arrows at right, then press Play). You should get something like this:

In this log result, you'll see a couple of main sections for the response. The first section is "data" which has the value from the various cells from each row that was "closed" status. The second section is "sheet" which has much more information about the sheet, including all the column Ids and all the rows again, but this time with row id information (which we need). This second "sheet" section only appears when you have checked the "Include Sheet Data" checkbox in the Search Sheet step.

4) In the log above, hover on the line that says "rows", under the "sheet" object. Click the three-dot menu that appears and delete "Copy Data Reference".

5) Now add another step to your workflow, this time it's under Utilities...Array Management...Extract Field from Array. In the options, in the "Array" field use paste (CTRL-V) to paste in the data reference you just copied. It should be {{states.startstate.smartsheet.search_sheet.sheet.rows}} . In the "Key" field put id (no quotes).

What you're doing here is picking up the row Id from each row where the status = closed and creating an array of those ids (basically a list of the ids) to use in the next step.

6) Save! Now run the workflow again to get the results. Refresh the log and open the result and you should see something like this:

7) Hover next to the "results" row and copy the Data Reference. (note...not the reference for the first number, the reference for the results row, however many results there may be). You should get: {{states.startstate.arraymgmt.extract_from_array.results}}

8) Now add the last step to the workflow, this one is Smartsheet...Delete Row. Put your sheet ID at the top and then paste the results data reference into the Row ID row:

9) Save! Then run the workflow again and you should see the closed items delete from your sheet. You may need to refresh the sheet to see the results. Cool !

10 ) Now you can decide how you want to trigger this. Easiest thing is to click the Trigger step and set a schedule, ie every 15 mins or so. Alternatively you can exit the workflow, click Integrations, and setup a Smartsheet trigger to run the workflow every time a row is added, or changed etc.

Good luck and let me know how it goes!

Brian_Richardson

I'm assuming, from your question, what you're asking is more about a Smartsheet sheet with a column called Jira Status, that you're populating somehow. And you want to seek those rows and delete them on a regular or triggered basis? Bridge can definitely do that.

Couple things to check though:

1 - If you're importing the Jira info using the Jira connector, then deleting rows in Smartsheet isn't going to do anything, it's just going to reappear on the next sync.

2 - If you're using the connector, deleting rows in Smartsheet also isn't going to delete the issue from Jira.

In order to use Bridge to delete rows in Smartsheet based on criteria, I'd set it up like below. Assuming you've already created the empty workflow in Smartsheet and have connected Bridge to Smartsheet in the integrations.

I created a quick sample sheet to show the results:

In Bridge:

1) Add a Search Sheet step (Integrations...Smartsheet...Search Sheet)

2) Use the row filter to search for Jira Status = Closed (or whatever the right status is). Also check the box under Advanced Options that says "Include Sheet Data"

3) Save! Then Run the workflow to get the results. (run by clicking the log button-the two arrows at right, then press Play). You should get something like this:

In this log result, you'll see a couple of main sections for the response. The first section is "data" which has the value from the various cells from each row that was "closed" status. The second section is "sheet" which has much more information about the sheet, including all the column Ids and all the rows again, but this time with row id information (which we need). This second "sheet" section only appears when you have checked the "Include Sheet Data" checkbox in the Search Sheet step.

4) In the log above, hover on the line that says "rows", under the "sheet" object. Click the three-dot menu that appears and delete "Copy Data Reference".

5) Now add another step to your workflow, this time it's under Utilities...Array Management...Extract Field from Array. In the options, in the "Array" field use paste (CTRL-V) to paste in the data reference you just copied. It should be {{states.startstate.smartsheet.search_sheet.sheet.rows}} . In the "Key" field put id (no quotes).

What you're doing here is picking up the row Id from each row where the status = closed and creating an array of those ids (basically a list of the ids) to use in the next step.

6) Save! Now run the workflow again to get the results. Refresh the log and open the result and you should see something like this:

7) Hover next to the "results" row and copy the Data Reference. (note...not the reference for the first number, the reference for the results row, however many results there may be). You should get: {{states.startstate.arraymgmt.extract_from_array.results}}

8) Now add the last step to the workflow, this one is Smartsheet...Delete Row. Put your sheet ID at the top and then paste the results data reference into the Row ID row:

9) Save! Then run the workflow again and you should see the closed items delete from your sheet. You may need to refresh the sheet to see the results. Cool !

10 ) Now you can decide how you want to trigger this. Easiest thing is to click the Trigger step and set a schedule, ie every 15 mins or so. Alternatively you can exit the workflow, click Integrations, and setup a Smartsheet trigger to run the workflow every time a row is added, or changed etc.

Good luck and let me know how it goes!

Sarah123

THANK YOU THANK YOU THANK YOU!! Your directions were EXACTLY what I needed to fully understand this! 😀

JodyRakow

@Brian_Richardson I'm a little late to this party but hoping you can clarify for me - can an array of row numbers also be used to populate the Row ID field in an Update Row step? If not, is there another way to update multiple rows when I have the array of their row numbers and want to update them all with the same new cell value?

Brian_Richardson

@JodyRakow you can do multiple row updates but you need to shape the body into an array of row objects, you cannot just pass a list of row numbers into an Update Row step.

See my post here for some examples:

https://community.smartsheet.com/discussion/131140/bridge-examples-including-javascript

JodyRakow

https://community.smartsheet.com/discussion/comment/477167#Comment_477167

Thanks, @Brian_Richardson! So it sounds like I cannot just follow step 7 above and then paste that into an "Update Row" step. Is there an alternate Data Reference from the workflow above that I could copy and paste instead to prompt an update on all of the rows that were found as part of the Search Sheet module?

Brian_Richardson

@JodyRakow You got me curious so I tried running Update Row and providing two row ids, comma separated, in the Row ID field. It failed with the error shown below, indicating that it reads the comma separated list as a single row ID.

I then tried pulling the 2 rowids as an array using Extract Field and giving Update Row the array of rowids, but that also failed with the same error.

So long story short, no there isn't a way to batch update multiple rows with the Update Row integration step in Bridge.

If you have just a small number of rows, you can skip the javascript and simply call a child workflow for each row you want to update and have the Update Row step in that child to make your updates for the row. If you set the Child Workflow call step to use the rows array as the "Number of runs" then it will call the child workflow once for each row individually.

If you have a lot of rows to update at once (like a hundred or more) then you'll want to use Javascript to streamline your workflow. It's not terribly hard once you get over the initial hurdle of getting the Javascript together. Feel free to steal from my examples in the posting above. The added bonus of my examples is the code to package updates in blocks of 500 rows, which is the limit for row updates to happen together in one step. It's much, much, much faster than calling a child workflow for each and every row individually and making the update. For hundreds or thousands of row updates you'll reduce hours of runtime to a minute or two.

JodyRakow

https://community.smartsheet.com/discussion/comment/477305#Comment_477305

Yes, that is the exact error I got as well. I did end up doing exactly as you suggested with a child workflow. It will never be a huge number of child rows at one time so this will work just fine. I was mostly just really curious about whether/why the "Update Row" module wasn't accepting the same input type as the "Delete Row" one. :-)

Thank you so much for digging in to this with me!