DEV Community

Cover image for Automating Internationalization with Google Spreadsheet and i18next.
Anlisha Maharjan
Anlisha Maharjan

Posted on • Originally published at anlisha.com.np on

1

Automating Internationalization with Google Spreadsheet and i18next.

The article is for the front-end developers who are suffering from the manual “copy-and-paste” internationalization process. Through this guide executing a single line of script shall automate the entire process.

Use Case

  1. React app that supports multiple languages (with i18next and react-i18next library)
  2. Setup Google Spreadsheet as JSON Hosting + v4 Google sheet API authentication
  3. Script that auto synchronize translations between Google Spreadsheet & JSON file (with google-spreadsheet library & Google Sheets API) by given two methods: – Scanning the key from the source code and uploading the scanned key to Google spreadsheet. – Downloading the translated strings from Google spreadsheet when building the source code.

Let’s Start

Step 1 — Install and configure i18next & react-i18next library

npx create-react-app react-internationalization
cd react-internationalization
npm install @mui/material @emotion/react @emotion/styled
Enter fullscreen mode Exit fullscreen mode

This will create a basic CRA. We also installed MUI 5 for creating UI components.

npm install i18next react-i18next i18next-browser-languagedetector i18next-http-backend
Enter fullscreen mode Exit fullscreen mode

This will install i18next framework and its React wrapper.

Next create a file i18n.js at the top level of the project with given configuration below:

import i18n from "i18next";
import { initReactI18next } from "react-i18next";
import HttpApi from "i18next-http-backend";
import LanguageDetector from "i18next-browser-languagedetector";
const fallbackLng = ["en"];
const availableLanguages = ["en", "no"];
i18n
.use(HttpApi) // load translations using http (default public/locals/en/translations)
.use(LanguageDetector) // detect user language
.use(initReactI18next) // pass the i18n instance to react-i18next.
.init({
fallbackLng, // fallback language is english.
preload: ["en", "no"],
detection: {
checkWhitelist: true, // options for language detection
},
supportedLngs: availableLanguages,
react: {
useSuspense: false,
},
debug: false,
whitelist: availableLanguages,
});
export default i18n;
view raw i18n.js hosted with ❤ by GitHub

Then import the file in index.js as shown below:

import React from "react";
import ReactDOM from "react-dom";
import App from "./App";
import reportWebVitals from "./reportWebVitals";
import CssBaseline from "@mui/material/CssBaseline";
import { ThemeProvider } from "@mui/material/styles";
import theme from "./configs/theme";
import "./assets/scss/main.scss";
import "./i18n";
ReactDOM.render(
<React.StrictMode>
<ThemeProvider theme={theme}>
{/* CssBaseline kickstart an elegant, consistent, and simple baseline to build upon. */}
<CssBaseline />
<App />
</ThemeProvider>
</React.StrictMode>,
document.getElementById("root")
);
// If you want to start measuring performance in your app, pass a function
// to log results (for example: reportWebVitals(console.log))
// or send to an analytics endpoint. Learn more: https://bit.ly/CRA-vitals
reportWebVitals();
view raw index.js hosted with ❤ by GitHub

All translations goes into — _public/locales _— with a separate JSON file for each supported language.

├── public/
│ ├── locales/
│ ├── en/
| ├── translation.json
|
│ ├── no
| ├── translation.json
|
Enter fullscreen mode Exit fullscreen mode

Also place the snippet below in App.js for UI components that reflects switch between multiple languages.

