Untitled

/**
 * @OnlyCurrentDoc
 */
function checkVacations() {
  var vacationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Отпуски");
  var terminatedSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Работници");
  var officialHolidaysSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Официални почивни дни");
  var sheetInfo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Info");

  var today = new Date();
  var todayDate = new Date(today.getFullYear(), today.getMonth(), today.getDate());
  var formattedDate = Utilities.formatDate(today, Session.getScriptTimeZone(), "d.M.y 'г.'");
  var timeText = "00" + ":" + "00" + ":" + "00";
  var timeText1 = "00" + ":" + "00" + ":" + "00";
  var timeText2 = "00" + ":" + "00" + ":" + "00";
  var timeText3 = "00" + ":" + "00" + ":" + "00";

  var rows = vacationSheet.getLastRow();
  var lastRow1 = officialHolidaysSheet.getLastRow();
  var officialHolidays = officialHolidaysSheet.getRange("A2:A" + lastRow1).getDisplayValues().flat();


  for (var i = 2; i <= rows; i++) {
    var startDate = vacationSheet.getRange(i, 5).getDisplayValue();
    var endDate = vacationSheet.getRange(i, 6).getDisplayValue();

    if (startDate && endDate) {
      var dateString = startDate.replace(" г.", "");
      var dateParts = dateString.split(".");
      var d = new Date(dateParts[2], dateParts[1] - 1, dateParts[0]);

      var dateString1 = endDate.replace(" г.", "");
      var dateParts1 = dateString1.split(".");
      var d1 = new Date(dateParts1[2], dateParts1[1] - 1, dateParts1[0]);

      if (todayDate >= d.getTime() && todayDate <= d1.getTime() && today.getDay() !== 0 && today.getDay() !== 6 && !officialHolidays.includes(formattedDate)) 
       {
        var employeeName = vacationSheet.getRange(i, 1).getValue();
        var value1 = vacationSheet.getRange(i, 4).getValue();
        if ( employeeName !== "Драгомир Чалъков")
        {
        var lastRow = terminatedSheet.getLastRow() + 1;
        var dataToSet = [
        [employeeName, 1, "Out", formattedDate, timeText, timeText1, '', '', timeText, value1]
        ];
        terminatedSheet.getRange(lastRow, 1, 1, dataToSet[0].length).setValues(dataToSet);
        
        var rows22=sheetInfo.getLastRow();
        var value12 = sheetInfo.getRange(rows22, 12).getValue(); // взема стойността на клетка 12
        sheetInfo.getRange(sheetInfo.getLastRow(), 13).setValue(value12);
        }
        if ( employeeName == "Драгомир Чалъков")
        {
        terminatedSheet.getRange(terminatedSheet.getLastRow()+1, 1).setValue(employeeName);
        terminatedSheet.getRange(terminatedSheet.getLastRow(), 2).setValue(1);
        terminatedSheet.getRange(terminatedSheet.getLastRow(), 3).setValue("Out");
        terminatedSheet.getRange(terminatedSheet.getLastRow(), 4).setValue(formattedDate);
        terminatedSheet.getRange(terminatedSheet.getLastRow(), 5).setValue(timeText);
        terminatedSheet.getRange(terminatedSheet.getLastRow(), 6).setValue(timeText2);
        terminatedSheet.getRange(terminatedSheet.getLastRow(), 9).setValue(timeText3);
        terminatedSheet.getRange(terminatedSheet.getLastRow(), 10).setValue(value1);
        var rows22=sheetInfo.getLastRow();
        var value12 = sheetInfo.getRange(rows22, 12).getValue(); // взема стойността на клетка 12
        sheetInfo.getRange(sheetInfo.getLastRow(), 13).setValue(value12);
        }
      }
    }
  }
}