Zenn
レスキューナウテックブログPublicationへの投稿
📌

【GAS】全てのGoogleグループのメンバーとプロパティのリストをスプレッドシートへ抽出する

ryana
2023/09/12に公開
GAS
Google Workspace
tech

背景

当社ではGoogleグループを全て情シスで管理していましたが、一部のグループの管理権限をグループオーナーに移管することになったため現在準備を進めています。
ただ管理者としては移管するにしても野放しにするわけにはいかないので、メンバーや権限、特定のプロパティの設定情報を任意のタイミングでリストにして確認できるようにしておきたいと思い、調べてみることにしました。

取得したい情報の条件

  • プライマリドメインだけでなくセカンダリドメインのグループも取得できるようにする
  • メンバー情報として氏名・メールアドレス・ロールは必須
  • 管理者として把握しておきたいプロパティの設定情報も含める

どうやって取得するか

最初は 管理コンソールで何とかならないか？ と思ったのですが、管理コンソールからは執筆時点では1グループずつしかCSVでダウンロードできず、、それでは作業が途方も無いことになるので即却下。
気を取り直してGASを使って抽出した事例を探していたらこちらの記事を見つけたので、参考にさせていただくことにしました。（かなり使わせていただきました・・）
https://qiita.com/kirurobo/items/9b2f99bc12672e30b5ce

処理の大まかな流れ

  • スプレッドシートを開いた時、メニューに処理を開始するためのボタンを追加
  • 確認メッセージを表示
  • 「メンバーリスト」用の配列と「グループプロパティ」用の配列を2つ作る
  • 各シートの1行目にある項目をそれぞれ各配列に渡す
  • ループ処理の階層は　ドメイン＞グループ＞メンバー
  • ループ処理ではグループの階層で「グループプロパティ」用の配列、メンバーの階層で「メンバーリスト」用の配列へそれぞれデータを追加
  • 配列へデータを追加し終わったら各シートの既存データを消去
  • 各配列のデータを各シートへ書き込む

つまづいたところ

  • 最初は1枚のシートにまとめる予定だったが、配列に追加するデータが多すぎたせいか何度試しても メモリ不足でエラーになりました というメッセージが出た
  • ならばスプレッドシートに直接書き込むか！と配列を使わない方法にしてみたら（やはり）処理にとんでもなく時間がかかったので断念
    （ExcelのVBAで真っ先に書いていたこれ↓の類がスプレッドシートには無く・・）
Application.ScreenUpdating = False
  • グループのプロパティ情報をメンバーの人数分書き込む必要はないので、配列から書き出す方法に戻し、書き込む配列とシートをメンバー情報とプロパティ情報で分けることに

手順

0. 権限の確認

処理を実行するにはGoogleWorkspaceで特権管理者の権限が付与されたアカウントが必要です。

1. スプレッドシートの準備

書き出すスプレッドシートを用意して適当に名前をつけます。
シートは2枚使います。シート名は グループプロパティ と メンバーリスト にしました。
取得したい設定内容を項目名にして各シートの1行目にそれぞれ書いておきます。

  • グループプロパティ シート

  • メンバーリスト シート

2. スクリプトエディタを開く

スプレッドシートのメニューで［拡張機能］-［Apps Script］をクリックし、スクリプトエディタを開きます。

3. プロジェクト名とファイル名を変更する

無題のプロジェクト と コード.gs を適当な名前に変更します。
（今回は テスト_Googleグループ情報取得 、 グループ取得.gs にしました）

4. サービスを追加

Googleの拡張サービスを追加します。APIですが認証情報の入力等は不要です。
［サービス］の横にある［＋］をクリックして、

以下の2つのAPIを追加します。


Admin SDK API


Groups Settings API


追加すると、追加したサービスのIDがこのように表示されます。

5. コードを書く

エディタにコードを書きます。（デフォルトで記載されている内容は消去しておく）
全体像はこんな感じです。

