Automating Internationalization with Google Spreadsheet and i18next.
Tags: react
Published: 2022-09-06 09:29:28

---
title: Automating Internationalization with Google Spreadsheet and i18next.
published: true
date: 2022-03-01 16:15:00 UTC
tags: React
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/574p16qzbfsy77fu9yj5.jpeg
canonical_url: https://anlisha.com.np/blog/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
```

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
```

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:

{% embed https://gist.github.com/anlisha-maharjan/a87c0a8e72abd46d8aab0bc0f3fa7647 %}

Then import the file in _index.js_ as shown below:

{% embed https://gist.github.com/anlisha-maharjan/f1c268af0d3fa393e7b550757be57770 %}

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

```
├── public/
│ ├── locales/
│ ├── en/
| ├── translation.json
|
│ ├── no
| ├── translation.json
|
```

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

{% embed https://gist.github.com/anlisha-maharjan/1dd486319b7e3328056a590ccaef006d %}

**Step 2 — Setup Google Spreadsheet**

Create a Google Sheet with the following structure:

![](https://anlisha.com.np/blog/wp-content/uploads/2022/05/1_WqWb1AC030Oev67_zewW6A-1024x206.png)

Note the spreadsheet id.

```
1B1njd441X7di47joj9OvQGuJZ-mR9yFQrpr0WNW6c94
```

To handle v4 Google sheet API authentication follow [service account option](https://theoephraim.github.io/node-google-spreadsheet/#/getting-started/authentication?id=service-account). 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"
}
```

**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
```

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.

{% embed https://gist.github.com/anlisha-maharjan/7cbff40393508484b07d455fd696b7bd %}

{% embed https://gist.github.com/anlisha-maharjan/4784f997e5a7c5ae74489b4ef32670ce %}

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"
}
```

- 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](https://github.com/anlisha-maharjan/react-internationalization-googlespreadsheet-i18next).

**More info!**

1. [List of all config options available for i18next_._](https://www.i18next.com/overview/configuration-options)
2. [v4 google sheets API Authentication using service account.](https://theoephraim.github.io/node-google-spreadsheet/#/getting-started/authentication?id=service-account)

The post [Automating Internationalization with Google Spreadsheet and i18next.](https://anlisha.com.np/blog/automating-internationalization-with-google-spreadsheet-and-i18next/) first appeared on [Anlisha Maharjan](https://anlisha.com.np/blog).