Google Sheet Translations

A Node.js package for managing translations stored in Google Sheets.

Features

• ✅ Bidirectional Sync: Sync changes from local files back to Google Sheets
• ✅ Auto-Translation: Automatic Google Translate formula generation for missing translations
• ✅ Smart Locale Filtering: Only locales with actual translations in content sheets are included in output files
• ✅ TypeScript Support: Full TypeScript definitions included
• ✅ Modular Architecture: Well-tested, maintainable codebase with clear separation of concerns
• ✅ Next.js Integration: Built-in support for Next.js static export workflows
• ✅ Flexible Configuration: Customizable paths, wait times, and processing options
• ✅ GitHub Action: One-step CI integration via el-j/google-sheet-translations@v2 — see GitHub Action

Installation

npm install @el-j/google-sheet-translations

Prepare Google Spreadsheet

1. Create Google Spreadsheet(s) with the following structure:
   • First Row: Header Row with Key | [language(-country)] | [language(-country)] | ...
   • Subsequent Rows: Translation Rows with Key used as variable name and corresponding translations.
2. Auto-translation for missing translations can be managed in two ways:
   • Manually, by using the =GOOGLETRANSLATE() function in the spreadsheet
   • Automatically, by enabling the autoTranslate option when using this package (see API Reference)

Environment Variables

Make sure to set the following environment variables:

• GOOGLE_CLIENT_EMAIL: Your Google service account email
• GOOGLE_PRIVATE_KEY: Your Google service account private key
• GOOGLE_SPREADSHEET_ID: ID of the Google Spreadsheet containing translations

Usage

import { getSpreadSheetData, validateEnv } from '@el-j/google-sheet-translations';

// Ensure environment variables are set
validateEnv();

// Fetch translations
async function fetchTranslations() {
  try {
    const translations = await getSpreadSheetData(
      ['sheet1', 'sheet2'], // Array of sheet names to process
      {
        rowLimit: 100, // Optional: rowLimit parameter (default is 100)
        waitSeconds: 3, // Optional: Time to wait between API calls (default is 1)
        dataJsonPath: 'path/to/languageData.json', // Optional: Custom path for languageData.json
        localesOutputPath: 'path/to/locales.ts', // Optional: Custom path for locales.ts
        translationsOutputDir: 'path/to/translations', // Optional: Custom translations output directory
      }
    );

    console.log('Translations fetched successfully');
  } catch (error) {
    console.error('Error fetching translations:', error);
  }
}

fetchTranslations();

Usage with NEXTJS

for nextjs, you can use the package in your instrumentation.ts file. This is useful for pre-fetching translations during the build process and storing them in a JSON file. Practically for static:export in next build

export async function register() {
	if (process.env.NEXT_RUNTIME === "nodejs") {
		const getData = await import("@el-j/google-sheet-translations");
		await getData.getSpreadSheetData(["Index"]);
		console.log("register done");
	}
}

read more internationalization with nextjs

API Reference

getSpreadSheetData(sheetTitles?, options?)

Fetches and processes data from a Google Spreadsheet.

Parameters

• sheetTitles: (Optional) Array of sheet titles to process. If not provided, only the "i18n" sheet will be processed.
• options: (Optional) Configuration object
  • rowLimit: Number - Maximum number of rows to fetch (default: 100)
  • waitSeconds: Number - Base back-off delay in seconds for retrying rate-limited API calls (HTTP 429/503). Actual delay per retry is waitSeconds × 2^attempt, capped at 30 s. (default: 1)
  • dataJsonPath: String - Path for languageData.json file (default: 'src/lib/languageData.json')
  • localesOutputPath: String - Path for locales.ts file (default: 'src/i18n/locales.ts')
  • translationsOutputDir: String - Directory for translations output (default: 'translations')
  • syncLocalChanges: Boolean - Whether to sync local changes back to the spreadsheet (default: true)
  • autoTranslate: Boolean - Whether to auto-generate Google Translate formulas for missing translations (default: false)

Returns

A Promise resolving to the processed translation data object.

validateEnv()

Validates that all required environment variables are set.

Returns

An object containing the validated environment variables.

Throws

Error if any required environment variable is missing.

Locale Filtering

The package includes intelligent locale filtering to ensure only valid locale identifiers with actual translation content are included in the generated locales.ts file.

Smart Content-Based Filtering

The locales.ts file will only include locales that have actual translations in non-i18n sheets. This means:

• ✅ Locales with translations in content sheets (like 'home', 'products', etc.) are included
• ❌ Locales that only exist in the 'i18n' configuration sheet are excluded
• ❌ Locales with empty translations in content sheets are excluded
• ❌ Common spreadsheet column names are filtered out

Example:

