コピペだけで5分で作れる！スプレッドシートで作成するタスク進捗管理のガントチャート

function create_1() {
var ui = SpreadsheetApp.getUi();
  
  var result = ui.alert(
    '【警告】',
    'テンプレートを新たに作成すると内容がリセットされますが、本当に大丈夫ですか？',
    ui.ButtonSet.YES_NO);
  
  if (result == ui.Button.YES) {
    // テンプレート作成の処理を実行する部分
    create_main()
    ui.alert('実施に成功しました');
  } else {
    ui.alert('実施に失敗しました');
  }
}


function create_main() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('ガントチャート');
  
  var headerValues = ["開始日", "終了日", "大項目", "中項目", "担当", "開始予定日", "終了予定日", "ステータス"];

  sheet.getRange("A1:A1").setValue(headerValues[0]); // 開始日
  sheet.getRange("A2:A2").setValue(headerValues[1]); // 終了日
  sheet.getRange("A4:A4").setValue(headerValues[2]); // 大項目
  sheet.getRange("B4:B4").setValue(headerValues[3]); // 中項目
  sheet.getRange("C4:C4").setValue(headerValues[4]); // 担当
  sheet.getRange("D4:D4").setValue(headerValues[5]); // 開始予定日
  sheet.getRange("E4:E4").setValue(headerValues[6]); // 終了予定日
  sheet.getRange("F4:F4").setValue(headerValues[7]); // ステータス

  // サンプルデータの作成
  var sampleData = [
    ["大項目1", "中項目1", "担当1", "2023/01/01", "2023/01/05", "完了"],
    [ "大項目2", "中項目2", "担当2", "2023/01/03", "2023/01/07", "進行中"],
    [ "大項目3", "中項目3", "担当3", "2023/01/06", "2023/01/10", "未着手"],
    [ "大項目4", "中項目4", "担当4", "2023/01/08", "2023/01/12", "未着手"],
    [ "大項目5", "中項目5", "担当5", "2023/01/11", "2023/01/15", "進行中"],
    [ "大項目6", "中項目6", "担当6", "2023/01/13", "2023/01/17", "未着手"],
    [ "大項目7", "中項目7", "担当7", "2023/01/16", "2023/01/20", "進行中"],
    [ "大項目8", "中項目8", "担当8", "2023/01/18", "2023/01/22", "未着手"],
    [ "大項目9", "中項目9", "担当9", "2023/01/21", "2023/01/25", "完了"],
    [ "大項目10", "中項目10", "担当10", "2023/01/23", "2023/01/27", "進行中"]
  ];

  var dataRange = sheet.getRange("A5:F14");
  dataRange.setValues(sampleData);
  
  // 入力規制の設定
  var dateValidation = SpreadsheetApp.newDataValidation().requireDate().setAllowInvalid(false).build();
  sheet.getRange("B1:B2").setDataValidation(dateValidation);
  
  // 網掛けとコメントの設定
  var cellB1 = sheet.getRange("B1");
  var cellB2 = sheet.getRange("B2");
  
  var yellowBackgroundColor = "#ffff00"; // 黄色
  var commentText = "日付を入力してください";
  
  cellB1.setBackground(yellowBackgroundColor);
  cellB2.setBackground(yellowBackgroundColor);
  
  cellB1.setComment(commentText);
  cellB2.setComment(commentText);
}

