Bridge - Auto Delete Newly Added Columns

Hello, I've been trying to create a workflow in bridge that when a Move Row automation happens to a target sheet. There are certain columns that will be delete each time the Move Row automation happens.

So in this example, the row is moved to this sheet, because the last two columns aren't in the sheet, I want to delete them to save on cell space, etc. They will be same column headers each time they reappear on the sheet from a Move Row.

I've messed with Bridge to create workflow when a new row is added to then make an API call and delete the columns by columnID.

I've referenced the Delete Column under the API - Column Delete Section - Smartsheet site but I am still not getting the results I need. Just curious if anyone has built something like this using Bridge and they'd be willing to talk more about.

I appreciate any help possible, thank you. - Michael

Find more posts tagged with

API and Developers

Bridge

Accepted answers

Genevieve P.

Hey @MHalvey

Since you're deleting out the columns coming in, when they're re-added they will be entirely new columns with new IDs, as you've found.

I am wondering if the easiest thing to do is to actually use Bridge to Copy the row instead of using the in-sheet workflow. This way you only have to manage one workflow instead of two, and you can be selective about the cells that you want to copy over, then delete the original row as part of that process.

Here's an article that explains how to do this: https://help.smartsheet.com/articles/2482720-copy-partial-rows-using-Bridge

However if that doesn't work, what I would do is use List Columns as the beginning of your workflow. This will list out all the new column IDs with their associated name.

There are probably then multiple ways to proceed, but my personal preference would be to then trigger a Child Workflow,

using the Column Array as the "number of runs".

In the next workflow, I would use a Conditional Match junction to look for as many column names as you want, identifying them by the title of the State: {{runtime.data.title}}

See: https://help.smartsheet.com/articles/2481998-route-workflows-with-junctions#toc-conditional-junctions-with-more-than-two-pathways

Notice that my purple state names under the Junction are the same as the column title. That's how it will find the match. Then you can set up your API to delete the column ID that came in as the trigger, since it matches the name: {{runtime.data.column_id}}

The "Other" state is your Fallback (aka the column is not one of your ones to delete) so it doesn't need anything under it.

Let me know if this makes sense and will work for you!

Cheers,
Genevieve

All comments

MHalvey

Update: As usual, it seems to be a small typo that has caused my initial error and hours of testing. Using the above format I was able to delete a column when a new row is added.

However when it returns again after another row was moved into that sheet with the same header name, it is now under a different ColumnID so I'm wondering if there is a way to use the column name instead of ColumnID for the Endpoint data link below?

Base URL - https://api.smartsheet.com/2.0/sheets

Endpoint - /{{SheetID}}/columns/{{ColumnID}}

@Genevieve P. - I'm sorry to tag you but you seem to always come to my rescue! 😁

I tried replacing the JSON source with the Data Reference and add it to the Endpoint part for {{ColumnID}} but that didn't work.

-Michael

Genevieve P.

Hey @MHalvey

Since you're deleting out the columns coming in, when they're re-added they will be entirely new columns with new IDs, as you've found.

Here's an article that explains how to do this: https://help.smartsheet.com/articles/2482720-copy-partial-rows-using-Bridge

However if that doesn't work, what I would do is use List Columns as the beginning of your workflow. This will list out all the new column IDs with their associated name.

There are probably then multiple ways to proceed, but my personal preference would be to then trigger a Child Workflow,

using the Column Array as the "number of runs".

In the next workflow, I would use a Conditional Match junction to look for as many column names as you want, identifying them by the title of the State: {{runtime.data.title}}

See: https://help.smartsheet.com/articles/2481998-route-workflows-with-junctions#toc-conditional-junctions-with-more-than-two-pathways

The "Other" state is your Fallback (aka the column is not one of your ones to delete) so it doesn't need anything under it.

Let me know if this makes sense and will work for you!

Cheers,
Genevieve

MHalvey

@Genevieve P. - Thank you so much for this. I believe I've followed this, I figured I'd give a little more information in case others are looking for something like this. Sharing is caring! 😀

"I am wondering if the easiest thing to do is to actually use Bridge to Copy the row instead of using the in-sheet workflow. This way you only have to manage one workflow instead of two, and you can be selective about the cells that you want to copy over, then delete the original row as part of that process." - I normally create Bridge workflows to "Get Row" and then "Add Row," which will take just the columns I need from a source sheet to a target sheet but it only works when I don't have attachments or comments to bring along. It's hard to do when you have 1 sheet/intake form for 8 different Ticket systems but only want certain columns going to target sheets AND can have attachments from the Intake Form. Here's a small simplified chart process.

So I'm hoping I can just use the simple "Move Row" automation based on the source sheet (intake form) and once the row (with attachment) gets to the target sheet, in the image above, then I can use Bridge to trim out and leave 4/35 columns for system A and 8/35 columns for B and so on.

Looking back at what you built, I have a few questions:

