Smart Life US

← All posts · 2026-08-29 · Excel & Automation

Google Sheets weekly KPI email: KPI Pt.4 final code

Google Sheets weekly KPI email: KPI Pt.4 final code

Introduction: So you’re not stuck in Excel every Monday

People who search for automatic weekly KPI report emails from Google Sheets are usually in a similar situation. Every Monday morning, they’re likely repeating the same process:整理 last week’s inbound appointment KPIs, paste them into Excel, aggregate scores by carrier, then share the result by email. A human can do it, but it’s a shame to spend that time every single week.

Weekly KPI automatic email flow

This article is part 4 of the Google Sheets KPI automation series.

By the end of the first three parts, we had the following in place:

In this part, we go one step further and add an Apps Script weekly report email. Using ISO week rules, we calculate “last week,” summarize carrier KPIs into an HTML table, and automatically email the report on Monday morning.

In real-world DC operations, you often have to share KPIs weekly with carriers and customers. In the past, the busier the owner, the later the report went out. Once we automated KPI history, carrier scores, and then layered on weekly email sending from Google Sheets, we made “the previous week’s report is already waiting in your inbox on Monday morning” the default state.

Calculating ISO week start: KPI_getWeekStart_ and KPI_getIsoWeekNo_

The first thing you must define for a weekly report is “what counts as a week.” Year‑end and year‑start are particularly tricky: you’ve probably noticed that different calendar apps sometimes show different week numbers. In this article we’ll use ISO week rules, which are common in logistics.

Under ISO rules, the week starts on Monday, and week 1 is “the week that contains the first Thursday of that year.” Because of that, some days at the end of December can fall into week 1 of the next year, and a few days at the start of January can land in week 52 or 53 of the previous year. Manually reconciling this is always confusing, so it’s safer to implement ISO rules precisely in code.

The main audit comments here were:

  1. ISO week numbers must always be calculated using NY (US Eastern) timezone, and
  2. Clean up any paths that mix timezones via direct getDay / setHours usage.

So we always convert dates to a New York timezone Y-M-D string first, and based on that Y-M-D, we create a “local noon Date” to run calculations. This avoids off‑by‑one‑day errors during DST changes.

Place the following three helper functions together at the top of the same file.

Apps Script (JavaScript)
// → Fixed timezone to use for KPI week calculations
const KPI_WEEK_TZ = 'America/New_York';

// → Strip a date down to year/month/day in NY timezone.
function KPI_ymdParts_(date) {
  if (!(date instanceof Date) || isNaN(date)) {
    throw new Error('KPI_ymdParts_: Invalid date');
  }
  const ymd = Utilities.formatDate(date, KPI_WEEK_TZ, 'yyyy-MM-dd');
  const m = /^(\d{4})-(\d{2})-(\d{2})$/.exec(ymd);
  if (!m) throw new Error('KPI_ymdParts_: Failed to parse date: ' + ymd);
  return { y: +m[1], m: +m[2], d: +m[3] };
}

// → Get the Monday (ISO week) for the week that contains the given date.
//   Uses NY timezone Y-M-D only to remove DST effects.
function KPI_getWeekStart_(date) {
  if (!(date instanceof Date) || isNaN(date)) {
    throw new Error('KPI_getWeekStart_: Invalid date');
  }

  const p = KPI_ymdParts_(date);
  // → Create a local noon Date for the NY-based Y-M-D
  const d = new Date(p.y, p.m - 1, p.d, 12, 0, 0, 0);
  const day = d.getDay();              // 0 (Sun)–6 (Sat)
  const isoDay = day === 0 ? 7 : day;  // ISO: Mon=1 … Sun=7
  d.setDate(d.getDate() - (isoDay - 1)); // Move to that week’s Monday
  d.setHours(0, 0, 0, 0);              // Normalize to midnight
  return d;
}