Spreadsheet structure:
- Sheet "content": key | en | de | fr | es
  - Row: "welcome" | "Welcome" | "Willkommen" | "" | ""
- Sheet "i18n": key | en | de | fr | es  
  - Row: "config" | "Config" | "Konfiguration" | "Configuration" | "Configuración"

Result: locales.ts will only contain ["en", "de"]

This ensures your application only includes locales that have meaningful content for users.

Supported Locale Formats

• Two-letter language codes: en, de, fr, ja
• Language-country codes with hyphens: en-us, de-de, fr-ca
• Language-country codes with underscores: en_us, de_de, zh_cn
• Extended locale codes: en-us-traditional, zh-cn-simplified

Filtered Keywords

The following common spreadsheet column names are automatically filtered out:

• key, keys, id, identifier, name, title, label
• description, comment, note, context, category, type
• status, updated, created, modified, version, source
• i18n, translation, namespace, section

Additional Utilities

The package exports several utility functions that can be used independently:

import { 
  validateEnv,
  isValidLocale,
  filterValidLocales,
  normalizeConfig,
  processSheet,
  writeTranslationFiles,
  convertToDataJsonFormat,
  convertFromDataJsonFormat,
  findLocalChanges
} from '@el-j/google-sheet-translations';

// Validate locale identifiers
console.log(isValidLocale('en-us')); // true
console.log(isValidLocale('description')); // false

// Filter valid locales from header row
const validLocales = filterValidLocales(['key', 'en', 'description', 'de'], 'key');
console.log(validLocales); // ['en', 'de']

// Normalize configuration with defaults
const config = normalizeConfig({ waitSeconds: 2 });

Auto-Creating a Spreadsheet

When no GOOGLE_SPREADSHEET_ID is set (and autoCreate is not false), the package automatically creates a new Google Spreadsheet on first run using your service-account credentials:

// No spreadsheetId needed — the package creates one for you
const translations = await getSpreadSheetData(['i18n'], {
  spreadsheetTitle: 'MyProject Translations',
  sourceLocale: 'en',
  targetLocales: ['de', 'fr', 'es'],
});

The created spreadsheet includes:

• A __welcome__ sheet with setup instructions and your spreadsheet ID.
• An i18n starter sheet with common translation keys and GOOGLETRANSLATE formulas for each target locale.

The new spreadsheet ID is printed prominently to the console and, if a .env file exists in your working directory, is written to it automatically:

✅ New spreadsheet created!
   Title  : MyProject Translations
   URL    : https://docs.google.com/spreadsheets/d/...
   ID     : 1abc...xyz

   Add this to your .env file (or environment):
   GOOGLE_SPREADSHEET_ID=1abc...xyz

Set autoCreate: false to disable this behaviour and require an explicit ID.

Bidirectional Sync Feature

This package supports bidirectional synchronization between local translation files and the Google Spreadsheet:

1. Pull from Spreadsheet: Fetches translations from Google Sheets and saves them to local JSON files.
2. Push to Spreadsheet: Detects changes in local files and pushes them back to the spreadsheet.

How Bidirectional Sync Works

1. The system checks if languageData.json has been modified more recently than the translation output files.
2. If so, it compares the local languageData.json content with the data fetched from the spreadsheet.
3. Any new keys found in the local data that don't exist in the spreadsheet will be added to the spreadsheet.
4. If auto-translation is enabled, Google Translate formulas will be automatically added for missing translations when new keys are added.

This workflow allows developers to:

1. Add new translation keys in local files during development.
2. Run getSpreadSheetData() to push those new keys to the shared spreadsheet.
3. Run it again later to pull the completed translations once they're filled in by translators.

Auto-Translation Feature

When enabled, the auto-translation feature automatically adds Google Translate formulas for missing translations when new keys are added to the spreadsheet. This helps translators by providing initial machine translations as a starting point.

The formula follows this format:

=GOOGLETRANSLATE(INDIRECT(sourceColumn&ROW());$sourceColumn$1;targetColumn$1)

For example, if you add a new key with an English translation in column B but no German translation in column C, the system will automatically add:

=GOOGLETRANSLATE(INDIRECT("B"&ROW());$B$1;C$1)

Where:

• INDIRECT("B"&ROW()) dynamically references the source text cell in the same row
• $B$1 references the header cell containing the source language code
• C$1 references the header cell containing the target language code

View the complete Auto-Translation Guide for more details and best practices.

Example Workflow

// During development: add new keys to local translation files

// Then run this to sync changes back to the spreadsheet
await getSpreadSheetData(['sheet1'], {
  syncLocalChanges: true  // This is true by default
});

// Later, run again to pull completed translations
await getSpreadSheetData(['sheet1']);

// Example with auto-translation enabled
await getSpreadSheetData(['sheet1'], {
  autoTranslate: true  // Automatically generate Google Translate formulas for missing translations
});