The trigger for the first workflow you created is "When A row is Added?" I am also seeing in your images later, you aren't in the first workflow anymore, you are in a second workflow, this is the child workflow? Use child workflows to manage complex processes. What did you use as the Trigger for the child workflow, was it just the link on the Parent workflow that triggers it? Once I added in the Junction Match and followed your steps, I added in 4 columns to be removed each time they are added and then the "Other."

I then applied the API Call to each section using the "{{runtime.data.column_id}}" in the ENDPOINT, using my API profile and Method of Delete for each of the 4 columns to be removed. Keeping the rest blank and the State: Other was left blank as well.

I added a new row and the workflows worked to remove the 4 columns! It did take about 3 minutes to run each workflow, is that speed pretty normal? I would imagine if I had 10 columns to delete, then it would take longer to complete the full column deletion? Is there a limit to the amount of columns I can delete in a junction?

This has got me thinking, since these are 2 sperate workflows, a parent and child. I wonder if I could use the SAME Child workflow for all 8 different target sheet ticket systems if the columns match. I would just need to create a new unique parent workflow for each ticket system and link them back to the single child workflow. I don't mind making parent/child for each ticket system but just trying to work smarter not harder.

Sheet Activity log shows the rows were deleted and all looks good!

Looking forwarding to hearing your thoughts on my case and my few questions. I can't thank you enough for helping out with this. I wish there was more information on how powerful Bridge can be or Pro Desks that work with it. It's such a great tool and I wish more users understood it's capabilities.

Thank you again - Michael

Paul Newcome

You can avoid using junctions in the Child workflow if you use a List Columns module to get the columns from the target sheet. This lists them out in order from left to right which means that you can feed this entire array to a JS script module to essentially find the index of the object with the "title" property of "END" then output an array consisting of any object with a higher index.

You could either use that same JS module to extract the column IDs from each of these objects or use an extract field from array module to do the same thing. Either way, you should end up with an array that consists only of column IDs where the index was greater than the index of END that you can then pass to the child workflow (also using the array to determine how many times to trigger)

Then your child workflow would be a very simple delete column API being passed the data from the trigger and would work for as many columns as you need. This means it could be used for quite a few sheets by setting up multiple triggers on the parent workflow and making sure that you always use "END" to denote where the sheet should end and not have to worry about replicating this for other projects or having to set up new modules in your junction every time you want to add a new column to your source sheet(s).

Genevieve P.

Hey @MHalvey

Thank you so much for sharing + caring! It's really helpful to understand your full process.

And thank you @Paul Newcome for stepping in with the JS module solution. You can see where my gaps in abilities are, which is why the hive-mind of the Community is so great.

To answer your questions Michael:

Child Workflow Questions
Trigger could be when a row is added or a time-based trigger (e.g. once a day, once per week)
Otherwise, yes! You got it.

What did you use as the Trigger for the child workflow, was it just the link on the Parent workflow that triggers it?

Yes, the Child Workflow Module in the Parent is what triggers the child to run. Your image with the red line explains it very well.

Run Time Question

Yes, it's expected that my suggested solution may take quite a few minutes. This is because I have your second workflow looking into each individual column which takes a few seconds each time.

This means if your sheet only had 3 columns it would be relatively quick. If it has 50 columns, then I would expect it to take much longer, regardless of how many of them will be deleted. You could delete all 50! It is processing each column individually to decide what to do with it.

Paul's solution of using JS to trim down the array is really helpful, as that will speed up the process for the child workflow for sure.

Use the Same Workflow - Parent - likely yes!

He also mentioned using the same Parent for all of your sheets, and I agree! You can use the same Child, yes, but you can also use the same Parent!

If you set multiple different sheets to trigger this Parent workflow, you should be able to reference the sheet ID from the trigger data in the workflow itself. That means the ID will be dynamic, so regardless of what sheet starts the workflow you can look for the same columns to delete.

Use the Same Child - likely not in your scenario

Where you might need more than 1 set is if you're deleting different columns, which it sounds like you want to do with the different ticketing systems.

You can have the same Parent workflow get the Columns and then trigger different Child workflows depending on the Sheet ID.

OR you could build a complex Child workflow that also checks the Sheet ID before deleting the column (e.g. if it comes from Sheet A, B, or C, do nothing. If it comes from Sheet D, delete), but this would require you to list all possible columns to delete across all possible sheets, and list out all possible sheets before performing the action.

My personal preference is usually to keep simple, smaller workflows versus one massive workflow. Although this does mean possible updating all X number of workflows if you make a change, it's also a lot easier to troubleshoot and understand if you hand this over down the line.

Lots to think about!

Paul Newcome

@Genevieve P. I've been using a lot of JS and API calls in Bridge lately, so they are pretty fresh on my mind. I also wanted to note that using the JS to only pass a list of column IDs means you should be able to use the same child workflow for multiple sheets as well. The idea of the JS is to only output column IDs that have an index greater than the END column. This means the child workflow can assume that all columns passed into it should be deleted regardless of name or sheet (which reminds me that a sheet ID would need to be passed with the column ID array as well).

