Build a translation QA checker with AI

Northwind ships copy in more than one language, and translation is the part nobody on the team can check. The German column looks plausible, the freelancer is reputable — but “looks plausible” is not the same as “means the same thing”, and a subtle drift in a price or a promise is exactly the kind of mistake that surfaces in front of a customer.

This script gives every translated row a sanity check using a classic technique: back-translation. It translates the German back to English, literally, then asks Claude whether the round-trip still means what the original said. The verdict lands in a qa column — “yes” for the rows you can trust, and “no” with a reason for the rows that need a human.

What you’ll need

• A Google Sheet with the source text in a column headed en, the translation in a column headed de, and an empty qa column for the result.
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.

The script

// The spreadsheet that holds source text and translations.
const TRANSLATIONS_SHEET_ID = '1abcTranslationsId';

/**
 * Walks the translations sheet, back-translates each German entry,
 * and writes a semantic-equivalence verdict into the qa column.
 */
function qaTranslations() {
  const sheet = SpreadsheetApp.openById(TRANSLATIONS_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet and split off the header row.
  const [h, ...rows] = sheet.getDataRange().getValues();

  if (!rows.length) {
    Logger.log('No translations to check — nothing to do.');
    return;
  }

  // 2. Map header names to column indexes so column order is flexible.
  const col = Object.fromEntries(h.map((k, i) => [k, i]));

  // 3. Walk each row, skipping ones already checked or with no translation.
  rows.forEach((r, i) => {
    if (r[col.qa] || !r[col.de]) return;

    // 4. Translate the German back to English, as literally as possible.
    const back = callClaude(
      'Translate back to English literally: ' + r[col.de]);

    // 5. Ask Claude whether the round-trip kept the original meaning.
    const verdict = callClaude(
      'Are these two sentences semantically equivalent? ' +
      'Return "yes" or "no: <reason>".\n\n' +
      'Original: ' + r[col.en] + '\n' +
      'Back-translated: ' + back);

    sheet.getRange(i + 2, col.qa + 1).setValue(verdict);
  });
  Logger.log('Translation QA pass complete.');
}

/**
 * Minimal Anthropic API call. Haiku handles both the back-translation
 * and the comparison, and the key lives in Script Properties — never
 * in 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: 200,
      messages: [{ role: 'user', content: prompt }],
    }),
    muteHttpExceptions: true,
  });
  return JSON.parse(res.getContentText()).content[0].text.trim();
}

How it works

1. qaTranslations opens the translations spreadsheet and reads the full data range, splitting the header row from the data.
2. If there are no data rows, it logs a message and stops.
3. It builds a name-to-index map from the header so the script keeps working even if columns are reordered.
4. It walks each row, skipping any that already have a verdict or have no German text — so a re-run only checks new rows and never burns API calls twice.
5. For each remaining row it makes the first call: a literal back-translation of the German into English.
6. It makes a second call comparing the original English with the back-translation, asking for "yes" or "no: <reason>", and writes that verdict into the qa column. Two calls per row is the cost of catching meaning drift that a single pass would miss.

Example run

Say the translations sheet holds these rows:

en	de	qa
Free delivery on orders over £50.	Kostenlose Lieferung ab 50 £.
Cancel any time, no fee.	Jederzeit kündbar, geringe Gebühr.

After a run, the qa column fills in:

en	qa
Free delivery on orders over £50.	yes
Cancel any time, no fee.	no: the German says “low fee”, not “no fee”

The first row checks out; the second flags a real error — “geringe Gebühr” means a small fee, not no fee at all — and points straight at the problem.

Run it

This is an on-demand job — run it whenever a batch of translations comes back:

1. In the Apps Script editor, select qaTranslations and click Run.
2. Approve the authorisation prompt the first time.
3. Filter the qa column for anything starting with no: and send those rows back to the translator.

To let reviewers run it themselves, add a custom menu to the sheet:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Translation tools')
    .addItem('QA translations', 'qaTranslations')
    .addToUi();
}

Watch out for

• Back-translation catches meaning drift, not style. A translation can be semantically fine and still read awkwardly to a native speaker — this is a safety net, not a substitute for a proper review of important copy.
• It uses two API calls per row. That doubles the cost and the run time against a single-pass check — fine for accuracy-critical copy, worth knowing for a long sheet.
• A literal back-translation can look clumsy even when the original is good. Judge the verdict, not the prose of the back-translation itself.
• The script assumes a de column. To QA another language, change the column name and tell the back-translation prompt which language it is reading.