Google Apps Scriptでグループメンバーとカレンダーリソースを取得する

現在開発中のアプリでちょっと必要になったので作ってる「グループアドレスからメンバー一覧取得」と「カレンダーのリソースを取得」の2本。小ネタをひとまとめにして、エントリーを作成しています。これらは都度都度アプリからAPI問い合わせして取りに行ってるとそれだけで無駄が多いので、スプレッドシートに書き出し、アプリからはそれをフィルタして利用するようにすることで、高速化しています。

取得したデータはQuery関数を使って結合してからアプリ側で利用する仕組みにしています。

今回利用するスプレッドシート等

グループアドレスの中に入ってるメンバー(また、その中にさらにグループが入ってる場合はその中のメンバーも)を一覧で取得しスプレッドシートに書き出します。また、Google Calendarの会議室などの「リソース」と呼ばれるデータも一括で取得しスプレッドシートに書き出します。

どちらも管理者権限を必要とするAdmin SDK APIを利用する事で取得することが出来ます(GroupsAppでもグループメンバーは取得できるのですが制約があります)。これらは夜中の0時にでも発動するようにしておけば、自動的に最新の状態に更新されるのでより便利になると思います。

グループ作成については、過去にエントリーでまとめています。

Google Apps Scriptでグループを作成する

Google Apps Scriptでトリガーを活用しよう【GAS】

事前準備

GroupsAppで取得する場合は不要ですが、Admin SDKを使ってのグループメンバー取得やカレンダーリソースは特権管理者の権限を持っているものでなければ使えないサービスを追加する必要があります。

以下に追加方法を記します。

  1. スクリプトエディタを開く
  2. 左サイドバーのサービスの+をクリックする
  3. Admin SDKを探してクリックし、追加をクリックする
  4. AdminDirectoryとしてサービスが追加された

これで、以降はAdminDirectoryというサービスで各種特権管理者用のメソッドを実行する事が可能になります。

図:Admin SDKのサービスを追加した

グループアドレスのメンバーを取得する

GroupsAppで取得する場合

こちらのコードはGoogle Apps Scriptの標準のメソッドにてグループアドレス内のメンバーのデータを取得する事が可能です。但し1度にとれるメンバー数が200名までのようです(通常はあまり困ることはないものの、全社員が参加してるMLなどですと超える可能性があります)。

  • getGroupMembersがメインの関数となります。こちらから他の関数に処理を渡したり配列を書き出したりを行います。
  • GroupsApp.getGroupByEmailにて対象のグループアドレスを取得させ、ここからメンバーと子グループアドレスを別々の関数で処理させます
  • getMemberlist関数がgroupの中からメンバーのメアドをグループアドレス名と合わせて二次元配列で返す関数です
  • getChildGroupman関数がgroupの中からグループアドレスを取り出し、1次元配列で返す関数です。
  • メンバーはarrayの配列にどんどん結合させていきます。
  • 子グループの一覧であるgrouparrを走査して、同様にメンバーとグループアドレスを取り出していきます。
  • 連続して処理をすると429エラーになるので1週ずつUtilities.sleepでウェイト処理を挟んでおきます。
  • 最後にgrouparrの先頭の値を削除する。もし、前の処理でグループアドレスがあれば再度Whileループが回ります。
  • arrayの中身に於いて、重複してるメンバーが居れば削除する処理を実行してnew_arrayとし、最後にMemberシートをクリアして一括でnew_arrayの値を書き出します。
  • メンバーの取得は読み込みの権限がないものは取得ができません。

この処理によって、親グループアドレス名とメンバーのアドレスの2列のデータを取り出すことが可能です。

Admin SDKで取得する場合

概要

