ポンコツエンジニアのごじゃっぺ開発日記。

いろいろポンコツだけど、気にするな。プログラム&ロボット大好きなポンコツが日々の記録を残していきます。 自動で収入を得られるサービスやシステムを作ることが目標!!

スプレッドシートとGASを組み合わせて簡単に入力できるガントチャートを作成してみた。

スケジュール管理ツールってたくさんあって、悩んでしまいますよね。

工数管理しなきゃ!スケジュール引かなきゃ!って思うけど、ツールの選定に時間を使ってしまっては勿体無い。だからといって、ツールを作るのも勿体無いですが、今回はあえてツールを作ってみました!

 

概要

今回作成したものは、スプレッドシート上でスケジュール管理ができるものです。

まずは、ざっくりとしたスクリーンショットを紹介したいと思います。

f:id:ponkotsu0605:20190401013739p:plain

こんな感じに、キレイに色がついていて、スプレッドシートで作ったものにしては見やすいんじゃないかなと思います。

こんなに色々な色をつけるのってすごいめんどくさいんじゃないかな?って思うかもしれないですが、そんなことはありません!

色をつけるところまで全部自動化されています!

実装方法

新しくスプレッドシートのプロジェクトを作成します。

f:id:ponkotsu0605:20190401013807p:plain

シートを2つ用意して、一つ目を「スケジュール」、二つ目を「tmp」とします。

「スケジュール」のシートのみを使うので、そちらのシートをアクティブにして使用してください。

また「tmp」は「スケジュール」のシートと同じかそれ以上のセルの数(行と列)を用意してあげないと正常に動きません。

f:id:ponkotsu0605:20190401013831p:plain

以下のような感じにデータを入れておきます。(ここは結構大変かも)

f:id:ponkotsu0605:20190401013856p:plain

「ツール」→「スクリプトエディタ」を開く。

f:id:ponkotsu0605:20190401013921p:plain

以下のスクリプトを以下の画像のように貼り付ける。

f:id:ponkotsu0605:20190401013939p:plain

var HOLYDAY_STRING = '休み'
// 項目名の列
var ITEM_NAME_ROW_INDEX = 1
// 項目の最初の行
var ITEM_NAME_START_LINE_INDEX = 3;

var DATA_START_ROW_INDEX = 3;

// 曜日の列
var DAY_ROW_LINE_INDEX = 2

var sheet,allDataRange,values,tmpSheet

function onEdit(event) {
  main()
}

function init() {
  sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('スケジュール')
  tmpSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('tmp')
  allDataRange = sheet.getDataRange()
  values = allDataRange.getValues()
  
}

function main() {
  init()
  if (sheet.getRange(1, 1).getFontColor() != '#ffffff') {
    sheet.getRange(1, 1).setBackground('#f00')
    tmpSheet.getRange(1, 1).setBackground('#f00')
    return
  }
  clear()
  sheet.getRange(1, 1).setFontColor('#000')
  tmpSheet.getRange(1, 1).setFontColor('#000')
  setNameLineLabelColor()
  setHoliday()
  setManHour()
  setHoliday()
  sheet.getDataRange().setBackgrounds(tmpSheet.getRange(1,1,values.length, values[0].length).getBackgrounds())
  sheet.getDataRange().setFontColors(tmpSheet.getRange(1,1,values.length, values[0].length).getFontColors())
  if (sheet.getRange(1, 1).getBackground() == '#ff0000') {
    sheet.getRange(1, 1).setFontColor('#fff')
    sheet.getRange(1, 1).setBackground('#fff')
    main();
  } else {
    sheet.getRange(1, 1).setFontColor('#fff')
  }
}

function clear() {
  tmpSheet.getRange(1,1,values.length, values[0].length).setBackground('#fff')
  tmpSheet.getRange(1,1,values.length, values[0].length).setFontColor('#000')
}

function setNameLineLabelColor() {
  for (var y = ITEM_NAME_START_LINE_INDEX; y < values.length; y++) {
    if (values[y][0] != '') {
      tmpSheet.getRange(y + 1, 1, 1, values[0].length).setBackground('#ff9')
    }
  }
}

