Smart Life US
Google Sheets Late-Arrival Alert Auto Email: Inbound KPI 5
If an inbound truck is late and nobody notices, the on-site plan is immediately derailed. This post explains how to set up Google Sheets late-arrival alert auto emails, along with an email automation that prepares and sends a summary of tomorrow’s inbound appointments. Once you wire up late-arrival alert emails and a “tomorrow’s appointments” preview using just Google Sheets and Apps Script, the manager can glance at their inbox and instantly see which time windows require action.
This is Part 5 of a KPI/reporting series based on a Google Sheets inbound appointment system that is actually in use at a logistics center. In the previous four posts, we covered how to calculate daily KPIs, build nightly triggers to store history, score carriers, and send weekly reports by email automatically. In this part, we extend that setup to automatically notify you of “appointments that are problematic right now” and “appointments you need to prepare for tomorrow.”
Why late-arrival alerts and tomorrow previews matter
Once you start using an appointment system, there are moments when KPIs alone don’t directly help same-day operations. KPIs mostly tell you the results “up to yesterday.” On the floor, what really matters is which trucks are late as of now, and in which time slots tomorrow’s volume will be concentrated.
At the logistics center I manage, we initially focused only on on-time rate and average dwell time. But even a small slippage in inbound appointments would throw dock and labor schedules off all at once, and the manager usually found out too late to do anything meaningful. That’s why we needed a flow that sends an alert as close as possible to “the moment a delay becomes a real issue,” and that also summarizes tomorrow’s appointments in advance.
The key is not just coloring appointments as “on-time/late,” but comparing actual check-in records with scheduled times and picking out only those that require action at this moment. The code in this post is designed to do exactly that. As long as you adapt the check-in detection part to your own warehouse’s logs, you can apply everything else as-is.
Basic rules and sheet structure for alerts
In this part we reuse the sheets and functions already built in the previous posts, such as Google Sheets Weekly Report Auto Email: KPI Part 4, with Final Code (Post-Review Version). In particular, we use the main appointment sheet APPT_MAIN, the KPI history sheet KPI_HISTORY, and the KPI_RECIPIENTS sheet that lists alert recipients. The sheet structure is already fixed at the series level, so we simply assume that structure here.
When implementing late-arrival alerts and tomorrow’s appointment preview, we’ll follow these basic rules:
- Scheduled time vs. arrival detection
The scheduled time comes from the date/time columns in APPT_MAIN. Arrival detection uses the check-in logs you’re already recording. We add a helper function that answers “Was this appointment actually checked in?” In this post we only stub out a placeholder, KPI_isApptCheckedIn_(apptId); you’ll implement the real logic to match your check-in log structure.
- Explicit grace period
Taking traffic and other factors into account, we don’t treat an appointment as late the moment its scheduled time passes. Instead, we add a configurable grace period (30 minutes in the example), and only treat an appointment as late if scheduled time + grace period < now. In the code, this value lives as a constant; you can later change it to be read from a settings sheet.
- No repeat alerts for the same appointment
We dedicate one spare column in APPT_MAIN as a “late-alert sent” flag. When an email for that appointment is sent successfully, we store Y there. When we search for late appointments, we only consider rows where this flag is blank, ensuring that the same appointment doesn’t trigger multiple alert emails.
- One grouped email, not one email per appointment
Even if multiple trucks are late, we send a single email listing all late appointments in time order. Likewise, tomorrow’s preview is a single email summarizing all of tomorrow’s appointments. If emails arrive too frequently, people on the floor will ignore them; the goal is “one concise digest when it’s needed”, not a flood of messages.
Step 1 — Define configuration constants for alerts
First, we define a configuration object just for this part. Following the series convention, we use the KPI_ prefix and avoid colliding with any existing CONFIG-style constants. It will hold values like grace period length, sheet names, email subjects, and flag column indexes.
- What this code does
- Centralizes all alert-related constants: timezone, sheet names, grace period, email subjects, and indicator column numbers.
- Where to paste it
- In Google Sheets: Extensions → Apps Script → in the existing KPI project’s
Code.gs, near the top, right below your otherKPI_constants.
- What to do after pasting
- Save (⌘S / CTRL+S). No need to run anything yet; later functions will reference these constants.
const KPI_ALERT_CONFIG = { // → Settings dedicated to KPI alerts
TIMEZONE: 'America/New_York', // → Common system timezone
LATE_GRACE_MINUTES: 30, // → Grace period (minutes) before treating as late
MAIN_SHEET_NAME: 'APPT_MAIN', // → Main appointment sheet
RECIPIENT_SHEET_NAME: 'KPI_RECIPIENTS', // → Recipient list sheet
LATE_ALERT_SUBJECT: '[Inbound] Late Appointment Alert', // → Subject line for late-alert emails
TOMORROW_PREVIEW_SUBJECT: '[Inbound] Tomorrow Appointment Preview', // → Subject line for preview emails
LATE_ALERT_SENT_COLUMN: 14, // → Column index in APPT_MAIN for "late alert sent" flag (example)
ID_COLUMN: 13 // → APPT_MAIN appointment ID column index (fixed: column M)
}; //How to check it: if there’s no red error marker on this constant definition in the Apps Script editor, it’s valid. Also confirm that column 14 in APPT_MAIN is actually free to use as the late-alert flag.
Step 2 — Load recipients and add a common email helper
Both late-arrival alerts and tomorrow’s preview emails go to the people listed in KPI_RECIPIENTS. We assume this sheet has at least three columns: email, name, and active flag.
- What this code does
- Reads email addresses and active status from
KPI_RECIPIENTSand returns an array of valid recipients. - Wraps Gmail sending in a helper so you can send an email with just a subject and body.
- Where to paste it
- Directly below
KPI_ALERT_CONFIG.
- What to do after pasting
- Later, use a small test function to call
KPI_loadAlertRecipients_()and inspect the log result.
function KPI_loadAlertRecipients_() { // → Load alert recipients
const ss = SpreadsheetApp.getActive(); // → Current spreadsheet
const sheet = ss.getSheetByName(KPI_ALERT_CONFIG.RECIPIENT_SHEET_NAME); // → Recipient sheet
if (!sheet) { // → Sheet missing
throw new Error('KPI_RECIPIENTS sheet not found.'); // → Throw error
}
const lastRow = sheet.getLastRow(); // → Last row
if (lastRow < 2) { // → No data
return []; // → Return empty array
}
const range = sheet.getRange(2, 1, lastRow - 1, 3); // → Data range (excluding header)
const values = range.getValues(); // → Read values
const recipients = []; // → Result array
values.forEach((row, idx) => { // → Iterate rows
const email = String(row[0] || '').trim(); // → Email
const name = String(row[1] || '').trim(); // → Name (optional)
const active = String(row[2] || '').trim().toUpperCase();// → Active flag
if (!email) { // → No email
return; // → Skip
}
if (active !== 'Y' && active !== 'YES' && active !== '1') { // → Inactive
return; // → Skip
}
recipients.push({ email, name: name || email }); // → Add recipient
});
return recipients; // → Return array
}
function KPI_sendEmail_(subject, body) { // → Email sending helper
const recipients = KPI_loadAlertRecipients_(); // → Load recipients
if (!recipients.length) { // → No one to send to
return { sent: 0, message: 'No active alert recipients.' }; // → Return status
}
const toList = recipients.map(r => r.email).join(','); // → Address list
GmailApp.sendEmail(toList, subject, body, { // → Send email
name: 'Inbound Appointment Alert Bot' // → Sender name
});
return { sent: recipients.length, message: 'OK' }; // → Send result
} How to check it: put a test email and Y in KPI_RECIPIENTS, then in a test function call KPI_loadAlertRecipients_() and check that the returned array has length ≥ 1 in the logs.
Step 3 — Core logic to find late appointments
Now we need a function that picks out “appointments which, as of now, are late, have no check-in record, and have not yet been flagged as alerted.” This function is the core logic used by the main late-alert sender.
In a real system you’ll typically have a CHECKIN_LOG or similar sheet and you’ll look up the appointment ID to determine if it has checked in. Here we leave a placeholder KPI_isApptCheckedIn_(apptId) that always returns false as a stub; you’ll replace this with your own logic.
- What this code does
- Reads date/time, appointment ID, and late-alert flag from
APPT_MAIN. - Returns an array of rows where
scheduled + grace < now, not checked in, and flag is blank.
- Where to paste it
- Directly below the code from the previous step.
- What to do after pasting
- Later, run a test function
KPI_testFindLateAppts_()and inspect the log output.
function KPI_isApptCheckedIn_(apptId) { // → Determine if appointment is checked in (example)
if (!apptId) { // → No appointment ID
return false; // → Treat as not checked in
}
// In the real implementation, look up the appointment ID in CHECKIN_LOG or similar. // → Needs log lookup
return false; // → Example: always treated as not checked in
}
function KPI_findLateAppts_() { // → Get list of late appointments
const ss = SpreadsheetApp.getActive(); // → Current workbook
const sheet = ss.getSheetByName(KPI_ALERT_CONFIG.MAIN_SHEET_NAME); // → Appointment sheet
if (!sheet) { // → Sheet missing
throw new Error('APPT_MAIN sheet not found.'); // → Error
}
const lastRow = sheet.getLastRow(); // → Last row
if (lastRow < 2) { // → No data
return []; // → Empty list
}
const now = new Date(); // → Current time
const values = sheet.getRange(2, 1, lastRow - 1, KPI_ALERT_CONFIG.LATE_ALERT_SENT_COLUMN).getValues(); // → Data
const lateAppts = []; // → Result array
values.forEach((row, idx) => { // → Iterate rows
const rowIndex = idx + 2; // → Actual row index
const dateVal = row[0]; // → A: Date
const timeVal = row[1]; // → B: Start time
const type = String(row[2] || '').trim(); // → C: Equipment type
const door = String(row[3] || '').trim(); // → D: Door
const container = String(row[4] || '').trim(); // → E: Container
const carrier = String(row[5] || '').trim(); // → F: Carrier
const client = String(row[6] || '').trim(); // → G: Customer
const remark = String(row[7] || '').trim(); // → H: Remark
const endTimeVal = row[8]; // → I: End time
const createdAt = row[9]; // → J: Created timestamp
const qty = row[10]; // → K: Quantity
const pallet = row[11]; // → L: Pallet
const apptId = String(row[12] || '').trim(); // → M: Appointment ID
const alertSentFlag = row[13]; // → N (example): Late-alert flag
if (!dateVal || !timeVal || !apptId) { // → Missing required values
return; // → Skip
}
if (alertSentFlag === 'Y') { // → Already alerted
return; // → Skip
}
if (KPI_isApptCheckedIn_(apptId)) { // → Already checked in
return; // → Skip
}
const dateObj = new Date(dateVal); // → Date object
const timeObj = new Date(timeVal); // → Time object
if (isNaN(dateObj.getTime()) || isNaN(timeObj.getTime())) { // → Invalid values
return; // → Skip
}
const scheduled = new Date(dateObj); // → Scheduled datetime
scheduled.setHours(timeObj.getHours(), timeObj.getMinutes(), 0, 0); // → Apply time
const graceMillis = KPI_ALERT_CONFIG.LATE_GRACE_MINUTES * 60 * 1000; // → Grace in ms
const deadline = new Date(scheduled.getTime() + graceMillis); // → Late threshold
if (deadline.getTime() >= now.getTime()) { // → Still within grace
return; // → Skip
}
lateAppts.push({ // → Add late appointment
rowIndex,
apptId,
date: dateObj,
time: timeObj,
type,
door,
container,
carrier,
client,
remark,
qty,
pallet
});
});
return lateAppts; // → Return list of late appointments
} How to check it: add a test row in APPT_MAIN with a scheduled time 1–2 hours in the past, then run KPI_testFindLateAppts_() (a small wrapper you write) and confirm in the logs that at least one late appointment is detected.
Step 4 — Build and send the late-arrival alert email
Now we implement sendLateAlerts(), the function that actually sends the late-arrival email. It gathers all late appointments, sends a single grouped email, and on success, flags each corresponding row as “alert sent.” We also use LockService to prevent duplicate sends if multiple users trigger this at the same time.
- What this code does
- Sorts late appointments by time, builds a text-table email body, and sends a single email.
- Marks the flag column with
Yonly if the email send succeeds.
- Where to paste it
- Directly below
KPI_findLateAppts_().
- What to do after pasting
- Write and run a
KPI_testSendLateAlerts_()test function that callssendLateAlerts()and inspects the result.
function sendLateAlerts() { // → Main function for late-arrival alerts
const lock = LockService.getScriptLock(); // → Script-wide lock
lock.waitLock(30000); // → Wait up to 30 seconds
try { // → Execute under lock
const lateAppts = KPI_findLateAppts_(); // → Fetch late appointments
if (!lateAppts.length) { // → None found
return { sent: 0, message: 'No late appointments.' }; // → Exit
}
lateAppts.sort((a, b) => { // → Sort by time
const t1 = a.date.getTime() + // → Base on a’s date
(a.time.getHours() * 60 + a.time.getMinutes()) * 60000; // → Add time in minutes
const t2 = b.date.getTime() + // → Base on b’s date
(b.time.getHours() * 60 + b.time.getMinutes()) * 60000; //
return t1 - t2; // → Ascending
});
let bodyLines = []; // → Email body lines
bodyLines.push('The following inbound appointments have exceeded the grace period and have not yet arrived.'); // → Intro text
bodyLines.push(''); // → Blank line
bodyLines.push('Date\tTime\tCarrier\tContainer\tDoor\tCustomer\tType\tQty\tPallets\tRemark'); // → Header
lateAppts.forEach(item => { // → Add each appointment row
const y = Utilities.formatDate(item.date, KPI_ALERT_CONFIG.TIMEZONE, 'yyyy-MM-dd'); // → Date string
const hm = Utilities.formatDate(item.time, KPI_ALERT_CONFIG.TIMEZONE, 'HH:mm'); // → Time string
bodyLines.push([
y,
hm,
item.carrier || '',
item.container || '',
item.door || '',
item.client || '',
item.type || '',
item.qty || '',
item.pallet || '',
item.remark || ''
].join('\t')); // → Tab-separated
});
bodyLines.push(''); // → Blank line
bodyLines.push('※ This email was sent by the Google Sheets inbound appointment KPI automation.'); // → Footer
const subject = KPI_ALERT_CONFIG.LATE_ALERT_SUBJECT; // → Email subject
const body = bodyLines.join('\n'); // → Body as string
const sendResult = KPI_sendEmail_(subject, body); // → Send email
if (sendResult.sent > 0) { // → If send succeeded
const ss = SpreadsheetApp.getActive(); // → Current spreadsheet
const sheet = ss.getSheetByName(KPI_ALERT_CONFIG.MAIN_SHEET_NAME); // → Appointment sheet
lateAppts.forEach(item => { // → Mark each appointment
sheet.getRange(item.rowIndex, KPI_ALERT_CONFIG.LATE_ALERT_SENT_COLUMN).setValue('Y'); // → Set alert flag
});
}
return { // → Return summary
sent: sendResult.sent,
lateCount: lateAppts.length,
message: sendResult.message
};
} finally {
lock.releaseLock(); // → Release lock
}
} How to check it: create a test appointment in the past, run sendLateAlerts(), and confirm that (1) you receive a single email, (2) the late-alert flag column for that row is set to Y, and (3) running sendLateAlerts() again with the same data sends no additional email.
Step 5 — Automate tomorrow’s appointment preview email
If late-arrival alerts tell you “which appointments are currently problematic,” tomorrow’s preview gives you “which appointments you need to prepare for tomorrow.” This function filters APPT_MAIN for appointments on tomorrow’s date, sorts them by time, and builds a compact list including carrier, container, and door.
- What this code does
- Filters for appointments on tomorrow’s date, sorts them by time, and sends a single grouped email.
- Sends nothing if there are no appointments tomorrow.
- Where to paste it
- Directly below
sendLateAlerts().
- What to do after pasting
- Add a couple of test rows for tomorrow in
APPT_MAIN, runsendTomorrowPreview(), and check the resulting email.
function sendTomorrowPreview() { // → Main function for tomorrow’s appointment preview
const ss = SpreadsheetApp.getActive(); // → Current spreadsheet
const sheet = ss.getSheetByName(KPI_ALERT_CONFIG.MAIN_SHEET_NAME); // → Appointment sheet
if (!sheet) { // → Missing sheet
throw new Error('APPT_MAIN sheet not found.'); // → Error
}
const lastRow = sheet.getLastRow(); // → Last row
if (lastRow < 2) { // → No data
return { sent: 0, message: 'No appointment data.' }; // → Exit
}
const now = new Date(); // → Current time
const tz = KPI_ALERT_CONFIG.TIMEZONE; // → Timezone
const tomorrow = new Date(now); // → Copy today
tomorrow.setDate(tomorrow.getDate() + 1); // → Move to tomorrow
const tomorrowYmd = Utilities.formatDate(tomorrow, tz, 'yyyy-MM-dd'); // → Tomorrow as yyyy-MM-dd
const values = sheet.getRange(2, 1, lastRow - 1, KPI_ALERT_CONFIG.ID_COLUMN).getValues(); // → A..M (date..ID)
const appts = []; // → Tomorrow appointments
values.forEach((row, idx) => { // → Iterate rows
const rowIndex = idx + 2; // → Actual row index
const dateVal = row[0]; // → A: Date
const timeVal = row[1]; // → B: Start time
const type = String(row[2] || '').trim(); // → C: Equipment type
const door = String(row[3] || '').trim(); // → D: Door
const container = String(row[4] || '').trim(); // → E: Container
const carrier = String(row[5] || '').trim(); // → F: Carrier
const client = String(row[6] || '').trim(); // → G: Customer
const remark = String(row[7] || '').trim(); // → H: Remark
const apptId = String(row[12] || '').trim(); // → M: Appointment ID
if (!dateVal || !timeVal || !apptId) { // → Missing required values
return; // → Skip
}
const dateObj = new Date(dateVal); // → Date object
if (isNaN(dateObj.getTime())) { // → Invalid date
return; // → Skip
}
const ymd = Utilities.formatDate(dateObj, tz, 'yyyy-MM-dd'); // → yyyy-MM-dd string
if (ymd !== tomorrowYmd) { // → Not tomorrow
return; // → Skip
}
const timeObj = new Date(timeVal); // → Time object
if (isNaN(timeObj.getTime())) { // → Invalid time
return; // → Skip
}
appts.push({ // → Add tomorrow appointment
rowIndex,
apptId,
date: dateObj,
time: timeObj,
type,
door,
container,
carrier,
client,
remark
});
});
if (!appts.length) { // → No appointments tomorrow
return { sent: 0, message: 'No appointments tomorrow.' }; // → Exit
}
appts.sort((a, b) => { // → Sort by time
const t1 = a.time.getHours() * 60 + a.time.getMinutes(); // → a in minutes
const t2 = b.time.getHours() * 60 + b.time.getMinutes(); // → b in minutes
return t1 - t2; // → Ascending
});
let bodyLines = []; // → Email body lines
bodyLines.push('Here is tomorrow’s inbound appointment schedule, sorted by time.'); // → Intro
bodyLines.push(''); // → Blank line
bodyLines.push('Time\tCarrier\tContainer\tDoor\tCustomer\tType\tRemark'); // → Header
appts.forEach(item => { // → Add each row
const hm = Utilities.formatDate(item.time, tz, 'HH:mm'); // → Time string
bodyLines.push([
hm,
item.carrier || '',
item.container || '',
item.door || '',
item.client || '',
item.type || '',
item.remark || ''
].join('\t')); // → Tab-separated
});
bodyLines.push(''); // → Blank line
bodyLines.push('※ This email was sent by the Google Sheets inbound appointment KPI automation.'); // → Footer
const subject = KPI_ALERT_CONFIG.TOMORROW_PREVIEW_SUBJECT; // → Subject
const body = bodyLines.join('\n'); // → Body
const sendResult = KPI_sendEmail_(subject, body); // → Send email
return { // → Return summary
sent: sendResult.sent,
apptCount: appts.length,
message: sendResult.message
};
} How to check it: create a couple of test appointments dated for tomorrow, run sendTomorrowPreview(), and confirm that you receive a single email listing tomorrow’s appointments by time. If there are no appointments tomorrow, no email should be sent.
Practical tips: triggers, testing, and day-to-day use
The code is ready, but production use depends heavily on how you set triggers and test. Based on real-world operation experience, consider these points:
- Be intentional about trigger frequency and timing.
- Late alerts: if you trigger
sendLateAlerts()too frequently, you get noise; too rarely, you find out too late. A 30-minute or 1-hour interval worked well in practice. - Tomorrow preview: once a day is enough. Schedule
sendTomorrowPreview()to run just before headcount or dock-planning meetings (for example, at 3:00 PM).
- Tune
LATE_GRACE_MINUTESto your operation.
Start with 30 minutes, then adjust for specific routes/customers as you observe patterns. If certain lanes constantly trigger “false alarms,” you might either extend the grace period for that lane or renegotiate the planned time slot.
- Keep the recipient list focused.
Rather than listing everyone in KPI_RECIPIENTS, keep it to people who can actually act on the information: dock assignment, gate operations, and labor scheduling. If needed, they can re-share the content in team channels. The more people receive an email, the more likely it is to be ignored.
- Always test with minimal data first.
- For late alerts: create one or two test appointments 1–2 hours in the past, set yourself as the sole active recipient, and manually run
sendLateAlerts(). - For tomorrow’s preview: create two or three test appointments for tomorrow and manually run
sendTomorrowPreview(). Only after validating the format and behavior should you attach time-based triggers.
Once these pieces are in place, and combined with the earlier parts of the series (daily KPIs, history, carrier reports, weekly summaries), you’ll have a complete inbound appointment alert automation in Google Sheets. KPIs cover “results up to yesterday,” while late alerts and tomorrow previews cover “what to act on now and tomorrow.” That way, you can prioritize operations from your inbox and KPI dashboard, without constantly opening and searching through the raw sheets.
Conclusion
Late-arrival alerts and tomorrow’s appointment preview are essentially the final pieces of the inbound appointment KPI automation puzzle. With just Google Sheets and Apps Script you can implement both automatic late-arrival alert emails and tomorrow’s appointment preview emails, so that managers can see today’s and tomorrow’s risk windows straight from their inbox instead of hunting through spreadsheets.
If you want to try this right away, do this one thing today:
Add your own email address as a row in KPI_RECIPIENTS, paste in the KPI_ALERT_CONFIG, KPI_loadAlertRecipients_(), KPI_findLateAppts_(), and sendLateAlerts() code from this post, then create a single test appointment in the past and run sendLateAlerts() manually.
Once you experience that first real alert email coming in, tuning the grace period and adding time-based triggers will feel much more straightforward.
