Re: Script to add users in various tables to a gro...

Ankur Bawiskar · ‎12-06-2024

try this -> declare the approvers array outside

var tableNames = 'table1,table2,table3';
var approvers = [];

var tables = tableNames.split(','); //split up the column names
for (var i = 0; i < tables.length; i++) {
    var table = tables[i];

    var columnNames = ''; //TARGET COLUMNS
    if (table == 'table1') {
        columnNames = 'column1,column2,column3';
    } else if (table == 'table2') {
        columnNames = 'column4,column5,column6';
    } else if (table == 'table3') {
        columnNames = 'column7,column8,column9';
    }
    var columns = columnNames.split(','); //split up the column names

    for (var x = 0; x < columns.length; x++) {
        var column = columns[x];
        //Get the Unique Approvers from each column in the table
        var gA = new GlideAggregate(table);
        gA.addQuery('u_active', true);
        gA.addQuery([column], '!=', "");
        gA.addNotNullQuery([column])
        gA.groupBy([column]);
        gA.query();
        while (gA.next()) {
            // Check if the user exists in the group member table
            var grGroup = new GlideRecord('sys_user_grmember');
            grGroup.addQuery('group', 'sys_id of group');
            grGroup.addQuery('user', gA[column]);
            if (!grGroup.hasNext()) {
                var approver = gA[column];
                //var approverString = approver.toString();
                //var approver = gA[column].toString();
                //gs.print('approver: ' + approver);
                approvers.push(approver.toString());
            }
        }
    }
}
gs.print('approvers: ' + approvers);
var uniqueApproversArray = new ArrayUtil();
var uniqueApprovers = uniqueApproversArray.unique(approvers.toString().split(','));
var approversArrayStrings = uniqueApprovers.toString().split(',');
//gs.print('approversArrayStrings: ' + approversArrayStrings)
for (var y = 0; y < approversArrayStrings.length; y++) {
    var uniqueApprover = approversArrayStrings[y];
    var grGroupAdd = new GlideRecord('sys_user_grmember');
    grGroupAdd.initialize();
    grGroupAdd.group = 'sys_id of group'; //
    grGroupAdd.user = uniqueApprover;
    //gs.print('uniqueApprover: ' + uniqueApprover);
    //grGroupAdd.insert()
}
If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
✨ Certified Technical Architect || ✨ 10x ServiceNow MVP || ✨ ServiceNow Community Leader

View solution in original post