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

Google Apps Account
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.

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

Now let’s set up the script.

Open the script editor tool by using going to tools > script editor.
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