Python script to parse the GAM / Google Workspace CSV that contains all members of all groups
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.
# 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()
