Bridge - Pull Multiple contacts from one sheet & update one cell with all?

bsanson

I am trying to use Bridge to pull all the contacts (Supervisors) from a roster grid that match the department defined by my trigger and then put all of these contacts into one single cell on my project tracking grid. So far, I've been able to get the array and then extract the emails I would need but there's a couple issues:

1) There are duplicates in the array that is causing the array to be larger than 20 contacts which the contact field cannot handle more than. Is there a way to remove duplicates and retain the unique/distinct values?

2) I tried to Trim Array before Extract Field From Array but my output from the Extract is now empty. Why is this? I've updated my Array field.

I'm new to Bridge and JSON so any tips are much appreciated. Thanks!

Ryan Sides

Leaving this here for anyone that needs it. (Thanks to @Samuel Mueller for the inspiration)

Sheet has two columns. Multi Select Contact and Single Select Contact.

For this example, we are taking all contacts from the Single Select column and adding them into the first row's Multi Select contact column.

Bridge Workflow:

Trigger as you need.

Call API to get the sheet. (Or you could use the Bridge Get Sheet)

JS: (pasted funky and I'm not a hardcode coder)
The JS gets all emails from the appropriate column and adds them to a list. If the length of the list is 1, it uses the 1 email in the result. If the length is not 1 it joins the emails with a comma.

//15collectEmails

function getEmails(rowData){   
columnId = 4717189490757508   
allEmails = []   
emails = []   
// get emails from column   
for (row of rowData){       
for (cell of row.cells){           
if (cell.columnId == columnId){               

if(cell.value){ allEmails.push(cell.value)}                            }
          } 
  }   

if (allEmails.length === 1){       
emails = allEmails    }   
else {        emails = allEmails.join()    }

return emails}
return getEmails(data)

Bridge Update Row:

The Key is the name of the multi select contact column. The Value is the Reference to the result of the JS

Enjoy!

Samuel Mueller

The easiest way in my opinion to remove duplicates would be to use the javascript module, and write a short script to do this.

The trim module just removes the bottom however many number or array items you specify, doesn't look at duplicates.

ChatGPT is a great resource for quick javascript code.

also, multiple select contact has to be in a specific json format, so you may run into that as well.

Samuel Mueller

@Genevieve P. I've never actually had success with this, do you know if it's even possible to update a multi select contact column with the standard Smartsheet update modules in bridge? I've always had to use an api call to make it work.

Genevieve P.

Thanks for the tag, @Samuel Mueller!

I have to admit I'm not sure if it's possible...there was a similar question that I answered without testing 🤔:

https://community.smartsheet.com/discussion/comment/360655#Comment_360655

bsanson

@Samuel Mueller Thanks for the response! I was trying the Trim module just to see if I could get multiple contacts to post to the row. I was able to find a department that provided less than 20 results in the array so I didn't need the Trim to test this. Unfortunately, it was unable to update row due to the format as you mentioned.

Kelly Moore

@bsanson @Samuel Mueller @Genevieve P.

hey. I've done the opposite and parsed multiselect contacts into individual rows, using Bridge - but I have never been successful in doing anything with a multiselect contact and have it remain a smartsheet contact. 😒

You might be able to get past the error by overriding validation on the column (Advanced Options)

Kelly

Samuel Mueller

so @bsanson it sounds like you will likely need the JavaScript module to create a unique array in the proper [{"name":"name", "email":"email.com"}] format, and then use the Call API module to update these cells. I did try a bunch of different options the other day using the built in update row module and also fell short again.

If you need an example on the JavaScript, and API call, I may be able to dig one up

Ryan Sides

Leaving this here for anyone that needs it. (Thanks to @Samuel Mueller for the inspiration)

Sheet has two columns. Multi Select Contact and Single Select Contact.

For this example, we are taking all contacts from the Single Select column and adding them into the first row's Multi Select contact column.

Bridge Workflow:

Trigger as you need.

Call API to get the sheet. (Or you could use the Bridge Get Sheet)

JS: (pasted funky and I'm not a hardcode coder)
The JS gets all emails from the appropriate column and adds them to a list. If the length of the list is 1, it uses the 1 email in the result. If the length is not 1 it joins the emails with a comma.

//15collectEmails

function getEmails(rowData){   
columnId = 4717189490757508   
allEmails = []   
emails = []   
// get emails from column   
for (row of rowData){       
for (cell of row.cells){           
if (cell.columnId == columnId){               

if(cell.value){ allEmails.push(cell.value)}                            }
          } 
  }   

if (allEmails.length === 1){       
emails = allEmails    }   
else {        emails = allEmails.join()    }

return emails}
return getEmails(data)

Bridge Update Row:

The Key is the name of the multi select contact column. The Value is the Reference to the result of the JS

Enjoy!

Samuel Mueller

Amazing @Ryan Sides. Thank you for sharing this unique solution!

Samuel Mueller

@bsanson circling back, using a modification of @Ryan Sides code to remove duplicates and join the emails by comma, you should be able to achieve this without using the api. If you need further help on specifics, let us know!