Untitled

avatar
unknown
plain_text
10 months ago
10 kB
12
Indexable
/*
* Time out email should follow the email thread
* Yellow color should be the same one currently in use
* Detention emails should be calculated based on appointment time plus one hour for stg and two if else 
*/



/**********************
 * CONFIG & CONSTANTS *
 **********************/

const TZ = "America/New_York";

// Hard-coded recipients (Phase 1: Matson Jamesburg)
const EMAIL_TO = "[email protected]";        // e.g., "[email protected]"
const EMAIL_CC = "[email protected]";    // e.g., "[email protected]" or ""

// Column indices (1-based)
const COL = {
  STATUS:   1,   // A — dispatcher notes ("arr 0700" then "arr 0700-0900")
  CUSTOMER: 4,   // D — must equal "Matson Jamesburg"
  LOAD:     7,   // G — required
  TOWN:     12,  // L — required
  ARR_DD:   28,  // AB — arrival de-dupe time (plain "HHMM")
  DEP_DD:   29,   // AC — departure de-dupe time (plain "HHMM")
  THREAD_ID:30    //AD - store Gmail thread id
};

const REQUIRED_CUSTOMER = "MATSON";

// Visuals
const PARTIAL_HL_END_COL = COL.LOAD; // highlight A→G on arrival
const HIGHLIGHT_COLOR = "#fff59d";   // light yellow


/****************************
 * INSTALLABLE ON-EDIT HOOK *
 ****************************/

function onEdit(e) {
  try {
    if (!e || !e.range || e.range.getNumRows() !== 1 || e.range.getNumColumns() !== 1) return;

    const sheet = e.range.getSheet();
    if (!todayMatchesTabName(sheet.getName(), TZ)) return;

    const col = e.range.getColumn();
    const row = e.range.getRow();
    if (col !== COL.STATUS) return; // only react to Column A edits

    // Read row values we need
    const loadNum = sheet.getRange(row, COL.LOAD).getValue();
    const town    = sheet.getRange(row, COL.TOWN).getValue();
    const customer= sheet.getRange(row, COL.CUSTOMER).getValue();
    const status  = sheet.getRange(row, COL.STATUS).getValue();
    const arrDD   = sheet.getRange(row, COL.ARR_DD).getValue();
    const depDD   = sheet.getRange(row, COL.DEP_DD).getValue();
    let threadId  = sheet.getRange(row, COL.THREAD_ID).getValue();

    // Guardrails
    if ((customer || "").toString().trim() !== REQUIRED_CUSTOMER) return;
    if (!stringPresent(loadNum)) return;
    if (!stringPresent(town)) return;

    const times = parseTimes(status);
    if (!times.arrival && !times.departure) return;

    const subject = buildSubject(loadNum, "Jamesburg NJ");

    // Branching logic with de-dupe
    const needArrival   = times.arrival && !hhmmEquals(times.arrival, arrDD);
    const needDeparture = times.departure && !hhmmEquals(times.departure, depDD);

    // Both arrival & departure present in the cell
    if (times.arrival && times.departure) {
      if (needArrival && needDeparture) {
        const body = "Time in " + times.arrival + "\n" +
                     "Time out " + times.departure;
        threadId = sendNewAndGetThreadId(EMAIL_TO, EMAIL_CC, subject, body)
        //sendEmail(EMAIL_TO, EMAIL_CC, subject, body);
        // Write de-dupe stamps
        setCell(sheet, row, COL.ARR_DD, times.arrival);
        setCell(sheet, row, COL.DEP_DD, times.departure);
        if (threadId) setCell(sheet, row, COL.THREAD_ID, threadId);
        // Full-row highlight
        highlightFull(sheet, row);
      } else if (!needArrival && needDeparture) {
        const body = "Time out " + times.departure;
        // Replying to existing thread; if missing, fallback to new + store id
        if (threadId) {
          replyToThread(threadId, body, EMAIL_CC);
        }
        else {
        threadId = sendNewAndGetThreadId(EMAIL_TO, EMAIL_CC, subject, body)
        if (threadId) setCell(sheet, row, COL.THREAD_ID, threadId)
        }
        setCell(sheet, row, COL.DEP_DD, times.departure);
        highlightFull(sheet, row);
      } else if (needArrival && !needDeparture) {
        const body = "Time in " + times.arrival;
        // New messge for arrival; capture thread id
        threadId = sendNewAndGetThreadId(EMAIL_TO, EMAIL_CC, subject, body);
        setCell(sheet, row, COL.ARR_DD, times.arrival);
        if (threadId) setCell(sheet, row, COL.THREAD_ID, threadId);
        highlightPartial(sheet, row);
      } else {
        // Neither needed (both same as de-dupe) → do nothing
        return;
      }
      return;
    }

    // Arrival-only present
    if (times.arrival && !times.departure) {
      if (!needArrival) return;
      const body = "Time in " + times.arrival;
      // New message for arrival; capture thread id
      threadId = sendNewAndGetThreadId(EMAIL_TO, EMAIL_CC, subject, body);
      setCell(sheet, row, COL.ARR_DD, times.arrival);
      if (threadId) setCell(sheet, row, COL.THREAD_ID, threadId)
      highlightPartial(sheet, row);
      return;
    }

    // (We don't support "dep-only" input per your spec; departures come via "-HHMM" append)
  } catch (err) {
    console.error("onEdit error:", err);
  }
}


/*****************
 * HELPER FUNCS  *
 *****************/

