Google Apps Script(GAS)でG Suite全ユーザーリストのスプレッドシート書き出しを自動化してみたお話

1 事前にやること
- 1.1 Admin Directory APIの有効化
- 1.2 Chrome V8のApps Scriptランタイムを無効化する
2 スクリプト内容
3 トリガーの設定
4 まとめ

概要/あらすじ

実務未経験から中小企業の情シスに転職。

従業員約350名規模、都内に3拠点、地方に4拠点の会社（非IT業種）で上司と後輩の3名体制。

業務上、データの付け合わせやライセンス数のカウント等や集計のために何かとG Suite上のユーザーリストをエクスポートする機会があるのだが、頻度が週に数回程度あり地味に面倒だなと思い、何か自動化する手立てはないかと検討をするのであった。

こんばんは、ばーふぃんです。

情シスの仕事をしていると何かとデータをエクスポートしてそこから集計をしたりという業務が出てくることかと思います（うちだけ？？）。

私がいる会社ではActive DirectoryからG Suiteへアカウントやそこに紐付くプロフィール情報、グループ等を同期しているので、特にアカウントに関してはAD、G Suiteどちらもほぼ正であると言っても過言ではない状態になっています。

事前にやること

Admin Directory APIの有効化

スクリプトエディタを開いた状態で、「リソース」→「Googleの拡張サービス」

※スクリプトエディタは、スプレッドシートを開いている状態で、「ツール」→「スクリプトエディタ」と進めるだけで開くことが出来ます。

「Admin Directory API」を「ON」にする（なぜか画像はONのとこが潰れちゃってますね。。。）。

Chrome V8のApps Scriptランタイムを無効化する

2020年2月からChrome V8のApps Scriptランタイムとやらがリリースされたみたいで、何故かこれが有効になっているとスプレッドシートへのアクセスが正しく動かなかったので無効化しました。デフォルトでは有効になってます。

同じくスクリプトエディタを開いた状態で「実行」→「Chrome V8を搭載したApps Scriptランタイムを無効にする」

スクリプト内容

さて、ここからが本番。実際に記述したスクリプト内容です。

ベースは以下のページを参照して、そこにアレンジ加えています。

lab.taf-jp.com

GoogleAppsScriptで全ユーザーを取得・改 | TF Lab - クラウド開発記録 -

https://lab.taf-jp.com/googleappsscriptで全ユーザーを取得・改/

コピペ中心でいけるかと思いきやそうでもなく、特に部署とか役職の値取得方法は意外とやってる人あんまいなくて情報なかったから、試行錯誤しながらで大変だった。

//※1スプレッドシートのデータを全消しする関数
function DeleteFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('AllUsers');
  sheet.getRange("A1:Z1000").clear();
}

