ものづくり：タスクリスト用GAS

　こんにちは、Dancing Shigekoです！

　今回は、最近使用頻度が低くなっているタスクリストのGASを特別公開！

　Googleスプレッドシートに貼り付けて、お好みに合わせて使ってみてね！

1行目の見出しは以下。

できること

・　G列に実行日を入れると、H列に開始時間が入力される。

・　I列に終了時間を入れると、E列が完了に変わる。

・　C列に”毎日”、”平日”などが入っていると、タスクが完了時に、空いている最終行にタスクがコピーされる。（繰り返しタスクの定義はプログラムを見てみてね。）

【プログラム】

function onEdit(e) {

  if (!e) return;

  const sheetName = "ToDo"; // ← 対象シート名をここに

  const sheet = e.range.getSheet();

  const range = e.range;

  const row = range.getRow();

  const col = range.getColumn();

  if (sheet.getName() !== sheetName) {

    return; // シート名が一致しない場合は処理しない

  }

  // ① I列に入力されたら → E列に「完了」を入力し、タスク処理も実行

  if (col === 9 && row > 1) {

   const value = range.getValue();

   const statusCell = sheet.getRange(row, 5); // E列

   const category = sheet.getRange(row,2).getValue(); // B　小分類

   const title = sheet.getRange(row,4).getValue(); // D 内容

   const validCategories = ["アニメ", "ドラマ", "映画"];

   // すでに処理済みなら何もしない

   const note = statusCell.getNote(); // セルの「メモ」を使って再実行防止

   if (note === "copied") return;

   if (value !== "") {

    statusCell.setValue("完了");

    statusCell.setNote("copied"); // コピー済みの印

    handleRecurringTaskCopy(sheet, row);

    updateTodayTotals(); //

    if (validCategories.includes(category)){

      insertTemplateTasks(title);

    }

   }

  }

  // ② D列（内容）に入力されたら → L列に実績計算式を自動入力

  if (col === 4 && row > 1) {

    const formula = `=(I${row}-H${row})*24`;

    sheet.getRange(`L${row}`).setFormula(formula);

  }

  // ③ J列（見込み）に「分」入力 → K列に時間換算式

  if (col === 10 && row > 1) {

    const formula = `=J${row}/60`;

    sheet.getRange(`K${row}`).setFormula(formula);

  }

  // ④ G列（実行日）に入力されたら → 同日の最新の終了時間をH列に入力

  if (col === 7 && row > 1) {

    const execDate = new Date(range.getValue());

    const allDates = sheet.getRange(2, 7, sheet.getLastRow() - 1).getValues(); // G列

    const allEnds  = sheet.getRange(2, 9, sheet.getLastRow() - 1).getValues(); // I列

    let latestEnd = null;

    for (let i = 0; i < allDates.length; i++) {

      const r = i + 2;

      if (r === row) continue;

      const g = allDates[i][0];

      const iVal = allEnds[i][0];

      if (g instanceof Date && iVal instanceof Date) {

        if (

          g.getFullYear() === execDate.getFullYear() &&

          g.getMonth() === execDate.getMonth() &&

          g.getDate() === execDate.getDate()

        )

        if (!latestEnd || iVal > latestEnd) {

          latestEnd = new Date(iVal);

        }

      }

    }

   if (latestEnd) {

     const base = new Date(1899, 11, 30); // Sheetsの時刻基準

     base.setHours(latestEnd.getHours(), latestEnd.getMinutes(), latestEnd.getSeconds());

     sheet.getRange(row, 8).setValue(base); // H列

     sheet.getRange(row, 8).setNumberFormat("hh:mm");

    }

  }

    if (latestEndTime) {

      const onlyTime = new Date(1899, 11, 30); // Sheetsの基準日付

      onlyTime.setHours(latestEndTime.getHours());

      onlyTime.setMinutes(latestEndTime.getMinutes());

      onlyTime.setSeconds(latestEndTime.getSeconds());

      sheet.getRange(row, 8).setValue(onlyTime); // H列（開始時間）

      sheet.getRange(row, 8).setNumberFormat("hh:mm");

    }

  // ⑤ E列に手動で「完了」が入力された場合も、コピー処理実行

  if (col === 5 && row > 1) {

    const value = range.getValue();

    if (value === "完了") {

      const note = range.getNote();

      if (note === "copied") return;

      range.setNote("copied");

      handleRecurringTaskCopy(sheet, row);

    }

  }

function handleRecurringTaskCopy(sheet, row) {

  const status = sheet.getRange(row, 5).getValue(); // E列（状況）

  const recurrence = sheet.getRange(row, 3).getValue().toString().trim(); // C列（定期）

  const execDate = sheet.getRange(row, 7).getValue(); // G列（実行日）

  Logger.log(`--- 定期タスクチェック開始 ---`);

  Logger.log(`行番号: ${row}, 状況: ${status}, 定期: ${recurrence}, 実行日: ${execDate}`);

  if (status !== "完了") {

    Logger.log("完了ではないためスキップ");

    return;

  }

  if (recurrence !== "不定期" && (!(execDate instanceof Date) || isNaN(execDate))) {

    Logger.log("納期が不正な日付のためスキップ");

    return;

  }

  let nextDueDate = null;

  if (recurrence === "不定期") {

    nextDueDate = "";

  } else {

    nextDueDate = getNextDueDate(recurrence, new Date(execDate));

    if (!nextDueDate) {

     Logger.log("定期種類が未対応のためスキップ");

     return;

    }

  }

  const taskData = sheet.getRange(row, 1, 1, 12).getValues()[0]; // A〜L列

  // 更新：納期＋リセット

  taskData[5] = nextDueDate;

  taskData[4] = "未着手"; // E列（状況）

  taskData[6] = ""; // G列（実行日）

  taskData[7] = ""; // H列（開始）

  taskData[8] = ""; // I列（終了）

  // J列（見込み）はそのまま残す

  // 追加処理

  sheet.appendRow(taskData);

  const newRow = sheet.getLastRow();

  sheet.getRange(newRow, 6).setNumberFormat("M/d"); // F列を 月/日 表示に

  sheet.getRange(newRow, 12).setFormula(`=(I${newRow}-H${newRow})*24`); // 実績式

}

function getNextDueDate(type, baseDate) {

  const day = baseDate.getDay(); // 0=日, 6=土

  const date = new Date(baseDate);

  switch (type) {

    case "毎日":

      date.setDate(date.getDate() + 1);

      return date;

    case "毎週":

      date.setDate(date.getDate() + 7);

      return date;

    case "隔週":

      date.setDate(date.getDate() + 14);

      return date;

    case "平日":

      do {

        date.setDate(date.getDate() + 1);

      } while (date.getDay() === 0 || date.getDay() === 6); // 土日をスキップ

      return date;

    case "土日":

      for (let i = 1; i <= 7; i++) {

        const tryDate = new Date(baseDate);

        tryDate.setDate(baseDate.getDate() + i);

        const d = tryDate.getDay();

        if (d === 0 || d === 6) return tryDate;

      }

      return null;

    case "毎月":

      const newDate = new Date(baseDate);

      newDate.setMonth(newDate.getMonth() + 1);

      return newDate;

    default:

      return null; // 非対応の定期はスキップ

  }

}

function setTimeFormatForTimeColumns() {

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  const rangeH = sheet.getRange("H2:H1000");

  const rangeI = sheet.getRange("I2:I1000");

  // 値を「時刻だけ」にして再設定（クリーニング）

  cleanToTimeOnly(rangeH);

  cleanToTimeOnly(rangeI);

  // 表示形式も再設定

  rangeH.setNumberFormat("hh:mm");

  rangeI.setNumberFormat("hh:mm");

}

// 値に含まれる日付部分を切り捨て、時刻だけにする関数

function cleanToTimeOnly(range) {

  const values = range.getValues();

  const newValues = values.map(row => {

    const cell = row[0];

    if (cell instanceof Date) {

      const hours = cell.getHours();

      const minutes = cell.getMinutes();

      const seconds = cell.getSeconds();

      const date = new Date(0); // 基準：1970/1/1

      date.setHours(hours, minutes, seconds);

      return [date];

    } else {

      return [cell]; // 日付じゃない場合はそのまま

    }

  });

  range.setValues(newValues);

}

function generateTodaySchedule() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const taskSheet = ss.getSheetByName("ToDo");

  const calendarSheetName = "Calendar";

  if (!taskSheet) {

    Logger.log("シート 'ToDo' が見つかりません。");

    return;

  }

  let calendarSheet = ss.getSheetByName(calendarSheetName);

  if (!calendarSheet) {

    calendarSheet = ss.insertSheet(calendarSheetName);

  } else {

    calendarSheet.clear();

  }

  const data = taskSheet.getDataRange().getValues();

  const today = new Date();

  const todayStr = Utilities.formatDate(today, Session.getScriptTimeZone(), "yyyy-MM-dd");

  Logger.log(`今日の日付: ${todayStr}`);

  const startHour = 8;

  const endHour = 20;

  calendarSheet.getRange(1, 1).setValue("時刻");

  calendarSheet.getRange(1, 2).setValue("タスク");

  for (let hour = startHour; hour <= endHour; hour++) {

    const row = hour - startHour + 2;

    calendarSheet.getRange(row, 1).setValue(`${hour}:00`);

  }

  for (let i = 1; i < data.length; i++) {

    const [,, , content,, , execDate, start, end] = data[i];

    if (!(execDate instanceof Date)) {

      Logger.log(`行${i + 1}: 実行日が無効 → ${execDate}`);

      continue;

    }

    const dateStr = Utilities.formatDate(execDate, Session.getScriptTimeZone(), "yyyy-MM-dd");

    if (dateStr !== todayStr) {

      Logger.log(`行${i + 1}: 日付不一致 → 実行日=${dateStr}`);

      continue;

    }

    if (!(start instanceof Date)) {

      Logger.log(`行${i + 1}: 開始時間が無効 → ${start}`);

      continue;

    }

    const taskHour = start.getHours();

    const row = taskHour - startHour + 2;

    if (row < 2 || row > endHour - startHour + 2) {

      Logger.log(`行${i + 1}: 時間範囲外 → ${taskHour}時`);

      continue;

    }

    calendarSheet.getRange(row, 2).setValue(content);

    Logger.log(`行${i + 1}: ${taskHour}:00 に '${content}' を表示`);

  }

  calendarSheet.setColumnWidths(1, 2, 120);

}

