Automate Sending Welcome Email to New Smartsheet Users in our Company?

Does anyone know of a way to automate sending a welcome email to new Smartsheet users in our company. Our company has access to the app but their account isn't "created" until they login the first time (Okta/Active DIrectory Authentication)

My idea would be to use the Bridge to:

Make API call using GET /users
Add Row to sheet with new users' info.
Use Automation to notify admin with the user's details.

Questions for the Community:

a. Can bridge do "new users" only, or does it have to add the entire list to the sheet?

b. Is there a better way to automate something like this?

Find more posts tagged with

Bridge

api

users

notification

automation

Accepted answers

SSFeatures

Hey @NeilKY!

Let skip question 1 for a second and jump to question 2, which will help us cover question 1.

Question 2

Can I avoid having to use static column IDs if I use the code that @Brian_Richardson posted here?

Yes absolutely. He mentions that you can retrieve all of the column IDs using the Get Sheet modules. You'll be able to specify the Column Name in your code, and then have your JavaScript code use the Column Name to dynamically find the Column ID.

Step 1: We need to add a targetColumns key into your JavaScript code. This key will come from your Get Sheet module.

Step 2: Update your Get Sheet module, make sure that it retrieves all of the columns.

Step 3: Add this key into your JavaScript module.

Step 4: Update your Javascript code to use merge the userArray and the targetColumns array.

This time I added comments into the script so that it's more clear what each step does. This script expects you to provide it with a key named userArray and another key named targetColumns.

You can modify the script however you want. But currently, it expects targetColumns to contain a column named "Email Column".

The script loops over all of the users and creates a row for each user. Each row has 3 cells, for the email, firstName, and lastName.

// Key 1: userArray - contains a list of all of the users.
// Key 2: targetColumns - contains a list of all of the columns in the sheet.

// Map the column title to the column ID.
const columnMap = new Map();
targetColumns.forEach((column) => columnMap.set(column.title, column.id));

// Create an array of all of the rows that we want to write into the sheet.
const rowsToWrite = [];

// Iterate over each user, and create a row to write into the sheet for the user.
for (const user of userArray) {
  rowsToWrite.push({
    // Add this row to the bottom of the sheet.
    toBottom: true,
    cells: [
      {
        columnId: columnMap.get("Email Column"),
        value: user.email,
      },
      {
        columnId: columnMap.get("First Name Column"),
        value: user.firstName,
      },
      {
        columnId: columnMap.get("Last Name Column"),
        value: user.lastName,
      },
    ],
  });
}

// Return the payload so that we can send it to the API.
return rowsToWrite;

This script returns an array of rows, and you can send this straight to the Smartsheet API. This is what it looks like:

[
  {
    "toBottom": true,
    "cells": [
      {
        "columnId": "1000",
        "value": "email1@example.com"
      },
      {
        "columnId": "2000",
        "value": "Alice"
      },
      {
        "columnId": "3000",
        "value": "Smith"
      }
    ]
  },
  {
    "toBottom": true,
    "cells": [
      {
        "columnId": "1000",
        "value": "email2@example.com"
      },
      {
        "columnId": "2000",
        "value": "Bob"
      },
      {
        "columnId": "3000",
        "value": "Smith"
      }
    ]
  },
  {
    "toBottom": true,
    "cells": [
      {
        "columnId": "1000",
        "value": "email3@example.com"
      },
      {
        "columnId": "2000",
        "value": "Carol"
      },
      {
        "columnId": "3000",
        "value": "Smith"
      }
    ]
  }
]

Example Script

I modified the script above to provide some example data. In this script, I "hard coded" the userArray and the targetColumns with some fake data, just so that we can see exactly what the script does.

You can open an online Javascript compiler and paste the code in, to see what it does. For example, go to https://www.programiz.com/javascript/online-compiler/ and then paste in this code.

In the real script, the userArray and the targetColumns should be added as keys into this JavaScript module.

// Key 1: userArray - contains a list of all of the users.
const userArray = [
  { email: "email1@example.com", firstName: "Alice", lastName: "Smith" },
  { email: "email2@example.com", firstName: "Bob", lastName: "Smith" },
  { email: "email3@example.com", firstName: "Carol", lastName: "Smith" },
];

// Key 2: targetColumns - contains a list of all of the columns in the sheet.
const targetColumns = [
  { id: "1000", title: "Email Column" },
  { id: "2000", title: "First Name Column" },
  { id: "3000", title: "Last Name Column" },
];

