Code.js

/**********************
 * MedOps Calendar Sync
 **********************/

const CALENDAR_ID = 'c_39024d4a6fcdd79dd21f17a9b3d9368b00489d102b07c044dba49d643d7aee0e@group.calendar.google.com';
const TIMEZONE = 'America/Los_Angeles';
const EVENT_ID_CANON = 'event id';
const MAX_SHIFT_HOURS = 5;     // used if no end time is specified
const HARD_STOP_TIME = '23:30';// or latest same-day end for single time

// Sheet name matcher checks if name contains: "January", "Feb 2025", "7-2025", etc.
const MONTH_SHEET_RE = /jan(uary)?|feb(ruary)?|mar(ch)?|apr(il)?|may|jun(e)?|jul(y)?|aug(ust)?|sep(t)?(ember)?|oct(ober)?|nov(ember)?|dec(ember)?|\d{1,2}[-/_]\d{2,4}/i;

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('MedOps Calendar')
    .addItem('Process ACTIVE sheet', 'processActiveSheet')
    .addItem('Process ALL month sheets', 'processAllMonthSheets')
    .addToUi();
}

function processActiveSheet() {
  processSheet(SpreadsheetApp.getActive().getActiveSheet());
}

function processAllMonthSheets() {
  const ss = SpreadsheetApp.getActive();
  const sheets = ss.getSheets();
  const monthLike = sheets.filter(sh => MONTH_SHEET_RE.test(sh.getName()));
  (monthLike.length ? monthLike : sheets).forEach(processSheet);
}

function processSheet(sheet) {
  const calendar = CalendarApp.getCalendarById(CALENDAR_ID);
  if (!calendar) throw new Error('Calendar not found. Check CALENDAR_ID.');

  const values = sheet.getDataRange().getValues();
  if (values.length < 2) return;

  // 1) Detect header row in first 10 rows
  const { headerRowIdx, header } = detectHeader(values);
  if (headerRowIdx === -1) {
    console.log(`No header row detected on sheet "${sheet.getName()}". Skipping.`);
    return;
  }

  // 2) Fuzzy-map columns
  const colIndex = indexHeadersFuzzy(header);

  // 3) Ensure/locate Event ID column on header row
  ensureOrLocateEventIdColumn(sheet, headerRowIdx, header, colIndex);

  console.log(`Sheet "${sheet.getName()}": header row = ${headerRowIdx + 1}`);
  console.log(`Columns -> Date:${colIndex.date}  Name:${colIndex.name}  Location:${colIndex.location}  Shift:${colIndex.shift}  EventID:${colIndex.eventId}`);

  let lastDate = null;
  let lastName = '';
  let lastLocation = '';

  // 4) Process rows after header
  for (let r = headerRowIdx + 1; r < values.length; r++) {
    const row = values[r];

    const rawDate = safeCell(row, colIndex.date);
    const rawName = safeCell(row, colIndex.name);
    const rawLocation = safeCell(row, colIndex.location);
    const shiftVal = rowValue(row, colIndex.shift); // keep original (Date or String)
    const existingId = safeCell(row, colIndex.eventId);

    // Skip spacer rows
    if (!rawDate && !rawName && !shiftVal) continue;

    // Carry-forward for multi-row events
    lastDate = rawDate || lastDate;
    lastName = rawName || lastName;
    lastLocation = rawLocation || lastLocation;

    // Need date, name, shift
    if (!lastDate || !lastName || !shiftVal) continue;

    const eventDate = normalizeDate(lastDate);
    if (!eventDate) {
      console.log(`Row ${r + 1}: bad/empty date "${lastDate}"`);
      continue;
    }

    // Compute times (robust to single time / ranges / Date values)
    let start, end;
    try {
      ({ start, end } = computeShiftTimes(eventDate, shiftVal));
    } catch (e) {
      console.log(`Row ${r + 1}: bad time "${shiftVal}" → ${e.message}`);
      continue;
    }

    const title = String(lastName).replace(/\s+/g, ' ').replace(/^"|"$/g, '').trim();
    const location = String(lastLocation || '').trim();
    const description = buildDescription(row, header, colIndex);

    try {
      if (existingId) {
        // Update existing event (if found), else recreate and store new ID
        const evt = CalendarApp.getCalendarById(CALENDAR_ID).getEventById(existingId);
        if (evt) {
          const needTimeUpdate = (evt.getStartTime().getTime() !== start.getTime()) ||
                                 (evt.getEndTime().getTime() !== end.getTime());
          if (evt.getTitle() !== title) evt.setTitle(title);
          if (evt.getLocation() !== location) evt.setLocation(location);
          if (evt.getDescription() !== description) evt.setDescription(description);
          if (needTimeUpdate) evt.setTime(start, end);
          continue; // done with this row
        } else {
          // Event was deleted or ID invalid → create new
          const newEvt = CalendarApp.getCalendarById(CALENDAR_ID).createEvent(title, start, end, {
            location, description
          });
          sheet.getRange(r + 1, colIndex.eventId + 1).setValue(newEvt.getId());
          console.log(`Recreated: ${title} @ ${start.toLocaleString()}`);
          continue;
        }
      }

      // No existing ID → create fresh
      const event = CalendarApp.getCalendarById(CALENDAR_ID).createEvent(title, start, end, {
        location, description
      });
      sheet.getRange(r + 1, colIndex.eventId + 1).setValue(event.getId());
      console.log(`Created: ${title} @ ${start.toLocaleString()}`);
    } catch (e) {
      console.log(`Row ${r + 1}: failed to create/update event → ${e.message}`);
    }
  }
}

