← Home

Multilinguage for JavaScripts with Google Sheets

Hieu Nguyen — November 25, 2019

You're building a product and start having users from many countries? I've built a library to help translate your apps up to 103 languages with Google Sheets


Saltar - a simple web builder, is recently released. We are lucky to be picked up by a local news, which caused the number of users to shook up. What interesting was the number of non-English speakers is significance.

More than half of the users are Spanish speakers (according to Google Analytics). Also, I recently came across a tutorial on using Google Translate in Spreadsheet. So I decided to add more languages to Saltar using Spreadsheet, some frontend and backend code put together.

To be fair, there were nearly 400 phrases need to translate. Which includes single words like Submit, Cancel, or longer phrase messages like What do you want this page called, when it has 10 million viewers a month?

This translation method comes with a few issues that I was aware of. For example:

With words: when using “state”, in mouse event (hover state, active state, etc…), it translated “state” as a region. With phrases: as we use a shorter form of phrases, for example, using “How it works” instead of “How does it works?”, the translation result might be differen. To fix this, use a longer form that has the correct English grammar. On the other hand, the solution provides a free and quick way to translate the app. Besides, using Spreadsheet allows you to fix the translation later.

The libraries and resources mention in this post is available at the bottom. And here is step by step about how to translate the entire app.

1. Prepare the SpreadSheet

The goal of the spreadsheet is to only fill in the default languages. Then use the formula to translate the rest. I organized the spreadsheet as follows:

Each column represents a language. The first column is reserved for the default language, which in my case is English. There are 3 important rows. The first one is the name of the language, which includes its English name and native name. For example, Vietnamese (Tiếng Việt). Then the second row is its ISO 639-1 code, such as “en” for English, or “vi” for Vietnamese. And the last one contains the GOOGLETRANSLATE formula You can find the template I prepared at the end of this post

2. Translate the SpreadSheet

Once the spreadsheet is ready, all I have to do now is find phrases in the app, fill them into the first column (default language) of the spreadsheet.

Note: The client library provides a method, which returns a list of not-translated phrases. You can get the list, then use Paste Special option on Google Sheet.

In the second column, start translating by using the formula:

=GOOGLETRANSLATE(sourceValue, sourceLanguageCode, targetLanguageCode)

Repeat to all other languages, for all phrases (see details in the spreadsheet template at the bottom). Then download as .csv (File > Download > Comma-separated…)


Everytime you open the translated spreadsheet, it took a while to re-load all the translation formula. Let say you have 100 phrases, it will be 99 * 103, which is 10.107 formulas to be process.

To prevent this, after the translation is done, use Paste values (right click, Paste Special > Paste values only). In the template provided below, I also freezed the 3rd row. You can leave the formula in this row as the template for later use

3. Setup a server handler

Leaving the language packages on the client-side isn’t a great idea. Since in my case, it added up to 0.5-MB in the total bundle size. While only 2 languages are required at a time (the default and the user-selected ones).

I decided to set up a server, which allows the client to pull and store the only 2 languages it needs. Note that we use the CSV data file downloaded in step 2.

The server APIs include:

  • [GET] /languages/list : listing all the available languages
  • [GET] /languages/:code1,:code2…?version=:version: Request multiple language packages (using version to check for changes)

Every time a request comes in, it reads metadata of the CSV file, gets the date, and match the “version” in the URL query. When the version is either empty or different, it loads the languages and sends back as JSON.

4. Setup client library

The main job of the client library is to get the requested language, then display it. Besides, each time the app started, it makes a request to the server and downloads the new language package if there are any changes.

Here is how the client library looks like (translate English to Vietnamese):

i8.text("Hello there") 
// => Xin chào
i8.text("Your app __name__ is being created", { data: { name: "Booking" }})
// => Ứng dụng Booking của bạn đang được khởi tạo
i8.text("Update", { case: "upper" }) 
// => CẬP NHẬT

The above example demonstrates 3 common cases for the translation. Which are (1) a plain text, (2) text with dynamic variables (using placeholder), (3) and addition options (text case transform).

Note: there are many cases that the library didn’t cover (since the app doesn’t require). For example, currency sign and conversion.

To wrap up, the whole point is to help someone who can’t understand English, have a fair idea about the app and its functionalities. And God knows, your project can be picked up by people from another part of the world.

Translate the app using a spreadsheet isn’t as sophisticated as some of the translation services out there. Though it did an ok job, free and simple

You can download the available resources, follow the instructions to install client and server library. Or making your own translation library as you wanted from the following links

  • Translation spreadsheet template: https://bit.ly/33WvRqY
  • itl: client library for translation: https://github.com/hieunc229/itl
  • itl-server: server handler https://github.com/hieunc229/itl-server

You can find example for itl and itl-server in the example repository. Feel free to give me feedback and share the solution.

Share this post


Hieu Nguyen

I'm a developer, hobbyist photographer. Building Inverr — a NoCode Site Builder



Rebit - Create Free Websites
Built with Rebit