In the below we use the List Columns module set to list columns from the sheet id that triggered the workflow. Then we use a JS module to extract the column ids from the columns after the [END] column (working script at end). The entities passed to the child workflow are "sheetId" which is the triggering sheet id and "deleteColumnsArray" which is the output of the JS module.

The child workflow starts with an Offset Array module. The array of choice is the deleteColumnsArray, Trim Size is 1, and offset is {{runtime.index}}. This extracts the column id from the array for whichever runthis is coming fromt he parent workflow. So first time parent pushes, this will extract first entry from the array. Second time coming from the parent extracts second entry from array. So on and so forth. This works great in conjunction with setting the child trigger in the parent workflow to run the same number of times as entries in the array.

Finally we use the API Call module to delete the column using runtime data in the endpoint.

sheets/{{runtime.entities.sheetId}}/columns/{{states.Choose Column From Array.arraymgmt.offset_array.result.0}}

Of course there are limits to api calls in an hour and all of that, but in theory, this should give us the ability to use this set of workflows for any sheet with any columns AS LONG AS the delimiting column is called "END". The names of the columns being deleted and the number of columns being deleted shouldn't matter.

Here is the working JS code. The top line shows your input / script parameters. Note: When trying to paste it in here, it is stripping out the spaces / tabs for indentation, so I will paste the text but also a snippet from how it looks in Bridge.

// INPUT: columnsArray -> {{states.List Columns.smartsheet.list_columns.data}}
let endIndex = -1;

for (let i = 0; i < columnsArray.length; i++) {
if (columnsArray[i].title === "END") {
endIndex = i;
break;
}
}

let result = [];
if (endIndex !== -1) {
for (let i = endIndex + 1; i < columnsArray.length; i++) {
if (columnsArray[i].column_id !== undefined) {
result.push(columnsArray[i].column_id);
}
}
}

return result;

Genevieve P.

AHA! Yes of course, I see what you mean - since the newly added columns will always be at the end of the array, you can delete them regardless of the specific name, so all columns passed to the Child can be deleted.

Very, very cool.

Paul Newcome

@Genevieve P. Exactly!! Just last week I used a similar method to get a variable list of columns from in between two other columns. Basically "give me all of the columns between the "Start" column and the "End" column regardless of the name(s) or how many". Otherwise I don't know that I would have thought to use the index of the "End" column here.

@MHalvey I do like this use case too using the Move / Copy row automations for ease of use with attachments then using a Bridge workflow to delete columns after the fact. It is simple and effective, and I am putting it in my back pocket for future use. Haha.

Lindsay Whitbread

Just looking at the underlying need through a different lens…would it be possible to use DataMesh to copy only the columns you need over to the other sheet?

Paul Newcome

@Lindsay Whitbread I don't think DataMesh manages attachments which is the main reason for using the move / copy row automation in the sheet in this particular use case. Without attachments, DataMesh would be a great solution.

MHalvey

Thank you all for your feedback and discussion! I love all the ideas and suggestions!

@Paul Newcome - Your genius is always welcomed! I don't have much experience in JavaScript so I'll need to dig deeper into your flow. In order to get to the next level in Bridge, I'll need to learn what JavaScript can do for it.

@Lindsay Whitbread - Paul is correct. I need the comments/attachments to follow which is why DataMesh won't work for me. If DataMesh evolves to allow that then I can definitively see that as a option.

I did have a ProDesk session setup last week and I know they don't help with Bridge but I figured I'd check to see if they have any ideas that would be easier than making Bridge's for each one of my ticket categories. The wonderful support team listen to my case and got back to me yesterday. She came up with a simpler idea to the duel Bridge workflows so I thought I'd share.

Using this basic workflow allows me to setup all the columns on each Target Sheet before creating this below and then any new columns added from my Source Sheet will delete them.

Trigger: On target sheet is "When columns are Added."

Call API:

API profile is the API call token
Endpoint is /SHEETID/columns/{{runtime.event.id}}
Method is DELETE

This basic workflow will delete any new columns added to the sheet and won't need to map columns by name. All I need to update are SHEETIDs on the Trigger and the Endpoint cell under the API Call.

If I do need to add a new column on the target sheet(s), I'll need to remove the trigger, add the columns and readd the original trigger to the workflow.

Thank you all again for the conversations and discussion. 😀

Michael

Paul Newcome

@MHalvey JavaScript basically allows us to take some inputs and manipulate them to generate an output. ChatGTP got me started and then Codecademy helped me learn how to run with it.

You should be able to copy/paste from my comment and then follow the screenshot for indentation.

In my solution above, we took the results from the List Columns module (as an array/list) and passed it as an input into the JS Module. The JS Module then searches through the array for the object titled "END" and gets the index (column number from left to right). Then the JS Module extracts and outputs all of the objects (columns) that have a higher index than "END" (are further to the right).

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.