function todayMatchesTabName(sheetName, tz) {
  const now = new Date();
  const fullDay  = Utilities.formatDate(now, tz, "EEEE"); // e.g., "Friday"
  const shortDay = Utilities.formatDate(now, tz, "EEE");  // e.g., "Fri"
  const md       = Utilities.formatDate(now, tz, "M/d");  // e.g., "8/29"

  // Accepted variants:
  const candidates = [
    `${fullDay} ${md}`,
    `${shortDay} ${md}`,
    `${fullDay} ${md.replace("/", "_")}`,
    `${shortDay} ${md.replace("/", "_")}`
  ].map(s => s.toLowerCase().trim());

  const name = (sheetName || "").toString().toLowerCase().trim();
  return candidates.includes(name);
}

// Parse "arr 0700" or "arr 07:00-09:00" (tolerate spaces).
// Returns: { arrival: "HHMM" | null, departure: "HHMM" | null }
function parseTimes(statusText) {
  if (!statusText) return { arrival: null, departure: null };
  let s = statusText.toString().trim().toLowerCase();

  // Must start with "arr"
  if (!/^arr\b/.test(s)) return { arrival: null, departure: null };

  // Remove any extra spaces around dash; normalize "arr" block
  // Examples handled: "arr 07:00-09:00", "arr 7:00 - 9:00", "arr 0700-0900"
  s = s.replace(/\s*-\s*/g, "-").replace(/\s+/g, " ");

  // Extract after 'arr'
  const afterArr = s.replace(/^arr\s*/, ""); // e.g., "07:00-09:00" or "0700"

  let arrival = null, departure = null;

  if (afterArr.includes("-")) {
    const [aRaw, dRaw] = afterArr.split("-");
    arrival   = normalizeToHHMM(aRaw);
    departure = normalizeToHHMM(dRaw);
  } else {
    arrival = normalizeToHHMM(afterArr);
  }

  if (!isValidHHMM(arrival))   arrival   = null;
  if (!isValidHHMM(departure)) departure = null;

  return { arrival, departure };
}

// Normalize inputs like "07:00" → "0700", "7:00" → "0700", "700" → "0700"
function normalizeToHHMM(raw) {
  if (!raw && raw !== 0) return null;
  let txt = raw.toString().trim();

  // If contains colon, split hours:minutes
  if (txt.includes(":")) {
    const parts = txt.split(":");
    if (parts.length < 2) return null;
    let h = parts[0].replace(/\D/g, "");
    let m = parts[1].replace(/\D/g, "");
    if (h.length === 0 || m.length === 0) return null;
    h = h.padStart(2, "0");
    m = m.padStart(2, "0");
    return `${h}${m}`;
  }

  // Digits-only path (could be "700", "0700", "0900")
  txt = txt.replace(/\D/g, "");
  if (txt.length === 3) {
    // e.g., "700" → "0700"
    txt = "0" + txt;
  } else if (txt.length !== 4) {
    return null;
  }
  return txt;
}

function isValidHHMM(s) {
  if (!s || typeof s !== "string" || s.length !== 4) return false;
  if (!/^\d{4}$/.test(s)) return false;
  const h = parseInt(s.slice(0, 2), 10);
  const m = parseInt(s.slice(2, 4), 10);
  return h >= 0 && h <= 23 && m >= 0 && m <= 59;
}

function hhmmEquals(a, b) {
  const aa = (a || "").toString().trim();
  const bb = (b || "").toString().trim();
  return aa === bb && aa !== "";
}

function buildSubject(loadNum, town) {
  return `${loadNum} / ${town} - Updates`;
}

function sendNewAndGetThreadId(to, cc, subject, body) {
  // send message first

  if (cc && cc.toString().trim() != "") {
    GmailApp.sendEmail(to, subject, body, { cc: cc});
  }
  else {
    GmailApp.sendEmail(to, subject, body);
  }
  // Find the newly created thread
  // Narrow the search window to reduce ambiguity
  const q = [
    `from:me`,
    `to:"${to}"`,
    `subject:"${escapeForQuery(subject)}"`,
    `newer_than:3d`
    ].join(' ');


    const threads = GmailApp.search(q, 0, 5);
    if (!threads || threads.length === 0) return "";
    // Heuristic: pick the most recent thread with exact subject match
    for (let i = 0; i < threads.length; i++) {
      const th = threads[i];
        if (th.getFirstMessageSubject() === subject) {
          return th.getId();
        }
      }
      // Fallback to the newest thread
      return threads[0].getId();
}

/**
 * Replies into an existing thread by id, keeping the conversation intact.
 * Adds CC if provided (GmailThread.reply honors cc option).
 */
function replyToThread(threadId, body, cc) {
  try {
    const thread = GmailApp.getThreadById(threadId);
    if (!thread) throw new Error("Thread not found for id: " + threadId);
    if (cc && cc.toString().trim() !== "") {
      thread.reply(body, { cc: cc });
    } else {
      thread.reply(body);
    }
  } catch (err) {
    console.error("replyToThread error:", err);
    throw err;
  }
}

// Escape quotes for Gmail search query
function escapeForQuery(s) {
  return (s || "").toString().replace(/"/g, '\\"');
}


function setCell(sheet, row, col, value) {
  sheet.getRange(row, col).setValue(value);
}

function highlightPartial(sheet, row) {
  sheet.getRange(row, 1, 1, PARTIAL_HL_END_COL).setBackground(HIGHLIGHT_COLOR);
}

function highlightFull(sheet, row) {
  const lastCol = sheet.getLastColumn();
  sheet.getRange(row, 1, 1, lastCol).setBackground(HIGHLIGHT_COLOR);
}

function stringPresent(v) {
  return v !== null && v !== undefined && v.toString().trim() !== "";
}
Editor is loading...
Leave a Comment