イノベーション エンジニアブログ


株式会社イノベーションのエンジニアたちの技術系ブログです


このエントリーをはてなブックマークに追加

GASでガントチャート的なものをやってみる

はじめまして、今月からイノベーションで働くことになりましたKTNと申します!
入ったばっかりなのでわからないことが多く、勉強勉強の毎日です。
なので、趣味のオンラインゲーム(Dota 2)をする時間がありません。
Dota 2ご存知ですか?大会では2000万ドルとか賞金が出るようなので、
プロゲーマーなって一攫千金いかがでしょうか!?

で、いざブログを書こうとなると『ネタ』が見つからず、困ってしまいました。
GASを使った記事が最近多かったので、
私も乗っかってGASでスケジュール管理出来そうなものを作ってみようと思います。
一回で完成まで行くのは難しそうなので、タスクの予定日(開始、終了)を設定すると、
ガントチャート的なものとイナズマ線的なものが作成されるというのを作ってみたいと思います。
あくまでも「的なもの」です。
まず、フォーマットを作りました。これはGASではなく手動で作ったものです。

2.png

その1 土日を灰色にする

単純に土日を探して灰色にしてみようと思います。

function setYasumiHaiiro(sheet) {
  const c_startcalendar_col = 8;
  const c_endcalendar_col = 38;
  const c_day_row = 2;
  const c_endcalendar_row = 18;

  //背景色クリア
  sheet.getRange("H2:AL18").setBackground("white");

  //休みを探して灰色に(今は土日だけ)
  for(var w_col = c_startcalendar_col; w_col <= c_endcalendar_col; w_col++){
    if(checkYasumi(new Date(sheet.getRange(c_day_row, w_col).getValue()))){
      sheet.getRange(c_day_row, w_col, c_endcalendar_row - c_day_row + 1, 1).setBackground("gray");
    }
  }
}

function checkYasumi(w_date){
  w_weekday = w_date.getDay();
  if (w_weekday == 0 || w_weekday == 6) {
    return true;
  }else{
    return false;
  }
}
3.png

こんな感じになりました。本当は1/2、1/3、1/9も灰色になって欲しいのですが、
今回は土日だけです。次回の伸び代として残しました。手抜きじゃ無いです!

その2 ガントチャート的なものを作る

function makeKeikakujisseki(sheet){
  const c_startcalendar_col = 8;
  const c_endcalendar_col = 38;
  const c_planstart_col = 3;
  const c_planend_col = 4;
  const c_day_row = 2;
  const c_startcalendar_row = 3;
  const c_endcalendar_row = 18;

  //セルの値クリア
  sheet.getRange("H3:AL18").setValue("");

  //予定開始日、終了日からガントチャートのようなものを作る(□を埋める)
  //タスクの行数分ぐるぐるする
  for(var w_row = c_startcalendar_row; w_row <= c_endcalendar_row; w_row++){
    //予定開始日が無い行はスキップ
    var w_planstart = sheet.getRange(w_row, c_planstart_col).getValue();
    if(sheet.getRange(w_row, c_planstart_col).getValue() == ""){
      continue;
    }

    //期間と進捗率から何セル■にするか計算する
    var w_kikan = sheet.getRange(w_row, 5).getValue();
    var w_sincyoku = sheet.getRange(w_row, 6).getValue();
    var w_jissizuminissuu = Math.floor(w_kikan * w_sincyoku)

    //予定開始日が入っている場合は、□か■を入れる場所を探す
    for(var w_col = c_startcalendar_col; w_col <= c_endcalendar_col; w_col++){
      //予定開始日の日付から□を入れる場所を探す
      var w_str2 = sheet.getRange(c_day_row, w_col).getValue();
      if(sheet.getRange(w_row, c_planstart_col).getValue().getTime() != w_str2.getTime()){
        continue;
      }
      //予定終了日まで□か■を入れていく
      for(var w_col2 = w_col; w_col2 <= c_endcalendar_col; w_col2++){
        if(w_jissizuminissuu > 0){
          sheet.getRange(w_row, w_col2).setValue("■");
          w_jissizuminissuu--;
        }else{
          sheet.getRange(w_row, w_col2).setValue("□");
        }
        //予定終了日を越えたら処理終了
        if(sheet.getRange(c_day_row, w_col2).getValue().getTime() == sheet.getRange(w_row, c_planend_col).getValue().getTime()){
          break;
        }
      }
    }
  }
}
4.png