function setHoliday() {
  for (var x = DATA_START_ROW_INDEX; x < values[0].length; x++) {
    var individualHoliday = false;
    var dayHoliday = false;
    var holidayColorCode = '#f99'
    var startHolidayRowIndex = ITEM_NAME_START_LINE_INDEX - 1;
    var holidayRowCount = 0;
    if (values[DAY_ROW_LINE_INDEX][x] == '土' || values[DAY_ROW_LINE_INDEX][x] == '日' || values[DAY_ROW_LINE_INDEX][x] == '休') {
      dayHoliday = true
      holidayColorCode = '#f99'
      if (values[DAY_ROW_LINE_INDEX][x] == '土') {
        holidayColorCode = '#99f'
      }
    }
    for (var y = ITEM_NAME_START_LINE_INDEX; y < values.length; y++) {
      if (!dayHoliday && values[y][ITEM_NAME_ROW_INDEX] == HOLYDAY_STRING) {
        if (individualHoliday || dayHoliday) setHolidayColor(x,startHolidayRowIndex,holidayRowCount,holidayColorCode)
        individualHoliday = values[y][x] != '';               
        startHolidayRowIndex = y + 1;
        holidayRowCount = -1;
        holidayColorCode = '#f99'
        continue;
      }
      holidayRowCount++;
    }
    if (individualHoliday || dayHoliday) {
      setHolidayColor(x,startHolidayRowIndex,holidayRowCount,holidayColorCode)
    }
  }
  sheet.getRange(DAY_ROW_LINE_INDEX + 1, DATA_START_ROW_INDEX + 1, 1,values[0].length).setFontColor('#000')
}
function setHolidayColor(x,y,n,code) {
  if (n == 0) return
  tmpSheet.getRange(y + 1, x + 1, n + 1).setBackground(code);
  tmpSheet.getRange(y + 1, x + 1, n + 1).setFontColor(code)
}

function setManHour() {
  for (var y = ITEM_NAME_START_LINE_INDEX; y < values.length; y++) {
    manHour = values[y][ITEM_NAME_ROW_INDEX + 1] * 1
    if (manHour == 0) continue;
    var addColor = false
    var startX = -1
    var colorCount = 0
    for (var x = DATA_START_ROW_INDEX + 1; x < values[0].length; x++) { if (values[y][x] != '') { addColor = true } if (addColor && manHour > 0 && tmpSheet.getRange(y + 1, x + 1).getBackground() == '#ffffff') {
        if (startX < 0) { startX = x colorCount = 1; } manHour--; } if (manHour > 0 && startX >= 0) {
        colorCount++
      }
    }
    if (colorCount > 0) {
      tmpSheet.getRange(y + 1, startX + 1, 1, colorCount).setBackground('#9f9')
      tmpSheet.getRange(y + 1, startX + 1, 1, colorCount).setFontColor('#9f9')
    }
  }
}

トリガーを設定する。上のメニューから「編集」→「現在のプロジェクトのトリガー」を選択する。

f:id:ponkotsu0605:20190401014009p:plain

以下のように、「スプレッドシート」の「値の変更」があったら「onEdit」というメソッドを実行するように設定する。

f:id:ponkotsu0605:20190401014037p:plain

保存を押すと、一回目のみ以下のように承認が必要になるので、「許可を確認」を押してあげる。

f:id:ponkotsu0605:20190401014103p:plain

これで、スプレッドシートの値を修正すると色がつくと思います。

f:id:ponkotsu0605:20190401014126p:plain

※それと、A1に「色塗り中」等と書いておいて文字色を白に設定しておいてください。

※また、スクリプトの10行目までの定数の宣言は各々のスプレッドシートの構造に合わせて数値を入力してください。

 

機能の紹介

今回作成したガントチャートの機能を紹介したいと思います。

  1. 個人の休みを設定できる
  2. 休日を設定することができる
  3. タスクの開始日に「1」等の文字を入れることで、そこから工数に入れた日数分だけ色がつく

 

1.個人の休みを設定できる

名前の横のセルに「休み」と入力して、その行に「x」などの文字を入れておくことで、その人はその日休むことを示すことができる。

そうすることで、休日を工数に数えなくなり、日曜日と同じく赤色に染まる。

 

2.休日を設定することができる

曜日のところに「休」という文字を入力することで、その日を休日扱いにする。

そうすることで、休日を工数に数えなくなり、日曜日と同じく赤色に染まる。

 

3.タスクの開始日に「1」等の文字を入れることで、そこから工数に入れた日数分だけ色がつく

文字通り、何かしらの文字を入力することで、その日を開始日として緑色の線が引かれる。

もちろん休日は工数として数えず飛ばしてくれる。

 

最後に

このように、今回はスプレッドシートでガントチャートを作ることができました。

もしかしたら他のツールのほうが使いやすいかもしれませんが、たまにはこういうのを作ってプログラムの練習をするのもいいかと思います!