Translate columns with a translation API

Northwind keeps its product copy in a spreadsheet — one row per item, English in the first column. When the studio launches in a new market, every line needs translating, and pasting strings into a translation site one at a time is slow and easy to lose track of.

This script fills a whole language column in one pass. It reads the English source, translates each row into the target language, and writes the result into the matching column — skipping any cell that already has a translation, so you can run it again as new rows are added without redoing work.

What you’ll need

• A Google Sheet with English source text in the first column and a header row.
• One column per target language, with the header set to the language code you want — for example de, fr or es. The script finds the column by that header.
• Nothing else — LanguageApp is built into Apps Script and needs no API key.

The script

// The spreadsheet holding the copy to localise.
const COPY_SHEET_ID = '1abcCopyId';

// The language of the source text in the first column.
const SOURCE_LANG = 'en';

/**
 * Fills the column whose header matches `targetLang` with translations
 * of the first column. Cells that already hold a translation are left
 * untouched, so the function is safe to re-run.
 *
 * @param {string} targetLang The language code to translate into,
 *   matching one of the header cells (e.g. 'de').
 */
function translateColumn(targetLang = 'de') {
  const sheet = SpreadsheetApp.openById(COPY_SHEET_ID).getSheets()[0];

  // 1. Read everything and split the header off from the data rows.
  const [headers, ...rows] = sheet.getDataRange().getValues();
  if (!rows.length) {
    Logger.log('No rows to translate — nothing to do.');
    return;
  }

  // 2. Find the column whose header is the target language code.
  const targetCol = headers.indexOf(targetLang);
  if (targetCol === -1) {
    Logger.log('No column headed "' + targetLang + '" — add one first.');
    return;
  }

  // 3. Walk each row and translate the cells still missing a value.
  let filled = 0;
  rows.forEach((row, i) => {
    const source = row[0];
    const existing = row[targetCol];

    // Skip rows with no source text, or ones already translated.
    if (!source || existing) return;

    // Translate the first-column text and write it into the target cell.
    const translated = LanguageApp.translate(source, SOURCE_LANG, targetLang);
    sheet.getRange(i + 2, targetCol + 1).setValue(translated);
    filled++;
  });
  Logger.log('Filled ' + filled + ' ' + targetLang + ' cells.');
}

How it works

1. translateColumn opens the sheet and reads everything in one go with getDataRange, then splits the header row off from the data rows.
2. If there are no data rows it logs a message and stops — no point looping over an empty sheet.
3. It locates the target column by indexOf on the header row, so the column can sit anywhere as long as its header is the language code. If no such header exists the script stops with a clear message.
4. It walks each row. A row with no source text, or one whose target cell is already filled, is skipped — that is what makes the function safe to run repeatedly as new copy is added.
5. For each remaining row it calls LanguageApp.translate, which uses Google Translate, and writes the result back. i + 2 accounts for the header row and 1-based ranges; targetCol + 1 converts the 0-based index to a column number.

Example run

The sheet starts with English source and an empty de column:

en	de
Add to basket
Free returns
Out of stock	Nicht vorrätig

Run translateColumn('de'). The two empty German cells are filled; the third is left alone because it already had a value:

en	de
Add to basket	In den Warenkorb
Free returns	Kostenlose Rücksendung
Out of stock	Nicht vorrätig

Add a fr column and run translateColumn('fr') to localise into French the same way.

Use it

This is a one-off bulk job, so run it by hand from the editor when a column needs filling:

1. In the Apps Script editor, select translateColumn and click Run. The default argument translates the de column.
2. Approve the authorisation prompt the first time.

To localise into another language, add a short wrapper for each, since the Run button cannot pass arguments:

/** Fills the French column. */
function translateFrench() {
  translateColumn('fr');
}

Run translateFrench from the editor, or wire these wrappers into a custom menu so the studio can trigger them from the spreadsheet itself.

Watch out for

• LanguageApp translations are machine output. They are fine for a first draft or low-stakes copy, but a native speaker should review anything customer-facing before launch.
• The skip-if-filled rule means the script never overwrites an existing translation, even a wrong one. To redo a row, clear its target cell first.
• Each row is a separate API call. A few hundred rows are fine; thousands can hit Apps Script’s runtime limit, so translate in batches if the sheet is large.
• LanguageApp has no glossary or tone control. For brand-sensitive copy where consistency matters, a paid service such as DeepL with a glossary gives more predictable results — call it via UrlFetchApp with an API key.
• The target column is found by header text. A typo in the header (ge instead of de) means the script stops with “no column” rather than translating into the wrong place.