Google Workspace上で「誰」が「どの」MLに所属しているのか一覧でマトリクス表示するGAS

何のために作るのか

超久しぶりに記事書きます＆Zenn登録しました。
これはGoogle Workspace(以降GWS)管理者の皆さんかゆいとこに届きそうかなーと思い作りました。⚪︎⚪︎さんって⚪︎⚪︎のML（メーリングリスト）に所属してたっけ？っていう問い合わせが多かったために、誰でもみられるマトリクス一覧（⚪︎が付いている＝所属している）を作りたかったのが当初の目的です。
※GWS管理者用に作成しており、一般的に見せる用には記載してませんので悪しからず。

何ができる？

タイトル通りですが、GWS上で「誰」が「どの」MLに所属しているのか一覧でマトリクス表示するGASになります。

用意するもの

GWSの管理者権限を持つアカウント

手順

1.新規スプレッドシートを作成する。
2.拡張機能からApps Scriptを選択する。

3.左ペインのファイル→コード.gsに、以下コードをコピペする。
一箇所あなたが使用している@付のドメイン名に変更し上書き保存。
例：const DOMAIN_TO_REMOVE = "⭐︎";
　"⭐︎";　→ "@hogehoge.jp";

ここをクリックするとコードが表示されます

// --- 定数 ---
// ユーザー名を出力する列 (B列 = 2列目)
const USERNAME_OUTPUT_COLUMN = 2;
// MLリストを出力し始める列 (C列 = 3列目)
const ML_LIST_START_COLUMN = 3;
// データ出力開始行 (最初のユーザー名の行と同じ)
const DATA_OUTPUT_START_ROW = 3;
// 削除するドメイン
const DOMAIN_TO_REMOVE = "⭐︎";

/**
* スプレッドシートを開いたときにカスタムメニューを追加します。
*/
function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('グループ確認 (管理者用)')
      .addItem(`GWSアカウントをB列に取得し、所属グループを⚪︎で表示。`, 'runListGroupsWithCircle')
      .addToUi();
}