// → Calculate ISO week number and ISO year.
//   Again uses NY timezone Y-M-D and does all math on a local noon Date.
function KPI_getIsoWeekNo_(date) {
  if (!(date instanceof Date) || isNaN(date)) {
    throw new Error('KPI_getIsoWeekNo_: Invalid date');
  }

  const p = KPI_ymdParts_(date);
  const d = new Date(p.y, p.m - 1, p.d, 12, 0, 0, 0); // Input day at local noon based on NY Y-M-D

  // ISO rule: the week that contains Thursday determines the year/week
  const day = d.getDay();              // 0–6
  const isoDay = day === 0 ? 7 : day;  // 1–7
  d.setDate(d.getDate() + (4 - isoDay)); // Move to Thursday of that week
  const weekYear = d.getFullYear();      // ISO week-year

  // Thursday of ISO week 1 for this week-year (always the week that includes Jan 4)
  const jan4 = new Date(weekYear, 0, 4, 12, 0, 0, 0);
  const jan4Day = jan4.getDay();
  const jan4IsoDay = jan4Day === 0 ? 7 : jan4Day;
  jan4.setDate(jan4.getDate() + (4 - jan4IsoDay)); // First Thursday of that ISO year

  const msInDay = 24 * 60 * 60 * 1000;
  const weekNo = 1 + Math.round((d - jan4) / (msInDay * 7));

  return { year: weekYear, week: weekNo };
}

Testing ISO week helpers: KPI_testWeekHelpers_

ISO week calculations are especially error‑prone around year‑end and year‑start, so the audit asked for concrete test cases. The test function below checks whether representative dates return the expected ISO week values.

Here we hard‑code the expected values according to common ISO rules, and throw on mismatch.

Test targets:

  • 2026-12-31
  • 2026-01-01
  • Today (sanity check)
  • Empty / invalid Date
Apps Script (JavaScript)
// → Tests for ISO week helper functions
function KPI_testWeekHelpers_() {
  function assertEqual(label, actual, expected) {
    const ok = JSON.stringify(actual) === JSON.stringify(expected);
    if (!ok) {
      const msg = 'FAIL ' + label + ' expected=' +
        JSON.stringify(expected) + ' actual=' +
        JSON.stringify(actual);
      Logger.log(msg);
      throw new Error(msg);
    } else {
      Logger.log('OK ' + label + ' = ' + JSON.stringify(actual));
    }
  }

  // 1) 2026-12-31 → ISO week-year/week (assume it is week 53 of 2026)
  const d1 = new Date('2026-12-31T00:00:00Z');
  const w1 = KPI_getIsoWeekNo_(d1);
  assertEqual('2026-12-31 ISO week', w1, { year: 2026, week: 53 });

  // 2) 2026-01-01 → ISO week-year/week (assume it is week 1 of 2026)
  const d2 = new Date('2026-01-01T00:00:00Z');
  const w2 = KPI_getIsoWeekNo_(d2);
  assertEqual('2026-01-01 ISO week', w2, { year: 2026, week: 1 });

  // 3) Basic behavior of weekStart for today
  const today = new Date();
  const wsToday = KPI_getWeekStart_(today);
  const isoToday = KPI_getIsoWeekNo_(today);
  Logger.log('Today weekStart=' + wsToday.toISOString() +
             ' iso=' + JSON.stringify(isoToday));

  // 4) Verify invalid Date input throws
  let threw = false;
  try {
    KPI_getWeekStart_(new Date('')); // Invalid Date
  } catch (e) {
    threw = true;
    Logger.log('OK invalid date for KPI_getWeekStart_ threw error: ' + e);
  }
  if (!threw) {
    throw new Error('FAIL KPI_getWeekStart_ did not throw on invalid date');
  }

  threw = false;
  try {
    KPI_getIsoWeekNo_(new Date('')); // Invalid Date
  } catch (e) {
    threw = true;
    Logger.log('OK invalid date for KPI_getIsoWeekNo_ threw error: ' + e);
  }
  if (!threw) {
    throw new Error('FAIL KPI_getIsoWeekNo_ did not throw on invalid date');
  }
}

If your actual ISO week results differ, adjust the expected values to match your operational calendar. The key is to explicitly encode expectations in tests and fail loudly when reality diverges.

Locking in KPI_HISTORY headers: error on mismatch with expected headers

