UKG Schedule Extractor

javascript:(function(){const YOUR_NAME="Jordan";const YOUR_LOCATION="My Store";const REMINDER_MINUTES="45";function waitForElements(selector,timeout){return new Promise((resolve,reject)=>{const interval=500;let elapsed=0;const id=setInterval(()=>{const elems=document.querySelectorAll(selector);if(elems.length>0){clearInterval(id);resolve(elems)}else if((elapsed+=interval)>=timeout){clearInterval(id);reject(new Error('Timeout waiting for '+selector))}},interval)})}function convertTo24Hour(time12h){const[time,modifier]=time12h.split(' ');let[hours,minutes]=time.split(':');if(hours==='12'){hours='00'}if(modifier==='PM'){hours=parseInt(hours,10)+12}return hours.toString().padStart(2,'0')+':'+minutes+':00'}function createDateTime(date,time){const time24=convertTo24Hour(time);return date+'T'+time24}(async function(){try{await waitForElements('li[id^="myschedule-day_"]',5000);const results=(function(){const dayItems=[...document.querySelectorAll('li[id^="myschedule-day_"]')];const res=[];dayItems.forEach(li=>{const dateMatch=li.id.match(/myschedule-day_(\d{4}-\d{2}-\d{2})/);const workDate=dateMatch?dateMatch[1]:null;if(!workDate)return;const timeLabel=li.querySelector('time.label');if(!timeLabel)return;const timeText=timeLabel.textContent.trim();const timeSpan=timeText.split('[')[0].trim();const[startTime,endTime]=timeSpan.split('-').map(t=>t.trim());if(startTime&&endTime){res.push({title:YOUR_NAME+' at '+YOUR_LOCATION+' '+startTime+' - '+endTime,startDateTime:createDateTime(workDate,startTime),endDateTime:createDateTime(workDate,endTime),description:'',location:YOUR_LOCATION,reminderMinutes:REMINDER_MINUTES,inviteEmails:''})}});return res})();if(results.length===0){alert('No shifts found!');return}let csv='title,startDateTime,endDateTime,description,location,reminderMinutes,inviteEmails\n';results.forEach(item=>{csv+='"'+item.title+'","'+item.startDateTime+'","'+item.endDateTime+'","'+item.description+'","'+item.location+'","'+item.reminderMinutes+'","'+item.inviteEmails+'"\n'});navigator.clipboard.writeText(csv).then(()=>{alert('Copied '+results.length+' shifts to clipboard!')}).catch(err=>{const ta=document.createElement('textarea');ta.value=csv;ta.style.position='fixed';ta.style.opacity='0';document.body.appendChild(ta);ta.select();document.execCommand('copy');document.body.removeChild(ta);alert('Copied '+results.length+' shifts to clipboard!')})}catch(error){alert('Error: '+error.message)}})()})();

/**
 * Google Apps Script to import your work schedule data into Google Calendar
 * CUSTOMIZE THE VARIABLES BELOW
 */

