Import Users, Groups and Group Memberships using Single Import Set

[Kishore47]

Hi Team,

I have an Excel file with the following columns and approximately 300 rows:

• Owners (Users)

• Groups (Group Names)

• Active

Example:

Owners | Groups
User 1 | SSO Group A
User 1 | SSO Group A
User 1 | SSO Group A

User 2 | SSO Group B
User 2 | SSO Group B
User 2 | SSO Group B

User 10 | SSO Group J
User 10 | SSO Group J

Like this, there are approximately 300 rows with duplicate user-group combinations.

I want to achieve the following from scratch using Import Set:

1. Create Users in sys_user (if not exists) — I was able to create successfully using Coalesce

2. Create Groups in sys_user_group (if not exists) — I was able to create successfully using Coalesce

3. Create Group Membership in sys_user_grmember — I am unable to create

4. Avoid duplicate records

I tried using multiple Transform Maps, but group membership is not getting created properly. The Group value is populated, but the User field is empty in sys_user_grmember.

Any guidance or example would be very helpful.

Thanks in advance.

[GlideFather]

Hi @Kishore47,

could you possibly provide some details? YOu mentioned transform map, how about mapping, is there any transform script (if so, share it)?

EDIT: are you uploading the excel against what table?

[Kishore47]

Hi @GlideFather,

Thanks for your response.

I am uploading the Excel file using Load Data, which creates a custom Import Set table: u_user_group_import (auto-created).

I created 3 Transform Maps using the same Source Table:

• Transform Map 1 → Target: sys_user

  • Mapping: Owners → Name (Coalesce enabled)

• Transform Map 2 → Target: sys_user_group

  • Mapping: Groups → Name (Coalesce enabled)

• Transform Map 3 → Target: sys_user_grmember

For Transform Map 3, I used an onBefore Transform Script:

(function runTransformScript(source, map, log, target) {

var userGR = new GlideRecord('sys_user');
userGR.addQuery('name', source.u_owners);
userGR.query();

if (userGR.next()) {
target.user = userGR.sys_id;
}

var groupGR = new GlideRecord('sys_user_group');
groupGR.addQuery('name', source.u_groups);
groupGR.query();

if (groupGR.next()) {
target.group = groupGR.sys_id;
}

})(source, map, log, target);

Result:

• Groups are getting populated

• User field is empty in sys_user_grmember

However, I am not very confident with scripting, so I may be missing something. Please let me know exactly what is required or if there is a better approach.

Please let me know if I am missing something.

Thanks again.

[GlideFather · Accepted solution]

@Kishore47,

I don't see anything utterly wrong :// only thing to review from my end would be "name" on [sys_user] table because the Name is calculated [First name + Last name], so populating directly the Name could bring some mess.. you can try to add more columns and to have it separately for each column/field.

But if users are created correctly then you can ignore this..

could you share the transform map for sys_user_grmember, especially the fields that are coalesce?

[Kishore47]

Hi @GlideFather,

Thank you for your suggestion. You are right — First Name and Last Name would be ideal, but the client provided only full names in a single Owners column. So I handled it using an onBefore Transform Script to split the names, and it worked correctly.

Appreciate your help and guidance!