How to translate only text in formatted HTML code using Google Apps Script?

Question

I have been trying to translate text from HTML code. Here is an example:

var s = '<span>X stopped the</span><icon></icon><subject>breakout session</subject>'

When I try =GOOGLETRANSLATE(s,"en","fi") in Google Sheet, it also changes the tags formatting and translates tags into simple text. Whereas the translation should be only for X stopped the breakout session. But that is not the case.

Then I tried this function:

function TransLang(string){

   return LanguageApp.translate(string,'en', 'fi', {contentType: 'text'});
}

This function worked well (for some time), but after that I got an error

Service invoked too many times in one day.

So I am stuck here. Is there any way that we can translate simple text of html code without translating/messing with HTML tags? Is there any regex that can avoid tags and translate all the other simple text?

I hope I am able to state my problem clearly. Please guide me if you have any suggestions. Thank you

Where is this string with tags coming from? Is there any way to get it without the tags or maybe use regex as mentioned on the answer below to remove all the tags and then translating the string? — Kessy, Feb 23 '22 at 15:01
yes, I have done that. I extracted text from the tags but the problem is how to put those extracted tags back to translated text at their exact position. — , Feb 23 '22 at 15:05
You can always create a function that separates each HTML tag and save each piece on an array and translate the text and then build it up again — Kessy, Mar 02 '22 at 08:35

score 0 · Answer 1 · answered Feb 23 '22 at 14:02

0

Is the text you want always inside a single <span>? Or could there be more than one span or other element types?

This works for extracting the inner text from a single <span>:

function getSpanText() {
  let s = '<span>X stopped the</span><icon></icon><subject>breakout session</subject>';
  var text = s.match("(?<=<span>).+(?=<\/span>)")[0]
  Logger.log(text);
  return text
}

answered Feb 23 '22 at 14:02

GreenFlux

52
9

There may be other tags also. Besides, I do not need to extract text from tags, rather I need to translate the above string without translating tags. – Feb 23 '22 at 14:05
1

I think the only way you can do that is break it apart by tags, translate the text and then put it back together. – TheWizEd Feb 23 '22 at 14:10
1

I tried several different tags and the GOOGLETRANSLATE always translates the tag to start with upper case letter. I'm not sure if that is allowed in HTML markup. – TheWizEd Feb 23 '22 at 14:16
Sorry, I misunderstood when you asked for a regex. No, there is no way to translate text with regex. If you solution works but the quota is a problem, I would recommend upgrading to a workspace account. – GreenFlux Feb 23 '22 at 14:17

score 0 · Accepted Answer · answered Mar 08 '22 at 02:04

So, after a lot of digging, I have been able to find what I was looking for.

function Translator(S){

  var sourceLang = "en";
  var targetLang = "fi"; 
  var url =
    'https://translate.googleapis.com/translate_a/single?client=gtx&sl=' 
    +
    sourceLang +
    '&tl=' +
    targetLang +
    '&dt=t&q=' +
     encodeURI(S);

  var result = JSON.parse(UrlFetchApp.fetch(url).getContentText());  
  return result[0][0][0];
     
}

This simple function calls Google translate Api and extracts the result from there. The best thing is you do not have to worry about the tags, as they are not translated by Google, so just the simple text is translated. There is just one limitation in the solution that Api calls are limited, so you can not make more than 5000 calls/day.

score 0 · Answer 3 · answered Apr 28 '22 at 17:06

Why not using LanguageApp.translate as a custom JS-Function (Extensions >> AppScripts)?!

var spanish = LanguageApp.translate('This is a <strong>test</strong>',
                                  'en', 'es', {contentType: 'html'});

// The code will generate "Esta es una <strong>prueba</strong>".

LanguageApp.translate (apidoc) accepts as fourth option a contentType, which can be text or html. For huge tables be aware that there are daily limits (quotas)!

KnausO · Answer 4 · 2023-06-28T14:56:06.110

@Roomi 's script only translated me first line of text.

@fraank got me on the right track.

This is how I did it for the translations inside Google Sheet

Create a new script by going to Extensions>App scripts in the Google sheet toolbar.

Then use this function:

function customTranslate(input) {
  var slovenian = LanguageApp.translate(input, "sr", "sl", {contentType: 'html'});
  return slovenian;
}

When you go back to sheet you can now write this formula where you want translation to appear and add target cell on the brackets:

=customTranslate(E11)

How to translate only text in formatted HTML code using Google Apps Script?

4 Answers4