In part 2, we already defined a structure for KPI_HISTORY. The audit requested that we hard‑code the header strings both in code and docs, and that we should not fail silently: if headers don’t match expectations, we must log the expected header list in the error message.

Here’s the header set we finalized in part 2 for KPI_HISTORY. Row 1 must contain these names in this exact order:

TEXT
DATE
FACILITY
CARRIER
APPOINTMENT_COUNT
TOTAL_LOADS
ON_TIME_LOADS
LATE_LOADS
CANCELLED_LOADS
AVG_DWELL_MIN
ON_TIME_RATE
LATE_RATE
CANCEL_RATE
CREATED_AT
UPDATED_AT

Your production sheet might have more columns, but these 14 columns are the ones this article’s code assumes explicitly. The aggregation and email code below uses this array as‑is and throws if the actual sheet headers differ, including the expected header list in the error.

First, the constant for expected headers:

Apps Script (JavaScript)
// → KPI_HISTORY headers finalized in part 2 (must exist in row 1 in this order)
const KPI_HISTORY_HEADERS = [
  'DATE',
  'FACILITY',
  'CARRIER',
  'APPOINTMENT_COUNT',
  'TOTAL_LOADS',
  'ON_TIME_LOADS',
  'LATE_LOADS',
  'CANCELLED_LOADS',
  'AVG_DWELL_MIN',
  'ON_TIME_RATE',
  'LATE_RATE',
  'CANCEL_RATE',
  'CREATED_AT',
  'UPDATED_AT'
];

Building the weekly carrier KPI table: KPI_getWeeklyCarrierReport

Now we get to the core of logistics KPI automation in Google Sheets. In Automatically calculating carrier scores and grades in Google Sheets — KPI Pt.3, we implemented KPI_getCarrierScores() to generate scores and grades per carrier. Here, we’ll create KPI_getWeeklyCarrierReport() which will pull just last week’s KPI history and summarize by carrier.

Design:

  • Input: a base date (usually “today”). Actual logic always uses last week (Mon–Sun) based on that.
  • Process:
  • From the base date, go 7 days back, then compute that date’s Monday and Sunday.
  • Read rows from KPI_HISTORY in that time range.
  • Aggregate by carrier: TOTAL_LOADS / ON_TIME_LOADS / LATE_LOADS.
  • Call part 3’s KPI_getCarrierScores(). As defined there, the return type is

{"CarrierName": { score: number, grade: string, ... }} — an object map — so this code matches that.

  • Output: an HTML table string ready to embed in an email, plus meta info for subject lines (date range, ISO week, etc.).
Apps Script (JavaScript)
// → Settings for weekly report
const KPI_WEEKLY_CONFIG = {
  HISTORY_SHEET_NAME: 'KPI_HISTORY',
  TZ: KPI_WEEK_TZ,
  MIN_DAYS_FOR_REPORT: 1  // At least this many distinct days of data last week before sending
};

// → Verify KPI_HISTORY sheet matches expected structure
function KPI_assertHistoryHeaders_(sheet) {
  const lastCol = sheet.getLastColumn();
  const headerRange = sheet.getRange(1, 1, 1, lastCol);
  const actual = headerRange.getValues()[0].map(String);

  const expected = KPI_HISTORY_HEADERS;
  const sameLength = actual.length === expected.length;
  const sameAll = sameLength && actual.every(function (h, i) {
    return h === expected[i];
  });

  if (!sameAll) {
    const msg = 'KPI_HISTORY headers differ from expectations.\n' +
      'Expected: ' + JSON.stringify(expected) + '\n' +
      'Actual:   ' + JSON.stringify(actual);
    throw new Error(msg);
  }

  // Header name → index map
  const colIdx = {};
  expected.forEach(function (h, i) {
    colIdx[h] = i;
  });
  return colIdx;
}

