4

I'm using a library nikaia/translation-sheet who basically pulls all the translations from the Laravel site into a google spreadsheet making it "easily" translatable with =GOOGLETRANSLATE(A1)

The problem comes with the parameters:

  • :price
  • :amount
  • :etc

So I've got the idea to substitute ":" with @nonmakingsenseworblablaprice so Google couldn't translate example:

=SUBSTITUTE(GOOGLETRANSLATE(SUBSTITUTE(B2;":";"@nonmakingsenseworblabla");"ES";"EU");"@nonmakingsenseworblabla";":")

Well, not sure why Google eats some letters and puts new ones:

:amount de saldo -> @nonmakingseseworblatamount of saldo

So I decided to do something like detect the parameter and change :amount to :a_m_o_u_n_t and that is apparently working and not being weirdly parsed converted or translated.

I was looking for a solution and found a similar idea but having problems migrating it to spreadsheets script plus is not detecting the parameter

Any one knows how to detect all :parameters in a sentence and put a symbol, slash, dash etc between the characters or letters? Example:

The amount :amount for this order number :order_id is :price

I've also tried regex but not been lucky so far

=REGEXREPLACE(GOOGLETRANSLATE(REGEXREPLACE(B22; ":(\w)([\w]+)"; "{%$1_$2%}"); "ES";  $C$1); "{%(\w)_([^_]+)%}"; ":$1$2")

There's a regex to select the spaces between letters, but good luck making that in excel or spreadsheets. Demo

TrOnNe
  • 1,632
  • 16
  • 30

1 Answers1

1

Finally I've created a script to avoid parameters translation:

  function translate(cell, lang) {
  const content = cell.toString();
  const keys = [];
  const enc = content.replace(/:([\w_]+)/ig, function(m, param) {
    const n = `[§${keys.length}]`;
    keys.push(param);
    return n; 
  });
  return LanguageApp.translate(enc, "es", lang).replace(/\[§(\d+)\]/ig, function(m, param) {
    return `:${keys[param]}`; 
  });
TrOnNe
  • 1,632
  • 16
  • 30