Extract a list of Google Group members into a Sheet

The following Google Apps Script is designed to extract a list of the current members of a Google Group (email address and role) into a Google Sheet. You simply enter the email address of the Google Group that you belong to and then run the Function.

The script starts by using the 'GroupsApp' to access the Group and then get a list of all the users:

var group = GroupsApp.getGroupByEmail('YOUR GROUP EMAIL ADDRESS HERE');
var members = group.getUsers();

Once we have an array (list) of all the members we need to cycle through each one and get their email address and role, as well as tidying up some of the formatting (eg setting everything to lowercase). Then we can push this detail into a new array (list) that we will later use to paste into our Sheet:

for (var i=0; i<membersLength; i++) {
      
   // get Email Address
   var memberEmailAddress = members[i].getEmail();
      
   // get Role and set as lowercase
   var memberRole = group.getRole(memberEmailAddress).toString().toLowerCase();
      
   // push details into array for later pasting into Google Sheet
   memberDetails.push([memberEmailAddress, memberRole]);
      
}

The final step is to get the relevant Google Sheet, clear any existing content and then paste in our collated members:

//get 'Group Members' sheet
var groupMembersSheet =
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Group Members');
    
// get last row of data
var lastRow = groupMembersSheet.getLastRow();
    
// clear current sheet content
groupMembersSheet.getRange(2, 1, lastRow, 2).clearContent();
    
// paste in collated members details from array
groupMembersSheet.getRange(2, 1, memberDetails.length, 2).setValues(memberDetails);

Extract a list of Google Group members into a Sheet download (please use 'File' > 'Make a copy' for your own version).