前述のGroupsAppでも良いのですが、200名の制限があるため、グループのメンバー取得に関してはAdmin SDKで取得するほうが色々と便利です。こちらのメソッドはGroupAppと違い以下の特徴があります。

  • ドメイン単位で作成されてる全グループを一括で取得する事が可能
  • pageTokenを回して全所属メンバーを取得する事が可能
  • USERなのか?GROUPなのかを判定できるので異なるメソッドを使ってグループだけ処理をする必要がありません。
  • 但し社外メンバーがグループに入ってる場合、そのメンバーはドメイン所属外となるのでリストに出てきません。
  • Admin Consoleの管理者権限が無いと実行できない
  • ユーザに与えられてるRole(権限)も取得することが可能です。

GroupsAppが1つのグループを処理するのに対して、こちらは一括で全グループをまとめて処理できるので大規模な処理に向いています。

ソースコード

  • 呼び出されるgetMemberlist2関数の中で処理をしています。GroupsAppと違ってメンバーのタイプ(USERか?GROUPか?を判定できるので)で関数は1つで対応させています
  • おおもとのgetGroupsMembers2関数はGroupsAppの時と大きく変更はありません。呼び出す関数は1個で引数がひとつ増えて、ドメインの指定が増えているだけです。
  • ドメインはhogehoge.co.jpといったGoogle Workspaceで利用してるドメインを利用します。サブドメイン等ある場合は要注意。
  • AdminDirectory.Groups.listでドメイン内全グループを取得し、AdminDirectory.Members.listでメンバーを取得します。
  • メンバーデータのtypeでUSERか?GROUPかを判定できるので、そこで処理を分岐させています。
  • 他、親グループにぶら下がる子グループについての再帰的な処理については前述のGroupsAppのケースと同じです。

ユーザが所属するグループアドレス一覧

逆に特定のユーザが直接所属するグループアドレスの一覧を取得したい場合があります。その場合もAdmin SDKにて取得させることが可能です。ただし、直接所属なので別のグループアドレスに子グループアドレスとして登録されていた場合、親グループアドレスには直接所属していないので、親のアドレスを取得するには色々と逆引きする処理が必要です。

  • AdminDirectory.Groups.listを利用してドメインとuserKeyにユーザのメアドを入れて実行します。
  • tmpgroupにはグループアドレスが次々入ってくるのでこれを配列に入れて返しています。

全ドメインの自動取得

同じくAdminSDKを利用して、Google Workspaceに設定されているドメインの一覧というものも取得することが可能です。複数ドメインを割り当ててる場合には以下の関数を利用する事で、複数ドメインを一括で取得することが可能です。

メンバーと社員マスターを結合する

前述まででMemberシートにメンバーのデータは取得できましたが、アドレスのみなので、このアドレスと別に用意してあるMasterシートのデータとをQuery関数で結合させ1つの完成したリストに仕立て上げます。これを自分の場合はウェブアプリの基準となるデータソースとして使っています。また、前述までの処理を自動的に毎晩行わせるためのトリガー処理を追加する事で、自動でMemberシートのメンテナンスも行わせメンテナンスフリーにする事で、少ない労力でアプリを維持する事が可能です。

Memberシートとemployeeシートをメールアドレスを持ってQuery関数で疑似外部結合したら、Masterシートに出力します。数式的には以下のような数式を入れておきます。

employeeシートはメアド、名前、部署名、電話番号の4列で出来ており、Memberシートの2列分とvlookupさせる2列目の値、それをarrayformulaで結合させています。employeeシートの1列目はMemberシートでもってきてるので{2,3,4}とすることで、2列目以降のemployeeシートの3列分を結合させています。

外部結合なので、Memberシートの全員と合致するemployeeシート側のデータで結合させるので、Memberシートのデータは全部出てきます。

※arrayformula関数等の配列に対して3列目でソートをしてるため、通常はselect * order by C Descとなるところは、select * order by Col3 Descとしなければソートする事は出来ません。ここは要注意です。Col3で計算結果の3列目という意味になります。

Query関数を使ってデータを集計しよう - 其の弐

カレンダーリソースを取得する