//ユーザーを全取得してスプレッドシートに書き出す関数
function AllGetFunction(){
  
  //頭文字検索に使用する文字列　「charAt()」関数で１文字ずつ呼び出す
  //※2参照元ページでは、大文字A-Zも入っていたが、それだと重複が発生するため消した
  var searchStrs = "abcdefghijklmnopqrstuvwxyz1234567890";

  //配列の定義宣言。でもここはいらないかも
  var array = new Array();
  
  //配列の定義宣言。最終的にこの配列にユーザー情報を格納する
  var userList =  new Array();

  //検索する（情報を抽出したい）ドメインを指定して変数定義
  var domains = ["josys-dairy.com","josys-dairy2.com"];

  //検索するドメインの数だけループ処理
  for(var i = 0;i &amp;lt; domains.length;i ++){

    //頭文字として検索するa-z,1-0の数だけループ処理
    for(var j = 0;j &amp;lt; searchStrs.length;j ++){
		
      //呼び出すユーザーの条件を指定する
      var optionalArgs = {
        domain:domains[i],		          //ドメインを入力
        maxResults:500,			          //デフォルトでは100件までなので、500（最大数）を指定
        query:"email:"+ searchStrs.charAt(j) +"*",//メールアドレスの頭文字で検索を行う
      };
		
      //ユーザー情報の呼び出し
      var userDatas = AdminDirectory.Users.list(optionalArgs);
		
      if(userDatas.users){

        //呼び出されたユーザーの回数分ループする
        for(var k = 0;k &amp;lt; userDatas.users.length;k ++){
                       
          //呼び出したメールアドレスには別名も含まれているため、その回数分をループ
          for(var l = 0;l &amp;lt; userDatas.users[k].emails.length;l ++){
                   
            //メールアドレスのうち、primary（一意である）かどうかをチェック
            //念のために、既に取得してあるものは重複しないように対象外にする（後半はちゃんと動いていないっぽいためいらないかも、前半は必須）
            if(userDatas.users[k].emails[l].primary == true &amp;amp;&amp;amp; array.indexOf(userDatas.users[k].emails[l].address) == -1){
                      
              //メールアドレスを取得して変数定義
              var mail = userDatas.users[k].emails[l].address;

              //氏名を取得して変数定義
              var fullName = userDatas.users[k].name.fullName;
              //名字を取得して変数定義
              var familyName = userDatas.users[k].name.familyName;
              //名前を取得して変数定義
              var givenName = userDatas.users[k].name.givenName;
                    
              //従業員コードを定義する（FALSEの場合"null"とする）
              var externalIds = "null";
               
              //※3TRUEの場合、従業員コードを取得する
              if(userDatas.users[k].externalIds){ 
                var externalIds = userDatas.users[k].externalIds[0].value;
              }
                      
              //部署を変数定義する（FALSEの場合"null"とする）
              var department = "null";
              //役職を変数定義する（FALSEの場合"null"とする）
              var department = "null";
                      
              //※4TRUEの場合、以下処理を実行
              if(userDatas.users[k].organizations){
   
                //organizationが複数ユーザーは正しく取得出来ないため、その数だけループしてprimary=trueのみ取得する
                for( var m = 0; m &amp;lt; userDatas.users[k].organizations.length; m ++){
                  if(userDatas.users[k].organizations[m].primary == true){       
                            
                    //部署を取得
                    var department = userDatas.users[k].organizations[m].department;
                    //役職を取得
                    var title = userDatas.users[k].organizations[m].title;
                  }
                }
              }
                     
              //組織部門を取得
              var orgUnitPath = userDatas.users[k].orgUnitPath;
                           
              //※5それぞれ取得した属性の値を配列に格納する
              var profile = [email,fullName,familyName,givenName,externalIds,orgUnitPath,department,title];
                      
              //ループ処理中に多次元配列を生成する
              userList.push(profile);
                   
            }  
          }
        } 
      }
    }
  }
  
 //※6スプレッドシートへアクセスする
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('AllUsers');

 //配列から行、列それぞれのデータの個数を算出する
 var rows = userList.length;
 var cols = userList[0].length;
  
 //スプレッドシートに配列データを格納していく（B2セル起点）
 sheet.getRange(2,2,rows,cols).setValues(userList);  
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

//※1スプレッドシートのデータを全消しする関数

function DeleteFunction() {

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('AllUsers');

sheet.getRange("A1:Z1000").clear();

}

//ユーザーを全取得してスプレッドシートに書き出す関数

function AllGetFunction(){

//頭文字検索に使用する文字列　「charAt()」関数で１文字ずつ呼び出す

//※2参照元ページでは、大文字A-Zも入っていたが、それだと重複が発生するため消した

var searchStrs = "abcdefghijklmnopqrstuvwxyz1234567890";

//配列の定義宣言。でもここはいらないかも

var array = new Array();

//配列の定義宣言。最終的にこの配列にユーザー情報を格納する

var userList = new Array();

//検索する（情報を抽出したい）ドメインを指定して変数定義

var domains = ["josys-dairy.com","josys-dairy2.com"];

//検索するドメインの数だけループ処理

for(var i = 0;i &lt; domains.length;i ++){

//頭文字として検索するa-z,1-0の数だけループ処理

for(var j = 0;j &lt; searchStrs.length;j ++){

//呼び出すユーザーの条件を指定する

var optionalArgs = {

domain:domains[i], //ドメインを入力

maxResults:500, //デフォルトでは100件までなので、500（最大数）を指定

query:"email:"+ searchStrs.charAt(j) +"*",//メールアドレスの頭文字で検索を行う

};

//ユーザー情報の呼び出し

var userDatas = AdminDirectory.Users.list(optionalArgs);

if(userDatas.users){

//呼び出されたユーザーの回数分ループする

for(var k = 0;k &lt; userDatas.users.length;k ++){

//呼び出したメールアドレスには別名も含まれているため、その回数分をループ

for(var l = 0;l &lt; userDatas.users[k].emails.length;l ++){

//メールアドレスのうち、primary（一意である）かどうかをチェック

//念のために、既に取得してあるものは重複しないように対象外にする（後半はちゃんと動いていないっぽいためいらないかも、前半は必須）

if(userDatas.users[k].emails[l].primary == true &amp;&amp; array.indexOf(userDatas.users[k].emails[l].address) == -1){

//メールアドレスを取得して変数定義

var mail = userDatas.users[k].emails[l].address;

//氏名を取得して変数定義

var fullName = userDatas.users[k].name.fullName;

//名字を取得して変数定義

var familyName = userDatas.users[k].name.familyName;

//名前を取得して変数定義

var givenName = userDatas.users[k].name.givenName;

//従業員コードを定義する（FALSEの場合"null"とする）

var externalIds = "null";

//※3TRUEの場合、従業員コードを取得する

if(userDatas.users[k].externalIds){

var externalIds = userDatas.users[k].externalIds[0].value;

}

//部署を変数定義する（FALSEの場合"null"とする）

var department = "null";

//役職を変数定義する（FALSEの場合"null"とする）

var department = "null";

//※4TRUEの場合、以下処理を実行

if(userDatas.users[k].organizations){

//organizationが複数ユーザーは正しく取得出来ないため、その数だけループしてprimary=trueのみ取得する

for( var m = 0; m &lt; userDatas.users[k].organizations.length; m ++){

if(userDatas.users[k].organizations[m].primary == true){

//部署を取得

var department = userDatas.users[k].organizations[m].department;

//役職を取得

var title = userDatas.users[k].organizations[m].title;

}

//組織部門を取得

var orgUnitPath = userDatas.users[k].orgUnitPath;

//※5それぞれ取得した属性の値を配列に格納する

var profile = [email,fullName,familyName,givenName,externalIds,orgUnitPath,department,title];

//ループ処理中に多次元配列を生成する

userList.push(profile);

}

//※6スプレッドシートへアクセスする

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('AllUsers');

//配列から行、列それぞれのデータの個数を算出する

var rows = userList.length;

var cols = userList[0].length;

//スプレッドシートに配列データを格納していく（B2セル起点）

sheet.getRange(2,2,rows,cols).setValues(userList);

}

処理内容については、コメントアウト付けていますが、何点かポイントになるところについて説明していきます。

スプレッドシートの値を全消しする関数

//※1スプレッドシートのデータを全消しする関数
function DeleteFunction() {
  var sheet=SpreadsheetApp.getActiveSheet();
  sheet.getRange("A1:Z1000").clear();
}

//※1スプレッドシートのデータを全消しする関数

function DeleteFunction() {

var sheet=SpreadsheetApp.getActiveSheet();

sheet.getRange("A1:Z1000").clear();

}

今回、毎日スプレッドシートにG Suiteの全ユーザーを書き出す処理を行いますが、その処理は特定のセルを起点にユーザー数分上から順に書き足していく処理になります。

つまり、ユーザーの削除や追加があった場合、そのユーザーが入る行数によってはそのまま上書くとスプレッドシート上に見えるユーザーに重複や不足が生じる可能性があります。

そのため、今回は書き出しを行う前に一度スプレッドシートにいるデータは全て消してしまうという方法を取りました。

記述内容は至って簡単です。削除する範囲を指定して上記の処理をするだけ。

メールアドレスの頭文字を起点に検索

//頭文字検索に使用する文字列　「charAt()」関数で１文字ずつ呼び出す
//※2参照元ページでは、大文字A-Zも入っていたが、それだと重複が発生するため消した
var searchStrs = "abcdefghijklmnopqrstuvwxyz1234567890";

//頭文字検索に使用する文字列　「charAt()」関数で１文字ずつ呼び出す

//※2参照元ページでは、大文字A-Zも入っていたが、それだと重複が発生するため消した

var searchStrs = "abcdefghijklmnopqrstuvwxyz1234567890";

参照元のページのコードでは、大文字のA-Zも変数に入っていましたが、結局大文字の方でも頭文字小文字のメールアドレスが検索に引っかかってしまい、最終的に書き出すユーザーがダブってしまったため、今回は消しました。
自分の書き方が悪かったのかもしれないですね。

従業員コードの値取得

//※3TRUEの場合、従業員コードを取得する
if(userDatas.users[k].externalIds){ 
  var externalIds = userDatas.users[k].externalIds[0].value;
}

//※3TRUEの場合、従業員コードを取得する

if(userDatas.users[k].externalIds){

var externalIds = userDatas.users[k].externalIds[0].value;

}

従業員コードの値を取得する際、G Suiteユーザーディレクトリの”externalIds”を読み取って取得するのですが、取得した際の形式が氏名等と違い配列ではなくオブジェクト状態で渡されるため、配列に変換してやる必要があります。

それが上記のifの中の処理です。ここ結構ハマったポイントです。

部署や役職情報の取得

//※4TRUEの場合、以下処理を実行
if(userDatas.users[k].organizations){
   
  //organizationが複数存在するユーザーは正しく取得出来ないため、その数だけループしてprimary=trueのみ取得する
  for( var m = 0; m &amp;lt; userDatas.users[k].organizations.length; m ++){
    if(userDatas.users[k].organizations[m].primary == true){       
                            
      //部署を取得
      var department = userDatas.users[k].organizations[m].department;
      //役職を取得
      var title = userDatas.users[k].organizations[m].title;
    }
  }
}

//※4TRUEの場合、以下処理を実行

if(userDatas.users[k].organizations){

//organizationが複数存在するユーザーは正しく取得出来ないため、その数だけループしてprimary=trueのみ取得する

for( var m = 0; m &lt; userDatas.users[k].organizations.length; m ++){

if(userDatas.users[k].organizations[m].primary == true){

//部署を取得

var department = userDatas.users[k].organizations[m].department;

//役職を取得

var title = userDatas.users[k].organizations[m].title;

}

部署や役職情報は、G Suiteユーザーディレクトリの”organizations”から取得してきます。

こちらも、従業員コード同様オブジェクトで渡されるのですが、ユーザーによっては配列が複数存在しているケースがありました（もしかしたら”externalIds”でも同じ現象は起こり得るかもしれないです）。

その場合は、単に配列に変換するだけではダメなので、[primary=true]の配列のみ取得するようfor文とif文を記述します。

上述が具体的にどういうことであるか、以下のAdmin SDK Directory APIページで値を取得してみるとイメージしやすいと思います。

Google Developers

Users: get | Directory API | Google Developers

https://developers.google.com/admin-sdk/directory/v1/reference/users/get

上記ページにアクセスし、「userKey」欄にユーザーのアカウントを入力し、「EXECUTE」

すると、ユーザーのディレクトリ情報が出力されます。

以下が”organizations”に配列が2つ複数存在してしまっているケース。きっと何かやってしまったんでしょうね。

今回はprimaryに入っている情報が取れればよかったので上述のコードで[primary=true]の配列を指定してやったわけです。

多次元配列の生成

//※5それぞれの取得した値を配列に格納する
var profile = [email,fullName,familyName,givenName,externalIds,orgUnitPath,department,title];
                      
////ループ処理中に多次元配列を生成する
userList.push(profile);

//※5それぞれの取得した値を配列に格納する

var profile = [email,fullName,familyName,givenName,externalIds,orgUnitPath,department,title];

////ループ処理中に多次元配列を生成する

userList.push(profile);

参照元ページでは、メールアドレスのみの取得を行なっていたため、処理内容が全ユーザーのメールアドレスを一次元配列に格納するというものでした。項目が1つであれば、一次元配列でその値を順番に書き出せばいいので、それで完結します。

イメージ

[“a@josys-daiary.com”,b@josys-daiary.com,”c@josys-daiary.com”……]

左から順番に書き出していくだけ。値1つに対してスプレッドシート1行というわけです。

しかし、今回はメールアドレスだけでなく他の情報も取得して書き出しを行うため、書き出す際に使用するデータを多次元配列にする必要がありました。

イメージ

[[“a@josys-daiary.com”,”田中太郎”,”営業部”,”部長”],[“b@josys-daiary.com”,”山田二郎”,”経理部”,”課長”]…]

みたいな。多次元配列の中の1配列に対してスプレッドシート1行、その1配列の中の値ごとにスプレッドシートの列、みたいな感じです。

ここでは、それぞれ取得した属性値を配列に格納したら、ループ処理ごとにpush関数を使用して多次元配列を生成しています。

スプレッドシートへのデータ書き出し

//※6スプレッドシートへアクセスする
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('AllUsers');

 //配列から行、列それぞれのデータの個数を算出する
 var rows = userList.length;
 var cols = userList[0].length;
  
 //スプレッドシートに配列データを格納していく（B2セル起点）
 sheet.getRange(2,2,rows,cols).setValues(userList);

//※6スプレッドシートへアクセスする

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('AllUsers');

//配列から行、列それぞれのデータの個数を算出する

var rows = userList.length;

var cols = userList[0].length;

//スプレッドシートに配列データを格納していく（B2セル起点）

sheet.getRange(2,2,rows,cols).setValues(userList);

さて、データの取得ができたらあとはスプレッドシートに書き出すだけです。

最初の行は、スプレッドシート内のシート名(“AllUsers”)を指定しています。この辺は、ネット上にたくさん情報あるかと。

その後もスプレッドシートに書き出すためのコードで、これもネット上に情報ゴロゴロしてます。

setValuseで、↑で作成した多次元配列”UserList”を指定してやりましょう。

トリガーの設定

スクリプトを書けたらあと一息です。

今回は、毎日定期実行を行いたいので、そのための条件（トリガー）を指定してやります。

「編集」→「現在のプロジェクトのトリガー」

右下の「+トリガーを追加」

まずスプレッドシートデータ全消しの関数実行トリガーを設定。

今回は毎日特定の時間帯に実行するよう設定したいので、以下のように設定しています。

次に、全ユーザー情報をスプレッドシートに書き出す関数のトリガー設定。

基本的には先ほどとほぼ同内容となりますが、当然時間帯は削除関数よりも後ろに設定してあげましょう。

ちなみに、Google Apps Scriptのトリガーは細かく何時何分に実行するといった設定はできず、●時〜●時の間に実行という指定のし方になりますので、注意してください。

まとめ

さて、実際に処理が実行されると、以下のようなイメージになります。

実際のデータではないので、あくまでイメージです。

読み取ってから書き出されるまでの処理時間はユーザー数400弱で30秒〜1分程度といったところです。

まあ自動化して自分が知らない間に実行されているので気にすることはないでしょう。

ちなみに、Google Apps Script(GAS)は、一度の処理で6分を超えてしまうと処理が中断されてしまうという情報を他のページでちらっと見ました。

なので、ユーザー数が数万規模の大企業では厳しいか、改良をするしかないでしょう（何万とあるユーザーのリストを常に出しておきたいシチュエーションがあるかは知りませんが。。。）。

私がいるようなユーザー数が数百規模の中小なんかだと、これ意外と便利だったりします。

すぐにユーザー情報にたどり着けるし称号もしやすくなるので。

ただ、もちろんユーザー情報のメンテナンスを常日頃タイムリーに行う必要がありますね。

はあー、大変。

Google Apps Script(GAS)でG Suite全ユーザーリストのスプレッドシート書き出しを自動化してみたお話

概要/あらすじ

事前にやること

Admin Directory APIの有効化

Chrome V8のApps Scriptランタイムを無効化する

スクリプト内容

スプレッドシートの値を全消しする関数

メールアドレスの頭文字を起点に検索

従業員コードの値取得

部署や役職情報の取得

多次元配列の生成

スプレッドシートへのデータ書き出し

トリガーの設定

まとめ

WSUSでWindows Updateが適用されない問題を解決したお話

未経験者の情シスの転職について考えてみたお話

概要/あらすじ

事前にやること

Admin Directory APIの有効化

Chrome V8のApps Scriptランタイムを無効化する

スクリプト内容

スプレッドシートの値を全消しする関数

メールアドレスの頭文字を起点に検索

従業員コードの値取得

部署や役職情報の取得

多次元配列の生成

スプレッドシートへのデータ書き出し

トリガーの設定

まとめ

関連記事

情シス未経験が最低限知っておいた方がいい用語のお話（サービス名編）

グループカレンダーの作成と共有を行ったお話

G Suiteで独自ドメイン所有権の証明をしたお話

G Suite導入/移行するために何をすべきか検討したお話

G Suite移行のハードルその2、Gmailへのメールサーバ移行のお話

WSUSでWindows Updateが適用されない問題を解決したお話

未経験者の情シスの転職について考えてみたお話