Alert the owner when imported data breaks a rule

Northwind pulls invoice data into a Sheet on a schedule — an export from the accounting tool lands in the Invoices tab every morning. The import itself rarely fails outright. What fails quietly is the data inside it: a row missing its amount, a date stamped in the future because of a timezone mix-up, a blank vendor where a join went wrong upstream.

Nobody notices until a report looks odd a week later, and by then the bad rows have been copied into three downstream Sheets. This script runs straight after the import, checks every row against a few plain rules, and emails the data owner the moment something breaks. A silent broken pipeline becomes a message in an inbox the same morning.

What you’ll need

• A Google Sheet with the imported data on the first tab. The header row must include amount, issuedAt, and vendor — the script looks columns up by name, so their order does not matter.
• The data owner’s email address — whoever should be told when the import produces bad rows.
• The import job itself already running on a schedule. This script is the validation step that runs after it.

The script

// The spreadsheet the import writes into.
const INVOICES = '1abcInvoicesSheetId';

// Who gets told when a row fails validation.
const OWNER = '[email protected]';

/**
 * Reads the imported invoice rows, checks each one against a few
 * validation rules, and emails the owner if any row breaks a rule.
 * Designed to run straight after the import job.
 */
function validateAfterImport() {
  // 1. Read the whole tab and split the header from the data rows.
  const [h, ...rows] = SpreadsheetApp.openById(INVOICES).getSheets()[0]
    .getDataRange().getValues();

  // Nothing imported yet — no rows to check, so stop here.
  if (!rows.length) {
    Logger.log('No data rows to validate — nothing to do.');
    return;
  }

  // 2. Build a header-name -> column-index map so rules read by name.
  const col = Object.fromEntries(h.map((k, i) => [k, i]));
  const now = new Date();

  // 3. Check every row. For each, collect a list of issues; keep
  //    only the rows that actually have one.
  const problems = rows.map((r, i) => {
    const issues = [];
    // amount must be present and a number.
    if (!r[col.amount] || isNaN(r[col.amount])) issues.push('missing/invalid amount');
    // An invoice dated in the future usually means a timezone bug.
    if (r[col.issuedAt] instanceof Date && r[col.issuedAt] > now) issues.push('future date');
    // A blank vendor usually means a broken join upstream.
    if (!r[col.vendor]) issues.push('no vendor');
    // Row 1 is the header, so the first data row is sheet row 2.
    return issues.length ? { row: i + 2, issues } : null;
  }).filter(Boolean);

  // 4. Clean import — say nothing. No news is good news.
  if (problems.length === 0) {
    Logger.log('Import validated cleanly — no issues found.');
    return;
  }

  // 5. One row per problem, then a single email to the owner.
  const body = problems.map((p) => `Row ${p.row}: ${p.issues.join(', ')}`).join('\n');
  GmailApp.sendEmail(OWNER, `Invoice import: ${problems.length} issue(s)`, body);
  Logger.log('Alerted owner about ' + problems.length + ' problem row(s).');
}

How it works

1. validateAfterImport opens the invoices spreadsheet and reads the first tab, splitting the header row away from the data rows.
2. If there are no data rows, it logs a message and stops — the import has not produced anything to check.
3. It builds a col map of header name to column index, so the rules can read r[col.amount] instead of relying on a fixed column order.
4. It walks every row and collects a list of issues: a missing or non-numeric amount, an issuedAt date in the future, or a blank vendor. Rows with no issues are dropped.
5. If every row passed, it logs that fact and sends nothing — you only hear from this script when something is wrong.
6. Otherwise it builds a plain-text body, one line per problem row, and sends a single summary email to the owner.

Example run

Say the morning import lands these rows in Invoices:

Row	vendor	amount	issuedAt
2	Acme Supplies	420.00	2026-05-22
3	Brightline		2026-05-22
4		180.00	2099-01-01
5	Castle Print	95.50	2026-05-23

Rows 2 and 5 are clean. Rows 3 and 4 break rules, so the owner gets one email:

Subject: Invoice import: 2 issue(s)

Row 3: missing/invalid amount
Row 4: no vendor, future date

Trigger it

Run this immediately after the import so the owner hears about bad data the same morning:

• If the import is itself an Apps Script function, call validateAfterImport() as its last line.
• If the import is a separate job, add a time-driven trigger a few minutes after it: in the Apps Script editor open Triggers, click Add Trigger, choose validateAfterImport, and set a daily timer to fire shortly after the import window.

Watch out for

• The rules are deliberately simple. They catch the obvious breakage — blanks, bad numbers, impossible dates — not subtle errors like a duplicated row or a vendor name that has been misspelt. Add rules to the issues list as you learn what your import gets wrong.
• It reports row numbers as they sit in the sheet at the time of the run. If the import reorders or appends rows before someone reads the email, a row number may point somewhere new.
• It emails on every failed run. If an upstream system breaks for a week you will get seven emails — fine as a nudge, but pair it with fixing the source rather than muting the alert.
• getDataRange reads the whole tab into memory. For tens of thousands of rows this is still fine, but a very large sheet will run slower.