Google Translate in LibreOffice Calc

YLC

Description

The standard functionality of the LibreOffice Calc program does not include functions for translating text into other languages. However, any user can create such a function independently.

In this article, we will look at the custom function GoogleTranslate_YouLibreCalc(), which will allow you to translate text directly in a cell on any worksheet.

For text translation, the function uses the "Google Translate" site and can be used in any formulas both independently and together with other standard functions.

BASIC code for the Google Translate function

To add the Google Translate function, open the menu Tools - Macros - Edit Macros..., select Module1 and copy the following text into the module:

Option VBASupport 1

Function GoogleTranslate_YouLibreCalc(TextToTranslate As String, SrcLang As String, TrgLang As String)
  ' moonexcel.com.ua 
  Dim FCalc      As Object
  Dim WebsiteURL As String  
  Dim XMLHTTP    As Object
  Dim oHTML      As Object
  Dim HTMLDoc    As HTMLDocument
  Dim ObjClass   As Object
    
  FCalc = CreateUnoService("com.sun.star.sheet.FunctionAccess") 
  TextToTranslate =  FCalc.callFunction("ENCODEURL", Array(TextToTranslate))
  
  SrcLang = LCase(SrcLang)
  TrgLang = LCase(TrgLang)
  
  IF SrcLang = "zh-cn" Then SrcLang = "zh-CN"
  IF SrcLang = "zh-tw" Then SrcLang = "zh-TW"
  
  IF TrgLang = "zh-cn" Then TrgLang = "zh-CN"
  IF TrgLang = "zh-tw" Then TrgLang = "zh-TW" 
    
  WebsiteURL = "https://translate.google.com/m?sl=" + SrcLang + "&tl=" + TrgLang + "&hl=en&q=" + TextToTranslate
    
  ' We execute the API call to the web server using an AJAX request 
  Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
 
  XMLHTTP.Open "GET", WebsiteURL, False  
  XMLHTTP.Send
 
  ' We create an HTML document using the response text of an AJAX request 
  Set oHTML = CreateObject("HTMLFile")
  With oHTML
        .Open
        .Write XMLHTTP.responseText
        .Close
  End With
  
  ' We convert HTML text into an object model using the library of web elements Microsoft HTML Object Library 
  Set HTMLDoc = oHTML  
  
  Set ObjClass = HTMLDoc.getElementsByClassName("result-container").Item(0)
  If Not ObjClass Is Nothing Then
    GoogleTranslate_YouLibreCalc = ObjClass.innerText    
  End If 
 
  ' We free the memory 
  Set ObjClass = Nothing
  Set oHTML    = Nothing
  Set XMLHTTP  = Nothing  
End Function

Next, close Macro Editor and return to the LibreOffice Calc worksheet, select any cell and use our new GoogleTranslate_YouLibreCalc() function.

Using the YouLibreCalc extension

You can also use the function GOOGLETRANSLATE() by setting the extension " YouLibreCalc.oxt ". After that, this function will be available in all files that will be opened in LibreOffice Calc.

VIDEO TUTORIAL