Bridge: How to automatically add users to "Licensed Users Group" or "Other Users Group"?

NeilKY

We're trying to maintain a "group" of users in our Smartsheet instance who are Licensed, so that we can use Smartsheet to share Licensed-user dashboards with them. And if they're not licensed, they would go into "Other Users Group" for a dashboard highlighting features available to free users.

Currently, we intermittently use the "User List" report to export all users, scrub the data, then import it to the respective groups in Smartsheet. However, We'd LOVE to automate this tedious task!

Has anyone created something like this already, or can you explain to a Bridge noob how to accomplish this, please?

Paul Newcome

Try this for the JS. Make sure that you include the key and value in the module. This is what gives the JS module data to work with.

Key: userArray

Value: Data reference to user array output by the API module.

// INPUT: userArray



let licensedCsv = [];

let nonLicensedCsv = [];



userArray.forEach(user => {


    const userData = {

        lastName: user.lastName,

        firstName: user.firstName,

        name: user.name,

        email: user.email

    };


    if (user.licensedSheetCreator === true) {


        licensedCsv.push(userData);

    } else {


        nonLicensedCsv.push(userData);

    }

});



function convertToCSV(array) {

    const header = "lastName,firstName,name,email";

    const rows = array.map(item => `${item.lastName},${item.firstName},${item.name},${item.email}`);

    return [header, ...rows].join("\n");

}



let licensedCsvOutput = convertToCSV(licensedCsv);

let nonLicensedCsvOutput = convertToCSV(nonLicensedCsv);



return {

    licensedCsv: licensedCsvOutput,

    nonLicensedCsv: nonLicensedCsvOutput

};

The above should do the following:

Each user object will contain lastName, firstName, name, and email properties (these are what get passed when adding a user to a group).

It will iterate through each user object and check the licensedSheetCreator property. If it is true, it will put the user object in the "licensedCsvOutput" array. Otherwise the object will be put in the "nonLicensedCsvOutput" array.

Once it has looped through all of the source array, it will convert both of the CsvOutput arrays into an actual csv string and output those.

NeilKY

Well, the solution was as simple as you said. Except I had to reformat the output to be in JSON format.

{{states.Output CSVs.javascript.run_script.result.licensedUsers}}

Here's the new code:

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

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

if (user.licensedSheetCreator === true) {

    licensedUsers.push(userData);

} else {

    nonLicensedUsers.push(userData);

}

});

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

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

return {
licensedUsers: licensedUsersJson,
nonLicensedUsers: nonLicensedUsersJson
};

Thank you @Paul Newcome !

Paul Newcome

You would first need to generate a list of all users. You will need the API or the API Module in Bridge to do this. The output includes quite a bit of data including whether or not they are licensed. I would use a JavaScript module from here to split the API output array into two separate csv's (licensed vs not). You could then run two separate API modules, one for each group where you pass the respective csv of users into the module.

NeilKY

I've attempted to do this and I am getting stuck outputting it to ONE CSV. The API Call works, but the Script gives me the dreaded error, "workflow execution failed: Plugin Error ERR_PROCESSING : failed to execute extension module : input is not defined"

const response = JSON.parse(apiResponse); // Parse the JSON input

const users = response.data;

const csvData = users.map(user => ({

    ID: user.id,

    Email: user.email,

    FirstName: user.firstName,

    LastName: user.lastName,

    CustomWelcomeScreenViewed: user.customWelcomeScreenViewed ? user.customWelcomeScreenViewed : 'N/A',

    Admin: user.admin ? user.admin : 'N/A',

    GroupAdmin: user.groupAdmin ? user.groupAdmin : 'N/A',

    JiraAdmin: user.jiraAdmin ? user.jiraAdmin : 'N/A',

    LicensedSheetCreator: user.licensedSheetCreator ? user.licensedSheetCreator : 'N/A',

    ResourceViewer: user.resourceViewer ? user.resourceViewer : 'N/A',

    SalesforceAdmin: user.salesforceAdmin ? user.salesforceAdmin : 'N/A',

    SheetCount: user.sheetCount ? user.sheetCount : 'N/A',

    Status: user.status ? user.status : 'N/A'

}));

return csvData;

Paul Newcome

Try this for the JS. Make sure that you include the key and value in the module. This is what gives the JS module data to work with.

Key: userArray

Value: Data reference to user array output by the API module.

// INPUT: userArray



let licensedCsv = [];

let nonLicensedCsv = [];



userArray.forEach(user => {


    const userData = {

        lastName: user.lastName,

        firstName: user.firstName,

        name: user.name,

        email: user.email

    };


    if (user.licensedSheetCreator === true) {


        licensedCsv.push(userData);

    } else {


        nonLicensedCsv.push(userData);

    }

});



