Apps Script - Google Groups - List all groups and users + Add/Remove User

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. 
     
   
   
}
4 Likes