GAS & 条件付書式を駆使してスプレッドシートでガントチャートを作った

2022/04/26

Googleスプレッドシートでガントチャートを作ってみました。ガントチャートはデフォルトで用意されているテンプレートにもありますが、あくまでフォーマットのみで進捗バーの更新はマニュアルだったので、日付を変えると自動で描画するようなものを作ってみようと思った次第です。

1 完成形の紹介
2 名前の範囲の設定
3 条件付き書式の設定
4 GASソースコード

完成形の紹介

完成形がこちらです。日付を変更すると自動で進捗バーが描かれるようになっています。

機能の概要は以下のとおりです。

プロジェクト開始日と日付間隔を設定すると表に自動で日付と曜日がセットされる。（GAS）
各タスクの開始予定日と終了予定日を設定すると水色の進捗バーが自動で描画される。（条件付き書式）
達成率を入力すると青のバーが上描きされる。

動画にもしているので参考まで。自動で描画される様が確認できます。こんな機能をGASと条件付き書式を駆使して実現しています。

名前の範囲の設定

以下のように各セルに名前付けを設定しています。GASからアクセスをセルアドレスではなく名前で指定しています。名前付き範囲の設定方法はこちらを参照ください。

こちらが設定した状態です。

条件付き書式の設定

条件付き書式は以下のように設定しました。範囲は進捗バーが描かれる範囲（H10:AC37）、カスタム数式を選んで以下の数式を設定しています。進捗バーはこれだけで描画できます。

Aの数式
=and((($F10-$E10)*$G10+$E10)>H$8,$E10<H$8+$H$6,$G10>0.05)

Bの数式
=and(H$8+$H$6>$E10,H$8<=$F10)

GASソースコード

最後にGAS側のソースコードです。onEdit関数を使って編集時にイベントを発生させています。プロジェクト開始日と間隔の日付を編集すると自動で表の日付がセットされるようになります。

var sheet = SpreadsheetApp.getActiveSheet();
var sp_sheet = SpreadsheetApp.getActiveSpreadsheet();
var st_range = sp_sheet.getRangeByName('プロジェクト開始日');
var dt_range = sp_sheet.getRangeByName('日付開始列');
var interval_range = sp_sheet.getRangeByName('間隔');
var progress_range = sp_sheet.getRangeByName('進捗');
var planCount = progress_range.getNumColumns();

function onEdit(e){
  //編集セルがプロジェクト開始日だった場合
  if(equals(e.range, st_range)==true || equals(e.range, interval_range)==true){
    setupDate();
  }
}

function setupDate(){
    var dt = st_range.getValue();
    var interval = interval_range.getValue();
    dt.setDate(dt.getDate()-interval);
    for(var c=0; c<planCount; c++){
      dt.setDate(dt.getDate()+interval);
      var rr = dt_range.getRow();
      var cc = c+dt_range.getColumn();
      sheet.getRange(rr, cc).setValue(dt);
      sheet.getRange(rr+1, cc).setValue('=text('+getColName(cc)+rr+ ',"ddd")');
    }
}

function equals(r1, r2){
  if(r1.getColumn()==r2.getColumn() && r1.getRow()==r2.getRow()){
    return true;
  }else{
    return false;
  }
}

function getColName(num) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var result = sheet.getRange(1, num);
  result = result.getA1Notation();
  result = result.replace(/\d/,'');
 
  return result;
}

スプレッドシートごとGitHubにアップできれば良いんですけどね。共有化が基本で他サーバーへのファイルコピーができないのでちょっと難しいかな。

- Google Apps Script