// Map the column title to the column ID.
const columnMap = new Map();
targetColumns.forEach((column) => columnMap.set(column.title, column.id));

// Create an array of all of the rows that we want to write into the sheet.
const rowsToWrite = [];

// Iterate over each user, and create a row to write into the sheet for the user.
for (const user of userArray) {
  rowsToWrite.push({
    // Add this row to the bottom of the sheet.
    toBottom: true,
    cells: [
      {
        columnId: columnMap.get("Email Column"),
        value: user.email,
      },
      {
        columnId: columnMap.get("First Name Column"),
        value: user.firstName,
      },
      {
        columnId: columnMap.get("Last Name Column"),
        value: user.lastName,
      },
    ],
  });
}

// Return the payload so that we can send it to the API.
console.log(JSON.stringify(rowsToWrite));

Question 3

You may not be able to answer this last question but could I just use "Array management: Extract Field from Array", for each of the fields i want in a row, then write to the sheet for each of those values in one API call?

You might be able to do this, but since you already have to use JavaScript to map the column IDs, you might as well use JavaScript to extract the user fields.

Best of luck!

All comments

Andrée Starå

https://community.smartsheet.com/discussion/139706/automate-sending-welcome-email-to-new-smartsheet-users-in-our-company

Hi,

I hope you're well and safe!

I'm not sure if it's possible to send an email automatically, but as you probably know, you can create a custom welcome screen with the same information.

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer! I appreciate it, thank you! 🙏

NeilKY

We are using the welcome page for brief disclosures, but we know people will close that window with their muscle memory.

I know I can send documents automatically. and I can use formulas to identify new entries.

After spending ALL DAY on this bridge workflow, I'm giving up. Maybe you, @Paul Newcome, or @Genevieve P. My main question above, #1. How do I simply get the "ALL USERS" Data into a sheet? It SEEMED SO SIMPLE! Yet, ChatGPT and I are quickly becoming enemies as it tells me to do the wrong thing to fix it >:(

Goal: Get data from API Call for ALL USERS into an existing blank sheet. (I already know how to clear the sheet)

