Why is this nested loop not working in Google Apps Script (Sheets/Admin) and throwing no errors?

Question

I'm trying to find a streamlined way to keep members in about 100 Google groups up to date from info already in a Google Sheets workbook. One sheet has a list of 1700 students w/ columns for student email and group email. Another sheet contains a list of just the group names and the group emails. Based on the codes I found on it seems possible with Google apps script. Unfortunately, I have no previous experience with Apps Script and minimal experience with javascript. I've been trying to piece together a process that might look like this.

1. Deleting all current members of the groups.
2. Uploading the correct members.
3. Rinse and repeat.

Not the most elegant, but it seemed workable.

This is what I've done so far and I've hit a wall.

I found the combination of these 2 functions will remove all members from 1 group:

//Function to Remove 1 group member from 1 group
  function removeGroupMember(groupEmail, userEmail) {
  Logger.log(userEmail)
  userEmail = userEmail.trim();
  AdminDirectory.Members.remove(groupEmail, userEmail);
}
//Remove all Members from 1 group
function removeAllMembers() {
  var groupEmail = '[email protected]';
  var members = AdminDirectory.Members.list(groupEmail).members;

  for (var m in members) {
    var member = members[m];
    var email = member.email;
    removeGroupMember(groupEmail, email)
  }
}

I thought I could nest another loop to pair it with this code that grabs the group emails from the sheet.

//get all of the groups from the spreadsheet
function getGroupsFromSpreadsheet(){
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName('google_groups');
  const data = sheet.getRange(2,2,100).getValues();
  return data;
  logger.log(data)
}

This is what I came up with after a few trial and errors. It runs from the script editor and the execution log says it's successful, but then nothing actually happens in Google Admin - they're all still in the groups.

function removeAllMembers() {
  var groups =  [getGroupsFromSpreadsheet()];
  var groupEmail =  [getGroupsFromSpreadsheet()];
  var members = AdminDirectory.Members.list(groupEmail).members;
  
  for (var element in groups) {
  
    for (var m in members) {
      var member = members[m];
      var email = member.email;
      removeGroupMember(groupEmail, email);
    }
  }
}

Answer 1

Untested as I don't have a G Suite account. Have never worked with AdminDirectory API, so I don't know if below is the best way to do this.

---

function* getGroupEmailsFromSpreadsheet() {
  // Should allow caller to access each "group" with a 
  // simple for-of loop. Generator used here, but could
  // have used anything that would flatten the nested array
  // that Range.getValues() returns.
  
  const ss = SpreadsheetApp.GetActiveSpreadsheet();
  const sheet = ss.getSheetByName('google_groups');
  const data = sheet.getRange(2, 2, 100).getValues();
  
  for (let rowIndex = 0; rowIndex < data.length; ++rowIndex) {
    for (let columnIndex = 0; columnIndex < data[0].length; ++columnIndex) {
      // Maybe do some validation here e.g. making sure email is a string, contains an "@" character
      yield data[rowIndex][columnIndex];
    }
  }
}


function* getAllMembersInGroup(groupKeyOrEmail) {
  // Should allow caller to access each "member" with a simple
  // for-of loop. Generator used here, so that call site
  // doesn't need to deal with handling pagination.
  
  const options = {
    maxResults: 200
  };
  
  // https://developers.google.com/admin-sdk/directory/v1/reference/members/list#request
  let groupMembers = AdminDirectory.Members.list(groupKeyOrEmail, options);
  do {
    for (const member of groupMembers.members) {
      yield member;
    }
    options.pageToken = groupMembers.nextPageToken;
    groupMembers = AdminDirectory.Members.list(groupKeyOrEmail, options)
  } while (groupMembers.nextPageToken);
}


function removeAllMembersFromAllGroups() {
  // Should remove all "members" in the "groups" on the sheet.
  for (const groupEmail of getGroupEmailsFromSpreadsheet()) {
    for (const member of getAllMembersInGroup(groupEmail)) {
      Logger.log(`Going to try and remove member ${member.email} from group ${groupEmail}.`);
      AdminDirectory.Members.remove(groupEmail, member.email);
    }
  }
}

---

Before running the removeAllMembersFromAllGroups function:

1. It would be good if you randomly picked one group (someGroup) from your worksheet, randomly picked one member in that group (someMember) and then create/run a temporary function containing AdminDirectory.Members.remove(someGroup, someMember) -- except replace someGroup and someMember with the actual values you've chosen. Then check if that member has actually been removed from the group. This should prove whether the code can actually delete members from groups (or whether there is a problem even with this simple scenario).
2. Double-check that the script has all necessary permissions granted.
3. Then set up a breakpoint inside the do-while loop in getAllMembersInGroup as the do-while loop is untested. Then debug the removeAllMembersFromAllGroups function. You'll want to check that the getAllMembersInGroup generates a finite sequence of "members" (and doesn't loop forever).

Answer 2

The issue in the code you have is in fact caused my the groups and groupEmail variables in the code. Therefore, I suggest you try this:

function getGroups() {
   var ss = SpreadsheetApp.openById('SPREADSHEET_ID').getSheetByName('SHEET_NAME');
   var groups = ss.getRange(2, 2, 2, 100).getValues();
   return groups;
}

function removeGroupMember(groupEmail, userEmail) {
   userEmail = userEmail.trim();
   AdminDirectory.Members.remove(groupEmail, userEmail);
}

function removeAllMembers() {
   var groups = getGroups();
   for (var i = 0; i < groups.length; i++) {
      var members = AdminDirectory.Members.list(theGroups[i][0]).members;
      for (var j = 0; j < members.length; j++) 
         removeGroupMember(theGroups[i][0], members[j].email);
   }
}

Reference

• Range Class Apps Script - getValues;

• SpreadsheetApp Class Apps Script - openById() .