google workspace

Python script to parse the GAM / Google Workspace CSV that contains all members of all groups

 in Google / Programming / Python  tagged csv / dataframe / google / google workspace / panda / python / to_excel / xlsx / xlsxwriter by

This script will parse the data generated by GAM and create an XLSX out of them.

Each sheet will be named by the group, containing all the emails of that group, including the sub-groups.

emails.py_Download
# This script will parse the data generated by GAM and create an XLSX out of them.
# Each sheet will be named by the group and it will contain all the emails of that group, including the sub-groups.

# Using sys to get command line arguments for the input file
import sys
# Using CSV to parse the input CSV file that GAM (GAMADV-XTD3) created after using the following command:
# gam print group-members > emails.2022.csv;
# Source: https://bytefreaks.net/google/rough-notes-on-using-gam-to-print-all-members-of-all-groups-in-google-workspace
import csv
# Using pandas to create an XLSX file with multiple sheets
import pandas as pd

# Creating an empty dictionary.
dictionary = {}

# Opening the CSV file that is the first command line argument
with open(sys.argv[1], newline='') as csvfile:
    reader = csv.reader(csvfile, delimiter=',', quotechar='"')
    # For each row, we are getting only the first column which is the group and the last which is the email
    for row in reader:
        dictionary.setdefault(row[0], []).append(row[5])

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter(sys.argv[1]+'.xlsx', engine='xlsxwriter')

# Iterating in Sorted Order
for key in sorted(dictionary):
    # Create a Pandas dataframes to add the data.
    df = pd.DataFrame({'Members': dictionary[key]})
    # Write each dataframe to a different worksheet.
    # To avoid the following exception:
    # xlsxwriter.exceptions.InvalidWorksheetName: Excel worksheet name '[email protected]' must be <= 31 chars.
    # We are truncating the domain from the group.
    group = key.split("@")[0]
    # In case the name is still to big, we truncate it further and append an ellipsis to indicate the extra truncation
    sheet = (group[:29] + '..') if len(group) > 31 else group
    # We are also removing the header and index of the data frame
    df.to_excel(writer, sheet_name=sheet, header=False, index=False)

# Close the Pandas Excel writer and output the Excel file.
writer.close()
emails.py_Download

Rough notes on using GAM to print all Members of all Groups in Google Workspace 1

 in Google  tagged gam / gamadv / google / google workspace by

Since google failed to provide helpful UI for the administrators, we used GAMADV to print all members of all groups.

GAMADV-XTD3 6.30.10 - https://github.com/taers232c/GAMADV-XTD3 - pyinstaller
Ross Scroggs <[email protected]>
Python 3.11.0 64-bit final
Linux Ubuntu 22.04 Jammy Jellyfish x86_64
Path: /home/bib/bin/gamadv-xtd3
Config File: /home/bib/.gam/gam.cfg, Section: DEFAULT, customer_id: my_customer, domain:

To install:

