Get Groups list of user with their roles with apps script

Whenever any bulk requirement comes, we think, there should be automated way for this. In google, google has apps script which can be used to automate the requirements. today we will see an apps script, where you can extract the groups of a particular user with their roles.

Pre-requisite

1. Google Apps Account
2. User account should be Super-admin account – as it uses admin directory API.

Set Up the Script

Let’s set up the script in your account.

If you don’t have any idea of Google apps script, no need of worrying, We are going to set up this from scratch.

Follow the below steps to set up the script.

1. Open your google drive.
2. Create a new spreadsheet named “Group detail” (or any name you want).
3. Create a first column header as ‘User’s email’

Now let’s set up the script.

1.  Open the script editor tool by using going to tools > script editor.
2. Copy paste the whole below script on this page.

function onOpen()
{
 SpreadsheetApp.getUi().createMenu('Fetch Groups').addItem('fetch','fetchdetails1').addToUi();
}
function fetchdetails1()
{
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var data = ss[0].getDataRange().getValues();
ss[0].clear();
var header = ["User's email",'User Id','Group Email','Group Name','User Role','User Status', 'Error'];
ss[0].appendRow(header).setFrozenRows(1);

for(var i=1; i< data.length; i++)
{
try
{
var user = data[i];
var row = [];
var groupPage = AdminDirectory.Groups.list({"userKey":user})
var groups = groupPage.groups;
if(groups)
{
for(var j in groups)
{
try
{
var groupdetail = groups[j];
var group = groupdetail.email;
var rows= [user, groupdetail.email, groupdetail.name];
row.push(rows);
}
catch(e)
{
ss[0].getRange(i+1,4).setValue("Error").setNote(e.message).clearNote();
ss[0].getRange(i+1,5).setValue(e.message);
}
}
}

var rowlength = ss[0].getLastRow();
ss[0].getRange(rowlength+1, 2, row.length, 3).setValues(row);
SpreadsheetApp.flush();
}

catch(e)
{
ss[0].getRange(i+1,8).setValue("Error").setNote(e.message).clearNote();
ss[0].getRange(i+1,9).setValue(e.message);
SpreadsheetApp.flush();
}
ss[0].getRange(i+1,1).setValue(user);
}
fetchroles1();

}

function fetchroles1()
{
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var data = ss[0].getDataRange().getValues();
Logger.log(data);

for(var i=1; i < data.length;i++)
{
try
{
var user = data[i][1];
var group = data[i][2];

var member = AdminDirectory.Members.get(group, user);
var role = member.role;
var status = member.status;

ss[0].getRange(i+1, 5).setValue(role);
ss[0].getRange(i+1, 6).setValue(status);
}
catch(e)
{
ss[0].getRange(i+1, 7).setValue(e.message);
}
}
}

It should look like this

How to Work with Script

Fill the User’s email address in the 1st column and make sure that other columns do not have anything.

Now refresh your sheet, You can see an option called ‘Fetch Groups’ in the menu.

Click on the option ‘Fetch groups > fetch’ from menu.

And it will fetch you the groups for users and their respective roles in the group.

NOTE: If you are running it for next time then make sure that you remove all other columns except the 1st column with user’s email address

You are done. if you have any doubts, feel free to ask in the comments section.