Overtime Alert: Copy-Paste Apps Script Pattern

What this script does

Prerequisites

Walkthrough

Edge cases

How to test

Hardening for production

Variations

Full code: overtimeAlert()

Run timesheetHoursCalculation first, then overtimeAlert() on a daily trigger near week end.

/**
 * Email managers when an employee exceeds weekly overtime hours.
 */
const TS = "Timesheets"; // A employee, B date, C hours, D alertedWeekKey
const OT_LIMIT = 40;
const MANAGER = "managers@example.com";

function overtimeAlert() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(TS);
  const values = sheet.getDataRange().getValues();
  const totals = {};
  const weekKeys = {};

  for (let i = 1; i < values.length; i++) {
    const emp = values[i][0];
    const dt = values[i][1];
    const hours = Number(values[i][2]) || 0;
    if (!(dt instanceof Date)) continue;
    const weekKey = emp + "|" + isoWeekKey_(dt);
    totals[weekKey] = (totals[weekKey] || 0) + hours;
    weekKeys[i] = weekKey;
  }

  const alerted = {};
  for (let i = 1; i < values.length; i++) {
    const weekKey = weekKeys[i];
    if (!weekKey) continue;
    if (totals[weekKey] <= OT_LIMIT) continue;
    if (values[i][3] === weekKey) continue; // row already marked for this week alert
    if (alerted[weekKey]) {
      values[i][3] = weekKey;
      continue;
    }
    const emp = values[i][0];
    MailApp.sendEmail(
      MANAGER,
      "Overtime alert: " + emp,
      emp + " has " + totals[weekKey].toFixed(2) + " hours in week " + weekKey.split("|")[1] +
        " (limit " + OT_LIMIT + ")."
    );
    alerted[weekKey] = true;
    values[i][3] = weekKey;
  }
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

function isoWeekKey_(date) {
  const d = new Date(Date.UTC(date.getFullYear(), date.getMonth(), date.getDate()));
  const dayNum = d.getUTCDay() || 7;
  d.setUTCDate(d.getUTCDate() + 4 - dayNum);
  const yearStart = new Date(Date.UTC(d.getUTCFullYear(), 0, 1));
  const weekNo = Math.ceil((((d - yearStart) / 86400000) + 1) / 7);
  return d.getUTCFullYear() + "-W" + String(weekNo).padStart(2, "0");
}

1. Line 5: Weekly hours threshold.
2. Line 19: Builds an employee-independent week id.
3. Line 20: Aggregates hours per employee-week.
4. Line 35: Manager notification.
5. Line 4: Column D dedupe stamp.
6. Line 30: Ensures one email even across many rows.