Skip to content

Instantly share code, notes, and snippets.

@dongsik-yoo
Last active May 10, 2024 07:44
Show Gist options
  • Save dongsik-yoo/fd9f005e540aa76a5903d14503a5289c to your computer and use it in GitHub Desktop.
Save dongsik-yoo/fd9f005e540aa76a5903d14503a5289c to your computer and use it in GitHub Desktop.
javascript i18n automation with google spread sheet
# locale json files
/assets/locales/
// place in translate/download.js
const fs = require('fs');
const mkdirp = require('mkdirp');
const {loadSpreadsheet, localesPath, ns, lngs, sheetId, columnKeyToHeader, NOT_AVAILABLE_CELL} = require('./index');
/**
* fetch translations from google spread sheet and transform to json
* @param {GoogleSpreadsheet} doc GoogleSpreadsheet document
* @returns [object] translation map
* {
* "ko-KR": {
* "key": "value"
* },
* "en-US": {
* "key": "value"
* },
* "ja-JP": {
* "key": "value"
* },
* "zh-CN": {
* "key": "value"
* },
* }
*/
async function fetchTranslationsFromSheetToJson(doc) {
const sheet = doc.sheetsById[sheetId];
if (!sheet) {
return {};
}
const lngsMap = {};
const rows = await sheet.getRows();
rows.forEach((row) => {
const key = row[columnKeyToHeader.key];
lngs.forEach((lng) => {
const translation = row[columnKeyToHeader[lng]];
// NOT_AVAILABLE_CELL("_N/A") means no related language
if (translation === NOT_AVAILABLE_CELL) {
return;
}
if (!lngsMap[lng]) {
lngsMap[lng] = {};
}
lngsMap[lng][key] = translation || ''; // prevent to remove undefined value like ({"key": undefined})
});
});
return lngsMap;
}
function checkAndMakeLocaleDir(dirPath, subDirs) {
return new Promise((resolve) => {
subDirs.forEach((subDir, index) => {
mkdirp(`${dirPath}/${subDir}`, (err) => {
if (err) {
throw err;
}
if (index === subDirs.length - 1) {
resolve();
}
});
});
});
}
async function updateJsonFromSheet() {
await checkAndMakeLocaleDir(localesPath, lngs);
const doc = await loadSpreadsheet();
const lngsMap = await fetchTranslationsFromSheetToJson(doc);
fs.readdir(localesPath, (error, lngs) => {
if (error) {
throw error;
}
lngs.forEach((lng) => {
const localeJsonFilePath = `${localesPath}/${lng}/${ns}.json`;
const jsonString = JSON.stringify(lngsMap[lng], null, 2);
fs.writeFile(localeJsonFilePath, jsonString, 'utf8', (err) => {
if (err) {
throw err;
}
});
});
});
}
updateJsonFromSheet();
const path = require('path');
const COMMON_EXTENSIONS = '/**/*.{js,jsx,ts,tsx,vue,html}';
module.exports = {
input: [`./pages${COMMON_EXTENSIONS}`, `./components${COMMON_EXTENSIONS}`, `./stories${COMMON_EXTENSIONS}`],
options: {
defaultLng: 'ko-KR',
lngs: ['ko-KR', 'en-US', 'ja-JP', 'zh-CN'],
func: {
list: ['i18next.t', 'i18n.t', '$i18n.t'],
extensions: ['.js', '.jsx', '.ts', '.tsx', '.vue', '.html'],
},
resource: {
loadPath: path.join(__dirname, 'assets/locales/{{lng}}/{{ns}}.json'),
savePath: path.join(__dirname, 'assets/locales/{{lng}}/{{ns}}.json'),
},
defaultValue(lng, ns, key) {
const keyAsDefaultValue = ['ko-KR'];
if (keyAsDefaultValue.includes(lng)) {
const separator = '~~';
const value = key.includes(separator) ? key.split(separator)[1] : key;
return value;
}
return '';
},
keySeparator: false,
nsSeparator: false,
prefix: '%{',
suffix: '}',
},
};
// place in plugins/i18next.js
import i18next from 'i18next';
import ko_KR from '../assets/locales/ko-KR/translation.json';
import en_US from '../assets/locales/en-US/translation.json';
import ja_JP from '../assets/locales/ja-JP/translation.json';
import zh_CN from '../assets/locales/zh-CN/translation.json';
const lngs = ['ko-KR', 'en-US', 'ja-JP', 'zh-CN'];
/**
* Must add new language here
* @param lng {Language} language
* @returns {Object} json resource
*/
function loadResource(lng) {
let module;
switch (lng) {
case 'ko-KR': {
module = ko_KR;
break;
}
case 'en-US': {
module = en_US;
break;
}
case 'ja-JP': {
module = ja_JP;
break;
}
case 'zh-CN': {
module = zh_CN;
break;
}
default:
break;
}
return module;
}
function getResources(lngs) {
const resources = {};
lngs.forEach((lng) => {
resources[lng] = {
translation: loadResource(lng),
};
});
return resources;
}
export function initializeI18next(lng = 'ko-KR') {
i18next.init({
lng,
fallbackLng: false,
returnEmptyString: false,
keySeparator: false,
nsSeparator: false,
interpolation: {
prefix: '%{',
suffix: '}',
},
parseMissingKeyHandler(key) {
/* eslint-disable-next-line no-console */
console.warn('parseMissingKeyHandler', `'key': '${key}'`);
const keySeparator = '~~';
const value = key.includes(keySeparator) ? key.split(keySeparator)[1] : key;
return value;
},
resources: getResources(lngs),
});
}
export function changeLanguage(lng) {
return i18next.changeLanguage(lng);
}
export const i18n = i18next;
// place in translate/index.js
const {GoogleSpreadsheet} = require('google-spreadsheet');
const creds = require('./.credentials/your-app-credentials-some-unique-id.json');
const i18nextConfig = require('../i18next-scanner.config');
const spreadsheetDocId = 'your-spreadSheetDocId';
const ns = 'translation';
const lngs = i18nextConfig.options.lngs;
const loadPath = i18nextConfig.options.resource.loadPath;
const localesPath = loadPath.replace('/{{lng}}/{{ns}}.json', '');
const rePluralPostfix = new RegExp(/_plural|_[\d]/g);
const sheetId = 1234; // your sheet id
const NOT_AVAILABLE_CELL = '_N/A';
const columnKeyToHeader = {
key: '키',
'ko-KR': '한글',
'en-US': '영어',
'ja-JP': '일본어',
'zh-CN': '중국어',
};
/**
* getting started from https://theoephraim.github.io/node-google-spreadsheet
*/
async function loadSpreadsheet() {
// eslint-disable-next-line no-console
console.info(
'\u001B[32m',
'=====================================================================================================================\n',
'# i18next auto-sync using Spreadsheet\n\n',
' * Download translation resources from Spreadsheet and make /assets/locales/{{lng}}/{{ns}}.json\n',
' * Upload translation resources to Spreadsheet.\n\n',
`The Spreadsheet for translation is here (\u001B[34mhttps://docs.google.com/spreadsheets/d/${spreadsheetDocId}/#gid=${sheetId}\u001B[0m)\n`,
'=====================================================================================================================',
'\u001B[0m'
);
// spreadsheet key is the long id in the sheets URL
const doc = new GoogleSpreadsheet(spreadsheetDocId);
// load directly from json file if not in secure environment
await doc.useServiceAccountAuth(creds);
await doc.loadInfo(); // loads document properties and worksheets
return doc;
}
function getPureKey(key = '') {
return key.replace(rePluralPostfix, '');
}
module.exports = {
localesPath,
loadSpreadsheet,
getPureKey,
ns,
lngs,
sheetId,
columnKeyToHeader,
NOT_AVAILABLE_CELL,
};
// place in translate/upload.js
const fs = require('fs');
const {
loadSpreadsheet,
localesPath,
getPureKey,
ns,
lngs,
sheetId,
columnKeyToHeader,
NOT_AVAILABLE_CELL,
} = require('./index');
const headerValues = ['키', '한글', '영어', '일본어', '중국어'];
async function addNewSheet(doc, title, sheetId) {
const sheet = await doc.addSheet({
sheetId,
title,
headerValues,
});
return sheet;
}
async function updateTranslationsFromKeyMapToSheet(doc, keyMap) {
const title = 'Your Sheet Title';
let sheet = doc.sheetsById[sheetId];
if (!sheet) {
sheet = await addNewSheet(doc, title, sheetId);
}
const rows = await sheet.getRows();
// find exsit keys
const exsitKeys = {};
const addedRows = [];
rows.forEach((row) => {
const key = row[columnKeyToHeader.key];
if (keyMap[key]) {
exsitKeys[key] = true;
}
});
for (const [key, translations] of Object.entries(keyMap)) {
if (!exsitKeys[key]) {
const row = {
[columnKeyToHeader.key]: key,
...Object.keys(translations).reduce((result, lng) => {
const header = columnKeyToHeader[lng];
result[header] = translations[lng];
return result;
}, {}),
};
addedRows.push(row);
}
}
// upload new keys
await sheet.addRows(addedRows);
}
function toJson(keyMap) {
const json = {};
Object.entries(keyMap).forEach(([__, keysByPlural]) => {
for (const [keyWithPostfix, translations] of Object.entries(keysByPlural)) {
json[keyWithPostfix] = {
...translations,
};
}
});
return json;
}
function gatherKeyMap(keyMap, lng, json) {
for (const [keyWithPostfix, translated] of Object.entries(json)) {
const key = getPureKey(keyWithPostfix);
if (!keyMap[key]) {
keyMap[key] = {};
}
const keyMapWithLng = keyMap[key];
if (!keyMapWithLng[keyWithPostfix]) {
keyMapWithLng[keyWithPostfix] = lngs.reduce((initObj, lng) => {
initObj[lng] = NOT_AVAILABLE_CELL;
return initObj;
}, {});
}
keyMapWithLng[keyWithPostfix][lng] = translated;
}
}
async function updateSheetFromJson() {
const doc = await loadSpreadsheet();
fs.readdir(localesPath, (error, lngs) => {
if (error) {
throw error;
}
const keyMap = {};
lngs.forEach((lng) => {
const localeJsonFilePath = `${localesPath}/${lng}/${ns}.json`;
// eslint-disable-next-line no-sync
const json = fs.readFileSync(localeJsonFilePath, 'utf8');
gatherKeyMap(keyMap, lng, JSON.parse(json));
});
updateTranslationsFromKeyMapToSheet(doc, toJson(keyMap));
});
}
updateSheetFromJson();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment