Automatic text translation in Google Sheets (Excel online). Let's translate firmware.

.
Google Sheets offer a wide range of features, here I will focus on one of them - the GOOGLETRANSLATE function. This function allows us to automatically translate words and phrases between two languages, just like regular Google Translate, but its presence in the sheet can really save us a lot of time. In this particular case, I will demonstrate the translation of a potential home automation firmware, but I think it has many more applications and there is something for everyone.


Basics of GOOGLETRANSLATE
Open our Google sheet and type in A1 some example word - "Relay":

.
.
In B1 type the equals sign and then start typing GOOGLE, a hint should already appear:
.
.
Validate, now three arguments need to be entered:
.
.
The arguments are:
- text to be translated (or a cell with it)
- source language
- target language
Done:
.
.
Everything works:



We are expanding the sheet .
We already know how GOOGLETRANSLATE works, but it's time to start using it better. First a small change - in my firmware everything is in English, so the base language will be English. Apart from that, I'll throw out the language codes written "hard" in the cells so that they are in the first cell. So we are looking into the language codes:
https://developers.google.com/admin-sdk/directory/v1/languages?hl=pl
We create columns for each of them:
.
.
We enter the magic formula. The whole idea here is to use the character $ to enable absolute addressing for column A (source word), for the source language and for the target language row:
.
.
Mark cell and stretch downwards:
.
.
And then to the right:
.


Related feature .
While we're on the subject of translations, it's worth mentioning a related function, namely DetectLanguage - this returns the language code for a given word:
.
Thus we get:
.
Can be useful if you do not recognise the language in question.

Potential pitfall
Is everything now perfectly translated? Unfortunately not, rather someone familiar with the language should review it. Google Translate isn't perfect and doesn't know the context, so strange translations still happen. Rather, what we have done should only be a base for further translations.


Export results .
Now it is a matter of further processing the data. A CSV file, or comma separated values, can be downloaded from Google Sheets.
.
.
This is how the file looks after downloading:

.
The structure of the file is really very simple. It can be loaded very easily in your own program, two steps are enough:
- reading it line by line
- divide the line into phrases according to commas
Example code in C#:
.
This code simply reads in such a file and extracts separate values from it. The loading is carried out line by line.

How is the translation implemented for programs written in C/C++? .
It's time to go one step further and think about how we can make language versions for our software. Let's look at some practical example here, how is this done in Tasmota for example?
https://tasmota.github.io/docs/
To start with, there can be two ways of translations:
- a language version embedded at compile time in the firmware (only one, selected) - this requires changing the batch to change the language, but saves memory
- you can also keep different language versions in the batch (or load them from files, etc.) - this takes more memory
Tasmota has separate binary files for the languages:
.
.
The translation is implemented using a preprocessor:
.
.
We have separate headers that define the subtitles, e.g:
.
Here are some of them:

.
For German:
.
.
And then in the application code the simply executed #define is used, like this:
.
It is important to note that only one of these headers is included at compile time. Then compilation is performed separately for each such header and the result is separate binaries, one file per language.


Automatic header generation .
Now that we know how translations are implemented by Tasmota, let's try to map the same in our code. Let's add the names of the preprocessor definitions to our sheet:
.
.
Next, let's modify our code so that it automatically reads the CSV and creates translation headers:
.
It's worth remembering that I start the loops here with 1 rather than 0, to skip the first name column for the preprocessor. I create separate files for each language in turn:
.
The files so far are very simple:
.
.
Here it would still be useful to make some kind of standard, instead of just NO, maybe better S_NO, etc. As in Tasmota, by the way, they have a D_ prefix there.
So we change:
.
to:
.
Of course, this is just a seed and there are still a few potential things to improve with this, but for demonstration purposes this will suffice.

Summary
Undoubtedly Google Sheets can help with translation automation, including when translating a program to a microcontroller, although I still think some care needs to be taken when doing this and running any translation through someone who knows the language. In addition, it is worth remembering that the adventure does not end with the Sheets alone, because you can always download the CSV and process it further, even if only in simple self-written programmes, as I have also shown here..
And what uses do you have for Google Sheets? Feel free to discuss.