function convertToCSV(array) {

    const header = "lastName,firstName,name,email";

    const rows = array.map(item => `${item.lastName},${item.firstName},${item.name},${item.email}`);

    return [header, ...rows].join("\n");

}



let licensedCsvOutput = convertToCSV(licensedCsv);

let nonLicensedCsvOutput = convertToCSV(nonLicensedCsv);



return {

    licensedCsv: licensedCsvOutput,

    nonLicensedCsv: nonLicensedCsvOutput

};

The above should do the following:

Each user object will contain lastName, firstName, name, and email properties (these are what get passed when adding a user to a group).

It will iterate through each user object and check the licensedSheetCreator property. If it is true, it will put the user object in the "licensedCsvOutput" array. Otherwise the object will be put in the "nonLicensedCsvOutput" array.

Once it has looped through all of the source array, it will convert both of the CsvOutput arrays into an actual csv string and output those.

NeilKY

@Paul Newcome Hmm.. I tried to match everything you did but it's not working? I even amended the States to match.

Error:

• workflow execution failed: Plugin Error ERR_PROCESSING : failed to execute extension module : userArray.forEach is not a function

Here are my settings:

So in the next step I'm referencing the Output in this step which is {{states.Get Users.call_api.make_api_call}}

Script: (Copied and pasted from your code)

Run Log:

Paul Newcome

I never try to use the "output" piece a the bottom of a module. I always run it (even if I know it is going to break) so that I can get the actual data reference from the run log.

A perfect example would be… Here is what I have in my JS Module for the value:

{{states.Get Users.call_api.make_api_call.response.data}}

NeilKY

https://community.smartsheet.com/discussion/comment/479679#Comment_479679

OHhhhhh i don't know why I didn't think of that?! {{states.Get Users.call_api.make_api_call.response.data}} is what came from the log on mine too and the end result is working!

Thank you!

Can you help me get the data from these values into their respective groups?

1. {{states.Output CSVs.javascript.run_script.result.licensedCsv}}
2. {{states.Output CSVs.javascript.run_script.result.nonLicensedCsv}}

NeilKY

@Paul Newcome

the CSV data contains "lastName", "firstName", "name" and "email" strings, which line up with the correct headers in the CSV we exported, which line up with the API documentation: https://developers.smartsheet.com/api/smartsheet/openapi/groupmembers/add-group-members , But how do I separate those values to tell the API the correct parameters?

Paul Newcome

You wouldn't use the form parameters. You would drop a reference to the csv into the HTTP Request Body as a JSON string. I imagine it would look something along the lines of

[reference to csv]

I haven't actually tried uploading multiple users to a group this way, but this is where I would start.

NeilKY

@Paul Newcome Thanks for the tips but this is me »»»

I've spend a few hours on it and can't figure out how to get it done.

• What I'm using in "HTTP Request Body": [{{states.Output CSVs.javascript.run_script.result.licensedCsv}}]
  • The Error is: "Unable to parse request. The following error occurred: Request body must be either a JSON object or JSON array."
• I also tried {"email":" {{states.Output CSVs.javascript.run_script.result.licensedCsv||}}"}, no change.

Is there any chance you would have time to try it and let me know what you find?

I've tried:

1. using another script to convert the two CSVs to JSON format

const csvData = runtime.data.states.Output_CSVs.javascript.run_script.result.licensedCsv;

if (!csvData) {
throw new Error("CSV data is not defined");
}

const rows = csvData.split('\n');
const headers = rows[0].split(',');
const jsonData = rows.slice(1).map(row => {
const values = row.split(',');
return headers.reduce((obj, header, index) => {
obj[header] = values[index];
return obj;
}, {});
});

return jsonData;

2. going directly from the all users list api to the post api to add it to the group (bypassing the java script)

3. multiple variations in the HTTP Request Body, and more.

NeilKY

Well, the solution was as simple as you said. Except I had to reformat the output to be in JSON format.

{{states.Output CSVs.javascript.run_script.result.licensedUsers}}

Here's the new code:

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

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

if (user.licensedSheetCreator === true) {

    licensedUsers.push(userData);

} else {

    nonLicensedUsers.push(userData);

}

});

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

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

return {
licensedUsers: licensedUsersJson,
nonLicensedUsers: nonLicensedUsersJson
};

Thank you @Paul Newcome !

Paul Newcome

Sorry I didn't get to do more troubleshooting for you, but glad you were able to get it sorted. It does make perfect sense to have the JS Module do the formatting for you.

NeilKY

https://community.smartsheet.com/discussion/comment/480563#Comment_480563

Honestly, I don't know how to do it without java :)