Build a context-aware AI data validator

Spreadsheet validation usually catches the obvious mistakes — a blank cell, text where a number should be, a date in the wrong format. What it cannot catch is a value that is perfectly well-formed but wrong in context. A £10 monthly retainer for a Northwind client is a valid number and a valid currency, yet for a London design-and-dev agency it almost certainly means someone dropped three zeros.

This script adds that missing layer of judgement. It sends each row of the clients sheet to Claude along with a short description of what Northwind is and what its numbers normally look like, and asks whether the row is plausible. Anything that smells wrong gets highlighted and annotated, so a human reviews a handful of suspicious rows instead of squinting at every cell.

What you’ll need

• A Google Sheet of client records, with a header row and one client per row (for example client, retainer, startDate, contact).
• A spare column to the right of the data for the validator’s notes — the script writes into the first column after the last header.
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.
• The spreadsheet ID, copied from the sheet’s URL.

The script

// The spreadsheet that holds your client records.
const CLIENTS_SHEET_ID = '1abcClientsId';

// Background Claude needs to judge whether a row is plausible. Tighten
// this to match your own business and the values will be judged better.
const BUSINESS_CONTEXT =
  'Northwind context (small London design+dev agency, ' +
  'retainers £2-20k/month).';

// Colour used to highlight a row the validator finds suspicious.
const FLAG_COLOUR = '#fff4d6';

/**
 * Asks Claude whether a single client row is plausible for Northwind.
 *
 * @param {Object} row - The row as a {column: value} object.
 * @return {string} "ok" or "suspicious: <reason>".
 */
function validateRow(row) {
  const prompt =
    'For ' + BUSINESS_CONTEXT + ' Is this row plausible? ' +
    'Reply "ok" or "suspicious: <reason>".\n\n' +
    JSON.stringify(row);
  return callClaude(prompt);
}

/**
 * Walks the clients sheet, validates each row, and highlights plus annotates
 * any row Claude flags as suspicious.
 */
function validateClientsSheet() {
  const sheet = SpreadsheetApp.openById(CLIENTS_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet and split off the header row.
  const [header, ...rows] = sheet.getDataRange().getValues();
  if (!rows.length) {
    Logger.log('No client rows to validate — nothing to do.');
    return;
  }

  // 2. Check each row in turn.
  let flagged = 0;
  rows.forEach((row, i) => {
    // Turn the flat row array into a {column: value} object so the
    // prompt carries meaningful field names.
    const obj = Object.fromEntries(header.map((name, j) => [name, row[j]]));
    const verdict = validateRow(obj);

    // 3. Only act on suspicious rows: highlight the data and write the
    //    reason into the note column just past the last header.
    if (verdict.startsWith('suspicious')) {
      sheet.getRange(i + 2, 1, 1, header.length).setBackground(FLAG_COLOUR);
      sheet.getRange(i + 2, header.length + 1).setValue(verdict);
      flagged++;
    }
  });

  Logger.log('Validated ' + rows.length + ' row(s), flagged ' + flagged + '.');
}

/**
 * Minimal Anthropic API call. The key lives in Script Properties — it is
 * never pasted into the code.
 */
function callClaude(prompt) {
  const key = PropertiesService.getScriptProperties()
    .getProperty('ANTHROPIC_API_KEY');
  const res = UrlFetchApp.fetch('https://api.anthropic.com/v1/messages', {
    method: 'post',
    contentType: 'application/json',
    headers: { 'x-api-key': key, 'anthropic-version': '2023-06-01' },
    payload: JSON.stringify({
      model: 'claude-haiku-4-5-20251001',
      max_tokens: 100,
      messages: [{ role: 'user', content: prompt }],
    }),
    muteHttpExceptions: true,
  });
  return JSON.parse(res.getContentText()).content[0].text.trim();
}

How it works

1. validateClientsSheet opens the clients spreadsheet and reads the whole data range in one call, then splits off the header.
2. If there are no data rows it logs a message and stops — no wasted API calls.
3. For each row it rebuilds the flat array into a {column: value} object, so the prompt sends Claude meaningful field names rather than bare values.
4. validateRow wraps that object in a prompt that states the BUSINESS_CONTEXT — what Northwind is and what its retainers normally cost — and asks for a one-line verdict: ok, or suspicious: with a reason.
5. When the verdict starts with suspicious, the script paints the whole row in FLAG_COLOUR and writes the reason into the first column past the last header.
6. Rows judged ok are left untouched, so the highlight column stays clean and only the rows worth a second look stand out.
7. callClaude is a small wrapper around the Anthropic Messages API; the key comes from Script Properties, never the code.

Example run

Say the clients sheet holds:

client	retainer	startDate	contact
Harbour Coffee	£6,000	2025-01-12	[email protected]
Meridian Books	£10	2025-03-01	[email protected]
Glasshouse Studio	£14,500	2024-11-04	[email protected]

After a run, only the middle row is highlighted, with a note appended:

client	retainer	startDate	contact	(note)
Harbour Coffee	£6,000	2025-01-12	[email protected]
Meridian Books	£10	2025-03-01	[email protected]	suspicious: £10 retainer far below the £2-20k range
Glasshouse Studio	£14,500	2024-11-04	[email protected]

The reviewer opens the sheet, sees one highlighted row, and fixes the obvious typo — instead of scanning every retainer by eye.

Run it

This is a spot-check you run when data has changed, not a constant background job:

1. In the Apps Script editor, select validateClientsSheet and click Run.
2. Approve the authorisation prompt the first time.
3. Open the sheet and review the highlighted rows.

To validate after every bulk import, attach validateClientsSheet to a Time-driven daily trigger, or add a custom menu so editors can run it on demand.

Watch out for

• This is a judgement aid, not a rule engine. Claude can flag a legitimate outlier or miss a subtle error — always treat a flag as “look closer”, never as proof.
• The quality of the verdicts depends entirely on BUSINESS_CONTEXT. The vaguer the description, the more false positives you will see; spell out the normal ranges for the numbers that matter.
• Every row is one API call. Apps Script caps execution at six minutes, so a sheet of several hundred rows may not finish in one pass — validate in batches or only re-check changed rows.
• Re-running re-validates every row and re-highlights flags. It does not clear old highlights, so an error you have since fixed stays painted until you clear the formatting and note cell by hand.
• The note is written one column past the last header. If your sheet already has data there, point header.length + 1 at a genuinely empty column.