// → Calculate last week’s (Mon–Sun) carrier KPI summary.
function KPI_getWeeklyCarrierReport(baseDate) {
  const today = baseDate instanceof Date && !isNaN(baseDate)
    ? new Date(baseDate)
    : new Date();

  // Reference day for last week: 7 days ago
  const lastWeekRef = new Date(today);
  lastWeekRef.setDate(lastWeekRef.getDate() - 7);

  // Last week’s Monday–Sunday
  const weekStart = KPI_getWeekStart_(lastWeekRef);
  const weekEnd = new Date(weekStart);
  weekEnd.setDate(weekEnd.getDate() + 6);

  const startYmd = Utilities.formatDate(
    weekStart, KPI_WEEKLY_CONFIG.TZ, 'yyyy-MM-dd'
  );
  const endYmd = Utilities.formatDate(
    weekEnd, KPI_WEEKLY_CONFIG.TZ, 'yyyy-MM-dd'
  );

  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName(KPI_WEEKLY_CONFIG.HISTORY_SHEET_NAME);
  if (!sheet) {
    throw new Error('KPI_HISTORY sheet not found');
  }

  const lastRow = sheet.getLastRow();
  if (lastRow < 2) {
    throw new Error('KPI_HISTORY sheet has no data');
  }

  const lastCol = sheet.getLastColumn();
  // Audit note: must always cover through the last row
  const range = sheet.getRange(2, 1, lastRow - 1, lastCol);
  const values = range.getValues();

  // Verify headers and build index map
  const colIdx = KPI_assertHistoryHeaders_(sheet);

  const carrierStats = {};          // { CARRIER: { total, onTime, late } }
  const daysInRange = new Set();    // Distinct dates found in range

  values.forEach(function (row) {
    const dateVal = row[colIdx['DATE']];
    if (!(dateVal instanceof Date) || isNaN(dateVal)) {
      return;
    }
    const ymd = Utilities.formatDate(
      dateVal, KPI_WEEKLY_CONFIG.TZ, 'yyyy-MM-dd'
    );
    if (ymd < startYmd || ymd > endYmd) return;

    const carrier = String(row[colIdx['CARRIER']] || '').trim();
    if (!carrier) return;

    daysInRange.add(ymd);

    const total = row[colIdx['TOTAL_LOADS']];
    const onTime = row[colIdx['ON_TIME_LOADS']];
    const late = row[colIdx['LATE_LOADS']];

    // If TOTAL_LOADS is not numeric, skip and log
    if (!Number.isFinite(Number(total))) {
      Logger.log('SKIP TOTAL_LOADS not numeric: ' +
        ymd + ' / ' + carrier + ' / value=' + total);
      return;
    }

    const nTotal = Number(total);
    const nOnTime = Number(onTime);
    const nLate = Number(late);

    if (
      !Number.isFinite(nOnTime) || nOnTime < 0 ||
      !Number.isFinite(nLate) || nLate < 0 ||
      nTotal < 0
    ) {
      Logger.log('SKIP invalid KPI numbers: ' +
        ymd + ' / ' + carrier +
        ' total=' + total + ' onTime=' + onTime + ' late=' + late);
      return;
    }

    if (!carrierStats[carrier]) {
      carrierStats[carrier] = { total: 0, onTime: 0, late: 0 };
    }
    carrierStats[carrier].total += nTotal;
    carrierStats[carrier].onTime += nOnTime;
    carrierStats[carrier].late += nLate;
  });

  if (daysInRange.size < KPI_WEEKLY_CONFIG.MIN_DAYS_FOR_REPORT) {
    throw new Error('Not enough KPI data in last week range. daysInRange=' +
      daysInRange.size + ' (min=' + KPI_WEEKLY_CONFIG.MIN_DAYS_FOR_REPORT + ')');
  }

  // Call the carrier score calculation from part 3
  // Actual return structure: { 'CarrierName': { score: number, grade: string, ... }, ... }
  const scoreInfo = KPI_getCarrierScores();

  const rows = [];
  rows.push([
    'Carrier',
    'Total inbound loads',
    'On-time arrivals',
    'Late arrivals',
    'On-time rate (%)',
    'Score',
    'Grade'
  ]);

  Object.keys(carrierStats).sort().forEach(function (carrier) {
    const st = carrierStats[carrier];
    const onTimeRate = st.total > 0
      ? Math.round((st.onTime / st.total) * 1000) / 10
      : 0;

    const sc = scoreInfo[carrier] || { score: 0, grade: '-' };

    rows.push([
      carrier,
      String(st.total),
      String(st.onTime),
      String(st.late),
      onTimeRate.toFixed(1),
      (typeof sc.score === 'number'
        ? sc.score.toFixed(1)
        : String(sc.score)),
      sc.grade
    ]);
  });

  let html = '<table border="1" cellpadding="4" cellspacing="0" style="border-collapse:collapse;">';
  rows.forEach(function (r, idx) {
    html += '<tr>';
    r.forEach(function (cell) {
      const tag = idx === 0 ? 'th' : 'td';
      html += '<' + tag + '>' + String(cell) + '</' + tag + '>';
    });
    html += '</tr>';
  });
  html += '</table>';

  const iso = KPI_getIsoWeekNo_(weekStart);

  return {
    startYmd: startYmd,
    endYmd: endYmd,
    isoYear: iso.year,
    isoWeek: iso.week,
    htmlTable: html,
    carrierCount: Object.keys(carrierStats).length
  };
}