全体
function onOpen() {

  SpreadsheetApp.getUi()
  .createMenu("リスト抽出")
  .addItem("グループ情報", "confirmLoadGroupInformation")
  .addToUi();

}


function confirmLoadGroupInformation() {

  const ui = SpreadsheetApp.getUi();
  let confirm = ui.alert(
    "処理確認",
    "グループの情報を更新します。",
    ui.ButtonSet.OK_CANCEL);

  if (confirm == "OK") { 
    const domains = getDomains();
    loadGroupInformation(domains);
    ui.alert(
      "処理完了",
      "完了しました。\nOKをクリックすると結果が表示されます。",
      ui.ButtonSet.OK);

  } else {
    ui.alert(
      "キャンセル",
      "キャンセルしました。",
      ui.ButtonSet.OK);
  }

}


function getDomains() {

  let mail = Session.getActiveUser().getEmail();
  let domainuser = AdminDirectory.Users.get(mail);
  let domains = AdminDirectory.Domains.list(domainuser.customerId).domains;

  let list = [];
  for (let i = 0; i < domains.length; i++) {
    let domain = domains[i];
    list.push(domain.domainName);
  }

  return list;

}


function loadGroupInformation(domains) {

   // グループプロパティシート
  const propsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('グループプロパティ');
  const propcolumns = propsheet.getMaxColumns();
  let propitem = propsheet.getRange(1, 1, 1, propcolumns).getValues();
  let propheader = propitem.flat();
  let proprows = [];
  proprows.push(propheader);

  // メンバーリストシート
  const membersheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('メンバーリスト');
  const membercolumns = membersheet.getMaxColumns();
  let memberitem = membersheet.getRange(1, 1, 1, membercolumns).getValues();
  let memberheader = memberitem.flat();
  let memberrows = [];
  memberrows.push(memberheader);


  // ドメインのループ
  for (let i = 0; i < domains.length; i++) {
    let domain = domains[i];
    let groupNextPageToken = null;

    // グループ登録状況のループ
    do {
      let grouplist = AdminDirectory.Groups.list({
        domain: domain,
        nextPageToken: groupNextPageToken
      });

      if (!grouplist.groups) break;
      groupNextPageToken = grouplist.nextPageToken;

      // グループごとの設定情報チェックのループ
      for(let j = 0; j < grouplist.groups.length; j++){           
        let group = grouplist.groups[j];
        let groupprop = AdminGroupsSettings.Groups.get(grouplist.groups[j].email);

        let proparray = [];
        proparray.push(group.email);
        proparray.push(group.name);
        proparray.push(group.directMembersCount);
        proparray.push(groupprop["whoCanJoin"]);
        proparray.push(groupprop["allowExternalMembers"]);
        proparray.push(groupprop["whoCanModerateMembers"]);
        proparray.push(groupprop["customRolesEnableForSettingsToBeMarged"]);
        proparray.push(groupprop["whoCanDiscoverGroup"]);

        proprows.push(proparray);


        let memberNextPageToken = null;
	
        // グループのメンバー登録状況のループ
        do {
          let memberlist = AdminDirectory.Members.list(group.email, {
            pageToken: memberNextPageToken
          });

          if (!memberlist || !memberlist.members) break;
          memberNextPageToken = memberlist.nextPageToken;

          // グループごとのメンバー情報チェックのループ
          for (let k = 0; k < memberlist.members.length; k++){
            let member = memberlist.members[k];

            let memberarray = [];
            memberarray.push(group.email);
            memberarray.push(group.name);
            memberarray.push(member.email);
            memberarray.push(member.role);

            memberrows.push(memberarray);

          }
        } while (memberNextPageToken);
      }
    } while (groupNextPageToken);
  }

  // 各シートの既存データを消去
  propsheet.getRange(1, 1, propsheet.getMaxRows(), propcolumns).clearContent();
  membersheet.getRange(1, 1, membersheet.getMaxRows(), membercolumns).clearContent();

  // 配列に格納した情報を各シートへ書き出す
  propsheet.getRange(1, 1, proprows.length , propcolumns).setValues(proprows);
  membersheet.getRange(1, 1, memberrows.length , membercolumns).setValues(memberrows);

}