In the spirit of Genevieve, I will show you my flow and the values in each below. (Could be a long post but then you'll have everything you need)

Column Headers: (Blank rows)

Full Name	Email	User ID	Admin	Licensed	Group Admin	Resource Viewer	Status	Alternate Email 1	Alternate Email 2	Company	Department	Title	Work Phone	Mobile Phone	Locale

{C105BA0D-8D1D-4D3F-AEC8-950CC4936829}.png

Get Sheet, (No filters or advanced options selected), image not required.

First API Call: List all users (No hidden options selected)

{91917A39-116B-48AE-BB6E-7774476E8F21}.png

Javascript:

{FC229D4F-6312-4C4F-91DE-FCB6EC0619C9}.png

let licensedUsers = [];let nonLicensedUsers = [];let allUsers = [];

userArray.forEach(user => {const userData = {email: user.email};

if (user.licensedSheetCreator === true) { licensedUsers.push(userData);} else { nonLicensedUsers.push(userData);}// Add userData to allUsers arrayallUsers.push(userData);

});

function convertArrayToJson(array) {return array.map(item => ({ email: item.email }));}

let licensedUsersJson = convertArrayToJson(licensedUsers);let nonLicensedUsersJson = convertArrayToJson(nonLicensedUsers);let allUsersJson = convertArrayToJson(allUsers);

return {licensedUsers: licensedUsersJson,nonLicensedUsers: nonLicensedUsersJson,allUsers: allUsersJson};

API Call to write the values to the sheet:

The end. The result? Nothing, absolutely nothing!

Paul Newcome

What does the run log look like for your Java module?

Genevieve P.

Hi @NeilKY
Just a side note to say that Support has informed me of your report that this thread is causing some issues and you're unable to Edit your posts. We've created a ticket with the site provider and I'll update you when I have more information.

In the meantime, please keep creating new posts/comments if there's anything that needs to be updated. It may also be best to parse out complex comments into multiple posts in case it's the complexity of the content (e.g. links & images) that are causing the issue.

From my understanding it's just this one thread, however if you have any issues with other Community threads, please use the flag icon to Report that post to our Moderation team. That way we can include that information into the ticket we've created with the site provider!

Thanks,
Genevieve

NeilKY

https://community.smartsheet.com/discussion/comment/485546#Comment_485546

{078BD5F1-C3A5-41A5-B2DC-4D2FD24EEB88}.png

Currently, it only grabs the email for testing purposes.

The 672 matches the total users in our organization.

Paul Newcome

What happens if you (temporarily for troubleshooting) replace the JS module with an Extract from Array module? Set the array to be the return from the Get Users API call, set the key as email, then set the output as csv which then gets passed to your API module for being output?

NeilKY

https://community.smartsheet.com/discussion/comment/485565#Comment_485565

@Paul Newcome Per your instructions, after the changes the array does grab the value properly. I end up with an array of email addresses.

However, I also updated the HTTP Request body for the final step to "{{states.Extract from Array.arraymgmt.extract_from_array}}" (copied source from log of extract from array). but it gives error.

SSFeatures

Hi @NeilKY, the problem is with the way that you're trying to write the data into your sheet.

Background Information

You're correct in trying to use the "/sheets/{sheetid}/rows" with POST endpoint, which allows you to add rows into the sheet.

Here is the documentation for this endpoint.

If you look at the example in the documentation, this is what the payload looks like:

This example adds 2 rows, and each row has 2 cells.

[{
    "toTop": true,
    "cells": [{
        "columnId": 7960873114331012,
        "value": true
    }, {
        "columnId": 642523719853956,
        "value": "New status",
        "strict": false
    }]
}, {
    "toTop": true,
    "cells": [{
        "columnId": 7960873114331012,
        "value": true
    }, {
        "columnId": 642523719853956,
        "value": "New status",
        "strict": false
    }]
}]

This means that to add a row, we need to explicitly specify each cell, and we need the column ID for the column corresponding to that cell.

Problem

So, we need to convert your current data into this format. Let's look at what your format looks like:

This data looks something like this:

{
  "allUsers": [{email: "1@example.com"}, {email: "2@example.com"}],
  "licensedUsers": [{email: "3@example.com"}, {email: "4@example.com"}],
  "nonLicensedUsers": [{email: "5@example.com"}, {email: "6@example.com"}],
}

Notice how this format is completely different from the format required by the API endpoint? If we try to push this data straight into the API endpoint, then Smartsheet doesn't know what to do with it, because it doesn't specify anything about which cells, or columns to place the data into.

Solution

You should update your JavaScript step to format the result in the correct format that the API is looking for. You will need to know the Column ID for your "Email" column in your sheet. You can find this using the Smartsheet Instructions, or you can find it directly with SSFeatures with these instructions.

Once you have the column ID, you can insert it into a script like this. Notice how this tries to format the request in the same format as the example in the API. I switched it to toBottom to insert it at the bottom of the sheet.

You should be able to take the payload right from this script and then throw it into the next step.

let allUsersPayload = userArray.map(user => ({
    toBottom: true,
    cells: [
        {
            columnId: COLUMN_ID_HERE,  // <<-- PUT YOUR COLUMN ID HERE
            value: user.email
        }
    ]
}));

return allUsersPayload;

I didn't test this out, since I don't have Smartsheet Bridge, but it should work!

I hope this helps!

NeilKY

https://community.smartsheet.com/discussion/comment/485618#Comment_485618

🤯 🤯 🤯

thank you both! I'll respond to this one on Monday :)

SSFeatures

@NeilKY You're welcome!

NeilKY

https://community.smartsheet.com/discussion/comment/485618#Comment_485618

Well Nathan, i took longer than Monday and I am totally lost for some reason.

Can you elaborate a little more about the code? What would the entire code be for the java step? And that's only for one field… What if I want to get all fields?
Can I avoid having to use static column IDs if I use the code that @Brian_Richardson posted here?
You may not be able to answer this last question but could I just use "Array management: Extract Field from Array", for each of the fields i want in a row, then write to the sheet for each of those values in one API call?

It seems to me this should be so much easier than I am making it out to be…

SSFeatures

Hey @NeilKY!

Let skip question 1 for a second and jump to question 2, which will help us cover question 1.

Question 2

Can I avoid having to use static column IDs if I use the code that @Brian_Richardson posted here?

Step 1: We need to add a targetColumns key into your JavaScript code. This key will come from your Get Sheet module.

Step 2: Update your Get Sheet module, make sure that it retrieves all of the columns.

Step 3: Add this key into your JavaScript module.

Step 4: Update your Javascript code to use merge the userArray and the targetColumns array.

This time I added comments into the script so that it's more clear what each step does. This script expects you to provide it with a key named userArray and another key named targetColumns.

You can modify the script however you want. But currently, it expects targetColumns to contain a column named "Email Column".

The script loops over all of the users and creates a row for each user. Each row has 3 cells, for the email, firstName, and lastName.

// Key 1: userArray - contains a list of all of the users.
// Key 2: targetColumns - contains a list of all of the columns in the sheet.

// Map the column title to the column ID.
const columnMap = new Map();
targetColumns.forEach((column) => columnMap.set(column.title, column.id));

// Create an array of all of the rows that we want to write into the sheet.
const rowsToWrite = [];

// Iterate over each user, and create a row to write into the sheet for the user.
for (const user of userArray) {
  rowsToWrite.push({
    // Add this row to the bottom of the sheet.
    toBottom: true,
    cells: [
      {
        columnId: columnMap.get("Email Column"),
        value: user.email,
      },
      {
        columnId: columnMap.get("First Name Column"),
        value: user.firstName,
      },
      {
        columnId: columnMap.get("Last Name Column"),
        value: user.lastName,
      },
    ],
  });
}

// Return the payload so that we can send it to the API.
return rowsToWrite;

This script returns an array of rows, and you can send this straight to the Smartsheet API. This is what it looks like:

[
  {
    "toBottom": true,
    "cells": [
      {
        "columnId": "1000",
        "value": "email1@example.com"
      },
      {
        "columnId": "2000",
        "value": "Alice"
      },
      {
        "columnId": "3000",
        "value": "Smith"
      }
    ]
  },
  {
    "toBottom": true,
    "cells": [
      {
        "columnId": "1000",
        "value": "email2@example.com"
      },
      {
        "columnId": "2000",
        "value": "Bob"
      },
      {
        "columnId": "3000",
        "value": "Smith"
      }
    ]
  },
  {
    "toBottom": true,
    "cells": [
      {
        "columnId": "1000",
        "value": "email3@example.com"
      },
      {
        "columnId": "2000",
        "value": "Carol"
      },
      {
        "columnId": "3000",
        "value": "Smith"
      }
    ]
  }
]

Example Script

You can open an online Javascript compiler and paste the code in, to see what it does. For example, go to https://www.programiz.com/javascript/online-compiler/ and then paste in this code.

In the real script, the userArray and the targetColumns should be added as keys into this JavaScript module.

// Key 1: userArray - contains a list of all of the users.
const userArray = [
  { email: "email1@example.com", firstName: "Alice", lastName: "Smith" },
  { email: "email2@example.com", firstName: "Bob", lastName: "Smith" },
  { email: "email3@example.com", firstName: "Carol", lastName: "Smith" },
];

// Key 2: targetColumns - contains a list of all of the columns in the sheet.
const targetColumns = [
  { id: "1000", title: "Email Column" },
  { id: "2000", title: "First Name Column" },
  { id: "3000", title: "Last Name Column" },
];

// Map the column title to the column ID.
const columnMap = new Map();
targetColumns.forEach((column) => columnMap.set(column.title, column.id));

// Create an array of all of the rows that we want to write into the sheet.
const rowsToWrite = [];

// Iterate over each user, and create a row to write into the sheet for the user.
for (const user of userArray) {
  rowsToWrite.push({
    // Add this row to the bottom of the sheet.
    toBottom: true,
    cells: [
      {
        columnId: columnMap.get("Email Column"),
        value: user.email,
      },
      {
        columnId: columnMap.get("First Name Column"),
        value: user.firstName,
      },
      {
        columnId: columnMap.get("Last Name Column"),
        value: user.lastName,
      },
    ],
  });
}

// Return the payload so that we can send it to the API.
console.log(JSON.stringify(rowsToWrite));

Question 3

You may not be able to answer this last question but could I just use "Array management: Extract Field from Array", for each of the fields i want in a row, then write to the sheet for each of those values in one API call?

You might be able to do this, but since you already have to use JavaScript to map the column IDs, you might as well use JavaScript to extract the user fields.

Best of luck!

NeilKY

@SSFeatures Thanks! It works like a charm!

SSFeatures

@NeilKY You're welcome! Glad we figured it out!

Brian_Richardson

@SSFeatures I have to say that's some seriously high quality community posting. Holy cow. Puts my walls of text to shame for sure.

Have you considered applying for the Overachievers community group? i think you'd be a great fit!

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.