Hi guys, here is my lastest script. It adds/removes users from google groups. Feel free to use it as you wish.
var RatatoskSheet = SpreadsheetApp.getActiveSpreadsheet();
// -- ADD USER TO GROUP -- Set trigger to onedit -- //
function addUsertoGroup(e) {
var sheet = e.source.getActiveSheet();
if (sheet.getName() === 'AddUser') { //Hinders edits on other sheets
var userData = SpreadsheetApp.getActive().getSheetByName('AddUser');
var userEmail = userData.getRange(2, 1).getValue(); //Gets data from AddUser.A2
var groupId = userData.getRange(2, 2).getValue(); //Gets data from cell B2
var newMember = {email: userEmail, role: "MEMBER"};
AdminDirectory.Members.insert(newMember, groupId); // Adds new member to a Google group
var groupData = SpreadsheetApp.getActive().getSheetByName('GroupAddress');
var groupTwo = [userEmail, groupId]
groupData.appendRow(groupTwo); //Add member and group to GroupAddress
var header = ['UserEmail', 'GroupID'];
userData.clear(); //Reset AddUser (Delete all)
userData.appendRow(header).setFrozenRows(1);
}
}
// -- REMOVE USER FROM GROUP -- Set trigger of this function to onedit -- //
function deleteGroupMember(e) {
var sheet = e.source.getActiveSheet();
if (sheet.getName() === 'RemoveUser') { //Hinders edits on other sheets
var RemoveUserData = SpreadsheetApp.getActive().getSheetByName('RemoveUser');
var groupData = SpreadsheetApp.getActive().getSheetByName('GroupAddress');
var userEmail = RemoveUserData.getRange(2, 1).getValue(); //Gets data from RemoveUser.A2
var groupId = RemoveUserData.getRange(2, 2).getValue(); //Gets data from RemoveUser.B2
AdminDirectory.Members.remove(groupId, userEmail); //Removes member from a Google group
var removeDataValues = RemoveUserData.getDataRange().getValues();
var groupDataValues = groupData.getDataRange().getValues();
var resultArray = [];
for(var n in groupDataValues){ //
var keep = true
for(var p in removeDataValues){
if( groupDataValues[n][0] == removeDataValues[p][0] && groupDataValues[n][1] == removeDataValues[p][1]){
keep=false ;
break ;
}
}
if(keep){ resultArray.push(groupDataValues[n])};
}
var start = 2; //Starts from Row 2 //
var killTheRows = groupData.getLastRow() - start + 1; // // These lines deletes all rows in GroupAddress
groupData.deleteRows(start, killTheRows); //Delete all rows with values//
groupData.getRange(2,1,resultArray.length,resultArray[0].length).setValues(resultArray); //Repopulate the rows in GroupAddress
var header = ['UserEmail', 'GroupID'];
RemoveUserData.clear();
RemoveUserData.appendRow(header).setFrozenRows(1);
}
}
// -- LISTS ALL GROUPS AND USERS WITHIN THEM -- Set this as a timed trigger to error correct once a day -- //
function listAllGroups() {
var grouprows= [];
var pageToken;
var page;
do { page = AdminDirectory.Groups.list({ domain: 'YOURDOMAIN', maxResults: 100, pageToken: pageToken});
var groups = page.groups;
if (groups){ for (var i = 0; i < groups.length; i++) {
var group = groups[i];
var grouprow = group.email;
grouprows.push(grouprow)
}
}
pageToken = page.nextPageToken;
} while (pageToken);
var rows = [];
var pageToken, page2;
for (var j = 0; j < grouprows.length; j++) {
do { page2 = AdminDirectory.Members.list(grouprows[j],{ domainName: 'YOURDOMAIN', maxResults: 500,pageToken: pageToken, });
var members = page2.members;
if (members){ for (var i = 0; i < members.length; i++) {
var member = members[i];
var row = [member.email,grouprows[j]];
rows.push(row);}
}
pageToken = page2.nextPageToken;
} while (pageToken);
if (rows.length > 1){
var groupData = RatatoskSheet.getSheetByName("GroupAddress");
var header = ['UserEmail', 'GroupID'];
groupData.clear();
groupData.appendRow(header).setFrozenRows(1);
groupData.getRange(2, 1, rows.length, header.length).setValues(rows);
}
}
groupData.deleteRow(2); //NB! Removes first group(all@yourdomain.com) Make this whole line a comment if unsure.
}
Thank you for sharing this, Ratatosk! I found the listAllGroups function very helpful.