Google Translate in LibreOffice Calc

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.

The function uses the site to translate the text "Google Translate" and can be used in any formulas both independently and together with other standard functions.

StarBASIC code for function Google Translate

To add function Google Translate , 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 the AJAX request 
  Set XMLHTTP = CreateObject("MSXML2.ServerXMLHTTP")
 
  XMLHTTP.Open "GET", WebsiteURL, False  
  XMLHTTP.Send
 
  ' We create an HTML document using the response text of the 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 worksheet LibreOffice Calc , select any cell and use our new feature GoogleTranslate_YouLibreCalc() .

Using the extension

You can also use the GOOGLETRANSLATE() function by installing the free extension YouLibreCalc.oxt or its full-featured version YLC_Utilities.oxt .

After that, this function will be available in all files that will be opened in LibreOffice Calc.