だんだんそれっぽくなってきましたね。なってきましたよね?
2月以降使え無いじゃないか。とか
タスク増やせ無いじゃないか。とか
全行処理してんの??? 日付を変更した行だけ処理すればいいんじゃないの??? とか
ツッコミは数々ありとは思いますが、そこも今後の伸び代です!

その3 イナズマ線的なものを作る

さて、ここからが本題です。 これがやりたくて作り始めたようなものです。
そして気合を入れてドキュメントを見ていて気がついたのですが、
GASからスプレットシートに線を引くことが出来無いっぽいです。。。
てっきりExcel VBAみたいに出来るのかと思っていたのですが、事前調査は大事ですね。
ということでGASでスケジュール管理は出来無いということで、終了したいと思います。





と、いくらなんでもこれで終わることは出来ませんので、なんとか代替案を考えようと思います。
背景を黄色に塗っていくことで、イナズマ線的な雰囲気を出して行こうかと。
しょうがないですよね。

function makeInazumaModoki(sheet){
  const c_startcalendar_col = 8;
  const c_endcalendar_col = 38;
  const c_planstart_col = 3;
  const c_planend_col = 4;
  const c_parcent_col = 6;
  const c_day_row = 2;
  const c_startcalendar_row = 3;
  const c_endcalendar_row = 18;

  //今日の日付の位置を取得する
  for(var w_col = c_startcalendar_col; w_col <= c_endcalendar_col; w_col++){
    var w_today = Utilities.formatDate(new Date(), "JST", "YYYYMMdd");
    var w_day = Utilities.formatDate(sheet.getRange(c_day_row, w_col).getValue(), "JST", "YYYYMMdd");
    if(w_today == w_day){
      break;
    }
  }
  var w_today_col = w_col;

  //タスクの行数分ぐるぐるする
  for(var w_row = c_startcalendar_row; w_row <= c_endcalendar_row; w_row++){
    var w_kikan = sheet.getRange(w_row, 5).getValue();
    var w_sincyoku = sheet.getRange(w_row, 6).getValue();
    var w_jissizuminissuu = Math.floor(w_kikan * w_sincyoku)

    //タスクが無い列は今日に線を引く
    if(sheet.getRange(w_row, c_planstart_col).getValue() == ""){
      sheet.getRange(w_row, w_today_col).setBackground("orange");
      continue;
    }

    //遅延が発生しているかチェック
    //遅延が発生している場合は進捗状況をチェックして線を引く
    //遅延が発生してい無い場合は今日に線を引く
    if((sheet.getRange(w_row, c_planend_col).getValue() < new Date()) && (sheet.getRange(w_row, c_parcent_col).getValue() != "1")){
      //■を探す
      for(var w_col = w_today_col; w_col >= c_startcalendar_col; w_col--){
        if(sheet.getRange(w_row, w_col).getValue() == "■"){
          break;
        }
      }

      //■が見つかった場合はそのセルに線を引く
      if(w_col >= c_startcalendar_col){
        sheet.getRange(w_row, w_col).setBackground("orange");
      }else{
        //□を探す
        for(var w_col2 = c_startcalendar_col; w_col2 <= c_endcalendar_col; w_col2++){
          if(sheet.getRange(w_row, w_col2).getValue() == "□"){
            sheet.getRange(w_row, w_col2).setBackground("orange");
            break;
          }
        }
      }
    }else{
      sheet.getRange(w_row, w_today_col).setBackground("orange");
    }
  }
}

一応最後に、呼び出し元も書いておきます。

function myFunction() {
  //スプレッドシートを取得
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  //現在のシートを取得
  var sheet = spreadsheet.getActiveSheet();

  //非営業日を灰色に
  setYasumiHaiiro(sheet);

  //予定開始日、終了日からガントチャートみたいなのを
  makeKeikakujisseki(sheet);

  //イナズマ線みたいなのを
  makeInazumaModoki(sheet);
}
5.png

ということで、GAS触って3日目 ぐらいですが、
なんとか動くものが出来ました。
というか今回の使い方では、Excel VBAと変わらない感じですね。
もっとGAS勉強して次回はイナズマ線引きたいと思います!

ではまた次回で。