Translate a column of text with context

A machine translation that swaps words one at a time reads like a machine translation. When Northwind localises its marketing copy, the goal is not a literal German or French rendering — it is text that sounds like Northwind wrote it in that language: friendly, direct, and on-brand. Idioms need to land, product names need to stay untouched, and the tone has to survive the trip.

This script keeps a translation memory in a single Sheet. The English source sits in one column and each target language gets its own column. The script walks the rows, and for every cell that is still blank it asks Claude to translate the English with full context — who Northwind is and how it speaks — then writes the result back. Already-filled cells are skipped, so a run only pays for the new strings.

What you’ll need

• A Google Sheet with one row per string to translate. Row 1 holds headers: an en column for the English source, plus one column per target language named by its language code (de, fr, es, and so on).
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.
• The target-language column must already exist as a header before you run the script for that language.

The script

// The spreadsheet that holds your translation memory.
const COPY_SHEET_ID = '1abcCopyId';

// Who Northwind is, in one line — this anchors the tone of every translation.
const BRAND_VOICE = 'we are Northwind Studios, friendly and direct';

/**
 * Translates the English column into a target language, filling only the
 * blank cells in that language's column.
 *
 * @param {string} targetLang A language-code header, e.g. "de" or "fr".
 */
function translateColumn(targetLang = 'de') {
  const sheet = SpreadsheetApp.openById(COPY_SHEET_ID).getSheets()[0];

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

  // 2. Build a name-to-index map so columns can be addressed by header.
  const col = Object.fromEntries(h.map((k, i) => [k, i]));
  const tgtCol = h.indexOf(targetLang);

  // 3. Bail out early if the target-language column does not exist.
  if (tgtCol === -1) {
    Logger.log('No column named "' + targetLang + '" — add the header first.');
    return;
  }
  if (!rows.length) {
    Logger.log('No rows to translate — nothing to do.');
    return;
  }

  // 4. Walk each row, translating only where the target cell is blank
  //    and an English source exists.
  rows.forEach((r, i) => {
    if (r[tgtCol] || !r[col.en]) return;

    // 5. Give Claude the brand voice as context, not just the raw string.
    const prompt = `Translate to ${targetLang}, preserving tone and brand ` +
      `voice (${BRAND_VOICE}). Return only the translation.\n\n${r[col.en]}`;

    sheet.getRange(i + 2, tgtCol + 1).setValue(callClaude(prompt));
  });

  Logger.log('Finished translating into "' + targetLang + '".');
}

/**
 * Minimal Anthropic API call. The key lives in Script Properties — it
 * is never pasted into the code.
 *
 * @param {string} prompt The full prompt to send.
 * @return {string} Claude's reply, trimmed.
 */
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: 500,
      messages: [{ role: 'user', content: prompt }],
    }),
  });
  return JSON.parse(res.getContentText()).content[0].text.trim();
}

How it works

1. translateColumn opens the copy spreadsheet and reads every row, splitting the header off the data with a destructuring assignment.
2. It builds a col map of header name to column index, so the English source can be addressed by name rather than a fragile number.
3. It locates the target-language column by its header. If that column does not exist, or there are no data rows, it logs a message and stops — no wasted API calls.
4. It walks each row and skips two cases: the target cell is already filled, or there is no English source to translate from.
5. For each remaining cell it builds a prompt that carries the brand voice as context, asks for only the translation back, and writes the reply into the target column.

Example run

Say the sheet has an en column and an empty de column:

en	de
Get started in minutes — no credit card needed.
Our team has your back, around the clock.
Built for studios that move fast.

After translateColumn('de'), the de column is filled with on-brand German rather than a literal gloss:

en	de
Get started in minutes — no credit card needed.	In wenigen Minuten startklar — ganz ohne Kreditkarte.
Our team has your back, around the clock.	Unser Team ist rund um die Uhr für dich da.
Built for studios that move fast.	Gemacht für Studios, die Tempo machen.

Note “has your back” became “ist für dich da” — the idiom is carried across, not translated word for word.

Run it

This is an on-demand job — run it whenever you add new English copy:

1. In the Apps Script editor, select translateColumn and click Run.
2. Approve the authorisation prompt the first time.
3. To translate into another language, change the default argument or call translateColumn('fr') from a wrapper function.

To make it reachable from the spreadsheet itself, add a custom menu:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Localisation')
    .addItem('Translate to German', 'translateColumn')
    .addToUi();
}

Watch out for

• One language per run. translateColumn fills a single column. To localise into several languages, call it once per language code — each call only pays for cells that are still blank.
• Add the header first. The script bails if the target column does not exist. It will not create the column for you, by design — a typo in a language code should fail loudly, not silently make a new column.
• Brand names can drift. Claude usually leaves “Northwind Studios” untouched, but if a product name keeps getting translated, name it explicitly in the prompt and tell Claude to keep it as-is.
• Long runs can time out. Apps Script caps a single execution at six minutes. For a few hundred strings that is fine; for thousands, process the sheet in batches across scheduled runs.
• Re-running never re-translates. Filled cells are skipped, so to refresh a translation you must clear the cell first.