i18n-po-sheet-sync

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