functionは全部で4つあります。

  • onOpen
    スプレッドシートを開いたときに［リスト抽出］というメニューを作成し、その中に［グループ情報］というサブメニューを作成します。
onOpen
function onOpen() {

  SpreadsheetApp.getUi()
  .createMenu("リスト抽出")
  .addItem("グループ情報", "confirmLoadGroupInformation")
  .addToUi();

}

onOpen はシンプルトリガーと呼ばれている関数で、ファイルを開いたとき中に書いてある処理を実行します。（VBAのイベントプロシージャっぽい・・と勝手に思っています）
こちらの場合はスプレッドシートを開いたらメニューとサブメニューを作成します。
＜参考：シンプルトリガーの公式リファレンス＞
https://developers.google.com/apps-script/guides/triggers?hl=ja

  • confirmLoadGroupInformation
    ここからは抽出処理に関するfunctionになります。
    こちらでは確認メッセージを表示した後、
    • OKをクリックした場合は処理を開始して完了メッセージを表示
    • キャンセルした場合は中断メッセージを表示

という流れを記載しています。（抽出処理全体のシナリオのようなイメージです）

confirmLoadGroupInformation
function confirmLoadGroupInformation() {

  const ui = SpreadsheetApp.getUi();
  let confirm = ui.alert(
    "処理確認",
    "グループの情報を更新します。",
    ui.ButtonSet.OK_CANCEL);

  if (confirm == "OK") { 
    const domains = getDomains();
    loadGroupInformation(domains);
    ui.alert(
      "処理完了",
      "完了しました。\nOKをクリックすると結果が表示されます。",
      ui.ButtonSet.OK);

  } else {
    ui.alert(
      "キャンセル",
      "キャンセルしました。",
      ui.ButtonSet.OK);
  }

}


  • getDomains
    確認メッセージで［OK］をクリックしたユーザーのメールアドレスを基にGoogleWorkspaceで設定されているドメインをループ処理でチェックし、配列に入れます。
    ちなみにコード中にある AdminDirectory.xxx〜 は 4 で Admin SDK API の Directory バージョンを正しく追加できていないとコードを書いていても候補が出てきません。
getDomains
function getDomains() {

  let mail = Session.getActiveUser().getEmail();
  let domainuser = AdminDirectory.Users.get(mail);
  let domains = AdminDirectory.Domains.list(domainuser.customerId).domains;

  let list = [];
  for (let i = 0; i < domains.length; i++) {
    let domain = domains[i];
    list.push(domain.domainName);
  }

  return list;

}


  • loadGroupInformation
    最後のfunctionです。
    上述した「処理の大まかな流れ」の3つ目以降の処理になります。
    コード中にある AdminGroupsSettings.xxx〜 ですが、こちらも 4 で Groups Settings API を正しく追加できていないとコードを書いていても候補が出てきません。
