Build a data-validation review panel

Bad rows hide well in a long sheet. Northwind’s clients tab had a few records with no email and a couple with a blank retainer, but they were scattered among hundreds of good rows — invisible until a mail merge bounced or an invoice came up short. Conditional formatting helps, but it still leaves you scrolling.

A review panel pulls every problem into one place. It scans the sheet, lists only the rows that fail a check, and shows each one with the exact issue spelled out and a link straight to the row. Instead of hunting, the person fixing data works down a short list.

What you’ll need

• A Google Sheet whose first tab is the clients data, with a header row that includes name, email, and monthlyRetainer columns.
• An HTML file named Validation in the Apps Script project, created with File > New > HTML file (a sample template is below).
• Edit access to the sheet — the panel runs as a bound script sidebar.

The script

// The spreadsheet whose first tab holds the client records.
const CLIENTS_SHEET_ID = '1abcClientsId';

/**
 * Builds the issue list and opens it in a sidebar panel.
 */
function showValidationPanel() {
  const template = HtmlService.createTemplateFromFile('Validation');

  // Expose the list of problem rows to the template.
  template.issues = findIssues();

  SpreadsheetApp.getUi()
    .showSidebar(template.evaluate().setTitle('Issues'));
}

/**
 * Scans the clients sheet and returns one entry per row that
 * fails a validation check. Clean rows are left out entirely.
 *
 * @return {Array<{row:number, problems:string[], name:string}>}
 */
function findIssues() {
  const [header, ...rows] = SpreadsheetApp.openById(CLIENTS_SHEET_ID)
    .getSheets()[0]
    .getDataRange()
    .getValues();

  // Guard: no data rows means nothing to review.
  if (!rows.length) return [];

  // Map each header name to its column index for readable lookups.
  const col = Object.fromEntries(header.map((key, i) => [key, i]));

  return rows
    .map((row, i) => {
      const problems = [];

      // Check 1: an email must be present and contain an "@".
      if (!row[col.email] || !String(row[col.email]).includes('@')) {
        problems.push('bad email');
      }

      // Check 2: the monthly retainer must not be blank.
      if (!row[col.monthlyRetainer]) {
        problems.push('missing retainer');
      }

      // Return an issue object, or null if the row is clean.
      // row + 2 converts a zero-based index back to a sheet row number.
      return problems.length
        ? { row: i + 2, problems, name: row[col.name] }
        : null;
    })
    // Drop the clean rows.
    .filter(Boolean);
}

The panel HTML (Validation.html)

<h3>Rows needing attention</h3>
<? if (!issues.length) { ?>
  <p>No issues found.</p>
<? } else { ?>
  <ul>
    <? for (const issue of issues) { ?>
      <li>
        <strong>Row <?= issue.row ?></strong> — <?= issue.name ?><br>
        <?= issue.problems.join(', ') ?>
      </li>
    <? } ?>
  </ul>
<? } ?>

How it works

1. showValidationPanel creates a template from Validation.html and attaches the issue list to it before rendering.
2. findIssues opens the clients sheet, splits off the header row, and bails out early if there is no data.
3. It builds a col map from header name to column index, so the checks read row[col.email] instead of a brittle numeric index.
4. For each row it runs two checks: the email must exist and contain an @, and the monthly retainer must not be blank. Each failure adds a short label.
5. A row with no failures maps to null; the trailing .filter(Boolean) drops those, so only problem rows survive.
6. Each surviving entry records the real sheet row number (i + 2), the list of problems, and the client name for context.
7. The template renders the list in the sidebar — or a “No issues found” message when the list is empty.

Example run

Given this clients tab:

name	email	monthlyRetainer
Atlas Foods	[email protected]	2400
Borealis	borealis.com	1800
Cresta Ltd	[email protected]

The panel lists two rows and skips the clean one:

Rows needing attention

• Row 3 — Borealis — bad email
• Row 4 — Cresta Ltd — missing retainer

The reviewer reads “bad email” on row 3, clicks into the sheet, and fixes borealis.com to a real address — no scrolling, no guessing.

Run it

This is an on-demand tool, opened when someone wants to clean the sheet:

1. Paste the script into the bound project and create the Validation HTML file alongside it.
2. Run showValidationPanel once from the editor and approve the authorisation prompt.
3. The panel opens beside the grid. To make it reusable, add an onOpen menu item that calls showValidationPanel so editors can open it themselves.

Watch out for

• The panel is a snapshot. It reads the sheet when it opens and does not refresh as you fix rows — close and reopen it to re-scan.
• The checks are hard-coded to two rules. Adding a rule means editing findIssues; there is no config sheet for non-developers to extend it.
• col lookups depend on exact header names. Rename email to Email Address and the check silently sees every row as missing an email.
• The email check is deliberately loose — an @ is not a valid address. Tighten the test only if your data warrants it; a strict regex rejects unusual but legal addresses.
• The sidebar lists rows but does not jump to them. Adding google.script.run click handlers to activate the row would make it a true click-to-fix panel.