Testing weekly aggregation: KPI_testWeeklyCarrierReport_

To confirm that the aggregation function works as intended, the audit also requested a representative test.

Key points:

  • Confirm that lastRow === 2 (header + one data row) is handled correctly.
  • Confirm that rows where TOTAL_LOADS is non‑numeric (e.g. 'ABC') are skipped and logged.
  • Check overall behavior via Logger.log.

The test below does not mutate sheet contents. Instead, it checks boundary conditions and logs FAIL messages appropriate to your sheet’s current state.

Apps Script (JavaScript)
function KPI_testWeeklyCarrierReport_() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName(KPI_WEEKLY_CONFIG.HISTORY_SHEET_NAME);
  if (!sheet) {
    throw new Error('KPI_HISTORY sheet not found; cannot run test');
  }

  // 1) Boundary test reminder if lastRow === 2 (header + single data row)
  const lastRow = sheet.getLastRow();
  if (lastRow === 2) {
    Logger.log('INFO lastRow===2 (header + 1 row). Good state for single-row boundary test.');
  } else {
    Logger.log('INFO lastRow=' + lastRow +
      '. For a strict single-row boundary test, temporarily leave just one data row.');
  }

  // 2) Reminder about non-numeric TOTAL_LOADS behavior
  Logger.log('INFO If any row has non-numeric TOTAL_LOADS, ' +
    'check for "SKIP TOTAL_LOADS not numeric" logs when running KPI_getWeeklyCarrierReport.');

  // 3) Run default flow and log output
  try {
    const rep = KPI_getWeeklyCarrierReport(new Date());
    Logger.log('OK KPI_getWeeklyCarrierReport: ' + JSON.stringify(rep, null, 2));
  } catch (e) {
    Logger.log('FAIL KPI_getWeeklyCarrierReport: ' + e);
    throw e;
  }
}

In practice you don’t need to run this regularly; just run it once after structural changes to confirm aggregation range and error handling.

HTML email recipient sheet: KPI_RECIPIENTS structure

In real operations, “who should receive this?” changes frequently. Hard‑coding emails in code is brittle; it’s better to manage recipients in a separate sheet.

We’ll use the following KPI_RECIPIENTS headers:

TEXT
ROLE
EMAIL
ACTIVE

Setup steps:

  1. Create a new sheet named KPI_RECIPIENTS in your spreadsheet.
  2. In row 1, enter: A1: ROLE, B1: EMAIL, C1: ACTIVE.
  3. From row 2 downward, add recipients:

The function below expects exactly this structure and will throw an error with the expected header list if the actual sheet differs.

Apps Script (JavaScript)
// → Settings for weekly report email
const KPI_WEEKLY_MAIL_CONFIG = {
  RECIPIENT_SHEET_NAME: 'KPI_RECIPIENTS',
  SUBJECT_PREFIX: '[Inbound KPI] Weekly report',
  TZ: KPI_WEEK_TZ
};

