この記事は 弁護士ドットコム Advent Calendar 2019 - Qiita の13日目の記事です。
弊社の Google Spreadsheet形式の組織図から、Google Apps Script (GAS) を使って人事異動情報を生成してみましたので、ご紹介します。
きっかけ
弊社では最近、人事異動情報のうち、一部のみが社内に公開されるようになっています。
社内に公開されている情報
- 最新の組織図
- 入社／退職する従業員名
社内に公開されていない情報
- 従業員ごとの部署／チーム間の異動
- 従業員ごとの昇格／降格
これは人事系システムの移行に伴う暫定状態なので、やがて解消するのですが、誰がどこに異動したかわからないと不便なこともありますよね。
かといって、組織図を目diffするのはしんどいし、見落としも多いです。
そこで組織図から自動的に差分を抽出し、異動情報として出力できないか、と考えました。
組織図サンプル
弊社の組織図は、Google Spreadsheetのファイルになっていて、毎月、最新版が「組織図_YYYYMMMDD」のファイル名で公開されます。
一般の従業員（私も含む）には編集権限はなく、閲覧のみ許可されています。
見た目はこんな感じです。
※公開用に架空の部署名・役職・氏名に置き換えてあります
上部（3行目）に部署の種類が記載されています。
本部, 部・室, チームの3種類ですね。時期によっては、グループ が増えたりします。
これらをヘッダーと呼ぶことにします。
また、各ヘッダーから下に見ていくと、ヘッダーと同じ列に事業本部, alphaチームなどの部署名が出現することがわかります。
さらに、各部署名の直下には、所属する従業員名, チームマネージャーなどの役職が書かれています。
これらのパターンに基づいて、情報を取得していきます。
処理の流れ
- Google Drive から 2019年の組織図ファイルをすべて取得します。
- 各組織図シートから、「完全な組織名の一覧」（◯◯本部 > ◯◯部 > ◯◯チーム）を生成します。これは階層化されたオブジェクトではなく、ただの文字列の配列にします。
- 各組織名に対して、所属する従業員を取り出し、「完全な組織＆従業員名の一覧」（◯◯本部 > ◯◯部 > ◯◯チーム 役職名 氏名）を生成します。こちらもただの文字列の配列です。
- 前月分と今月分の「完全な組織＆従業員名の一覧」を突き合わせて、片方にのみ存在するものを出力します。
この方法で概ね問題なく自動化できます。
しかしながら、情報をスプレッドシートから読み取るという特性上、きれいに情報取得できない部分がありました。
下図の赤線の部分が、つながっているか、つながっていないかは判断できません。
GAS では、罫線の状態を設定はできるものの、取得はできない ためです。
編集部は、事業本部に属した部門なのか、はたまた独立しているのか見分けがつかないのです。
この差を自動的に見分ける方法が見つからなかったので、代わりに次のようにしました。
- 基本的には上部部門（スプレッドシートでは左側にある部門）に属しているとみなします
- 編集部のような特定の部署名が出現した時だけ、その直前で紐付けがなくなったものとみなします
幸いにも、年内のすべての組織図で、上部部門との紐付けが途切れたタイミングで必ず特定の部署名が出現しているので、汎用性は失われるものの、見分けを付けるという目的は果たせました。
コード
今回のコードは以下の４つのファイルで構成されています。
| gsファイル名 | 概要 |
|---|---|
| GDriveService | 汎用的な Google Drive の操作 |
| GSheetsService | 汎用的な Google SpreadSheet の操作 |
| Bengo4OrgService | 弊社の組織図の操作 |
| main | エントリポイント |
まずは Google Drive および Google Spreadsheet を操作するための、擬似クラスを用意してみました。目的は、少しでも本質的なビジネスロジックの見通しを良くすることです。いまのところ GAS では ECMAScript 2015 における JavaScript クラス に相当するものはなく、今回は TypeScript も使わないため、擬似的な形になっています。
/**
* Google Drive の操作クラス
*/
(function (global) {
/**
* コンストラクタ
*/
var me = function (searchQuery) {
if (!searchQuery) {
throw new Error("searchQuery is required.");
}
this.searchQuery = searchQuery;
};
/**
* Google Driveから条件に合うファイルを、ファイル名順の配列で返却します
*/
me.prototype.list = function () {
return filesItrToArray(DriveApp.searchFiles(this.searchQuery));
};
/**
* ファイルイテレータをファイルの配列に変換して返却します
*/
var filesItrToArray = function (filesItr) {
var files = [];
while (filesItr.hasNext()) {
files.push(filesItr.next());
}
files.sort(function (a, b){ return a.getName().localeCompare(b.getName()) });
return files;
}
global.GDriveService = me;
})(this);
/**
* Google Spreadsheet の操作クラス
*/
(function (global) {
/**
* コンストラクタ
*/
var me = function (file) {
if (!file) {
throw new Error("file is required.");
}
this.file = file;
};
/**
* ファイルから最初のシートを返却します
*/
me.prototype.first = function (file) {
return this.sheetByIndex(0);
};
/**
* ファイルから指定したインデックスのシートを取得します
*/
me.prototype.sheetByIndex = function (index) {
return SpreadsheetApp.openById(this.file.getId()).getSheets()[index];
}
global.GSheetsService = me;
})(this);
次に、ビジネスロジックの部分です。
updateColIndexOrgNameMap に格納されている関数では、
完全組織名を作るために組織名を次々と上書きするようにしています。
シート内の全組織をイテレートする中で、以下の処理が繰り返されます。
- 上部部門（スプレッドシートでは左側にある部門）の名前は基本的にそのまま残す
- 「編集部」だけは例外的に左側の部門もクリアする
- 自組織と同列の組織名を上書きする
- 下部部門（スプレッドシートでは右側にある部門）の名前はクリアする
/**
* 弁護士ドットコム組織 の操作クラス
*/
(function (global) {
/**
* コンストラクタ
*/
var me = function (sheet) {
if (!sheet) {
throw new Error("sheet is required.");
}
this.values = sheet.getDataRange().getValues();
this.ORG_HEADER_ROW_INDEX = 2;
}
/**
* 組織の種類が表記されている行（＝ヘッダー行と呼ぶ）の列（＝ヘッダー列と呼ぶ）の列番号を取得します
*/
me.prototype.getOrgHeaderColIndices = function () {
if (!this.orgHeaderColIndices) {
this.orgHeaderColIndices = [];
const rowValues = this.values[this.ORG_HEADER_ROW_INDEX];
for (var colIndex = 0; colIndex < rowValues.length; colIndex++) {
if (!rowValues[colIndex]) {
continue;
}
this.orgHeaderColIndices.push(colIndex);
}
}
return this.orgHeaderColIndices;
};
/**
* 完全な組織名（◯◯本部 > ◯◯部 > ◯◯チーム) とシート上の位置を取得します
*/
me.prototype.getFullOrgNamePositionMap = function () {
if (!this.fullOrgNamePositionMap) {
this.fullOrgNamePositionMap = {};
var colIndexOrgNameMap = {};
const colIndices = this.getOrgHeaderColIndices();
//ヘッダー行の直下から１行ごとに見る
for (var rowIndex = this.ORG_HEADER_ROW_INDEX + 1; rowIndex < this.values.length; rowIndex++) {
//ヘッダー行に現れたヘッダーの列ごとに見る
for (var i = 0; i < colIndices.length; i++) {
var colIndex = colIndices[i];
var value = this.values[rowIndex][colIndex];
//セルに値（組織名 or 役職名）があり、かつ コロンがない（＝役職名ではない）場合のみ処理し、それ以外はスキップ
if (!(value && value.indexOf('：') == -1) {
continue;
}
//セルに入っている組織名と、そのセルの位置に応じて、列インデックス×組織のハッシュマップを更新する
colIndexOrgNameMap = updateColIndexOrgNameMap(colIndexOrgNameMap, colIndex, value);
//列インデックス×組織のハッシュマップから、完全な組織名を生成して、完全組織名x位置のハッシュマップに追加
var fullOrgName = generateFullOrgName(colIndexOrgNameMap);
this.fullOrgNamePositionMap[fullOrgName] = [rowIndex, colIndex];
};
}
}
return this.fullOrgNamePositionMap;
}
/**
* 完全組織名・役職名・人名の一覧を取得します
*/
me.prototype.getFullOrgTitleMembers = function () {
if (!this.fullOrgTitleMembers) {
this.fullOrgTitleMembers = [];
var fullOrgNamePositionMap = this.getFullOrgNamePositionMap();
var orgNames = Object.keys(fullOrgNamePositionMap);
orgNames.forEach(function (orgName) {
var position = fullOrgNamePositionMap[orgName];
var titleCol = position[1];
var memberNameCol = position[1] + 1;
for (var row = position[0] + 2; row < this.values.length; row++) {
var member = this.values[row][memberNameCol].replace(/[(（].*$/g, '');
if (!member){
break;
}
var title = this.values[row][titleCol].replace('：', '').replace('部直下', '');
var fullOrgTitleMember = [orgName, title, member].join(' ');
this.fullOrgTitleMembers.push(fullOrgTitleMember);
}
}, this);
}
return this.fullOrgTitleMembers;
};
/**
* ２つの「完全組織名・役職名・人名の一覧」から差分を取得します
*/
me.prototype.getFullOrgTitleMembersDiff = function (prevs, targets) {
if (!prevs || !targets) {
throw new Error('prevs and targets are both required.');
}
var onlyInPrevs = [];
var onlyInTargets = [];
prevs.forEach(function (prev) {
if (targets.indexOf(prev) == -1) {
onlyInPrevs.push(prev);
}
});
targets.forEach(function (target) {
if (prevs.indexOf(target) == -1) {
onlyInTargets.push(target);
}
});
return {onlyInPrevs: onlyInPrevs, onlyInTargets: onlyInTargets};
}
/**
* セルに入っている組織名と、そのセルの位置に応じて、列インデックス×組織のハッシュマップを更新します
*/
var updateColIndexOrgNameMap = function (colIndexOrgNameMap, colIndex, orgName) {
// 現在の列に対して、キーを追加または値を上書きする
colIndexOrgNameMap[colIndex] = orgName;
Object.keys(colIndexOrgNameMap).forEach(function (key) {
if (parseInt(key) > colIndex) {
// 現在よりも右の列のキーは、現在の組織とは紐付かない下部組織なので、要素をクリアする
delete colIndexOrgNameMap[key];
} else if (parseInt(key) < colIndex) {
// 基本的には左側の列の値は維持する（変更しない）
if (orgName === '編集部') {
//例外的に、組織名が「編集部」だった場合は、左側の列に相当するキーの値をクリアする
delete colIndexOrgNameMap[key];
}
}
});
return colIndexOrgNameMap;
}
/**
* 列インデックス×組織のハッシュマップから、完全な組織名（◯◯本部 > ◯◯部 > ◯◯チーム) を生成します
*/
var generateFullOrgName = function (colIndexOrgNameMap) {
const orgNames = [];
var keys = Object.keys(colIndexOrgNameMap);
keys.sort(function (a, b) { return parseInt(a) - parseInt(b); });
keys.forEach(function (key) {
orgNames.push(colIndexOrgNameMap[key]);
});
return orgNames.filter(function (orgName) { return !!(orgName.replace(/(^\s+)|(\s+$)/g, "")); }).join(' > ');
}
global.Bengo4OrgService = me;
})(this);
最後に、全体のエントリポイントです。
Google Driveで組織図を検索するための条件指定や、ログ出力の処理が入っています。
/**
* 弁護士ドットコム人事異動チェッカーのエントリポイント
*/
function main() {
const SEARCH_QUERY = 'title contains "組織図_2019" and mimeType = "application/vnd.google-apps.spreadsheet"';
const gDriveService = new GDriveService(SEARCH_QUERY);
//google drive から すべての組織図ファイルを取得します
gDriveService.list().forEach(function (file) {
const gSheetsService = new GSheetsService(file);
//組織図が書かれているシートを取得します
const sheet = gSheetsService.first(file);
//組織図から、完全組織名・役職名・人名の一覧を取得します
const bengo4OrgService = new Bengo4OrgService(sheet);
const fullOrgTitleMembers = bengo4OrgService.getFullOrgTitleMembers();
//直前の組織図と比べて差分を出力します
if (this.prevFullOrgTitleMembers) {
var fullOrgTitleMembersDiff = bengo4OrgService.getFullOrgTitleMembersDiff(prevFullOrgTitleMembers, fullOrgTitleMembers);
Logger.log('============================= ' + file.getName() + 'の異動情報 ☆ミ =============================');
fullOrgTitleMembersDiff['onlyInPrevs'].forEach(function (prev) {
Logger.log('--- ' + prev);
});
fullOrgTitleMembersDiff['onlyInTargets'].forEach(function (target) {
Logger.log('+++ ' + target);
});
}
this.prevFullOrgTitleMembers = fullOrgTitleMembers;
}, this);
}
出力結果サンプル
前提として、さきほどの組織図サンプルを2019年12月分とみなし、下図を2019年11月分とします（順序が逆なので注意してください。下図は異動前の状態です）。
そして下記が出力結果になります。
--- で始まる行は 前月と比べて無くなった 情報、
+++ で始まる行は 今月追加になった 情報です。
============================= 組織図_20191201の異動情報 ☆ミ =============================
--- 事業本部 > 営業部 吉田 夏子
--- 事業本部 > 営業部 > アフターセールスチーム 佐藤 次郎
--- 事業本部 > 開発部 伊藤 太郎
--- 事業本部 > 開発部 > alphaチーム 中村 次郎
--- 事業本部 > 開発部 > alphaチーム 小林 三郎
--- 編集部 > ニュースチーム 山本 夏子
+++ 事業本部 > 営業部 > アフターセールスチーム 吉田 夏子
+++ 事業本部 > 営業部 > 新規営業チーム 山本 春子
+++ 事業本部 > 開発部 > bravoチーム チームマネージャー 伊藤 太郎
+++ 事業本部 > 開発部 > bravoチーム 中村 次郎
+++ 事業本部 > 開発部 > bravoチーム 小林 三郎
+++ 経営企画室 吉田 夏子
たとえば、吉田 夏子 さんに着目すると、
事業本部 > 営業部 （直下）から 離脱して、
事業本部 > 営業部 > アフターセールスチーム に 異動。と同時に、
経営企画室 も兼務になっています。
実際には、一度の実行で2019年の1年分を、連続して出力しています。
あとがき
GASは JavaScript とは似て非なるもので、思い通りに動かせず、アドベントカレンダーの期日直前で苦しみました。しかし、コードは思い通りに動くのではなく、書いたとおりに動くものでしたね。
コードは動くところまでなんとか持っていったもので、改善点やバグはたくさんあると思います。遠慮無くご指摘頂けると幸いです。
今回使ったのは架空の組織図ですが、弁護士ドットコムの実際のエンジニアリング組織については、弁護士ドットコムアドベントカレンダー 7日目の PHPカンファレンス2019に登壇してきました（スポンサー枠で） にて、弊社テックリードが説明しております。