Translating strings for web apps is a pretty anoying task. Every time you make an update, you have to check all of the new language files and identify if there are missing translations.

I used to rely on a paid service that would provide me with a ui. However, I found this service to expensive as I am not using it daily.

This time, instead of reactivating that service for a single update, I decided to take things into my own hands.

Luckily I already have found a great package I can use for my Laravel project: nikaia/translation-sheet.

After some setup, it uploads all my strings to a single file, and I can edit them from there.

Identifying untranslated strings

Now, this spreadsheet is huge, I have about 1500 strings, in 5 languages.

Luckily in google sheets you can use “Conditional markup”.

Select all the columns that hold translations, right click and choose “Conditional markup.”

Then set it to “one colour”, “Text matches exact” and in the inputbox put =$J1 where J is the column that holds the source language value.

Choose a style and save. Now all the fields that match with the source language will be highlighted.

Providing automatic translations

An automatic translation is often a good starting point to get you going.

But in my translation files there are placeholders such as:

the quick {colour} :animal jumps over the {targetMood} :targetAnimal

There are 2 types of placeholders because I have mixed strings from Laravel and VueJS

We do not want to translate these placeholders, so we will make a custom translation function.

In the google sheets menu go to “Extra” then “Script editor” and create a new script:

function mytranslate(text, source_language, target_language) {
  if(text && text.toString()) {
    var str = text.toString();
    var regex = /:[\w.]+|{[\w.]+}/g; // g flag for multiple matches
    var replace = 'VAR'; // Replace {variable} and :variable to prevent from translation
    var hasMatches = str.match(regex) !== null;
    var number = 0;

    if (hasMatches) {
      var vars = str.match(regex);
      str = str.replace(regex, function() {
        return replace + number++
      });
    }

    str = LanguageApp.translate(str, source_language, target_language);

    if (hasMatches) {
      for (var i = 0; i < vars.length; i++) {
        str = str.replace(replace + i, vars[i]); // Replace back in the correct order.
       }
    }
    return str;
  }
  return null;
}

I am not going to go into too much detail but this script will turn: the quick {colour} :animal jumps over the {targetMood} :targetAnimal into the quick VAR0 VAR1 jumps over the VAR2 VAR3 before doing a translation request.

Once the translation is done, it will replace again these value with their placeholders.

We are almost done, we just have to make an easy to use formula to paste over our coloured columns to get a translation.

It took me a bit of time, but the following seems to do the trick:

=mytranslate(INDIRECT(ADDRESS(ROW(); 10));"en"; INDIRECT(ADDRESS(1; COLUMN())))

Where 10 is the 1 indexed Column (J in this example).

This calls our function, with the value J{CURRENTROW} as that is the original string, our source language en and our target language {CURRENTCOLUMN}1.

What is next

Some things to keep in mind, there is a daily limit to how many translations you can do. For this it is important that you copy the values and save them without the formula, otherwise the formula will execute every time you open the spreadsheet.

Once you have all your translations in and double checked them, you can download the spreadsheet to import in your system again.

Notes

This blogpost was mainly made to remind myself of all of this!