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
  // Columns are: [Name, Col2, Col3, Value1, StartDate, EndDate]
  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) {
      // Get values from each row
      const employeeName = row[0];  // Column 1
      const value1 = row[3];        // Column 4
      const startDate = row[4];     // Column 5
      const endDate = row[5];       // Column 6

      if (!startDate || !endDate) continue;

      // Convert dates from spreadsheet format to Date objects
      const startDateStr = startDate.toString().replace(" г.", "");
      const endDateStr = endDate.toString().replace(" г.", "");
      const vacationStart = parseCustomDate(startDateStr);
      const vacationEnd = parseCustomDate(endDateStr);

      if (todayDate >= vacationStart && todayDate <= vacationEnd) {
        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);
}