스도쿠 탈출?! : 팀매니저의 꿈, 근무 스케줄 자동화 도전기

function fillEmptyCellsWithConditions() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // 시트 이름을 맞게 변경하세요.
  var range = sheet.getRange("E4:AG13");
  var values = range.getValues();
  var headerRange = sheet.getRange("E2:AG2");
  var headerColors = headerRange.getFontColors()[0];
  var gradeRange = sheet.getRange("A4:A13");
  var grades = gradeRange.getValues().flat();

  var nScheduleCount = Array(values.length).fill(0);
  var aScheduleCount = Array(values.length).fill(0);
  var nMaxCount = 4;
  var nMaxCountForJuniors = 2;
  var juniorIndexes = grades.map((grade, index) => grade === "Junior" ? index : -1).filter(index => index !== -1);

  for (var col = 0; col < headerColors.length; col++) {
    var dayType = headerColors[col] == "#ff0000" ? "holiday" : "weekday";
    var emptyCells = [];
    var A_count = 0;
    var N_count = 0;
    var O_count = 0;

    for (var row = 0; row < values.length; row++) {
      var cellValue = values[row][col];
      if (cellValue === "") {
        emptyCells.push([row, col]);
      } else if (cellValue === "A") {
        A_count++;
        aScheduleCount[row]++;
      } else if (cellValue === "N") {
        N_count++;
        nScheduleCount[row]++;
      } else if (cellValue === "O") {
        O_count++;
      }
    }

    while (A_count < 2 && emptyCells.length > 0) {
      assignSchedule(emptyCells, values, aScheduleCount, "A");
      A_count++;
    }

    if (N_count < 1) {
      assignNSchedule(emptyCells, values, nScheduleCount, juniorIndexes, nMaxCountForJuniors, nMaxCount, col);
    }

    if (dayType == "holiday" && O_count > 0) {
      throw new Error("휴일에 'O' 값이 입력되어 있습니다.");
    }

    fillRemainingCells(emptyCells, values, "X");
  }

  checkAndAssignAMinimum(values, aScheduleCount);
  fixNextDayRestrictions(values);

  range.setValues(values);

  adjustAUValues(sheet);
}

function assignSchedule(emptyCells, values, scheduleCount, value) {
  var minCount = Math.min(...scheduleCount);
  for (var i = 0; i < emptyCells.length; i++) {
    var row = emptyCells[i][0];
    if (scheduleCount[row] === minCount) {
      values[row][emptyCells[i][1]] = value;
      scheduleCount[row]++;
      emptyCells.splice(i, 1);
      break;
    }
  }
}

function assignNSchedule(emptyCells, values, nScheduleCount, juniorIndexes, nMaxCountForJuniors, nMaxCount, col) {
  var assigned = false;

  for (var i = 0; i < juniorIndexes.length; i++) {
    var index = juniorIndexes[i];
    if (nScheduleCount[index] < nMaxCountForJuniors && !isNextDayRestricted(values, index, col)) {
      for (var j = 0; j < emptyCells.length; j++) {
        if (emptyCells[j][0] === index) {
          values[index][emptyCells[j][1]] = "N";
          nScheduleCount[index]++;
          emptyCells.splice(j, 1);
          assigned = true;
          break;
        }
      }
    }
    if (assigned) break;
  }

  if (!assigned) {
    assignSchedule(emptyCells, values, nScheduleCount, "N");
  }
}

function isNextDayRestricted(values, row, col) {
  return col + 1 < values[0].length && (values[row][col + 1] === "O" || values[row][col + 1] === "A");
}

function fillRemainingCells(emptyCells, values, fillValue) {
  while (emptyCells.length > 0) {
    var cell = emptyCells.pop();
    values[cell[0]][cell[1]] = fillValue;
  }
}

function checkAndAssignAMinimum(values, aScheduleCount) {
  for (var col = 0; col < values[0].length; col++) {
    var A_count = 0;
    var emptyCells = [];
    for (var row = 0; row < values.length; row++) {
      if (values[row][col] === "A") {
        A_count++;
      } else if (values[row][col] === "") {
        emptyCells.push([row, col]);
      }
    }
    while (A_count < 2 && emptyCells.length > 0) {
      assignSchedule(emptyCells, values, aScheduleCount, "A");
      A_count++;
    }
  }
}

function fixNextDayRestrictions(values) {
  for (var col = 0; col < values[0].length - 1; col++) {
    for (var row = 0; row < values.length; row++) {
      if (values[row][col] === "N" && (values[row][col + 1] === "O" || values[row][col + 1] === "A")) {
        values[row][col + 1] = "X";
      }
    }
  }
}

function adjustAUValues(sheet) {
  var auRange = sheet.getRange("AU4:AU13");
  var auValues = auRange.getValues().flat();
  var maxAttempts = 10; // 최대 시도 횟수

  for (var row = 0; row < auValues.length; row++) {
    var attempts = 0;
    while ((auValues[row] < -15 || auValues[row] > 25) && attempts < maxAttempts) {
      if (auValues[row] < -15) {
        adjustRowForNegativeAU(sheet, row);
      } else if (auValues[row] > 25) {
        adjustRowForPositiveAU(sheet, row);
      }
      auValues = sheet.getRange("AU4:AU13").getValues().flat(); // Update AU values after adjustments
      attempts++;
    }
  }
}

function adjustRowForNegativeAU(sheet, row) {
  var colorRange = sheet.getRange("E" + (row + 4) + ":AG" + (row + 4));
  var colors = colorRange.getBackgrounds()[0];
  var values = sheet.getRange("E" + (row + 4) + ":AG" + (row + 4)).getValues()[0];

  for (var col = 0; col < values.length; col++) {
    if (values[col] === "X" && colors[col] === "#ffffff") {
      sheet.getRange("E" + (row + 4) + (col + 5)).setValue(Math.random() < 0.5 ? "A" : "O");
      break;
    }
  }
}

function adjustRowForPositiveAU(sheet, row) {
  var colorRange = sheet.getRange("E" + (row + 4) + ":AG" + (row + 4));
  var colors = colorRange.getBackgrounds()[0];
  var values = sheet.getRange("E" + (row + 4) + ":AG" + (row + 4)).getValues()[0];

  for (var col = 0; col < values.length; col++) {
    if (values[col] === "A" && colors[col] === "#ffffff") {
      sheet.getRange("E" + (row + 4) + (col + 5)).setValue(Math.random() < 0.5 ? "X" : "O");
      break;
    }
  }
}

```