401 — Translations - The-Balthazar/SupCom-Mod-Tutorials GitHub Wiki

Transferring strings_db.lua LOC files to Google sheets

This section assumes you have one or more pre-existing LOC documents that you wish to convert into a master translation spreadsheet.

Copy your primary LOC document into the first cell (A1) on a fresh Google Sheets sheet. You should end up with a key/value pair per cell. In B1 enter =REGEXEXTRACT(A1,"^[a-zA-Z_][a-zA-Z0-9_]*=") and in C1 enter =REGEXEXTRACT(A1,"=(.*$)"). Select B1 and C1 and double click the box in the bottom right corner of the selection to propagate the formula to all cells. You should now have all the keys down B and all the values down C, complete with = and surrounding "", which we want. If you used Split text to columns to break at the = it would have stripped those, and you'd have to re-add them yourself. From here, select columns B and C, copy, then shift-paste to convert them to values instead of formulas, and delete column A then add two header rows. In B1 put the two letter SupCom folder name for that language, such as US and in B2 but the appropriate ISO_639-1 code, such as EN.

You should now have something that looks like:

A	B
	US
	EN
Engine0001=	"Connecting to game host..."
Engine0002=	"%s disconnected."
Engine0003=	"Lost connection to %s."
Engine0004=	"Connection to %s established."
Engine0005=	"Connecting to %s..."
Engine0006=	"%s: adjusting game speed to %+d"

If you have multiple existing LOC sheets for different languages, do the same for each on a different sheet within the dame document. You don't need the headers on the others, but it may help you keep track of things.

Combining multiple language LOC sheets

You can skip this step if you only had one LOC sheet to begin with.

Next we'll use VLOOKUP to merge the sheets. Starting from what should be C3, enter =VLOOKUP( then click A3, type ,, then select the sheet you're importing from and highlight columns A and B on it, and finish it off with , 2). The resulting formula should look something like =VLOOKUP(A3, RU!A:B, 2) if you also renamed your sheets or =VLOOKUP(A3, Sheet2!A:B, 2) if you didn't. Do that for each other LOC sheet in cell D3 and so on, and propagate them all down as before.

Any errors with the base sheets should be obvious at this stage, so take a moment to deal with any, then use the same copy shift-paste trick to convert everything to values and once that's done everything should be in one place. If you want to make sure you could do a VLOOKUP on the other sheets targeting the primary to see if anything was missed in the primary language, and once you're convinced it's all together, delete the other sheets.

Filling in the gaps with machine translations

For each additional language you wish to support, add column headers for them as described above. The full list of languages supported by the base game is:

	English	Russian	German	French	Spanish	Italian	Polish	Chinese	Czech
Folder:	US	RU	DE	FR	ES	IT	PL	CN	CZ
639-1:	EN	RU	DE	FR	ES	IT	PL	ZH	CS

Then in cell J8 insert the following formula:

=IF(LEFT($B8,2)="--","--" & GoogleTranslate(right($B8,len($B8)-2),$B$2,J$2),IF($B8="","",CHAR(34)&GoogleTranslate(right(left($B8,len($B8)-1),len($B8)-2),$B$2,J$2)&CHAR(34)))

It needs to be J8 for that formula to function as expected. It takes whatever is in column B and if it starts with -- (A Lua comment) it strips that part, translates it, then re-adds the --. Otherwise it strips the first and last character (Assumed to be "") translates it, then surrounds it with "".

From here, select the cell and propagate out the function to all the empty cells. When selecting the cell, you can drag the lower right box to propagate it that way.

Unlike previous formulas we've used, we don't want to convert these machine translations into values. It'll make it easier to keep track of what is and what isn't translated by a human.

For translators: To translate an item replace the formula with your string, surrounded by "". If a machine translation is actually good, copy shift-paste it, to bake it in.

Conditional formatting for visibility

Optionally, in Format > Conditional formatting add the following rules for the whole sheet:

Text starts with --; and make it blue
Custom formula is =LEFT($A:$A,2)="--"; and make it blue
Cell is empty; and make it red
Custom formula is =ISFORMULA(A1); and make it purple
Custom formula is =AND(LEFT(A1,1)=CHAR(34),RIGHT(A1,1)=CHAR(34)); and make it green

The first two highlight comments, the 4th will highlight all machine translations, and the last will highlight every value starting and ending with "; that's what CHAR(34) is; writing " explicitly will cause it to try to interpret it and break.

Exporting to files

In cell A1 add the relative path you want to export to. For example /loc/ or /mods/myCoolMod/hook/loc/, and in cell A2 add --. Then, in Extensions > Apps Script paste the following function:

function generateUpdatedStringsDBFiles() {
  function replaceAll(str, find, replace) {
    return str.replace(new RegExp(find, 'g'), replace);
  }

  var routeFolder = DriveApp.getFolderById('REPLACE ME');

  var curDocument = SpreadsheetApp.getActive();
  var allSheets = curDocument.getSheets();

  for (var sheet in allSheets) {
    var curSheet = allSheets[sheet];
    var lastCol = curSheet.getLastColumn();
    var totalRows = curSheet.getLastRow();

    var translationVarList = curSheet.getRange(1, 1, totalRows).getValues();

    var directoryString = translationVarList[0][0];
    var regex = /([^/]+)/g;
    var dirName;
    var sheetFolder = null;
    while((dirName = regex.exec(directoryString)) !== null) {
      if (sheetFolder == null) {
        sheetFolder = routeFolder.createFolder(dirName[0]);
      } else {
        sheetFolder = sheetFolder.createFolder(dirName[0]);
      }
    }

    for (var curLangCol = 2; curLangCol <= lastCol; curLangCol++) {
      var outputArray = [];
      var translationResultList = curSheet.getRange(1, curLangCol, totalRows).getValues();

      //skipping the first row because it contains folder data
      for (var curRow = 1; curRow < totalRows; curRow++) {
        var locKey = translationVarList[curRow][0]
        var locValue = translationResultList[curRow][0]
        if (locValue.substring(0,1) == '#') { // #ERROR! or #REF! or whatever
          return Logger.log('Error value recieved from '+translationVarList[0][0]+' at '+locKey+' in '+translationResultList[1][0])
        } else {
          locValue = replaceAll(locValue,"\r\n","\\n")
          locValue = replaceAll(locValue,"\n","\\n")
          outputArray.push(locKey + locValue);
        }
      }
      outputArray = outputArray.join('\n');

      var targetFolder = sheetFolder.createFolder(translationResultList[0][0]);
      targetFolder.createFile("strings_db.lua", outputArray+'\n', MimeType.PLAIN_TEXT);
    }
  }
}

In line 6 replace REPLACE ME with the folder ID of the target export folder. You can get the folder ID by copying it from a folder sharing link. For example, from: https://drive.google.com/drive/folders/1_9b33c680f39c4037a0_e2693d67a1a2?usp=sharing the part of that you want would be the part between the last / and the ?: 1_9b33c680f39c4037a0_e2693d67a1a2. Which should look like this after pasting it in:

var routeFolder = DriveApp.getFolderById('1_9b33c680f39c4037a0_e2693d67a1a2');

Then run the function and download the output from the export folder. Be sure to clear the export folder before you next export.

The script supports multiple sheets of the sort described above, for if you were doing this for multiple mods or something. When the exporter creates additional folders in the Google Drive export folder, it will create independent folder trees per sheet regardless of any overlap in existing folders within the export folder. These will be condensed down when downloaded.