Manage i18n translations from Google Sheet

Discover a surprising Google Sheet Add-on and find a use case with the vue-i18n plugin.

Published in

Vue.js Developers

6 min readAug 13, 2020

Managing translations of an app, a web site… can be very painful.

Indeed, we have to think about how new translations are added into the app, what about the updated ones and all that with the lowest cost as possible. Yes, the lowest cost, the less time to waste by the developer and by the product owner !

That takes a lot of time !

Just imagine that case i’m sure you already know. The product owner wants to add a new functionality to the app and brings with it new translations in 10 different languages. At the moment to develop the functionality, you have to think about the keys that will be used inside the i18n library you use, and copy-paste each message. That takes a lot of time and it’s not really funny to do that !

A simple tool to manage translations ?

And what about a simple tool that centralizes the translations accessible for both product owners and developers ? Build it by your own ? No, very costly.

What do we need for that tool ? An access for users with permissions, a user interface that allow to easily add, edit and remove messages and a little button to export all theses messages in a JSON file.

Google Sheet, seriously ?

Well, there is a solution. Google Sheet. Seriously ? Yes, as simple as that. Google Sheet can centralize all the translated messages and handle the permissions to access or to edit the file. But where is the little button that generates the JSON file ? Did you that you can use add-ons with Google Sheet ? Me, I didn’t know until I found an add-on in the market place of GSuite. Export Sheet Data : https://gsuite.google.com/marketplace/app/export_sheet_data/903838927001

How to use the power of Google Sheet and Export Sheet Data it’s add-on ?

Organize the translation in the Google Sheets

First of all, the translations will handle 3 languages : English (en), French (fr), Spanish (es).

Let’s assume that there are 2 kinds of translations :
- generic : simple words that can be used several times with no context (Edit, Save, Add, Delete, Hello, Good Bye, Houses, Cars….)
- contextual: from simple word to long sentence that can appear often once within a special context (page, functionality, section…)

Well, I suggest to group the translations according to these kinds (generic or contextual).
Each context will be represented by a sheet in the Google Sheet Document.
About generic case, I suggetst to split into several sheets (action, state (Loading, Saving…), greetings, Country…).

Let’s create named tabs. “actions”, “state”, “greetings” and “shop-list”.

The name of the sheet (or tab) will be in lowercase as it will be used as a key for the translations.

Then, in each sheet, the first column is named “key”. It will be used to build the translations keys :

{tab_name}.{key}

The following columns are the language ones. “en”, “fr” and “es”.

Export the translations in a JSON file

As i said previously, i use the GSuite add-on Export Sheet Data. Install it then the plugin will be available in the section “Add-ons” of the Google Sheet toolbar. (“Module complémentaires” here in French).

As you can see, the add-on offers a lot of options but let’s focus on “Export format” and “Export Sheet(s)”. Make sure the first one has the value “JSON” and the second one “All sheets”.

The add-on allow to visualize the export. Let’s use it since the Google Sheet has few translations but I advice to export to ensure all translations are included in the file.

Here is the content of the JSON file. As expected, the object collection is grouped by tabs then by key. Remember, the final goal will be to use a unique translation key. For example : “actions.add” which would be used in a i18n library function.

translate(‘actions.add’) // "Add" (if "en" locale)

You notice the add-on uses the value of the first column as the main key which simplifies the job.

Generated JSON

Use case of Google Sheet integration with VueJS and its library vue-18n.

The use case is to create a simple shop list with add and remove features.

In a Vue app built with Vue CLI using the plugin vue-i18n, there is a folder named “locales” where are placed the translations files per language. For the current use case, 3 files are expected : en.json, fr.json and es.json

By the way, Vue UI offers a way to add translations and locales but it is limited since the interface allows to add one message at a time. As previously said, it would take a lot of time !

Let’s go back to the files we have to create. Well, i will use a node script that will parse the generated file and create the languages files. I suggest to copy the generated file in the assets folder, i think it can be useful to version it.

Here is the script I've coded to generate the files. It reads the output file from the add-on and it detects the languages.

I suggest to add the command line in the package.json of your project. It will be easier to retrieve and execute the command. Moreover, if you use VSCode, the command will appear in the NPM Script tab.

"i18n:build": "node ./scripts/build-lang-files.js"

Let’s execute the script to see the result. The 3 files have been generated successfully. The content is one expected.

Important : Each time you build new languages files, you have to restart the local server of Vue CLI.

But as you can see, the whole JSON content holds in one line. This will make difficult the versioning of the files and will produce git conflicts at every merge. So, i will use prettier to prettify the file. For that, i install prettier in my project and add the prettier command to the “i18n:build”‘s.

"i18n:build": "node ./scripts/build-lang-files.js && prettier --parser json-stringify --write src/locales/*.json"

The result after the prettier command. The files are read to be committed and pushed

The final step : insert the translations keys in the templates

Let’s add the shop list feature with simple actions : add and remove.

The default locale set by the plugin vue-i18n is “en”. Just change it in the env file, rebuilt the app and voilà, it works perfectly. Feel free to add your own language switcher.