const KPI_RECIPIENT_HEADERS = [
  'ROLE',
  'EMAIL',
  'ACTIVE'
];

// → Verify KPI_RECIPIENTS headers
function KPI_assertRecipientHeaders_(sheet) {
  const lastCol = sheet.getLastColumn();
  const headerRange = sheet.getRange(1, 1, 1, lastCol);
  const actual = headerRange.getValues()[0].map(String);

  const expected = KPI_RECIPIENT_HEADERS;
  const sameLength = actual.length === expected.length;
  const sameAll = sameLength && actual.every(function (h, i) {
    return h === expected[i];
  });

  if (!sameAll) {
    const msg = 'KPI_RECIPIENTS headers differ from expectations.\n' +
      'Expected: ' + JSON.stringify(expected) + '\n' +
      'Actual:   ' + JSON.stringify(actual);
    throw new Error(msg);
  }

  const colIdx = {};
  expected.forEach(function (h, i) {
    colIdx[h] = i;
  });
  return colIdx;
}

// → Return list of EMAIL values where ACTIVE=TRUE in KPI_RECIPIENTS.
function KPI_loadActiveRecipients_() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName(KPI_WEEKLY_MAIL_CONFIG.RECIPIENT_SHEET_NAME);
  if (!sheet) {
    throw new Error('KPI_RECIPIENTS sheet not found');
  }

  const lastRow = sheet.getLastRow();
  if (lastRow < 2) {
    throw new Error('KPI_RECIPIENTS sheet has no data');
  }

  const lastCol = sheet.getLastColumn();
  const colIdx = KPI_assertRecipientHeaders_(sheet);

  const range = sheet.getRange(2, 1, lastRow - 1, lastCol);
  const values = range.getValues();

  const emails = [];

  values.forEach(function (row) {
    const active = row[colIdx['ACTIVE']];
    const isActive = (typeof active === 'boolean') ? active : Boolean(active);
    if (!isActive) return;

    const email = String(row[colIdx['EMAIL']] || '').trim();
    if (!email) return;
    if (email.indexOf('@') === -1) {
      Logger.log('SKIP invalid email: ' + email);
      return;
    }
    emails.push(email);
  });

  if (emails.length === 0) {
    throw new Error('No active recipient emails found');
  }

  return emails;
}

Sending the HTML email: KPI_sendWeeklyCarrierReport

Now we’ll send the weekly carrier KPI report email using MailApp. This function:

  1. Loads recipients with KPI_loadActiveRecipients_().
  2. Builds last week’s carrier KPI summary via KPI_getWeeklyCarrierReport().
  3. Sends an HTML email through MailApp.
Apps Script (JavaScript)
// → Send last week’s carrier KPI report as an HTML email.
function KPI_sendWeeklyCarrierReport() {
  const recipients = KPI_loadActiveRecipients_();
  const report = KPI_getWeeklyCarrierReport(new Date());

  const subject = Utilities.formatString(
    '%s %s~%s (ISO week %d)',
    KPI_WEEKLY_MAIL_CONFIG.SUBJECT_PREFIX,
    report.startYmd,
    report.endYmd,
    report.isoWeek
  );

  let body = '';
  body += '<p>Hello,</p>';
  body += Utilities.formatString(
    '<p>Here is the carrier KPI summary for inbound appointments from %s to %s (ISO week %d).</p>',
    report.startYmd,
    report.endYmd,
    report.isoWeek
  );
  body += report.htmlTable;
  body += '<p>※ This email was sent by an automated weekly report built with Google Sheets Apps Script.</p>';

  const options = {
    htmlBody: body
  };

  const to = recipients.join(',');
  MailApp.sendEmail(to, subject, '', options);

  return {
    sentTo: recipients,
    subject: subject,
    carrierCount: report.carrierCount
  };
}

Email sending test: KPI_testSendWeeklyCarrierReport_

Finally, here’s a test function that validates the entire flow end‑to‑end.

  • Running it will send a real email.
  • On error, it logs FAIL and re‑throws immediately.