function importScheduleToCalendar() {
  try {
    // ========================================
    // CUSTOMIZE THESE VARIABLES
    // ========================================
    const CALENDAR_ID = 'YOUR_CALENDAR_ID_HERE'; // Paste your Calendar ID here
    const SHEET_NAME = 'work_cal'; // Don't change unless you named your sheet differently
    const DEFAULT_LOCATION = 'YOUR_STORE_ADDRESS_HERE'; // Optional: full store address
    const EVENT_COLOR = CalendarApp.EventColor.GREEN; // Options: RED, ORANGE, YELLOW, GREEN, CYAN, BLUE, PURPLE, GRAY

    // ========================================
    // DON'T EDIT BELOW THIS LINE
    // ========================================

    const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = spreadsheet.getSheetByName(SHEET_NAME);

    if (!sheet) {
      throw new Error(`Sheet "${SHEET_NAME}" not found`);
    }

    const calendar = CalendarApp.getCalendarById(CALENDAR_ID);
    if (!calendar) {
      throw new Error('Calendar not found or no access');
    }

    const lastRow = sheet.getLastRow();
    console.log(`Sheet has ${lastRow} rows total`);

    const data = sheet.getRange(1, 1, lastRow, sheet.getLastColumn()).getValues();
    const headers = data[0];
    const rows = data.slice(1).filter(row => row[1] && row[2]);

    console.log(`Found ${rows.length} valid rows with data`);

    if (rows.length === 0) {
      SpreadsheetApp.getUi().alert('No Data', 'No valid rows found to import', SpreadsheetApp.getUi().ButtonSet.OK);
      return;
    }

    const colIndices = {
      title: headers.indexOf('title'),
      startDateTime: headers.indexOf('startDateTime'),
      endDateTime: headers.indexOf('endDateTime'),
      description: headers.indexOf('description'),
      location: headers.indexOf('location'),
      reminderMinutes: headers.indexOf('reminderMinutes'),
      inviteEmails: headers.indexOf('inviteEmails')
    };

    const requiredCols = ['title', 'startDateTime', 'endDateTime'];
    for (const col of requiredCols) {
      if (colIndices[col] === -1) {
        throw new Error(`Required column "${col}" not found`);
      }
    }

    let minDate = null;
    let maxDate = null;

    rows.forEach(row => {
      const start = new Date(row[colIndices.startDateTime]);
      const end = new Date(row[colIndices.endDateTime]);

      if (!isNaN(start.getTime())) {
        if (!minDate || start < minDate) minDate = start;
      }
      if (!isNaN(end.getTime())) {
        if (!maxDate || end > maxDate) maxDate = end;
      }
    });

    console.log(`Fetching existing events from ${minDate} to ${maxDate}`);
    const existingEvents = calendar.getEvents(minDate, maxDate);

    const existingEventsMap = new Map();
    existingEvents.forEach(event => {
      const key = `${event.getTitle()}_${event.getStartTime().getTime()}`;
      existingEventsMap.set(key, event);
    });

    console.log(`Found ${existingEvents.length} existing events in date range`);

    let processedCount = 0;
    let skippedCount = 0;
    let updatedCount = 0;

    for (let i = 0; i < rows.length; i++) {
      const row = rows[i];
      console.log(`Processing row ${i + 1} of ${rows.length}`);

      try {
        const title = row[colIndices.title] || 'Work Shift';
        const startDateTime = new Date(row[colIndices.startDateTime]);
        const endDateTime = new Date(row[colIndices.endDateTime]);
        const description = row[colIndices.description] || '';
        const location = row[colIndices.location] || DEFAULT_LOCATION;
        const reminderMinutes = row[colIndices.reminderMinutes] || '';
        const inviteEmails = row[colIndices.inviteEmails] || '';

        if (isNaN(startDateTime.getTime()) || isNaN(endDateTime.getTime())) {
          console.error(`Invalid date format in row ${i + 2}`);
          skippedCount++;
          continue;
        }

        const eventKey = `${title}_${startDateTime.getTime()}`;
        const existingEvent = existingEventsMap.get(eventKey);

        const options = {
          description: description,
          location: location
        };

        let event;
        if (existingEvent) {
          event = existingEvent;
          event.setTime(startDateTime, endDateTime);
          event.setDescription(description);
          event.setLocation(location);
          updatedCount++;
        } else {
          event = calendar.createEvent(title, startDateTime, endDateTime, options);
          processedCount++;
        }

        event.setColor(EVENT_COLOR);

        if (inviteEmails) {
          const guestList = inviteEmails.toString().split(',')
            .map(email => email.trim())
            .filter(email => email.length > 0 && email.includes('@'));

          if (guestList.length > 0) {
            guestList.forEach(email => {
              try {
                event.addGuest(email);
              } catch (e) {
                console.log(`Could not add guest ${email}: ${e.message}`);
              }
            });
          }
        }

        if (reminderMinutes) {
          event.removeAllReminders();
          const remindersList = reminderMinutes.toString().split(',');
          remindersList.forEach(reminder => {
            const minutes = parseInt(reminder.trim());
            if (!isNaN(minutes) && minutes >= 0) {
              event.addPopupReminder(minutes);
            }
          });
        }

        Utilities.sleep(1000);

      } catch (error) {
        console.error(`Error processing row ${i + 2}:`, error);
        skippedCount++;
      }
    }

    const message = `Import Complete!\n\nNew events created: ${processedCount}\nExisting events updated: ${updatedCount}\nRows skipped: ${skippedCount}\nTotal rows processed: ${rows.length}`;

    SpreadsheetApp.getUi().alert('Schedule Import Results', message, SpreadsheetApp.getUi().ButtonSet.OK);
    console.log(message);

  } catch (error) {
    console.error('Error in importScheduleToCalendar:', error);
    SpreadsheetApp.getUi().alert('Error', `Failed to import schedule: ${error.message}`, SpreadsheetApp.getUi().ButtonSet.OK);
  }
}

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Work Schedule')
    .addItem('Import to Calendar', 'importScheduleToCalendar')
    .addToUi();
}

function testCalendarAccess() {
  const CALENDAR_ID = 'YOUR_CALENDAR_ID_HERE'; // Same Calendar ID as above

  try {
    const calendar = CalendarApp.getCalendarById(CALENDAR_ID);
    if (calendar) {
      console.log(`Calendar found: ${calendar.getName()}`);
      SpreadsheetApp.getUi().alert('Success', `Calendar access confirmed: ${calendar.getName()}`, SpreadsheetApp.getUi().ButtonSet.OK);
    } else {
      throw new Error('Calendar not found');
    }
  } catch (error) {
    console.error('Calendar access error:', error);
    SpreadsheetApp.getUi().alert('Error', `Cannot access calendar: ${error.message}`, SpreadsheetApp.getUi().ButtonSet.OK);
  }
}