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: