One spreadsheet single formula to save a bit of time on web page translations.

Ludwig Moreno
3 min readOct 3, 2020

Steps:
1. Open a .json locale, select both Comma and the “:” as delimiter
(Note LibreOffice Calc was used for this example)

2. The .json file will be imported into Sheet1. Rename this sheet to “Source”

3. Create another sheet, and name it ”Locales”, copy the whole column B of the sheet “Source” and paste it in column B of the sheet “Locale”
4. Open your preferred web translation service (I used Google Translate)
5. Paste the array of terms copied from the column B of sheet “Source”

6. Copy the translated array of terms
7. Paste the translated terms to the column A of the sheet “Source”

8. Carefully check that the terms are aligned to the corresponding row
9. Review the resulting translation terms
10. Create a third sheet, copy the following formula and paste it in cell A1

=IF(LEFT($Source.A1)=”{“,”{“,IF(RIGHT($Source.A1)=”}”,$Source.A1,CHAR(34)&$Source.A1&CHAR(34))&IF($Source.B1=””,””,”: “&IF(RIGHT($Source.B1)=”{“,”{“,CHAR(34)&$Locales.A1&CHAR(34)))&IF(RIGHT($Source.B1)<>”{“,IF(A2=””,””,IF(RIGHT($Source.A2)=”}”,””,”,”)),””))

11. Copy it from the cell A1, to as many cells in the column A as cells are used in the sheet “Source” (just because the formula parses row by row to combine the content of “Source” and “Locales”)

12. Select all the cells with content in the row A containing the resulting array of term pairs. Copy them and paste to your favorite coding editor. Format and save your new locale .json file.

It is not a bad idea to save the worksheet in order to track any change.

Of course there are many “professional” ways to make translations. I’ve tested some of them, but this may be the one of the simplest.

It is not a bad idea to save the worksheet in order to track any change. Of course there are many “professional” ways to make translations. I’ve tested some of them, but this may be the one of the simplest.

Enjoy

Ludwig

--

--