Untitled

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

  // Get all data at once instead of individual cell operations
  const vacationData = vacationSheet.getRange(2, 1, vacationSheet.getLastRow() - 1, 6).getValues();
  const officialHolidays = officialHolidaysSheet.getRange(2, 1, officialHolidaysSheet.getLastRow() - 1, 1)
    .getDisplayValues()
    .flat();

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

  // Prepare batch updates
  const newRows = [];
  const isWeekend = today.getDay() === 0 || today.getDay() === 6;
  const isHoliday = officialHolidays.includes(formattedDate);

  if (!isWeekend && !isHoliday) {
    for (const row of vacationData) {
      const [employeeName, , , value1, startDate, endDate] = row;
      
      if (!startDate || !endDate) continue;

      // Parse dates
      const dateString = startDate.toString().replace(" г.", "");
      const dateString1 = endDate.toString().replace(" г.", "");
      const d = parseCustomDate(dateString);
      const d1 = parseCustomDate(dateString1);

      if (todayDate >= d && todayDate <= d1) {
        if (employeeName !== "Драгомир Чалъков") {
          newRows.push([employeeName, 1, "Out", formattedDate, timeText, timeText, '', '', timeText, value1]);
        } else {
          newRows.push([employeeName, 1, "Out", formattedDate, timeText, timeText2, '', '', timeText3, value1]);
        }
      }
    }
  }

  // Batch update if there are new rows
  if (newRows.length > 0) {
    const lastRow = terminatedSheet.getLastRow() + 1;
    terminatedSheet.getRange(lastRow, 1, newRows.length, newRows[0].length).setValues(newRows);

    // Update Info sheet
    const infoLastRow = sheetInfo.getLastRow();
    const value12 = sheetInfo.getRange(infoLastRow, 12).getValue();
    sheetInfo.getRange(infoLastRow, 13).setValue(value12);
  }
}

// Helper function to parse custom date format
function parseCustomDate(dateStr) {
  const [day, month, year] = dateStr.split(".");
  return new Date(year, month - 1, day);
}