2019年9月18日 / 最終更新日時 : 2024年6月14日 officeの杜 Google

Google Apps Scriptで組織図を作ろう【GAS】

2月前や8月前、また4月や9月など人事異動や転勤、新規採用そして退職。これらの移動に伴って、また組織改編に伴って毎回手動で組織図を作成している会社は多いでしょう。Excelのスマートアートなどの部品パーツを使って手動で作るわけですが、非常に面倒で時間の掛かる作業です。場合によっては、1日時間を潰してしまうでしょう。

Google Apps Scriptの場合はスプレッドシートに定義されたデータを書き換えるだけで、自動的に組織図を生成する事が可能です。色々なライブラリがありますが、今回はベタにGoogle Visualization APIを用いて組織図を作成してみたいと思います。また、クリックする事でその人の社員IDを元に情報を引き出すイベントも追加しています。

※Vue.js対応のライブラリを使って同様に組織図を生成する場合の手法を以下のエントリーにて行っています。

Google Apps ScriptでVue対応組織図を生成する【GAS】

1 今回使用するスプレッドシート等
2 ソースコード
- 2.1 GAS側コード
- 2.2 HTML側コード
3 実行と結果
4 関連リンク

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

ソースコード

GAS側コード

function onOpen() {
    var ui = SpreadsheetApp.getUi();
    ui.createMenu('▶セットアップ')
    .addItem('初期化', 'getMySheetId')
    .addToUi();
}

//フォーム表示用
function doGet(){
  var html = HtmlService.createHtmlOutputFromFile('workflow')
    .setSandboxMode(HtmlService.SandboxMode.IFRAME)
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
  return html;
}

//自分自身のIDを取得するコード
function getMySheetId(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var myid = sheet.getId();
 
  var Properties = PropertiesService.getScriptProperties();
  Properties.setProperty("sheetid", myid);
  
  return myid;
}

//部署データを取得して返すコード
function getbusyo(){
  //シート情報を取得
  var Properties = PropertiesService.getScriptProperties();
  var sheetid = Properties.getProperty("sheetid")
  var sheet = SpreadsheetApp.openById(sheetid);
  
  //シートデータを取得
  var ss = sheet.getSheetByName("busyo").getRange("A2:D").getValues();

  //データを返す
  return JSON.stringify(ss);

}

//メンバーデータを取得して返すコード
function getMember(){
  var Properties = PropertiesService.getScriptProperties();
  var sheetid = Properties.getProperty("sheetid")
  var sheet = SpreadsheetApp.openById(sheetid);
  
  //シートデータを取得
  var ss = sheet.getSheetByName("data").getRange("A2:E").getValues();

  //データを返す
  return JSON.stringify(ss);
}

//指定のIDのレコードだけを返す関数
function getEmpInfo(uid){
  var Properties = PropertiesService.getScriptProperties();
  var sheetid = Properties.getProperty("sheetid")
  var sheet = SpreadsheetApp.openById(sheetid);

  //シートデータを取得
  var ss = sheet.getSheetByName("data").getRange("A2:E").getValues();
  var length = ss.length;
  
  //対象のIDのレコードを取得する
  var rec = "";
  var flg = false;
  var array = [];
  
  for(var i = 0;i<length;i++){
    //レコードが見つかったら処理する
    if(ss[i][2] == uid){
      rec = ss[i];
      flg = true;
    }
  }
  
  //配列にデータをpushする
  array.push(rec);
  array.push(flg);
  
  //取得したレコードを返す
  return JSON.stringify(array);
}

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu('▶セットアップ')

.addItem('初期化', 'getMySheetId')

.addToUi();

}

//フォーム表示用

function doGet(){

var html = HtmlService.createHtmlOutputFromFile('workflow')

.setSandboxMode(HtmlService.SandboxMode.IFRAME)

.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);

return html;

}

//自分自身のIDを取得するコード

function getMySheetId(){

var sheet = SpreadsheetApp.getActiveSpreadsheet();

var myid = sheet.getId();

var Properties = PropertiesService.getScriptProperties();

Properties.setProperty("sheetid", myid);

return myid;

}

//部署データを取得して返すコード

function getbusyo(){

//シート情報を取得

var Properties = PropertiesService.getScriptProperties();

var sheetid = Properties.getProperty("sheetid")

var sheet = SpreadsheetApp.openById(sheetid);

//シートデータを取得

var ss = sheet.getSheetByName("busyo").getRange("A2:D").getValues();

//データを返す

return JSON.stringify(ss);

}

