0

My goal is to use a google sheet to add and delete members of a particular group. Adding was not an issue but I'm running into issues comparing the two arrays so that I can delete email addresses that belong to the group but not the sheet. Please let me know if there is another avenue to pursue.

I've attempted to write code that will loop through the users in the group, then another loop to get the email addresses from a sheet. The comparison works but only when there is a match on both sides. If I swap the logic to look for addresses that are missing, false positives appear as the array compares the values and they do not match.

Here is what I have so far:

function listGroupMembers() {
  var GROUP_EMAIL = "group@group.com";
  var group = GroupsApp.getGroupByEmail(GROUP_EMAIL);
  var users = group.getUsers();

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("main list");
  var emailsFromSheet = sheet.getRange(1,7,sheet.getLastRow()-1,1).getValues();
  var group = GroupsApp.getGroupByEmail(GROUP_EMAIL);

  console.log(users.length);
  console.log(emailsFromSheet.length);
  for (i=0;i<users.length;i++) {
    
    var user = users[i];

    for (j=0;j<emailsFromSheet.length;j++) {
      var emailStuff = emailsFromSheet[j];
      if (user.getEmail() == emailStuff) {
          console.log(user.getEmail() + " " + emailStuff);
        }
      }
    }
  }
Rubén
  • 34,714
  • 9
  • 70
  • 166
someguy
  • 3
  • 2
  • Does this answer your question? [Javascript algorithm to find elements in array that are not in another array](https://stackoverflow.com/questions/2963281/javascript-algorithm-to-find-elements-in-array-that-are-not-in-another-array) – Kos Aug 19 '22 at 20:31

1 Answers1

0

Members of a Group

function listGroupMembers() {
  var GROUP_EMAIL = "group@group.com";
  var group = GroupsApp.getGroupByEmail(GROUP_EMAIL);
  var us = group.getUsers();
  const usem = us.map(u => u.getEmail()).flat();
  var sh = SpreadsheetApp.getActive().getSheetByName("main list");
  var em = sh.getRange(1,7,sh.getLastRow()).getValues().flat();
  em.forEach(e => {
    let idx = usem.indexOf(e)
    if(~idx) {
      Logger.log(`${e} is a member of ${GROUP_EMAIL}` )
    } else {
      Logger.log(`${e} is not a member of ${GROUP_EMAIL}` )
    }
  })
}

Maybe this will work:

function listGroupMembers() {
  var GROUP_EMAIL = "group@group.com";
  var group = GroupsApp.getGroupByEmail(GROUP_EMAIL);
  var us = group.getUsers();
  const usem = us.map(u => u.getEmail()).flat();
  var sh = SpreadsheetApp.getActive().getSheetByName("main list");
  var em = sh.getRange(1,7,sh.getLastRow()).getValues().flat();
  usem.forEach(e => {
    let idx = em.indexOf(e)
    if(~idx) {
      Logger.log(`${e} is a member of usem list` )
    } else {
      Logger.log(`${e} is not a member of usem list` )
    }
  })
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • I'll look around for more info on maps, I receive the following error when running the code: ReferenceError: r is not defined – someguy Aug 19 '22 at 22:05
  • Yeah it should have been u – Cooper Aug 19 '22 at 22:13
  • Awesome, thanks! It's very likely I didn't describe my goal appropriately. I want to do the opposite of what is printed out. I would like to check the group against the sheet. On the forEach loop it seems like I can swap em for usem and usem for em. – someguy Aug 19 '22 at 22:29
  • In testing it looks like it does work. Thanks! I just need to fix the text as it's all case sensitive. – someguy Aug 19 '22 at 23:00