import React from "react";
import * as Mui from "@mui/material";
import { useTranslation } from "react-i18next";
import en from "./assets/images/en.svg";
import no from "./assets/images/no.svg";
function App() {
const { i18n, t } = useTranslation();
const setLocale = (lng) => {
i18n.changeLanguage(lng);
};
return (
<Mui.Container className="mt-2 h-100vh" fixed>
<Mui.Box display="flex" justifyContent={"flex-end"} className="mb-3">
<Mui.Button
className={`mr-2 ${
i18n.language === "en"
? "border-1 border-color-danger border-rad-4 text-color-danger"
: "text-color-black"
}`}
onClick={() => setLocale("en")}
>
<span className="d-flex">
<img src={en} alt="" className="img-fluid mr-1" />
EN
</span>
</Mui.Button>
<Mui.Button
className={`${
i18n.language === "no"
? "border-1 border-color-danger border-rad-4 text-color-danger"
: "text-color-black"
}`}
onClick={() => setLocale("no")}
>
<span className="d-flex">
<img src={no} alt="" className="img-fluid mr-1" />
NO
</span>
</Mui.Button>
</Mui.Box>
<Mui.Box
display="flex"
flexDirection="column"
justifyContent="center"
alignItems="center"
>
<Mui.Typography
component="h2"
variant="h2"
className="mb-3 font-weight-bold"
textAlign="center"
>
{t("APP.WELCOME")}
</Mui.Typography>
<Mui.Typography
component="h3"
variant="h3"
className="mb-3 font-weight-medium"
textAlign="center"
>
{t("APP.TITLE")}
</Mui.Typography>
<Mui.Typography
component="p"
variant="p"
className="mb-2 font-weight-normal"
textAlign={"justify"}
>
{t("APP.SUBTITLE")}
</Mui.Typography>
<Mui.Typography
component="p"
variant="p"
className="font-weight-normal"
textAlign={"justify"}
>
{t("APP.DESCRIPTION")}
</Mui.Typography>
</Mui.Box>
</Mui.Container>
);
}
export default App;
view raw App.js hosted with ❤ by GitHub

Step 2 — Setup Google Spreadsheet

Create a Google Sheet with the following structure:

Note the spreadsheet id.

1B1njd441X7di47joj9OvQGuJZ-mR9yFQrpr0WNW6c94
Enter fullscreen mode Exit fullscreen mode

To handle v4 Google sheet API authentication follow service account option. Generated JSON file as shown below; secret.json is kept at the top level of the project and add it to .gitignore!

{
  "type": "service_account",
  "project_id": "XXXXXXXXXXXXXXX",
  "private_key_id": "XXXXXXXXXXXXXXX",
  "private_key": "XXXXXXXXXXXXXXX",
  "client_email": "service-account-google-sheet-a@XXXXXXXXXXXX.iam.gserviceaccount.com",
  "client_id": "XXXXXXXXXXXXXXX",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/service-account-google-sheet-XXXXXXXXXXXXX.iam.gserviceaccount.com"
}
Enter fullscreen mode Exit fullscreen mode

Step 3 — Automated script to synchronize translations between Google Spreadsheet & JSON file

google-spreadsheet is a wrapper library that lets you use the Google Sheets API. It can be used to create a new sheet or to read, write, and manage cells and rows.

npm i google-spreadsheet
Enter fullscreen mode Exit fullscreen mode

Create fetch-google-spreadsheet.js and push-google-spreadsheet.js file at the top level of the project and insert the below code in it.