/* ---------- Helpers ---------- */

function detectHeader(values) {
  // A header row contains at least two of: date, name, location, shift/time
  const KEY_PATTERNS = [/date/i, /name/i, /location|venue|place/i, /shift|time/i];
  const maxScan = Math.min(values.length, 10);

  for (let i = 0; i < maxScan; i++) {
    const row = values[i].map(v => String(v || '').trim());
    let hits = 0;
    for (const cell of row) {
      if (KEY_PATTERNS.some(rx => rx.test(cell))) hits++;
    }
    if (hits >= 2) return { headerRowIdx: i, header: row };
  }
  return { headerRowIdx: -1, header: [] };
}

function indexHeadersFuzzy(header) {
  const lower = header.map(h => h.toLowerCase());

  const findOne = (alts) => {
    for (const a of alts) {
      const i = lower.findIndex(h => h.includes(a));
      if (i !== -1) return i;
    }
    return null;
  };

  const date = findOne(['date']);
  const name = findOne(['name of event', 'event name', 'name']);
  const location = findOne(['location', 'venue', 'place']);
  const shift = findOne(['shift time', 'shift', 'time']);
  const notes = findOne(['notes', 'note']);
  const medops = findOne(['medops', 'med ops', 'med-ops']);

  // EMTs = cols between Shift and Notes/Medops
  let emtStart = shift != null ? shift + 1 : null;
  let emtEnd = header.length - 1;
  if (notes != null) emtEnd = Math.min(emtEnd, notes - 1);
  if (medops != null) emtEnd = Math.min(emtEnd, medops - 1);
  const emtCols = (emtStart != null && emtStart <= emtEnd)
    ? Array.from({ length: emtEnd - emtStart + 1 }, (_, i) => emtStart + i)
    : [];

  // Event ID
  let eventId = lower.findIndex(h => h.includes(EVENT_ID_CANON));
  if (eventId === -1) eventId = null;

  return { date, name, location, shift, notes, medops, emtCols, eventId };
}

function ensureOrLocateEventIdColumn(sheet, headerRowIdx, header, colIndex) {
  if (colIndex.eventId != null) return;
  const row1 = headerRowIdx + 1;
  const lastCol = header.length + 1; // 1-based to append
  sheet.getRange(row1, lastCol).setValue('Event ID');
  colIndex.eventId = lastCol - 1; // store 0-based index
}

function safeCell(row, i) {
  if (i == null) return '';
  const v = row[i];
  if (v instanceof Date) return v;           // pass Date through
  return (v == null) ? '' : String(v).trim();
}

function rowValue(row, i) {
  if (i == null) return '';
  return row[i]; // raw: may be string, Date, or empty
}

function buildDescription(row, header, colIndex) {
  const lines = [];

  if (colIndex.location != null) {
    const loc = safeCell(row, colIndex.location);
    if (loc) lines.push(`Location: ${loc}`);
  }

  if (colIndex.emtCols && colIndex.emtCols.length) {
    const emtLines = [];
    colIndex.emtCols.forEach((c) => {
      const label = (header[c] || '').replace(/^EMTs?\s*/i, '');
      const val = safeCell(row, c);
      if (val) emtLines.push(`• ${label} ${val}`);
    });
    if (emtLines.length) {
      lines.push('---', 'EMTs / Staffing:', ...emtLines);
    }
  }

  const notes = safeCell(row, colIndex.notes);
  if (notes) lines.push('---', `Notes: ${notes}`);

  const medops = safeCell(row, colIndex.medops);
  if (medops) lines.push('---', `MedOps: ${medops}`);

  return lines.join('\n');
}