/**
* カスタムメニューから呼び出され、GWSアカウントをB列に取得し、所属グループを⚪︎で横並び表示します。
*/
function runListGroupsWithCircle() {
  const ui = SpreadsheetApp.getUi();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // B2セルに"ML/名前"と記載
  sheet.getRange("B2").setValue("ML/名前");

  ui.alert(`処理開始: GWSアカウントをB列に取得し、所属グループを⚪︎で表示します。`);

  // --- B列の既存の内容とC列以降の内容をクリア ---
  const lastRowBeforeClear = sheet.getLastRow(); // クリア前の最終行を取得
  if (lastRowBeforeClear >= DATA_OUTPUT_START_ROW) {
    sheet.getRange(DATA_OUTPUT_START_ROW, USERNAME_OUTPUT_COLUMN, lastRowBeforeClear - DATA_OUTPUT_START_ROW + 1, sheet.getLastColumn() - USERNAME_OUTPUT_COLUMN + 1).clearContent();
    // 罫線もクリア
    sheet.getRange(DATA_OUTPUT_START_ROW - 1, USERNAME_OUTPUT_COLUMN, lastRowBeforeClear - (DATA_OUTPUT_START_ROW - 1) + 1, sheet.getLastColumn() - USERNAME_OUTPUT_COLUMN + 1).setBorder(false, false, false, false, false, false);
  }

  let allUsers = [];
  let nextPageTokenUsers;
  let allGroups = []; // すべてのグループ（ML）を格納する配列
  let nextPageTokenGroupsAll;

  // --- GWSのすべてのグループ（ML）を取得し、シートのC3列以降に横並びで出力 ---
  do {
    try {
      const groupsResponseAll = AdminDirectory.Groups.list({
        domain: Session.getActiveUser().getEmail().split('@')[1],
        maxResults: 200,
        pageToken: nextPageTokenGroupsAll,
        fields: 'nextPageToken, groups(email)'
      });

      if (groupsResponseAll && groupsResponseAll.groups) {
        allGroups = allGroups.concat(groupsResponseAll.groups.map(group => group.email.replace(DOMAIN_TO_REMOVE, '')));
      }

      nextPageTokenGroupsAll = groupsResponseAll.nextPageToken;

    } catch (error) {
      Logger.log(`全グループリスト取得エラー: ${error}`);
      ui.alert(`エラー: GWSのグループリスト取得に失敗しました。\n${error}`);
      return;
    }
  } while (nextPageTokenGroupsAll);

  // MLリストをシートのヘッダー行に出力
  if (allGroups.length > 0) {
    sheet.getRange(DATA_OUTPUT_START_ROW - 1, ML_LIST_START_COLUMN, 1, allGroups.length).setValues([allGroups]);
  }

  // --- GWSのすべてのアカウントを取得 ---
  do {
    try {
      const usersResponse = AdminDirectory.Users.list({
        domain: Session.getActiveUser().getEmail().split('@')[1],
        maxResults: 200,
        pageToken: nextPageTokenUsers,
        fields: 'nextPageToken, users(primaryEmail)'
      });

      if (usersResponse.users) {
        allUsers = allUsers.concat(usersResponse.users);
      }
      nextPageTokenUsers = usersResponse.nextPageToken;
    } catch (error) {

      Logger.log(`ユーザーリスト取得エラー: ${error}`);
      ui.alert(`エラー: GWSアカウントのリスト取得に失敗しました。\n${error}`);
      return;
    }
  } while (nextPageTokenUsers);

  Logger.log(`取得したアカウント数: ${allUsers.length}`);

  // データ出力用の配列を準備
  const usernamesToOutput = [];
  const groupMembershipData = [];

  // --- 各アカウントの所属グループを取得してシートに出力 (⚪︎で表示) ---
  for (let i = 0; i < allUsers.length; i++) {
    const userEmail = allUsers[i].primaryEmail;
    const usernamePart = userEmail.split('@')[0];
    usernamesToOutput.push([usernamePart]); // B列用データ

    let userGroups = [];
    let nextPageTokenUserGroups;
    let attempts = 0;
    const maxAttempts = 3;

    // --- Admin SDK でユーザーの所属グループを取得 ---
    do {
      try {
        const groupsResponseUser = AdminDirectory.Groups.list({
          userKey: userEmail,
          maxResults: 200,
          pageToken: nextPageTokenUserGroups
        });

        if (groupsResponseUser && groupsResponseUser.groups) {
          userGroups = userGroups.concat(groupsResponseUser.groups.map(group => group.email.replace(DOMAIN_TO_REMOVE, '')));
        }
        nextPageTokenUserGroups = groupsResponseUser.nextPageToken;
        attempts = 0;
      } catch (apiError) {
        if ((apiError.message.includes('Rate Limit Exceeded') || apiError.message.includes('User Rate Limit Exceeded')) && attempts < maxAttempts) {
          attempts++;
          Logger.log(`APIレート制限エラー (ユーザー: ${userEmail})。${attempts * 5}秒待機してリトライします... (${attempts}/${maxAttempts})`);
          Utilities.sleep(attempts * 5000);
        } else {
          Logger.log(`グループリスト取得エラー (ユーザー: ${userEmail}): ${apiError}`);
          nextPageTokenUserGroups = null; // ループを抜ける
        }
      }
    } while (nextPageTokenUserGroups);

    // --- 所属グループを⚪︎で表示 ---
    const outputRow = [];
    for (let j = 0; j < allGroups.length; j++) {
      if (userGroups.includes(allGroups[j])) {
        outputRow.push("⚪︎");
      } else {
        outputRow.push("");
      }
    }
    groupMembershipData.push(outputRow); // C列以降用データ

    // 処理の進捗をログに記録 (アカウント数が多い場合に便利)
    if ((i + 1) % 10 === 0) {
      Logger.log(`${i + 1} / ${allUsers.length} アカウントの処理が完了`);
    }
  }

  // 一括でシートに書き込み
  if (usernamesToOutput.length > 0) {
    sheet.getRange(DATA_OUTPUT_START_ROW, USERNAME_OUTPUT_COLUMN, usernamesToOutput.length, 1).setValues(usernamesToOutput);
  }
  if (groupMembershipData.length > 0 && allGroups.length > 0) {
    sheet.getRange(DATA_OUTPUT_START_ROW, ML_LIST_START_COLUMN, groupMembershipData.length, allGroups.length).setValues(groupMembershipData);
  }

  // --- 罫線の設定 ---
  // データがある場合のみ罫線を引く
  if (allUsers.length > 0) {
    const startRow = DATA_OUTPUT_START_ROW - 1; // ヘッダー行も含む
    const endRow = DATA_OUTPUT_START_ROW + allUsers.length - 1;
    const startCol = USERNAME_OUTPUT_COLUMN;
    const endCol = ML_LIST_START_COLUMN + allGroups.length - 1;

    // 取得したグループがない場合（allGroups.lengthが0）はMLリストの列はUsernameの列までとする
    const actualEndCol = (allGroups.length > 0) ? ML_LIST_START_COLUMN + allGroups.length - 1 : USERNAME_OUTPUT_COLUMN;

    // 罫線を適用する範囲
    const rangeToBorder = sheet.getRange(startRow, startCol, endRow - startRow + 1, actualEndCol - startCol + 1);

    // 範囲内のすべてのセルに罫線を引く
    rangeToBorder.setBorder(true, true, true, true, true, true);
  }
  // --- 罫線の設定ここまで ---

  ui.alert(`完了: GWSアカウントをB列に取得し、所属グループを⚪︎で表示しました 。`);
}

4.左ペインのサービス内で＋で追加し、Admin SDK APIを選択後、追加を押下します。

5.App Scripts側の設定は完了したので、スプシに戻ります。
　再読み込みを行うと以下表示が増えているはずなので、「GWSアカウントをB列に〜」を押下します。

6.手順6-7はは初回のみになります。
認証が必要ですになりますので、OKを押下します。

7.管理者のアカウントであれば以下のような許可をする。

結果

以下のようなマトリクス表が表示されます。
200人以上も時間がかかるが、できる事は確認しています。

まとめ

誰がどのMLに入っているなどの一覧化、目視確認が簡単にできるようになりました。
MLの棚卸などで大活躍しております。