Apps Script (JavaScript)
function KPI_testSendWeeklyCarrierReport_() {
  try {
    const result = KPI_sendWeeklyCarrierReport();
    Logger.log('OK KPI_sendWeeklyCarrierReport: ' +
      JSON.stringify(result, null, 2));
  } catch (e) {
    Logger.log('FAIL KPI_sendWeeklyCarrierReport: ' + e);
    throw e;
  }
}

Practical tips: designing weeks, recipients, and KPIs

Key takeaways:

  1. Hard‑code what “last week” means in code.

With the approach here — “the ISO week (Mon–Sun) that contains the date 7 days before today” — you always look at the same range regardless of whether it’s Monday or Tuesday.

  1. Manage recipients in a sheet, not in code.

In logistics, roles and coverage change often. Being able to toggle recipients via ACTIVE in KPI_RECIPIENTS without code changes is much more practical.

  1. Keep outward-facing KPIs simple.

In this example, we limited the shared metrics to “loads, on‑time/late counts, on‑time %, score, and grade.” Internal reports can be richer, but external reports are easier to align on when they stick to a minimal, agreed‑upon set of indicators.

The helper functions KPI_ymdParts_, KPI_getWeekStart_, and KPI_getIsoWeekNo_ are reusable across outbound, inventory, yard turns, and any other weekly reports. Once you’ve validated this date and week logic, subsequent reports are just a matter of changing the aggregation logic.

Wrap-up: One thing to do today — send one test “last week” email

At this point, the core of “automatically email last week’s carrier KPI summary from Google Sheets” is complete. In production, you’d attach a time‑based trigger to this function so it runs automatically every Monday morning, making KPI sharing the default rather than a manual task.

There’s one concrete action you can take today:

In the Apps Script editor, run KPI_testSendWeeklyCarrierReport_() once and verify that an email for last week arrives correctly.

Visually cross‑check the date range and week number in the subject line, and confirm that totals in the HTML table look right. Once you’ve done that, you can safely add a time‑based trigger and hand weekly sending over to automation. From there, you can extend the same pattern to weekly reports for outbound, inventory, and other areas.

More in this category

Google Sheets bulk appointment booking: finish with one LockService and setValues call
2026-09-01 Excel & Automation

Google Sheets bulk appointment booking: finish with one LockService and setValues call

When you look for ways to bulk‑register appointments in Google Sheets, you often hit this: “validation is fast enough, but actually writing to the sheet is painfully slow.” In operations like inbound appointment booking, where dozens or hundreds of rows pile up daily, you really want to paste the carrier’s list once and process everything in one go. In the previous post…

Google Sheets bulk booking validation: check everything in one paste (final)
2026-08-31 Excel & Automation

Google Sheets bulk booking validation: check everything in one paste (final)

If you’re looking for a way to bulk‑validate bookings in Google Sheets, this post lays it out. When you paste multiple bookings into a sheet, you often burn time just figuring out which row is failing and why. Especially when you paste an inbound booking list copied from Excel: one row with a bad date format can block the whole batch…

How to Auto-Refresh a Google Sheets Dashboard: Refresh Multiple Views at Once and Automate with Triggers
2026-08-25 Excel & Automation

How to Auto-Refresh a Google Sheets Dashboard: Refresh Multiple Views at Once and Automate with Triggers

When you look up how to auto-refresh a Google Sheets dashboard, most guides stop at “make one function per view and attach a button.” As soon as you have three or more views—daily appointments, status overview, yard trailers—you end up with three buttons and three time triggers. The result: multiple time triggers stack on the same function, three functions run in parallel, and they keep hitting the 6‑minute Google Apps Script limit and stopping…

Google Sheets Yard Trailer Status Dashboard — Part 3 (Final)
2026-08-24 Excel & Automation

Google Sheets Yard Trailer Status Dashboard — Part 3 (Final)

If you’re looking for how to build a Google Sheets yard trailer status dashboard, this post walks through the setup. The inbound appointment system in this series connects reservations, check-in, and yard moves into a single flow. In the previous post - Automate Google Sheets appointment views: build a Today sheet — Dashboard Part 1…