i18n-po-sheet-sync

English | 한국어

A Node.js library that facilitates synchronization between multilingual PO files and Google Spreadsheets.
Paid collaborative tools are often used for efficient multilingual management.
However, in most cases, paid tools can be a financial burden. Therefore, Google Spreadsheets alone are usually sufficient.

The .PO extension is widely used in internationalization and localization (i18n) systems for multilingual programs. i18n-po-sheet-sync enables quick and simple multilingual management through synchronization between .PO files and Google Spreadsheets.

Features

• Export multilingual data from Google Spreadsheets to PO files
• Upload multilingual data from PO files to Google Spreadsheets
• Support for two upload methods (incremental update / batch update after sheet reset)
• Synchronize context, references, and comments of multilingual items
• Automatically apply conditional formatting to empty translation cells
• TypeScript support
• Usage with Other Multilingual Frameworks (lingui, i18next)

Example

• Please refer to the example folder for usage instructions of i18n-po-sheet-sync.
• The locales folder contains PO files organized by language (en, ko, etc.).
• example-account-auth.json is an example of service account credentials with access permissions to Google Spreadsheet. Please change it to your own service account credentials when using.
• To view an example Google Spreadsheet managed by i18n-po-sheet-sync, click here.

Installation

npm install i18n-po-sheet-sync
# or
yarn add i18n-po-sheet-sync

Usage

Basic Configuration

import I18nPOSheetSync from 'i18n-po-sheet-sync';
import { join } from 'path';

// Configuration
const config = {
  serviceAccount: {
    email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
    key: process.env.GOOGLE_SERVICE_ACCOUNT_PRIVATE_KEY,
  },
  spreadsheetId: 'GOOGLE_SHEET_ID',
  languages: ['ko', 'en', 'ja', 'zh', '...'],
  poFilesBasePath: join(__dirname, 'locale/locales')
};

// Create instance
const i18nSync = new I18nPOSheetSync(config);

Export from Google Spreadsheet to PO files

async function exportToPO() {
  try {
    const results = await i18nSync.exportToPO({
      filterMissingTranslations: false,
      preserveExistingItems: true
    });
    
    console.log('Export completed:', results);
  } catch (error) {
    console.error('Export failed:', error);
  }
}

exportToPO();

Upload from PO files to Google Spreadsheet

async function uploadFromPO() {
  try {
    const result = await i18nSync.uploadFromPO({
      applyConditionalFormatting: true, // Apply background color to empty cells
      emptyColor: '#FFEBEE', // Empty cell background color (light red)
      preserveExistingTranslations: true, // Preserve existing translations
    });
    
    console.log('Upload from po completed:', result);
  } catch (error) {
    console.error('Upload failed:', error);
  }
}

uploadFromPO();

Apply Conditional Formatting Only

async function applyFormatting() {
  try {
    await i18nSync.applyConditionalFormatting('#FFEBEE');
    console.log('Conditional formatting applied successfully');
  } catch (error) {
    console.error('Failed to apply formatting:', error);
  }
}

applyFormatting();

Configuration Options

Basic Configuration (I18nSyncConfig)

| Option | Type | Description | |------|------|------| | serviceAccount | ServiceAccountConfig | Google service account information | | languages | string[] | Array of supported language codes | | poFilesBasePath | string | Base path where PO files are stored | | sheetIndex | number (optional) | Sheet index within the spreadsheet (default: 0) |

Spreadsheet Header Titles

| Option | Type | Description | |------|------|------| | msgid | string | Message ID header (default: 'messageId') | | msgctxt | string (optional) | Context header (default: 'context') | | references | string (optional) | References header (default: 'references') | | comments | string (optional) | Comments header (default: 'comments') | | extractedComments | string (optional) | Extracted comments header (default: 'extractedComments') |

Export Options (ExportOptions)

| Option | Type | Default | Description | |------|------|--------|------| | filterMissingTranslations | boolean | false | Whether to filter items with missing translations | | preserveExistingItems | boolean | false | Whether to preserve existing PO items not in the spreadsheet | | pluralFormsByLanguage | Record<string, string> | - | Specify plural rules by language |

Upload Options (UploadOptions)

| Option | Type | Default | Description | |------|------|--------|------| | applyConditionalFormatting | boolean | false | Whether to apply background color to empty translation cells | | emptyColor | string | #FFEBEE | Background color for empty translation cells (HEX code) | | preserveExistingTranslations | boolean | false | Whether to preserve existing translation values |

Google Spreadsheet Format

The library expects a spreadsheet in the following format:

| messageId | ko | en | ... | context | references | comments | extractedComments | |-----------|----|----|-----|-----------|---------------|------|-------------| | hello | 안녕하세요 | Hello | ... | greeting | src/App.js | Greeting | User greeting | | goodbye | 안녕히 가세요 | Goodbye | ... | greeting | src/App.js | Farewell | On logout |

Header names can be changed through the HeaderMapping option.

Usage with Other Multilingual Frameworks

Using with lingui

• Can be used together with lingui, an i18n multilingual framework.

yarn add --dev @lingui/cli

• Add to package.json

{
  "scripts": {
    "extract": "lingui extract",
    "compile": "lingui compile",
    "upload": "ts-node scripts/upload-translate.ts",
    "export": "ts-node scripts/export-translate.ts",
  }
}

• Running the extract script generates .PO files currently in use.
• Running the upload script reflects changes to the Google Spreadsheet.
• Running the export script applies multilingual changes from the Google Spreadsheet.
• Running the compile script compiles the updated .PO multilingual files.

i18next

• With i18next, you can convert to .PO multilingual files using i18next-gettext-converter.

i18next-conv -l en -s ./locales/en.translation.json -t ./locales/en/translation.po

License

MIT