Googleカレンダーで会議のイベントを登録する場合によく利用する「リソース」。会議室や物品のレンタル等の数に限りのあるものを社内でやり取りする為の仕組みなのですが、このリソースの情報を都度取得しにいくのは非常に無駄が多い為(通常リソースの増減は殆ど起こりえない)、これらを一度取得して定期的に同期するようにし、アプリからはスプレッドシートのリソース情報を元に処理を行うようにすることで高速化することが可能です。

しかし、リソース情報はGroupsAppでは取得が出来ないので、Admin SDKを利用する必要性があります。カレンダーリソースおよびビルディングに関してはトリガーなどで自動取得は行わず手動で取得するようにしています(頻繁に内容が変わるものでは無いのと、自作アプリで表示非表示のチェックボックスを付けてるので変わると逆に困る)

カレンダーリソースの作成

カレンダーリソースはGoogle Workspaceの管理者がAdmin Consoleで事前に設定しておく必要性があります。設定手順は以下の通りです。

  1. Admin Consoleへログインする
  2. 左サイドバーからディレクトリ => ビルディングとリソース=>リソースの管理を開きます。
  3. すべてのビルディングに対してぶら下がる形でビルディング(拠点等)が列挙され、それぞれに所属するリソースが右側に出てきます。

上記に於いて、まずは拠点であるビルディングを組織部門(OU)のように作成します(下の方にあるビルディングを管理にて作成)。ビルディングIDを命名し名前やフロアを設定しておきます。

次に、各ビルディングにぶら下がる形で会議室1,2,3であったり、貸与する社用車やらサービスをぶら下げることでカレンダーリソースとして使えるようになります。それぞれにはカレンダーが用意されますので、リソースに対して付与されるメールアドレスでアクセスが可能です。

図:個別のリソースの情報

図:ビルディング情報作成中

リソース情報を取得する

まずは、現在用意されてるリソースの情報を取得し、スプレッドシートに書き出します。アプリで必要になる情報は主に以下の通りですが、こちらに取得できる情報の一覧があります。

  • リソースの名前
  • リソースのメールアドレス(カレンダーで利用します)
  • リソースID(スクリプトで利用します)
  • キャパシティ(部屋の人数等)
  • リソースカテゴリ(リソースがどのようなものなのかの種別)
  • ビルディングID(拠点となる場所のID)

となっています。但し、ビルディングIDに対するビルディング名は取得がこのメソッドでは取得ができません。

  • 実行するユーザはAdmin Consoleにログイン出来てアクセス権限を持ってるユーザになります。
  • ユーザのメアドをもってcustomerIdを取得し、AdminDirectory.Resources.Calendars.listにてカレンダーリソースを一括で取得します。
  • カレンダーリソースのitemsに入ってるデータをもとに配列を組んで、カテゴリ・拠点を元に昇順でソート(文字列)します。
  • 最後に配列をスプレッドシートのresourceシートに一括で書き出します。
  • temparrの最後は使用可否の項目として自分がスプレッドシートに設けたチェックボックスのオンオフを指定します。デフォルトでオンにするためにtrueとしています。

ビルディングを取得する

前述のカレンダーリソースを取得するメソッドでは所属するビルディングのIDは取得できるのですが、肝心のビルディングの名称は取得が出来ません。こちらについては、別途用意されているメソッドを使う事で取得できるので、合わせて利用する事で完璧なカレンダーリソースデータを取得することが可能です。

GAS内で連結しても良いですし、Query関数で連結して使うのも良いでしょう。

  • コード的には殆どリソースの取得と変わりません
  • 今回はビルディングID、ビルディング名だけを取得し3列目は使用するかどうかとう列にしてるのでtrueだけをデフォルトで入れています。
  • スプレッドシート上では使用可否はチェックボックスとしています。
  • 今回はビルディング名でソートをするに当たって降順にしないとなぜか01.xxxとして命名したものが昇順的に並ばなかったので逆にしています。
  • 取得したデータはbuildingsシートに一括で書き出ししています。
  • 拠点IDを元に前述のデータと結合すると良いでしょう。

関連リンク

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。