javascriptgoogle-apps-scriptgoogle-sheetsgoogle-groupsgoogle-groups-api

Getting Google Groups for a list of users

I have a Google Spreadsheet with 2 Sheets in it (Input and Output).
The Input sheet contains a list of user email addresses in column A (with A1 being a header).

I want to iterate through the list of users and pull a list of Google groups that each user is in, then dump that data into the Output sheet. With Column A being their email address, and B,C,D etc containing their groups.

So something like:

╔═══════════════════╦════════════════════╦════════════════════╦════════════════════╦════════════════════╗
║       User        ║    User Groups     ║                    ║                    ║                    ║
╠═══════════════════╬════════════════════╬════════════════════╬════════════════════╬════════════════════╣
║ user@example.com  ║ group1@example.com ║ group2@example.com ║ group3@example.com ║ group4@example.com ║
║ user2@example.com ║ group4@example.com ║ group2@example.com ║                    ║                    ║
║ user3@example.com ║ group5@example.com ║ group1@example.com ║ group3@example.com ║                    ║
╚═══════════════════╩════════════════════╩════════════════════╩════════════════════╩════════════════════╝

So far, this is what I have:

function getUserGrps(){
  var ssID = "spreadsheetID"
  var ss = SpreadsheetApp.getActiveSpreadsheet() || SpreadsheetApp.openById(ssID)
  var inputSheet = ss.getSheetByName("Input")
  var outputSheet = ss.getSheetByName("Output")
  var groups = []
  
  var userList = inputSheet.getDataRange().offset(1, 0).getValues()
  userList.pop()
  
  userList.forEach(function(user){
    var response = AdminDirectory.Groups.list({userKey: user})
    var userGroups = response.groups
    userGroups.forEach(function(group){
      Logger.log(group.name)
    })
  })
}

I have tried the following:

userList.forEach(function(user){
    var response = AdminDirectory.Groups.list({userKey: user})
    var userGroups = response.groups
    userGroups.forEach(function(group){
      groups.push(group.name)
    })
  })

And then dumping that output, but there are a few issues with it. Firstly, it just dumps in in column A (as a list). Secondly, I'm struggling to find a way to concat/add the user email address to the list of groups.

Basically, I'm having a total brain fart, and any guidance would be much appreciated.

Solution

  • There are lots of ways to add something to the top of an array. I think what you're trying to do lends itself to creating a new array using map() and array concatenation (I used the spread syntax).

    function getUserGrps(){
  const ssID = "spreadsheetID";
  const ss = SpreadsheetApp.getActiveSpreadsheet() || SpreadsheetApp.openById(ssID);
  const inputSheet = ss.getSheetByName("Input");
  const outputSheet = ss.getSheetByName("Output");
  let groups = [];
  
  const userList = inputSheet.getDataRange().offset(1, 0).getValues();
  userList.pop();
  
  let maxGroups = 0;
  userList.forEach(function(user) {
    const userEmail = user[0];
    const response = AdminDirectory.Groups.list({userKey: userEmail});
    const userGroups = response.groups ? response.groups.map(group => group.email) : [];
    groups.push([userEmail, ...userGroups]);
    
    // Set the maximum number of groups
    if (userGroups.length > maxGroups)
      maxGroups = userGroups.length;
  });
  
  // When printing a table with setValues(), every row needs to have the same length
  const numColumns = maxGroups + 1;
  const table = groups.map(function(row) {
    if (row.length < numColumns) {
      return [...row, ...new Array(numColumns - row.length)];
    }
    return row;
  });
  
  outputSheet.getRange(1, 1, table.length, table[0].length).setValues(table);
}

    Suggested Reading: