Bridge: Update a Contact Column Dropdown List?

MCorbin

Has anyone successfully used Bridge to update the dropdown values in a Contact List Column?

I'm trying to modify a workflow that updates a Dropdown column - and it seems like it should work, but I keep getting this error (or something similar):

I've tried formatting the source column as a Contact Column, a Text Column (with entries being email addresses) and a Contact Column with entries being email addresses. Still getting the same error.

Here's my (otherwise simple) workflow. HELP! :-D

(This is something that would save me about a million hours if I could get it to work... (Or more accurately, would actually get my contact column updates done instead of them all being out of date if I could get it to work))

Genevieve P.

Hey @MCorbin

Great question! Contact Dropdown Lists work a little differently to regular text dropdowns. If you look at the Update Column module for the Contact column, you'll see this message next to "values":

This requires the values to be formatted as pairs, with {"name": "name", "email": "email"}

But the array that's brought through from Get Sheet has separate displayValue (name) and Value (email). This means that way that the values are coming in from the array will be different than what's needed to input into the Update Column module, and we can't directly input them in from the Get Sheet module to Update Column.

Additional Module: JavaScript Module

You can build out the object with a JavaScript module (see: JavaScript module reference) to get it in the correct formatting. Here are a couple of screen captures to show an example of how to do this:

Grab the Rows Array from your Get Sheet instruction:

Then you can use this in your JavaScript module as "rowData":

This then iterates through the row to check if the Contact cell is blank or not (called "Portfolio Manager" in my sheet:)

You can see how this script then changes the data to be in the correct format, with "name" : name and "Email": email. It also filters through and only gets a single object when there are duplicate contacts in the cells of that column.

This means you can take the results array from the JS module in the Run Log:

And use that as the reference in your Update Column module:

Let me know if that works for you!

Cheers,

Genevieve

MCorbin

@Genevieve P. - you are the best! I'll definitely be playing with this.

Our trainer gave us an overview of the JavaScript function today, but we'll be going through in more detail in the next session. So this is perfect timing.

THANK YOU!!

Genevieve P.

Awesome!! Great timing! 🙂

MCorbin

@Genevieve P. - I'm getting an error:

• it did tell me I needed a JavaScript account when I selected the Javascript integration, but then it never asked me for an account (which I don't have) - so could that be the issue?

I modified your script slightly for my column name:

Genevieve P.

Hi @MCorbin

This error has to do with how your Javascript is written. Syntax, including capitalizations, is important when writing script.

For example, you have

Let result=[];

instead of:

let result = [];

I'm not equipped to debug or review JavaScript scripts, however from what I can see I would say that the If should be if and the Return result should be return result.

Try adjusting those capitals to be lower case:

let result = [];
rowData.map(row => {

 if (row.cells['Team Member'].value && !JSON.stringify(result).includes(row.cells['Team Member'].value)) {
  result.push({
   "name": row.cells['Team Member'].displayValue,
   "email": row.cells['Team Member'].value
   });
  };
 });

return result;

Cheers!

Genevieve

MCorbin

Very cool - I'll try that.....

(and then I'll bribe one of my programmers to review it with me if I still have trouble. Thank goodness she likes sushi!) 😁

Neil Watson

@Genevieve P. Thanks very much for this post. As a non JS programmer, a huge amount of time and frustration was avoided! I was able to use ChatGPT to explain what each element of the code does (which only further illustrated my ignorance!).

Genevieve P.

I'm glad I could help, @Neil Watson!

Neil Watson

@Genevieve P. from time to time there are errors in the source data sheets which is causing the JS to fail. The error message is "failed to execute extension module : Invalid email"

Is there a way to ignore any errors?

Genevieve P.

Hey @Neil Watson

What are the errors in the source data? Can we prevent those errors from getting to the JS Module or the Bridge workflow all together?

Neil Watson

Hi @Genevieve P.

I reviewed the source column and found a record with no email address, which was causing the error. I removed the offending culprit.

DavidJay

There is a method to accomplish this in Bridge without the Javascript module. Here is how I achieved it.

1. I have a sheet with a Contact List column in it that has all the contacts I want in both list of available contacts in the column properties and also listed again as rows in the column.
2. I trigger the Bridge workflow when a value is changed in this column. So to manage the list, I add a contact in the column properties and then add them as a new row, then save.
3. Get Sheet - I filtered the Columns to just the column with the Contact List I was to extract.
4. Array Management: Sort Object Array - I am grabbing the array from the data reference in the Get Sheet step at sheet > columns > 0 > contactOptions and then set the Sort Field as "name"
5. Update Column - I set the Type as Contact List and the Values I set as the data reference from "results" array in the Sort Object Array step. Using the output of the sort step does not work.

I hope this helps others!

Julie Fortney

@Genevieve P. Thank you for these easy-to-follow instructions! I hadn't used the JavaScript module until I came across this post yesterday while looking for a way to update a contact list dropdown. I'm still having trouble with it though, which I suspect is due to the size of the dropdown.

I have 1,700+ contacts I need to populate to a dropdown on another sheet. I tried it with Data Shuttle first, and after some troubleshooting Smartsheet Support confirmed that the list is too large to populate using Data Shuttle. I tried this Bridge workflow yesterday and it worked without errors as I was building it until I added the Update Column module. After that it just ran and ran for hours, and this morning I went to check the progress and I see the "Run logs aren't loading right now…" message. From past experience, this shows up when the data the workflow is processing is too large to show.

My question is - if the data set is too large to for Data Shuttle to process, is it likely too large for Bridge to handle also?

I have a plan B that involves breaking up the contacts into separate columns on my target sheet, but that is not ideal.

Thanks!

Genevieve P.

Hey @Julie Fortney

Great question! I know what you're seeing in the Run Log is usually indicative of too much data to display, so yes, my assumption here is that if it's too large for Data Shuttle to process, it's too large for Bridge as well. They both use the Smartsheet API as the source of their updating power, so they'll have similar capacities.

I would recommend a maximum of 1,000 defined values per Contact column if possible!