function updateTodayTotals() {

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  const data = sheet.getDataRange().getValues();

  const today = new Date();

  const todayStr = Utilities.formatDate(today, Session.getScriptTimeZone(), "yyyy-MM-dd");

  let totalEstimateMin = 0; // 見込み（分）

  let totalActualHr = 0; // 実績（時間）

  for (let i = 1; i < data.length; i++) {

    const execDate = data[i][6]; // G列（実行日）

    const estimate = parseFloat(data[i][9]); // J列（見込み）

    const actual = parseFloat(data[i][11]); // L列（実績）

    // 実行日が今日かどうかを確認

    const dateStr = execDate instanceof Date

      ? Utilities.formatDate(execDate, Session.getScriptTimeZone(), "yyyy-MM-dd")

      : "";

    if (dateStr === todayStr) {

      if (!isNaN(estimate)) totalEstimateMin += estimate;

      if (!isNaN(actual)) totalActualHr += actual;

    }

  }

  // 表示：見込みは時間に変換（60分換算）

  const estHour = Math.round((totalEstimateMin / 60) * 10) / 10;

  const actHour = Math.round(totalActualHr * 10) / 10;

  sheet.getRange("M1").setValue(`見込み ${estHour} 時間`);

  sheet.getRange("N1").setValue(`実績 ${actHour} 時間`);

}

　それでは、また明日！