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! 😀