bash <(curl -s -S -L https://git.io/fhZWP) -l;

To authorize:

gam user [email protected] check serviceaccount;

This created a link for the browser which we followed to authorize the API.
Example output:

System time status
  Your system time differs from admin.googleapis.com by less than 1 second  PASS
Service Account Private Key Authentication
  Authentication                                                            PASS
Service Account Private Key age; Google recommends rotating keys on a routine basis
  Service Account Private Key age: 350 days                                 WARN
Domain-wide Delegation authentication:, User: [email protected], Scopes: 28
  https://mail.google.com/                                                  FAIL (1/28)
  https://sites.google.com/feeds                                            FAIL (2/28)
  https://www.googleapis.com/auth/apps.alerts                               FAIL (3/28)
  https://www.googleapis.com/auth/calendar                                  FAIL (4/28)
  https://www.googleapis.com/auth/classroom.announcements                   FAIL (5/28)
  https://www.googleapis.com/auth/classroom.coursework.students             FAIL (6/28)
  https://www.googleapis.com/auth/classroom.courseworkmaterials             FAIL (7/28)
  https://www.googleapis.com/auth/classroom.profile.emails                  FAIL (8/28)
  https://www.googleapis.com/auth/classroom.rosters                         FAIL (9/28)
  https://www.googleapis.com/auth/classroom.topics                          FAIL (10/28)
  https://www.googleapis.com/auth/cloud-identity                            FAIL (11/28)
  https://www.googleapis.com/auth/cloud-platform                            FAIL (12/28)
  https://www.googleapis.com/auth/contacts                                  FAIL (13/28)
  https://www.googleapis.com/auth/contacts.other.readonly                   FAIL (14/28)
  https://www.googleapis.com/auth/datastudio                                FAIL (15/28)
  https://www.googleapis.com/auth/directory.readonly                        FAIL (16/28)
  https://www.googleapis.com/auth/documents                                 FAIL (17/28)
  https://www.googleapis.com/auth/drive                                     FAIL (18/28)
  https://www.googleapis.com/auth/drive.activity                            FAIL (19/28)
  https://www.googleapis.com/auth/drive.admin.labels                        FAIL (20/28)
  https://www.googleapis.com/auth/drive.labels                              FAIL (21/28)
  https://www.googleapis.com/auth/gmail.modify                              FAIL (22/28)
  https://www.googleapis.com/auth/gmail.settings.basic                      FAIL (23/28)
  https://www.googleapis.com/auth/gmail.settings.sharing                    FAIL (24/28)
  https://www.googleapis.com/auth/keep                                      FAIL (25/28)
  https://www.googleapis.com/auth/spreadsheets                              FAIL (26/28)
  https://www.googleapis.com/auth/tasks                                     FAIL (27/28)
  https://www.googleapis.com/auth/userinfo.profile                          FAIL (28/28)
Some scopes FAILED!
To authorize them, please go to the following link in your browser:

    https://admin.google.com/ac/owl/domainwidedelegation?clientScopeToAdd=https://mail.google.com/,https://sites.google.com/feeds,https://www.googleapis.com/auth/apps.alerts,https://www.googleapis.com/auth/calendar,https://www.googleapis.com/auth/classroom.announcements,https://www.googleapis.com/auth/classroom.coursework.students,https://www.googleapis.com/auth/classroom.courseworkmaterials,https://www.googleapis.com/auth/classroom.profile.emails,https://www.googleapis.com/auth/classroom.rosters,https://www.googleapis.com/auth/classroom.topics,https://www.googleapis.com/auth/cloud-identity,https://www.googleapis.com/auth/cloud-platform,https://www.googleapis.com/auth/contacts,https://www.googleapis.com/auth/contacts.other.readonly,https://www.googleapis.com/auth/datastudio,https://www.googleapis.com/auth/directory.readonly,https://www.googleapis.com/auth/documents,https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/drive.activity,https://www.googleapis.com/auth/drive.admin.labels,https://www.googleapis.com/auth/drive.labels,https://www.googleapis.com/auth/gmail.modify,https://www.googleapis.com/auth/gmail.settings.basic,https://www.googleapis.com/auth/gmail.settings.sharing,https://www.googleapis.com/auth/keep,https://www.googleapis.com/auth/spreadsheets,https://www.googleapis.com/auth/tasks,https://www.googleapis.com/auth/userinfo.profile,https://www.googleapis.com/auth/userinfo.email&clientIdToAdd=101337532152182481803&overwriteClientId=true&dn=bytefreaks.net&authuser=bob@bytefreaks.net

You will be directed to the Google Workspace admin console Security > API Controls > Domain-wide Delegation page
The "Add a new Client ID" box will open
Make sure that "Overwrite existing client ID" is checked
Click AUTHORIZE
When the box closes you're done
After authorizing it may take some time for this test to pass so wait a few moments and then try this command again.

After authorizing, we executed again to verify that all tests passed:

System time status
  Your system time differs from admin.googleapis.com by 1 second            PASS
Service Account Private Key Authentication
  Authentication                                                            PASS
Service Account Private Key age; Google recommends rotating keys on a routine basis
  Service Account Private Key age: 350 days                                 WARN
Domain-wide Delegation authentication:, User: [email protected], Scopes: 28
  https://mail.google.com/                                                  PASS (1/28)
  https://sites.google.com/feeds                                            PASS (2/28)
  https://www.googleapis.com/auth/apps.alerts                               PASS (3/28)
  https://www.googleapis.com/auth/calendar                                  PASS (4/28)
  https://www.googleapis.com/auth/classroom.announcements                   PASS (5/28)
  https://www.googleapis.com/auth/classroom.coursework.students             PASS (6/28)
  https://www.googleapis.com/auth/classroom.courseworkmaterials             PASS (7/28)
  https://www.googleapis.com/auth/classroom.profile.emails                  PASS (8/28)
  https://www.googleapis.com/auth/classroom.rosters                         PASS (9/28)
  https://www.googleapis.com/auth/classroom.topics                          PASS (10/28)
  https://www.googleapis.com/auth/cloud-identity                            PASS (11/28)
  https://www.googleapis.com/auth/cloud-platform                            PASS (12/28)
^[[C  https://www.googleapis.com/auth/contacts                                  PASS (13/28)
  https://www.googleapis.com/auth/contacts.other.readonly                   PASS (14/28)
  https://www.googleapis.com/auth/datastudio                                PASS (15/28)
  https://www.googleapis.com/auth/directory.readonly                        PASS (16/28)
  https://www.googleapis.com/auth/documents                                 PASS (17/28)
  https://www.googleapis.com/auth/drive                                     PASS (18/28)
  https://www.googleapis.com/auth/drive.activity                            PASS (19/28)
  https://www.googleapis.com/auth/drive.admin.labels                        PASS (20/28)
  https://www.googleapis.com/auth/drive.labels                              PASS (21/28)
  https://www.googleapis.com/auth/gmail.modify                              PASS (22/28)
  https://www.googleapis.com/auth/gmail.settings.basic                      PASS (23/28)
  https://www.googleapis.com/auth/gmail.settings.sharing                    PASS (24/28)
  https://www.googleapis.com/auth/keep                                      PASS (25/28)
  https://www.googleapis.com/auth/spreadsheets                              PASS (26/28)
  https://www.googleapis.com/auth/tasks                                     PASS (27/28)
  https://www.googleapis.com/auth/userinfo.profile                          PASS (28/28)
All scopes PASSED!

Service Account Client name: 101337532152182481803 is fully authorized.

Finally, we executed the following to create a CSV file that contained all members of all groups:

gam print group-members > emails.2022.csv;