function normalizeDate(value) {
  if (value instanceof Date) {
    return new Date(value.getFullYear(), value.getMonth(), value.getDate(), 0, 0, 0);
  }
  const s = String(value).trim();
  if (!s) return null;

  const m = s.match(/^(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{2,4})$/);
  if (m) {
    let [_, mm, dd, yy] = m;
    mm = parseInt(mm, 10) - 1;
    dd = parseInt(dd, 10);
    yy = parseInt(yy, 10);
    yy = yy < 100 ? 2000 + yy : yy;
    return new Date(yy, mm, dd, 0, 0, 0);
  }

  const d = new Date(s);
  return isNaN(d) ? null : new Date(d.getFullYear(), d.getMonth(), d.getDate(), 0, 0, 0);
}

/** Compute start/end from shift value:
 *  - If Date: treat as time-of-day on eventDate (single time)
 *  - If String range: "9:00-13:00", "9am-1pm", "09:00–13:00"
 *  - If String single time: apply 8h/23:30 rule
 */
function computeShiftTimes(eventDate, shiftVal) {
  // Case 1: time cell is a Date (Google Sheets time)
  if (shiftVal instanceof Date) {
    const { hours, minutes } = { hours: shiftVal.getHours(), minutes: shiftVal.getMinutes() };
    const start = new Date(eventDate.getFullYear(), eventDate.getMonth(), eventDate.getDate(), hours, minutes, 0);
    const end = capSingleEnd(start, eventDate);
    return { start, end };
  }

  // Case 2: parse string
  const raw = String(shiftVal).trim();
  if (!raw) throw new Error('Empty time');

  // Normalize dashes
  const norm = raw.replace(/[–—−]/g, '-').replace(/\s+/g, ' ');
  const parts = norm.split('-').map(s => s.trim());

  if (parts.length > 1 && parts[1]) {
    // Range
    const start = parseFlexibleTimeOnDate(eventDate, parts[0]);
    let end = parseFlexibleTimeOnDate(eventDate, parts[1]);
    if (end <= start) {
      // Overnight → push to next day
      end = new Date(end.getTime() + 24 * 60 * 60 * 1000);
    }
    return { start, end };
  } else {
    // Single time
    const start = parseFlexibleTimeOnDate(eventDate, parts[0]);
    const end = capSingleEnd(start, eventDate);
    return { start, end };
  }
}

function capSingleEnd(start, eventDate) {
  const eightHours = new Date(start.getTime() + MAX_SHIFT_HOURS * 60 * 60 * 1000);
  const hardStop = parseFlexibleTimeOnDate(eventDate, HARD_STOP_TIME);
  let end = new Date(Math.min(eightHours.getTime(), hardStop.getTime()));
  if (end <= start) end = new Date(start.getTime() + 30 * 60 * 1000);
  return end;
}

/** Parse flexible time strings:
 * Accepts:
 *  - "9", "09", "9:00", "09:00", "9am", "9:30am", "9 pm", "12:15AM", 24h, "18:30"
 */
function parseFlexibleTimeOnDate(dayDate, timeStr) {
  const s = String(timeStr).trim().toLowerCase();

  // "9", "09"
  let m = s.match(/^(\d{1,2})$/);
  if (m) {
    const hh = parseInt(m[1], 10);
    if (hh >= 0 && hh <= 23) {
      return new Date(dayDate.getFullYear(), dayDate.getMonth(), dayDate.getDate(), hh, 0, 0);
    }
  }

  // "9am", "9pm", "9 am", "9:30am", "09:30 pm"
  m = s.match(/^(\d{1,2})(?::(\d{2}))?\s*(am|pm)$/i);
  if (m) {
    let hh = parseInt(m[1], 10);
    const mm = m[2] ? parseInt(m[2], 10) : 0;
    const ap = m[3].toLowerCase();
    if (ap === 'pm' && hh < 12) hh += 12;
    if (ap === 'am' && hh === 12) hh = 0;
    return new Date(dayDate.getFullYear(), dayDate.getMonth(), dayDate.getDate(), hh, mm, 0);
  }

  // "09:00", "9:00", "18:30"
  m = s.match(/^(\d{1,2}):(\d{2})$/);
  if (m) {
    const hh = parseInt(m[1], 10);
    const mm = parseInt(m[2], 10);
    return new Date(dayDate.getFullYear(), dayDate.getMonth(), dayDate.getDate(), hh, mm, 0);
  }

  throw new Error(`Unrecognized time: "${timeStr}"`);
}