For more detailed examples, check out the examples directory where you'll find working code samples for all features including:

• Basic usage
• Bidirectional sync
• Auto-translation
• Next.js integration

GitHub Action

This repository ships a composite GitHub Action that lets you fetch translations in any workflow without writing any Node.js scripts yourself.

Quick Start

Add the following step to your workflow after checking out your repository:

- name: Fetch translations
  uses: el-j/google-sheet-translations@v2
  with:
    google-client-email: ${{ secrets.GOOGLE_CLIENT_EMAIL }}
    google-private-key: ${{ secrets.GOOGLE_PRIVATE_KEY }}
    google-spreadsheet-id: ${{ secrets.GOOGLE_SPREADSHEET_ID }}
    sheet-titles: 'home,about,pricing'

Required Secrets

Store these as encrypted repository secrets before using the action:

| Secret | Description | |--------|-------------| | GOOGLE_CLIENT_EMAIL | Service-account e-mail from your Google Cloud credentials JSON | | GOOGLE_PRIVATE_KEY | Private key from your Google Cloud credentials JSON (include the full PEM block) | | GOOGLE_SPREADSHEET_ID | The long ID found in the spreadsheet URL: …/spreadsheets/d/<ID>/edit (can be left empty to trigger auto-create) |

Complete Example

Full workflow that fetches translations on a schedule and commits the results:

name: Sync Translations
on:
  schedule:
    - cron: '0 6 * * 1'   # every Monday at 06:00 UTC
  workflow_dispatch:

jobs:
  sync:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Fetch translations
        id: translations
        uses: el-j/google-sheet-translations@v2
        with:
          google-client-email: ${{ secrets.GOOGLE_CLIENT_EMAIL }}
          google-private-key: ${{ secrets.GOOGLE_PRIVATE_KEY }}
          google-spreadsheet-id: ${{ secrets.GOOGLE_SPREADSHEET_ID }}
          sheet-titles: 'home,about,pricing'
          translations-output-dir: 'src/translations'
          locales-output-path: 'src/i18n/locales.ts'
          data-json-path: 'src/lib/languageData.json'
          sync-local-changes: 'true'

      - name: Commit updated translations
        run: |
          git config user.name "github-actions[bot]"
          git config user.email "github-actions[bot]@users.noreply.github.com"
          git add src/translations src/i18n/locales.ts src/lib/languageData.json
          git diff --staged --quiet || git commit -m "chore(i18n): sync translations from Google Sheets [skip ci]"
          git push

All Inputs

| Input | Required | Default | Description | |-------|----------|---------|-------------| | google-client-email | ✅ | — | Service-account e-mail (GOOGLE_CLIENT_EMAIL) | | google-private-key | ✅ | — | Service-account private key (GOOGLE_PRIVATE_KEY) | | google-spreadsheet-id | ❌ | '' | Spreadsheet ID from the sheet URL; if empty and auto-create is true, a new spreadsheet is auto-created | | sheet-titles | ✅ | — | Comma-separated list of sheet tab names to process (i18n is always auto-included) | | row-limit | ❌ | 100 | Maximum rows to read per sheet | | wait-seconds | ❌ | 1 | Base back-off delay in seconds for retrying rate-limited API calls (HTTP 429/503) | | translations-output-dir | ❌ | translations | Directory for per-locale JSON files | | locales-output-path | ❌ | src/i18n/locales.ts | Path for the generated locales.ts | | data-json-path | ❌ | src/lib/languageData.json | Path for the languageData.json snapshot | | sync-local-changes | ❌ | true | Push local changes back to the sheet before fetching | | auto-create | ❌ | true | Automatically create a new spreadsheet when google-spreadsheet-id is empty | | spreadsheet-title | ❌ | google-sheet-translations | Title for the auto-created spreadsheet | | source-locale | ❌ | en | Source locale used as the base for auto-translate formulas | | target-locales | ❌ | de,fr,es,it,pt,ja,zh | Comma-separated target locales added to the auto-created spreadsheet |

Outputs

| Output | Description | |--------|-------------| | translations-dir | Absolute path of the directory containing locale JSON files | | locales-file | Absolute path of the generated locales.ts | | data-json-file | Absolute path of the languageData.json snapshot |

Auto-Create Feature

If google-spreadsheet-id is left empty and auto-create is true (the default), the action will automatically create a new Google Spreadsheet using your service-account credentials. The spreadsheet is pre-populated with an i18n starter sheet containing GOOGLETRANSLATE formulas for every locale listed in target-locales. The new spreadsheet ID is printed to the workflow log and written to your .env file (if one exists in the repository root), allowing you to commit it and reuse it in future runs.

License

MIT