Fix react-i18next Missing Translation Keys from a Malformed Excel Export

Nested resource for default i18next

With the default keySeparator ('.'), i18next expects nesting. nested: true builds it, and t('home.title') resolves.

Sheet (first tab):
key          | en
-------------|------------
home.title   | Welcome
home.cta     | Sign up

Options: keyColumn=key, valueColumn=en, nested=true

Output → en.json:
{
  "home": {
    "title": "Welcome",
    "cta": "Sign up"
  }
}

// i18next default config → t('home.title') === 'Welcome'

Flat resource for keySeparator: false

If you deliberately disabled the key separator, i18next treats the whole dotted string as one literal key. Then you need flat output, so leave nested off.

Sheet:
key          | en
-------------|------------
home.title   | Welcome
home.cta     | Sign up

Options: nested=false

Output → en.json:
{
  "home.title": "Welcome",
  "home.cta": "Sign up"
}

// i18next init: { keySeparator: false } → t('home.title') works

Trailing-space key that never matched

The classic 'works for everyone but me' key. The sheet has 'home.title ' with a trailing space; t('home.title') never matched it. Regenerating trims the key.

Sheet cell (note trailing space):
key            | en
---------------|--------
'home.title '  | Welcome

Before: hand-built JSON had "home.title " (space) → t('home.title') missed

After regenerate (key trimmed):
{ "home": { "title": "Welcome" } }
→ t('home.title') === 'Welcome'

Separator row that broke a hand-built file

A blank row in the sheet had become a "": "" entry in a manually built JSON, which some loaders choke on. The tool skips empty-key rows, so the resource is clean.

Sheet:
key          | en
-------------|----------
login.title  | Log in
             |            ← blank separator
login.error  | Wrong password

Output → en.json (no empty key):
{
  "login": {
    "title": "Log in",
    "error": "Wrong password"
  }
}

Interpolation values pass through

i18next interpolation uses {{var}}. That's part of the value and is written verbatim, so your interpolated strings keep working after regeneration.

Sheet:
key            | en
---------------|--------------------------
welcome.user   | Welcome back, {{name}}!
cart.items     | You have {{count}} items

Output → en.json:
{
  "welcome": { "user": "Welcome back, {{name}}!" },
  "cart": { "items": "You have {{count}} items" }
}
(i18next {{name}} interpolation preserved)

This is the core bug. Nested JSON with keySeparator: false, or flat dotted JSON with the default separator, makes i18next return the key string instead of the value. Match nested to your config: true for default i18next, false for keySeparator: false.

With nested: true, having both home (a value) and home.title (a child) collides on home. One overwrites the other by row order, so t('home.title') or t('home') will miss depending on which survived. Rename so a key is never both a leaf and a parent.

Two home.title rows mean the second value silently overwrites the first. Output key count drops below row count. If the value you see in the app is the 'wrong' one, you likely have a duplicate; dedupe with the duplicate-purge tool.

Set valueColumn: EN against a header en and i18next renders blanks (every value is empty string). It looks like a translation bug but it's a column-name typo. Match the header exactly.

Only sheet 1 is parsed. If your debug copy lives on sheet 2, the tool reads sheet 1 instead and the resource won't contain your keys. Move them to the first sheet or export that sheet as CSV.

i18next namespaces (t('ns:home.title')) come from separate resource files per namespace, not from the colon inside one file. Generate one file per namespace by filtering the sheet, or keep one default namespace. The tool doesn't split on : — only . (and only when nested).

A translation like He said "hi" is escaped correctly by JSON.stringify (\"). This is exactly why regenerating beats hand-editing — the manual route is where the unescaped quote that breaks the file gets introduced.

Output follows sheet row order; there's no sort. i18next doesn't care about order, but if you want tidy diffs, sort the spreadsheet first. Order has no effect on whether t() resolves.

i18next plurals use suffixed keys like item_one and item_other. Put each suffix on its own row in the sheet; they're ordinary keys to this tool and pass through unchanged into the resource.

Above 5 MB or 10,000 rows on Free, the file is rejected before processing. A single-language debug resource is far smaller; only a giant multi-language master would hit this. Split or upgrade if needed.