The Problem

As a Google Group admin, you have an option of exporting a list of group members as a CSV file. However, the exported CSV file does not contain nested members. In other words, if a group A has another group B as a member, the members under group B are not exported. If group B contains yet another group C, members of group C are not exported either.

Google group export member list option
Google group export member list option

The Solution

The following open source Google Apps Script can export your all the members of a Google Group including nested groups into a spreadsheet in your Drive root folder.

Instructions

  1. Open https://script.google.com
  2. Click New project.
  3. Name the script project in the title
  4. Copy and paste the following script to the script editor, overwriting existing function myFunction() {} code
  5. Change the groupEmail variable to the primary email of your Google Group.
  6. Click Services in the left navigation panel
    1. Select Admin SDK API
    2. Click Add
  7. Click Save Google Apps Script project button in the toolbar
  8. In the toolbar, select the exportGroupMembers function
  9. Click Run
  10. It will ask for your permissions in the first run
  11. After you grant the permission, the script shall run.
  12. Once the script is done, the member list is saved into your Drive root folder with the group email as the file name.
    Exported member list of Google Group

Source code

/**
 * Export all group members including nested groups
 * into a spreadsheet in the your Drive root folder
 * Usage:
 * 1. Copy and paste the script to your Apps Script Editor
 * 2. Change the 'groupEmail' variable to your group email
 * 3. Add the Admin SDK service
 * 4. Click 'Run'.
 *
 * © 2021 xFanatical, Inc.
 * @license MIT
 * @version 1.0.0 proof of concept
 */
const groupEmail = ''

function exportAsSheet(data) {
  SpreadsheetApp.create(groupEmail).getSheets()[0].setName('members').getRange(1, 1, data.length, data[0].length).setValues(data)
  Logger.log(`members exported to ${groupEmail} in your Drive root folder`)
}

function exportGroupMembers() {
  let pageToken
  let page
  do {
    page = AdminDirectory.Members.list(groupEmail, {
      maxResults: 200,
      includeDerivedMembership: true,
      pageToken,
      fields: 'members(email,role,type),nextPageToken',
    })
    let { members } = page
    members.sort((a, b) => a.email.localeCompare(b.email))
    if (members) {
      members.forEach((member) => {
        Logger.log(`Member email: ${member.email}, type: ${member.type}, role: ${member.role}`)
      })
      exportAsSheet(members.reduce((arr, member, index) => {
        arr.push([member.email, member.role, member.type])
        return arr
      }, [['Email', 'Role', 'Type']]))
    } else {
      Logger.log('No group members found.')
    }
    pageToken = page.nextPageToken
  } while (pageToken)
}

If you're interested in extending functions or automating your tasks as much as possible on Google Workspace, please check out our code-free workflow automation software Foresight. We also provide custom apps script development services to increase your productivity. Please don't hesitate to contact us. It's our passion and expertise to assist you on Google Workspace.