const { GoogleSpreadsheet } = require("google-spreadsheet");
const secret = require("./secret.json");
const fs = require("fs");
//# Initialize the sheet
const doc = new GoogleSpreadsheet(
"1B1njd441X7di47joj9OvQGuJZ-mR9yFQrpr0WNW6c94"
); //# spreadsheet ID
//# Initialize Auth
const init = async () => {
await doc.useServiceAccountAuth({
client_email: secret.client_email,
private_key: secret.private_key,
});
};
const read = async () => {
await doc.loadInfo(); //# loads document properties and worksheets
const sheet = doc.sheetsByTitle.Sheet1; //# get the sheet by title, I left the default title name. If you changed it, then you should use the name of your sheet
await sheet.loadHeaderRow(); //# loads the header row (first row) of the sheet
const colTitles = sheet.headerValues; //# array of strings from cell values in the first row
const rows = await sheet.getRows({ limit: sheet.rowCount }); //# fetch rows from the sheet (limited to row count)
let result = {};
//# map rows values and create an object with keys as columns titles starting from the second column (languages names) and values as an object with key value pairs, where the key is a key of translation, and value is a translation in a respective language
// eslint-disable-next-line array-callback-return
rows.map((row) => {
colTitles.slice(1).forEach((title) => {
result[title] = result[title] || [];
const key = row[colTitles[0]];
result = {
...result,
[title]: {
...result[title],
[key]: row[title] !== "" ? row[title] : undefined,
},
};
});
});
return result;
};
function parseDotNotation(str, val, obj) {
let currentObj = obj;
const keys = str.split(".");
let i;
const l = Math.max(1, keys.length - 1);
let key;
for (i = 0; i < l; ++i) {
key = keys[i];
currentObj[key] = currentObj[key] || {};
currentObj = currentObj[key];
}
currentObj[keys[i]] = val;
delete obj[str];
}
Object.expand = function (obj) {
for (const key in obj) {
if (key.indexOf(".") !== -1) {
parseDotNotation(key, obj[key], obj);
}
}
return obj;
};
const write = (data) => {
Object.keys(data).forEach((key) => {
const tempObject = Object.expand(data[key]);
fs.writeFile(
`./public/locales/${key}/translation.json`,
JSON.stringify(tempObject, null, 2),
(err) => {
if (err) {
console.error(err);
}
}
);
});
};
init()
.then(() => read())
.then((data) => write(data))
.catch((err) => console.log("ERROR!!!!", err));
const { GoogleSpreadsheet } = require("google-spreadsheet");
const secret = require("./secret.json");
const fs = require("fs");
//# Initialize the sheet
const doc = new GoogleSpreadsheet(
"1B1njd441X7di47joj9OvQGuJZ-mR9yFQrpr0WNW6c94"
); //# spreadsheet ID
//# Initialize Auth
const init = async () => {
await doc.useServiceAccountAuth({
client_email: secret.client_email,
private_key: secret.private_key,
});
};
const traverse = function (enObj, noObj, arr) {
const enObjData = enObj.data;
const noObjData = noObj.data;
for (const i in enObjData) {
if (enObjData[i] !== null && typeof enObjData[i] == "object") {
//# going one step down in the object tree!!
const label = enObj.label !== "" ? `${enObj.label}.${i}` : `${i}`;
const childEn = { label: label, data: enObjData[i] };
const childNo = { label: label, data: noObjData[i] };
traverse(childEn, childNo, arr);
} else {
arr.push({
key: enObj.label !== "" ? `${enObj.label}.${i}` : `${i}`,
en: enObjData[i],
no: noObjData[i],
});
}
}
return arr;
};
const read = async () => {
await doc.loadInfo(); //# loads document properties and worksheets
const sheet = doc.sheetsByTitle.Sheet1; //# get the sheet by title, I left the default title name. If you changed it, then you should use the name of your sheet
const rows = await sheet.getRows({ limit: sheet.rowCount }); //# fetch rows from the sheet (limited to row count)
//# read /public/locales/en/translation.json
const en = fs.readFileSync(`./public/locales/en/translation.json`, {
encoding: "utf8",
flag: "r",
});
//# read /public/locales/no/translation.json
const no = fs.readFileSync(`./public/locales/no/translation.json`, {
encoding: "utf8",
flag: "r",
});
const enObj = { label: "", data: JSON.parse(en) };
const noObj = { label: "", data: JSON.parse(no) };
//# loop over JSON object and create new array
// eslint-disable-next-line no-undef
const result = traverse(enObj, noObj, (arr = []));
//# difference between google-spreadsheet rows and newly created array
const el = result.filter(
({ key: id1 }) => !rows.some(({ key: id2 }) => id2 === id1)
);
return el;
};
const append = async (data) => {
await doc.loadInfo(); //# loads document properties and worksheets
const sheet = doc.sheetsByTitle.Sheet1; //# get the sheet by title, I left the default title name. If you changed it, then you should use the name of your sheet
await await sheet.addRows(data); //# append rows
};
init()
.then(() => read())
.then((data) => append(data))
.catch((err) => console.log("ERROR!!!!", err));

Insert scripts in package.json

{
"start": "npm run fetch:i18n && react-scripts start",
"build": "npm run fetch:i18n && react-scripts build",
"test": "react-scripts test",
"eject": "react-scripts eject",
"push:i18n": "node push-google-spreadsheet.js",
"fetch:i18n": "node fetch-google-spreadsheet.js"
}
Enter fullscreen mode Exit fullscreen mode
  • Developer: Run npm run push:i18n then request translation.
  • Translator: Enter translations in the spreadsheet

This is it. Every time you build/start application, the npm run fetch:i18n will be executed and the most recent translation will be applied to the build. I hope this helps.

Source Code!

The full source code is available here — https://github.com/anlisha-maharjan/react-internationalization-googlespreadsheet-i18next.

More info!

  1. List of all config options available for i18next_._
  2. v4 google sheets API Authentication using service account.

The post Automating Internationalization with Google Spreadsheet and i18next. first appeared on Anlisha Maharjan.

Top comments (0)

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more