function create_2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('ガントチャート');

  var lastColumn = sheet.getLastColumn();
  var startColumn = sheet.getRange('G1').getColumn();
  var numColumnsToDelete = lastColumn - startColumn + 1;

  // 列の固定を解除
  sheet.setFrozenColumns(0);

  // 行の固定を解除
  sheet.setFrozenRows(0);

  // データを削除
  if (numColumnsToDelete > 1) {
    sheet.deleteColumns(startColumn, numColumnsToDelete);
  }

  var startDate = sheet.getRange('B1').getValue();
  var endDate = sheet.getRange('B2').getValue();

  // 開始日と終了日に合わせて日付を設定
  var days = Math.ceil((endDate - startDate) / (1000 * 60 * 60 * 24)) + 1;
  var dateRange = sheet.getRange('G3').offset(0, 0, 1, days);
  var dayRange = sheet.getRange('G4').offset(0, 0, 1, days);

  // 列が足りない場合は自動的に追加
  if (numColumnsToDelete < days) {
    var missingColumns = days - numColumnsToDelete;
    sheet.insertColumnsAfter(lastColumn, missingColumns);
    lastColumn = sheet.getLastColumn();
  }

  // データ数に合わせて貼り付け先のデータ数を調整
  dateRange.clearContent();
  dayRange.clearContent();

  var dates = [];
  var weekdays = [];

  for (var i = 0; i < days; i++) {
    var date = new Date(startDate.getTime() + i * 24 * 60 * 60 * 1000);
    dates.push([date]);
    weekdays.push([Utilities.formatDate(date, Session.getScriptTimeZone(), 'EEE')]);
  }

  dateRange.setValues([dates]);
  dayRange.setValues([weekdays]);

  // G3列の表示形式を "mm/dd" に設定
  dateRange.setNumberFormat('mm/dd');

  // G3以降の列の幅を現状の半分に設定
  var columnWidth = sheet.getColumnWidth(startColumn);
  var newWidth = Math.floor(columnWidth / 2);
  sheet.setColumnWidths(startColumn, days, newWidth);

  // 条件付き書式をリセット
  sheet.clearConditionalFormatRules();

  // G4:4に赤色と青色の条件付き書式を設定
  var dayRow = 4;
  var dayRange = sheet.getRange(dayRow, startColumn, 1, days);
  
  var redRule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo('Sun')
    .setBackground('#ffc0cb')
    .setRanges([dayRange])
    .build();

  var blueRule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo('Sat')
    .setBackground('#add8e6')
    .setRanges([dayRange])
    .build();

  // G5からデータがある最終行・最終列に灰色の条件付き書式を設定
  
  var lastColumn = sheet.getLastColumn();
  var lastRow = sheet.getLastRow();
  var dataRange = sheet.getRange('G5:'+lastColumn + lastRow);
  
  var grayRule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied('=OR(AND(G$4="Sun",$D5<>""),AND(G$4="Sat", $D5<>""))')
    .setBackground('#f5f5f5')
    .setRanges([dataRange])
    .build();

  // ガントチャート
  var rule = SpreadsheetApp.newConditionalFormatRule()
    .setRanges([dataRange])
    .whenFormulaSatisfied('=AND($D5<>"",$D5<=G$3,$E5>=G$3)')
    .setBackground('#ffdab9') 
    .build();

  // var status_range = sheet.getRange('F5:'+lastColumn + lastRow);

  // // 完了状態の条件付き書式を追加
  // var completedRule = SpreadsheetApp.newConditionalFormatRule()
  //   .whenTextEqualTo('完了')
  //   .setBackground('#98fb98') // 色を設定（例：明るい緑色）
  //   .setRanges([status_range])
  //   .build();
  
  
  // // 進行中状態の条件付き書式を追加
  // var inProgressRule = SpreadsheetApp.newConditionalFormatRule()
  //   .whenTextEqualTo('進行中')
  //   .setBackground('#f0e68c') // 色を設定（例：明るい黄色）
  //   .setRanges([status_range])
  //   .build();
  
  
  // // 未着手状態の条件付き書式を追加
  // var notStartedRule = SpreadsheetApp.newConditionalFormatRule()
  //   .whenTextEqualTo('未着手')
  //   .setBackground('#ffb6c1') // 色を設定（例：明るいピンク色）
  //   .setRanges([status_range])
  //   .build();
  

  // 条件付き書式を設定
  // 条件付き書式を設定
  var rules = sheet.getConditionalFormatRules();
  // rules.push(inProgressRule);
  // rules.push(completedRule);
  // rules.push(notStartedRule);
  rules.push(redRule);
  rules.push(blueRule);
  rules.push(grayRule);
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);




  // G列に固定ビューを設定
  sheet.setFrozenColumns(6); // G列を固定

  // 3行目に固定ビューを設定
  sheet.setFrozenRows(3); // 3行目を固定

  // 土日判定関数
  function isWeekend(date) {
    var day = date.getDay();
    return day === 0 || day === 6; // 0: Sunday, 6: Saturday
  }

  // 祝日判定関数（必要に応じて実装してください）
  function isHoliday(date) {
    // 祝日の判定処理を実装してください
    return false;
  }
}

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [
    { name: 'テンプレートを作成', functionName: 'create_1' },
    { name: 'ガントチャートを更新', functionName: 'create_2' }
  ];
  ss.addMenu('カスタムボタン', menuEntries);
}