//メンバーデータを取得して返すコード

function getMember(){

var Properties = PropertiesService.getScriptProperties();

var sheetid = Properties.getProperty("sheetid")

var sheet = SpreadsheetApp.openById(sheetid);

//シートデータを取得

var ss = sheet.getSheetByName("data").getRange("A2:E").getValues();

//データを返す

return JSON.stringify(ss);

}

//指定のIDのレコードだけを返す関数

function getEmpInfo(uid){

var Properties = PropertiesService.getScriptProperties();

var sheetid = Properties.getProperty("sheetid")

var sheet = SpreadsheetApp.openById(sheetid);

//シートデータを取得

var ss = sheet.getSheetByName("data").getRange("A2:E").getValues();

var length = ss.length;

//対象のIDのレコードを取得する

var rec = "";

var flg = false;

var array = [];

for(var i = 0;i<length;i++){

//レコードが見つかったら処理する

if(ss[i][2] == uid){

rec = ss[i];

flg = true;

}

//配列にデータをpushする

array.push(rec);

array.push(flg);

//取得したレコードを返す

return JSON.stringify(array);

}

GAS側はデータの塊を取得して返したりする事が主な仕事です。
また、指定のIDを元にdataシートからレコードを返すのは情報データを返すための関数です。組織図のクリック時に発動します。

HTML側コード

今回のアプリケーションの主役はHTML側です。組織図というかChartなのでグラフのライブラリを使っています。D3.jsやjQuery OrgChartと違って高機能ではありませんが、小さな組織のデータを管理するには十分なライブラリです。大きな組織や細かい管理が必要な組織の場合には、D3.jsなどのライブラリを使って表現すると良いでしょう。

<html>
  <head>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">
      //OrgChartライブラリをロード
      google.charts.load('current', {packages:["orgchart"]});
      google.charts.setOnLoadCallback(init);

      //グローバル変数
      var data;
      var array = [];
      
      //初期化
      function init(){
        data = new google.visualization.DataTable();
        
        //データテーブルを作る
        data.addColumn('string', 'Name');
        data.addColumn('string', 'Manager');
        data.addColumn('string', 'ToolTip');
      
        //スプレッドシート側からデータを取得する（busyoシート）
        google.script.run.withSuccessHandler(onSuccess).getbusyo();
      }

      //部署データを元にデータを生成
      function onSuccess(data){
        //JSONデータの取得
        var jsonman = JSON.parse(data);
        var length = jsonman.length;
        
        //部署データをまずdataに挿入する
        for(var i = 0;i<length;i++){
          //一時配列を用意
          var tempArray = [];
          
          //連想配列を用意
          var renArray = {};
          
          //部署名とリーダー名をpushする
          renArray.v = jsonman[i][0];
          renArray.f = jsonman[i][0] + "<div style='color:red; font-style:italic'>" + jsonman[i][3] + "</div>";
          tempArray.push(renArray);
          
          //上位組織をpushする
          tempArray.push(jsonman[i][1]);
          
          //社員IDをpushする
          tempArray.push(jsonman[i][2]);
        
          //データ用配列にpushする
          array.push(tempArray);
          
        }
        
        //所属メンバーデータを取得させる
        google.script.run.withSuccessHandler(onMember).getMember();

      }
      
      //所属メンバーデータを元にデータを生成
      function onMember(data){
        //JSONデータの取得
        var jsonman = JSON.parse(data);
        var length = jsonman.length;
        
        //メンバーデータを配列に追加する
        for(var i = 0;i<length;i++){
          //一時配列を用意
          var tempArray = [];
          
          //リーダーフラグがあるモノは処理をスルーする
          if(jsonman[i][4] == true){
            //役職者なのでスルーする
          }else{
            //メンバー名をpushする
            tempArray.push(jsonman[i][0]);
            
            //所属組織をpushする
            tempArray.push(jsonman[i][1]);
            
            //社員IDをpushする
            tempArray.push(jsonman[i][2]);
            
            //配列にpushする
            array.push(tempArray);

          }
        }

        //チャートを描画する
        drawChart();
      
      }

      //チャートを描画する
      function drawChart() {
        //配列データをaddRowsする
        data.addRows(array);

        //チャートを作成する
        var chart = new google.visualization.OrgChart(document.getElementById('chart_div'));
        chart.draw(data, {allowHtml:true});
        
        // 組織図選択時にモーダルを表示するイベントを追加
        google.visualization.events.addListener(chart, 'select', function() {
          //社員IDを取得する
          var employee = data.getValue(chart.getSelection()[0].row,2);
          
          //社員IDを元に社員情報を取得してくる
          google.script.run.withSuccessHandler(onInfo).getEmpInfo(employee);
          
        });
      }
      
      //取得したIDを元に組織図シートから情報を取得する
      function onInfo(data){
        //JSONデータの取得
        var jsonman = JSON.parse(data);
        
        //flg見て処理を分岐
        if(jsonman[1] == true){
          //レコードデータを取得する
          var rec = jsonman[0];
          
          //情報だけ表示する
          alert(rec[3]);

        }else{
          //データが存在しなかった場合の処理
          alert("情報が見つかりませんでした");
        }
      }
      
   </script>
    </head>
  <body>
    <div id="chart_div"></div>
  </body>
</html>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

<html>

<head>

//OrgChartライブラリをロード

google.charts.load('current', {packages:["orgchart"]});

google.charts.setOnLoadCallback(init);

//グローバル変数

var data;

var array = [];

//初期化

function init(){

data = new google.visualization.DataTable();

//データテーブルを作る

data.addColumn('string', 'Name');

data.addColumn('string', 'Manager');

data.addColumn('string', 'ToolTip');

//スプレッドシート側からデータを取得する（busyoシート）

google.script.run.withSuccessHandler(onSuccess).getbusyo();

}

//部署データを元にデータを生成

function onSuccess(data){

//JSONデータの取得

var jsonman = JSON.parse(data);

var length = jsonman.length;

//部署データをまずdataに挿入する

for(var i = 0;i<length;i++){

//一時配列を用意

var tempArray = [];

//連想配列を用意

var renArray = {};

//部署名とリーダー名をpushする

renArray.v = jsonman[i][0];

renArray.f = jsonman[i][0] + "<div style='color:red; font-style:italic'>" + jsonman[i][3] + "</div>";

tempArray.push(renArray);

//上位組織をpushする

tempArray.push(jsonman[i][1]);

//社員IDをpushする

tempArray.push(jsonman[i][2]);

//データ用配列にpushする

array.push(tempArray);

}

//所属メンバーデータを取得させる

google.script.run.withSuccessHandler(onMember).getMember();

}

//所属メンバーデータを元にデータを生成

function onMember(data){

//JSONデータの取得

var jsonman = JSON.parse(data);

var length = jsonman.length;

//メンバーデータを配列に追加する

for(var i = 0;i<length;i++){

//一時配列を用意

var tempArray = [];

//リーダーフラグがあるモノは処理をスルーする

if(jsonman[i][4] == true){

//役職者なのでスルーする

}else{

//メンバー名をpushする

tempArray.push(jsonman[i][0]);

//所属組織をpushする

tempArray.push(jsonman[i][1]);

//社員IDをpushする

tempArray.push(jsonman[i][2]);

//配列にpushする

array.push(tempArray);

}

//チャートを描画する

drawChart();

}

//チャートを描画する

function drawChart() {

//配列データをaddRowsする

data.addRows(array);

//チャートを作成する

var chart = new google.visualization.OrgChart(document.getElementById('chart_div'));

chart.draw(data, {allowHtml:true});

// 組織図選択時にモーダルを表示するイベントを追加

google.visualization.events.addListener(chart, 'select', function() {

//社員IDを取得する

var employee = data.getValue(chart.getSelection()[0].row,2);

//社員IDを元に社員情報を取得してくる

google.script.run.withSuccessHandler(onInfo).getEmpInfo(employee);

});

}

//取得したIDを元に組織図シートから情報を取得する

function onInfo(data){

//JSONデータの取得

var jsonman = JSON.parse(data);

//flg見て処理を分岐

if(jsonman[1] == true){

//レコードデータを取得する

var rec = jsonman[0];

//情報だけ表示する

alert(rec[3]);

}else{

//データが存在しなかった場合の処理

alert("情報が見つかりませんでした");

}

</script>

</head>

<body>

</body>

</html>

チャート表示用の変数およびデータ生成用の変数はグローバル変数として宣言。
冒頭で変数dataに対してnew google.visualization.DataTable()にて初期化を実行後、まずはbusyoシートのデータから生成しています。
その後dataシートのデータを処理し、チャートを描画しています。
event.addListnerにて各組織図のパーツをクリック時に社員IDの値を取得、それを元にシートから情報列の情報を取得してalertで表示するようイベントハンドラーを加えています。
allowHtmlをtrueにするオプションを指定しないと、パーツ内でHTMLタグを利用する事ができません。

実行と結果

データの整備方法

通常組織図は部署だけを取り上げて表示しますが、今回の組織図は部署にさらに人がぶら下がる形式にしました。そのため、データの整備方法が少し変則的です。以下の2つのシートの整備方法を習得できれば、また、構造を理解できれば人事異動・新規採用・退職などの発生をトリガーとして自動的に組織図の改変まで行うことが可能になります。

もう組織図づくりに１日時間を掛けたり漏れやミスに振り回される必要はありません！！

busyoシート

ここは組織図の根幹である「部署」について整備する項目です。

部署名、ぶら下がり先上位組織、そこを管理する長の社員ID、そこを管理する長の氏名の4項目を整備します。リーダー名が赤字で表示されますが、1人目のメンバーは部署名に対してぶら下がりますので、注意！！

dataシート

リーダーフラグがあるものは、データ生成時には利用されません。情報データ参照時に利用されるようになります。ですので、部署側で整備したからといって、それらの管理職をここに記載しないとクリック時に情報が引き出せなくなります。また、部門管理職はリーダーフラグを付けておきましょう。

他のメンバーは並び順の列が変則的で今回の一番の注意点です。そのまま全員を部署にぶら下げてしまうと、横に広がってしまい、チャートとして見づらくなるため、今回は前の人にぶら下がるように設定します。

1人目は部署名を指定してぶら下げます。
2人目以降は前の人(今回でいえば1人目、次の人は2人目といった具合）にぶら下げます。

こうする事でメンバーが部署に対して縦にぶら下がっているかのように見え、扱いやすい組織図になります。

※注意点として名前に対してぶら下がるので、同姓同名が居た場合ややこしい事になります。

組織図の生成

スプレッドシートのメニューよりセットアップ⇒初期化は必ず実行しましょう。スプレッドシートのIDを取得し、スクリプトプロパティに格納され、以降データの取得時に利用されます。各セクションはそれぞれ個人に紐つけてあり、IDを元に検索し、その人の情報をalertで表示するようにコードが組まれています。

組織図の要素の幅を指定する

Visualization APIのOrgChart、スプレッドシート連動させると非常に便利な反面、融通がきかないシーンが出てきます。あまりにも部署が多く横に広がってしまう場合、そのままの状態では、横幅に収めようとする為に１つ１つの要素の横幅が異常に狭くなる。これをある程度の幅を維持しつつ、横スクロールで全体を見えるようにする為にはちょっとした工夫が必要です。

ポイントは2つ

chart_divの横幅を任意のサイズで指定する（横スクロール幅を指定）
要素のスタイルシートで幅を指定する（1個ずつの要素の幅を指定）

chart_divの横幅は直接インラインで指定してみました。かなり部署数が多いので、横幅をかなり広く取っています。

//chart_divの横幅を指定する
<div id="chart_div" style="height: 900px; width: 4000px"></div>

1 2	//chart_divの横幅を指定する <div id="chart_div" style="height: 900px; width: 4000px"></div>

１つ１つの要素はこれでも具合がよろしく無いので、以下のようにスタイルシートを指定します。

.google-visualization-orgchart-table tr td.google-visualization-orgchart-node div {
     width: 200px!important; 
}

.google-visualization-orgchart-table tr td.google-visualization-orgchart-node div {

width: 200px!important;

}

これで横スクロールで幅をもたせて、1個ずつの要素の幅を指定できるため、異常に狭い要素で表示といったことが回避可能です。また、要素の細かな指定としてはdrawをする際にクラスを指定できるので、それを用いても良いかもしれません。

//draw時にクラスを指定
chart.draw(org_view, {
    allowHtml:true,
    nodeClass: 'nodenormal',
    selectedNodeClass: 'nodeselected'
});

//draw時にクラスを指定

chart.draw(org_view, {

allowHtml:true,

nodeClass: 'nodenormal',

selectedNodeClass: 'nodeselected'

});

//通常時と選択時の指定のクラスのCSS
.nodenormal {
    text-align: center;
    vertical-align: middle;
    font-weight: bold;
    cursor: default;
    border: 2px solid #3bdd05;
    -moz-border-radius: 5px;
    -webkit-border-radius: 5px;
    -webkit-box-shadow: rgba(0, 0, 0, 0.5) 3px 3px 3px;
    -moz-box-shadow: rgba(0, 0, 0, 0.5) 3px 3px 3px;
    background-color: #92eeaf;
}
.nodeselected {
    border: 2px solid #170e96;
    background-color: #9d9bcb;
}