loadGroupInformation
function loadGroupInformation(domains) {

   // グループプロパティシート
  const propsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('グループプロパティ');
  const propcolumns = propsheet.getMaxColumns();
  let propitem = propsheet.getRange(1, 1, 1, propcolumns).getValues();
  let propheader = propitem.flat();
  let proprows = [];
  proprows.push(propheader);

  // メンバーリストシート
  const membersheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('メンバーリスト');
  const membercolumns = membersheet.getMaxColumns();
  let memberitem = membersheet.getRange(1, 1, 1, membercolumns).getValues();
  let memberheader = memberitem.flat();
  let memberrows = [];
  memberrows.push(memberheader);


  // ドメインのループ
  for (let i = 0; i < domains.length; i++) {
    let domain = domains[i];
    let groupNextPageToken = null;

    // グループ登録状況のループ
    do {
      let grouplist = AdminDirectory.Groups.list({
        domain: domain,
        nextPageToken: groupNextPageToken
      });

      if (!grouplist.groups) break;
      groupNextPageToken = grouplist.nextPageToken;

      // グループごとの設定情報チェックのループ
      for(let j = 0; j < grouplist.groups.length; j++){           
        let group = grouplist.groups[j];
        let groupprop = AdminGroupsSettings.Groups.get(grouplist.groups[j].email);

        let proparray = [];
        proparray.push(group.email);
        proparray.push(group.name);
        proparray.push(group.directMembersCount);
        proparray.push(groupprop["whoCanJoin"]);
        proparray.push(groupprop["allowExternalMembers"]);
        proparray.push(groupprop["whoCanModerateMembers"]);
        proparray.push(groupprop["customRolesEnableForSettingsToBeMarged"]);
        proparray.push(groupprop["whoCanDiscoverGroup"]);

        proprows.push(proparray);


        let memberNextPageToken = null;
	
        // グループのメンバー登録状況のループ
        do {
          let memberlist = AdminDirectory.Members.list(group.email, {
            pageToken: memberNextPageToken
          });

          if (!memberlist || !memberlist.members) break;
          memberNextPageToken = memberlist.nextPageToken;

          // グループごとのメンバー情報チェックのループ
          for (let k = 0; k < memberlist.members.length; k++){
            let member = memberlist.members[k];

            let memberarray = [];
            memberarray.push(group.email);
            memberarray.push(group.name);
            memberarray.push(member.email);
            memberarray.push(member.role);

            memberrows.push(memberarray);

          }
        } while (memberNextPageToken);
      }
    } while (groupNextPageToken);
  }

  // 各シートの既存データを消去
  propsheet.getRange(1, 1, propsheet.getMaxRows(), propcolumns).clearContent();
  membersheet.getRange(1, 1, membersheet.getMaxRows(), membercolumns).clearContent();

  // 配列に格納した情報を各シートへ書き出す
  propsheet.getRange(1, 1, proprows.length , propcolumns).setValues(proprows);
  membersheet.getRange(1, 1, memberrows.length , membercolumns).setValues(memberrows);

}

グループのプロパティについては公式リファレンスに詳しく書いてあります。
https://developers.google.com/admin-sdk/groups-settings/v1/reference/groups?hl=ja

6. 承認（初回実行時のみ）

保存した後、エディタで「実行する関数」がonOpenになっていることを確認して［実行］をクリックすると、

承認を促すメッセージが表示されるので［権限を確認］をクリックします。

アカウントの選択画面がされたら実行するアカウントを選択し、

アクセスのリクエスト画面が表示されたら［許可］をクリックすれば完了です。

エディタに戻ると実行ログが表示されています。「実行完了」となっていればOKです。

スプレッドシートを見てみると、［リスト抽出］というメニューが表示されていると思います。

7. 処理の実行

さきほどの［リスト抽出］をクリックすると［グループ情報］というサブメニューが表示されるのでクリックします。

処理確認メッセージが表示されるので［OK］をクリックします。
（件数が多い場合このあと少し時間がかかります）

処理完了メッセージが表示されたら［OK］をクリックして終了です。

感想

今後も増えていくであろうGoogleグループを一括で抽出できるようになってよかったです。
他の作業をしながらでもリストを作成できるようになるので、管理コンソールから1つずつダウンロードする手間を考えると特権管理者の方は知っておいて損はないと思いました。

最後に

参考になった記事と公式リファレンスのまとめです。
https://qiita.com/kirurobo/items/9b2f99bc12672e30b5ce

レスキューナウテックブログ
レスキューナウテックブログPublication

日本で唯一の危機管理情報を専門に取り扱う防災Techのスタートアップ、(株)レスキューナウです。当社で活躍するエンジニアの技術ブログを中心に公開していきます。

Discussion