はじめに
株式会社LIFULLの斉藤です。
この記事は LIFULLその2 Advent Calendar 2020 の6日目の記事です。
今年も、こよなく愛するGoogleAppsScript(GAS)について書きます。
前書き
突然ですが、皆さんは自身が所属する部署でプロジェクト群をどのように管理していますか？
私が所属する部署では、タスクベースで管理するJIRAとは別に施策群全体を把握するため、
スプレッドシート管理をしているのですが、
目的や優先度、その他リスク諸々様々な情報が書き込まれていますし、直近での変化がわかりづらいため、
週次での共有MTGでは、前回報告した内容との変化がピンポイントでわかるよう、
都度、専用の文章を用意しています。
この記事では、プロジェクト管理表の更新をしていくだけで、
他に何もすることなくそのまま報告文書として使えてしまうBPRを紹介します。
これをすることによって、効率がよくなるだけでなく、
報告者に依存することなく共有が図れるようになるといった、リスクヘッジにもなります。
プロジェクト管理表を更新するだけでOK！にする
ここからが本題です。
前書きに書いた課題を解消させるため、以下を実現させます。
- 前回報告した内容とどこが変わっているかをわかりやすくする
- 報告に必要な項目のみに絞って表示する
これを人力ではなく、プログラム(GAS)によって自動反映されるようにすることで、
プロジェクト管理表を更新した人は何も意識することなく、
それがそのまま報告用としても使えるようになれればと考えました。
1. 前回報告した内容とどこが変わっているかをわかりやすくする
これを実現するための工程を以下のようにしました。
1-1. どのセルが更新されたかを捕捉し、更新日を記録する
1-2. 〇日以内に更新されたセルを目立たせる
1-1. どのセルが更新されたかを捕捉する
※今回紹介するScriptは、「Container Bound Script」で作成しています。
詳しくは Google Apps Script で Spreadsheet にアクセスする方法まとめ をご覧ください。
まず、どのセルが更新されたかを検知させる方法なのですが、以下の名前のメソッドを書くことで、
セルの内容を更新した際（どこかのセル内に入力後、確定させたタイミング）に、
勝手に発動するようになります。
function onEdit(e) {
// 更新時に実行させたいメソッドをここに書きます
recodeUpdateDay(e);
}
実際に実行させるメソッドが以下です。
それぞれどういったことを行っているかはコメントアウトを参照ください。
function recodeUpdateDay(e) {
// 更新日を記録したいシート（下記例は「施策リスト」という名前のシート）以外は除外します
var ss = SpreadsheetApp.getActiveSpreadsheet(); // スプレッドシート
var sheet = ss.getActiveSheet(); // 更新したセルがある対象シート
var sheetName = sheet.getName(); // 選択シート名
if (sheetName != '施策リスト') {
return;
}
// 変更したセルの行番号と列番号を取得します
var range = sheet.getActiveRange(); // 選択セル範囲
var rowIndex = range.getRowIndex(); // セル範囲の行番号
var colIndex = range.getColumnIndex(); // セル範囲の列番号
// 変更したかどうかを捕捉させたい項目を絞り、それぞれ用に設けた列に更新日を挿入します。
// 私の場合は、「ステータス」（例：検討中→実装中）、「進捗状況」、「リリース日」にしています。
var today = Utilities.formatDate(new Date(), "JST","YYYY/MM/dd");
switch(colIndex) {
case 24: // 「ステータス」欄がある列番号
// 「ステータス」欄の更新日記録用列を54列目に用意した場合
sheet.getRange(rowIndex, 54).setValue(today);
break;
case 25: // 「進捗状況」欄がある列番号
// 「進捗状況」欄の～（以下略）
sheet.getRange(rowIndex, 55).setValue(today);
break;
case 27: // 「リリース日」欄がある列番号
// 「リリース日」欄の～（以下略）
sheet.getRange(rowIndex, 56).setValue(today);
break;
default:
break;
}
}
1-2. 〇日以内に更新されたセルを目立たせる
スプレッドシート側で、更新日と現在の日付を見比べて、〇日以内かどうかを判定します。
スプレッドシートの「表示形式」メニューから「条件付き書式」を選んで設定画面を呼び出します。
例えば、上記の「ステータス」欄の変更箇所を目立たせたい場合、以下のように設定します。
| 項目 | 設定内容 |
|---|---|
| 適用範囲 |
X2:X999 ※X列＝上記で説明したコードで使用した列番号(colIndex)24です |
| 書式ルール | カスタム書式 |
| 設定内容 |
=datedif(today(), X2, "D")<=7 ※更新日から7日以内を目立たせたい場合 ※適用範囲の先頭行を指定すれば、全行適用されます （比較対象にしたいセルを固定にしたい場合は$を使用します） |
これで完成です。
上記は毎週開催なため、7日以内で設定していますが、
共有会議が2週間置きであれば14にするなど、何日以内の更新を目立たせたいかで調整してみてください。
2. 報告に必要な項目のみに絞って表示する
後は、報告に必要そうな条件や並び順をフィルタ表示として保存して、
呼び出しURLを共有すれば一応目的は果たせるのですが、
管理表をもっとすっきり見せる方法として、スプレッドシートが用意しているquery関数を活用します。
報告用シートを別に用意し、以下の設定を行います。
※自身が実際に管理しているシートで出力したい列名と「ステータス」欄の値をそのまま記載しています。
=query(
'施策リスト'!A2:BD999,
"select
A, C, X, V, W, Y, AA, BB, BC, BD
where
X MATCHES '^(2.企画アサイン済み|3.企画・調査中|4.仕様FIX|5.開発中|6.リリース済／完了)$'
order by
X"
)
今回、query関数のことに関しては深く触れませんが、ここで一つ問題が発生します。
それっぽいものが表示できるようになったものの、
一部、複数の行に記載したはずの内容が１つの行にまとまって表示されてしまうといった現象が発生。
調べたところ、参照元にしているシートで表示形式が「書式設定なしテキスト」以外に設定されていると起こる現象とのこと。
全て「書式設定なしテキスト」にするといった選択肢もあるものの、それだと別の弊害が生まれたため、
テキスト形式に変換してくれるTO_TEXT関数を使用しようとしたものの、単体だとうまく動かなかったのですが、
ARRAYFORMULAと併用することで解消しました。
=query(
ARRAYFORMULA(TO_TEXT('施策リスト'!A2:BD999)),
"select
Col1, Col3, Col24, Col22, Col23, Col25, Col27, Col54, Col55, Col56
where
Col24 MATCHES '^(2.企画アサイン済み|3.企画・調査中|4.仕様FIX|5.開発中|6.リリース済／完了)$'
order by
Col24"
)
この場合、ARRAYFORMULAをかましたことで、変更前で使用していた「A」や「C」といった列名ではなく、
「Col1」といった記法に変える必要があります。
※「Col」の後に何列目かの数値を記載すればOKです。
後は、報告用シートにも1-2の方法を適用し、直近で変更した内容が目立つようにします。
更なる改善
後は、報告文書内に完成したシートへのリンクを貼ればよいのですが、
自社で使用しているConfluenceから直接見られるよう、HTMLマクロを用いて
<iframe src="報告用シートのURL" width="800" height="500">
といった形で埋め込めば、更に報告が楽になります。
最後に
スプレッドシートとGoogleAppsScriptの良さをうまく活用してちょっとした工夫をすれば、
他にも色々とBPRできちゃいます！
紹介した内容を一部だけを切り取っても何かに使えるかと思いますので、是非参考にしてみてください。