Build a multi-language document generator

Northwind ships the same client welcome pack to studios in three countries, and for a while each language was its own Doc. Edit the English version and someone had to remember to mirror the change into German and Spanish — which, of course, nobody did. Translations drifted, and a client in Berlin ended up with last quarter’s pricing.

This script keeps one source of truth: a Strings sheet with one row per phrase and one column per language. A single Doc template holds the layout with {{placeholder}} markers, and the script stamps out a localised copy for every language column. Change a string once and the next run rebuilds all three Docs in step.

What you’ll need

• A Google Doc template with {{placeholder}} markers where text should go — for example {{title}}, {{intro}}, {{signoff}}. The markers are the same in every language; only the values change.
• A Strings Google Sheet. The first column holds the placeholder key (without the braces), and each remaining column is one language — header row key, English, German, Spanish.
• A Drive folder to hold the generated Docs.
• The three IDs above, pasted into the config block at the top of the script.

The script

// Doc template containing the {{placeholder}} markers.
const TEMPLATE = '1abcMultilangTemplateId';

// Sheet with one row per string and one column per language.
const STRINGS = '1abcStringsSheetId';

// Drive folder where the localised Docs are saved.
const OUTPUT = '1abcLocalisedFolderId';

/**
 * Builds one localised Doc per language column in the Strings sheet,
 * replacing every {{placeholder}} in the template with the matching
 * value for that language.
 *
 * @param {string} name - Base name for the generated Docs, e.g. "Welcome pack".
 */
function buildLocalisedDocs(name) {
  // 1. Read the Strings sheet in one call: header row plus data rows.
  const [header, ...rows] = SpreadsheetApp.openById(STRINGS).getSheets()[0]
    .getDataRange().getValues();

  // 2. The first column is the key; every other header is a language.
  const languages = header.slice(1);

  if (!languages.length || !rows.length) {
    Logger.log('Strings sheet has no languages or no rows — nothing to do.');
    return;
  }

  // 3. Build a lookup: key -> array of values, one per language column.
  const strings = Object.fromEntries(rows.map((r) => [r[0], r.slice(1)]));

  const folder = DriveApp.getFolderById(OUTPUT);

  // 4. One pass per language column.
  for (let i = 0; i < languages.length; i++) {
    const lang = languages[i];

    // 5. Copy the template into the output folder, named per language.
    const copy = DriveApp.getFileById(TEMPLATE)
      .makeCopy(`${name} — ${lang}`, folder);

    // 6. Open the copy and swap every placeholder for this language's value.
    const doc = DocumentApp.openById(copy.getId());
    const body = doc.getBody();
    for (const [key, values] of Object.entries(strings)) {
      body.replaceText(`{{${key}}}`, values[i] || '');
    }
    doc.saveAndClose();
    Logger.log(`Built ${lang} copy: ${copy.getUrl()}`);
  }
}

How it works

1. buildLocalisedDocs opens the Strings sheet and reads it in a single call, splitting the header row off from the data rows.
2. Everything after the first header cell is treated as a language. The first column is the placeholder key.
3. If there are no language columns or no rows, it logs a message and stops before copying anything.
4. It builds a lookup object mapping each key to its row of translations, so strings['title'] returns ['Welcome', 'Willkommen', 'Bienvenido'].
5. For each language it copies the template into the output folder, naming the copy with the language so the three Docs are easy to tell apart.
6. It opens the copy and runs replaceText for every key, picking the value at the column index for that language. A missing translation falls back to an empty string rather than leaving a raw {{marker}} in the Doc.

Example run

A small Strings sheet:

key	English	German	Spanish
title	Welcome to Northwind	Willkommen bei Northwind	Bienvenido a Northwind
intro	We’re glad to have you.	Schön, dass Sie da sind.	Nos alegra tenerle.
signoff	The Northwind team	Ihr Northwind-Team	El equipo de Northwind

Calling buildLocalisedDocs('Welcome pack') produces three Docs in the output folder:

• Welcome pack — English
• Welcome pack — German
• Welcome pack — Spanish

Each one is the template with its {{title}}, {{intro}} and {{signoff}} markers filled in from the matching column.

Run it

This is an on-demand job — run it when the strings change or a new pack is due:

1. In the Apps Script editor, select buildLocalisedDocs.
2. Because it takes a name argument, call it from a small wrapper rather than the Run button — add function buildWelcomePack() { buildLocalisedDocs('Welcome pack'); } and run that.
3. Approve the authorisation prompt the first time.
4. Open the output folder to check the three Docs.

Watch out for

• replaceText takes a regular expression. If a placeholder key contains a character like . or +, it will be treated as regex syntax. Stick to plain letters, digits, and underscores in keys to stay safe.
• A blank cell in a language column produces an empty string in the Doc, not the English fallback. If you want fallback behaviour, change values[i] || '' to values[i] || values[0].
• This swaps text only. It does not translate — every language column must be filled in by a human or a translation step. LanguageApp.translate can seed a draft, but machine output still needs a review before it reaches a client.
• Each run creates fresh copies; it never updates the previous ones. Old Docs pile up in the folder, so clear or archive them between runs.
• replaceText does not reach into headers, footers, or text inside tables unless you also call it on those elements. If your template uses them, extend the loop to cover body.getParent() regions as needed.