GoogleSpreadSheetでガントチャートを使うときのユーティリティ

gasGannt.js

const headers = ["ID", "タスク名", "開始日", "終了日", "日数", "依存タスク"];

function setup() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1");
  if(sheet.getRange(1, 1).getValue() == "入力用") {
    throw new Error("すでに実行済みです");
  }
  sheet.appendRow(["入力用"])
  sheet.appendRow(headers)
  
  sheet.getRange(1, 8).setValue("=calcTaskTerm(A:F)");
  
}

function calcTaskTerm(values) {
  var repository = new Repository();
  var result = [["描画用"], ["開始日", "終了日"]];
  values.slice(2)
    .filter(v => v[0] != "")
    .forEach((v) => {
      var t = new Task({
        id: v[0],
        start: toDate(v[2]),
        end: toDate(v[3]),
        duration: v[4],
        dependencies: v[5].split(",").map(v => v.trim())
      }, repository);
    repository.add(t);
  })

  result = [
    ...result,
    ...repository.findAll().map(v => [v.start, v.end])
  ]

  return result;
}

class Repository {
  constructor() {
    this.tasks = {};
  }
  add(task) {
    this.tasks[task.id] = task;
  }
  find(id) {
    return this.tasks[id];
  }
  findAll() {
    return Object.keys(this.tasks).map((v) => this.tasks[v]);
  }
}


class Task {
  constructor(obj, repository) {
    console.log('start', obj.start);
    console.log('end', obj.end);
    this.repository = repository;
    this.id = obj.id;
    this._start = obj.start ? new Date(obj.start) : null;
    this._end = obj.end ? new Date(obj.end) : null;
    this.obj = obj;
  }
  get start() {
    if (this._start) {
      return this._start;
    }
    var lastEndDate = this.obj.dependencies
      .map((v) => this.repository.find(v).end)
      .reduce(
        (memo, v) => (memo.getTime() > v.getTime() ? memo : v),
        new Date("2000/1/1")
      );
    this._start = addBusinessDays(lastEndDate, 1, []);
    this.obj.start = this._start.toISOString().split("T")[0];
    console.log(lastEndDate);
    return this._start;
  }

  get end() {
    if (this._end) {
      return this._end;
    }
    if (!this.obj.duration) {
      throw new Error("duration not found: " + this.obj.id);
    }
    this._end = addBusinessDays(
      new Date(this.start),
      this.obj.duration - 1,
      []
    );
    this.obj.end = this._end.toISOString().split("T")[0];
    return this._end;
  }
}

function toDate(value) {
  if(value instanceof Date) {
    return new Date(value.getTime());
  }
  if(typeof value !== 'string') {
    throw new Error(`DateでもStringでもない値: ${value}`)
  }
  if(value.length == 0) {
    return null;
  }

  return getDateFromString(value, []);
}

/**
 * 土日祝日を加味して営業日後の日付を計算する
 */
function addBusinessDays(date, daysToAdd, holidays) {
  holidays = holidays || [];
  // weekends: 0 = Sunday, 6 = Saturday
  const weekendDays = [0, 6];
  // create a set of holidays for O(1) lookup time
  const holidaySet = new Set(holidays.map((holiday) => holiday.getTime()));
  let businessDaysAdded = 0;
  let currentDate = new Date(date.getTime());

  while (businessDaysAdded < daysToAdd) {
    // add one day to the current date
    currentDate.setDate(currentDate.getDate() + 1);
    // check if the current date is a weekend or holiday
    const currentDay = currentDate.getDay();
    const isWeekend = weekendDays.includes(currentDay);
    const isHoliday = holidaySet.has(currentDate.getTime());
    // if it's not a weekend or holiday, count it as a business day added
    if (!isWeekend && !isHoliday) {
      businessDaysAdded++;
    }
  }

  return currentDate;
}

/**
 * 特殊な日付表記の判定
 */
function isSpecial(value) {
  return '初末上中下'.indexOf(value.slice(0,1)) != -1;
}

function getDateFromStringByFy(dateString, holidays) {
  const match = dateString.split('/');
  if(match[0].indexOf('FY') !== 0) {
    throw new Error('FYで始まってない');
  }
  const fy = match[0];
  const q = match[1];
  const special = match[2];
  
  var year = parseInt('20' + fy.slice(2));
  if(q == '4Q') {
    year++;
  }
  if(special == '初') {
    let month = {
      '1Q': 4,
      '2Q': 7,
      '3Q': 10,
      '4Q': 1,
    }[q];
    return getDateFromString(`${year}/${month}/初`, holidays)
  }
  if(special == '末') {
    let month = {
      '1Q': 6,
      '2Q': 9,
      '3Q': 12,
      '4Q': 3,
    }[q];
    return getDateFromString(`${year}/${month}/末`, holidays)
  }
}

function getDateFromString(dateString, holidays) {
  const match = dateString.split('/')

  // FY23のような年度表記への対応
  if(match[0].indexOf('FY') == 0) {
    return getDateFromStringByFy(dateString, holidays);
  }
  const year = parseInt(match[0], 10);
  const month = parseInt(match[1], 10);
  const dayOfMonth = isSpecial(match[2]) ? null : parseInt(match[2], 10);
  const special = isSpecial(match[2]) ? match[2] : '';

  console.log(dayOfMonth, special);

  // 日付の特別な形式に応じて、日付を計算する
  let date;
  switch (special) {
    case '初':
      date = new Date(year, month - 1, 1);
      break;
    case '末':
      date = new Date(year, month, 0);
      break;
    case '上':
      date = new Date(year, month - 1, 5);
      break;
    case '中':
      date = new Date(year, month - 1, 15);
      break;
    case '下':
      date = new Date(year, month - 1, 25);
      break;
    default:
      date = new Date(year, month - 1, dayOfMonth);
  }

  // 土曜日、日曜日、祝日の場合は、前の営業日を返す
  while (true) {
    const dayOfWeek = date.getDay();
    const isHoliday = holidays.some((holiday) => {
      return holiday.getFullYear() === date.getFullYear() &&
             holiday.getMonth() === date.getMonth() &&
             holiday.getDate() === date.getDate();
    });
    if (dayOfWeek === 0 || dayOfWeek === 6 || isHoliday) {
      date.setDate(date.getDate() - 1);
    } else {
      break;
    }
  }

  return date;
}