Extract Email Address from Contact Column

Neil Watson

Hi,

I am looking for a way to use Bridge to extract the email address from a contact and update an "Email" column in the same row for a record, when added.

I assume this can be done with Java Script but I have no idea where to start!

Heather Hutchens

I've had a lot of success by asking ChatGPT these kinds of technical questions - so I thought I would try this one.

I literally typed "In smartsheet..." and then copied and pasted this question in. Following is the response I got. I hope it's helpful - and I also hope it's helpful for the community to know that ChatGPT knows Smartsheet inside and out :)

----------------------------------

To extract an email address from a contact and update the "Email" column in Smartsheet using JavaScript, you can leverage the Smartsheet API and the Smartsheet SDK for Node.js. Here's a general outline of the steps you would need to follow:

1. Set up a Smartsheet API access token: You'll need an API access token to authenticate your requests. You can generate a token from the Smartsheet Developer Portal.
2. Set up a Smartsheet webhook: Create a webhook in Smartsheet to trigger your JavaScript code whenever a new record is added. You can configure the webhook to send a POST request to your server with the relevant data.
3. Set up a server to handle the webhook: You'll need a server to receive the POST requests from Smartsheet's webhook. You can use a server-side JavaScript platform like Node.js to handle this.
4. Extract the email address from the contact: In your server-side JavaScript code, you can extract the email address from the contact information received in the POST request payload.
5. Update the "Email" column in Smartsheet: Once you have extracted the email address, you can use the Smartsheet SDK for Node.js to update the corresponding cell in the "Email" column for the record. You'll need to identify the row using the information provided in the webhook payload and update the appropriate cell.

Here's a sample code snippet to give you an idea of how this could be implemented using Node.js and the Smartsheet SDK:

const smartsheet = require('smartsheet');

const webhookSecret = 'your_webhook_secret'; // Secret used for webhook verification

const accessToken = 'your_access_token'; // Smartsheet API access token

const smartsheetClient = smartsheet.createClient({

 accessToken: accessToken

});

// Handle the webhook request

app.post('/webhook', (req, res) => {

 // Verify the webhook request

 const signature = req.headers['x-smartsheet-signature'];

 if (signature !== webhookSecret) {

  res.status(403).send('Invalid signature');

  return;

 }

 // Extract necessary information from the webhook payload

 const recordId = req.body.recordId;

 const contactEmail = req.body.contact.email;

 // Update the "Email" column in Smartsheet

 const cellData = {

  columnId: 'your_email_column_id', // Replace with the actual column ID for the "Email" column

  value: contactEmail

 };

 smartsheetClient.sheets.updateRow({

  sheetId: 'your_sheet_id', // Replace with the actual sheet ID

  rowId: recordId,

  cells: [cellData]

 })

 .then(() => {

  res.status(200).send('Email updated successfully');

 })

 .catch((error) => {

  console.error('Error updating email:', error);

  res.status(500).send('Error updating email');

 });

});

Remember to replace the placeholders in the code with your actual values: your_webhook_secret, your_access_token, your_email_column_id, and your_sheet_id.

Note that this is just a basic example to give you a starting point, and you may need to adapt it based on your specific requirements and the structure of your Smartsheet data. Make sure to refer to the Smartsheet API documentation and the Smartsheet SDK documentation for more details and additional methods you might need.

Additionally, you'll need to set up a server that listens for incoming requests on the /webhook endpoint and configure your Smartsheet webhook to send requests to that endpoint.

Scott Peters

After a little help from Smartsheet professional services, we were able to do this with Bridge but without using any scripting. I have a CONTACT column and a CONTACT EMAIL column (text)

1. Trigger (lots of ways to trigger the event, mine is based on new rows created using a form, where the email address is entered into a Contact column)
2. GET ROW: Sheet={{runtime.sheetID}}, Row ID={{runtime.event.rowId}}
3. UPDATE ROW: Sheet={{runtime.sheetID}}, Row ID={{runtime.event.rowId}}, Cells: Key #1 = Contact Email (my text column name), Value #1 = {{states.Get the Contact Email.smartsheet.get_row.row.cells.Contact.value}}

Neil Watson

Hi @Scott Peters thanks that does the trick!