Translate Text in LibreOffice Calc Using WEBSERVICE() and REGEX()

Someone recently posted an Excel function to poll the Google Translate API and return translated text using LET(), WEBSERVICE() and TEXTSPLIT().

LibreOffice Calc does not provide LET() or TEXTSPLIT(), but it does provide WEBSERVICE() and REGEX().

So here’s how to do the exact same thing without paying Microsoft for things that are free and work just as well. Oh, and it also uses ENCODEURL() on the input text.

BTW, since LibreOffice doesn’t offer a JSON-parsing function I’ve been using Python’s dicttoxml2 within my FastAPI APIs’ GET endpoints to return XML with an "?xml=True" query parameter.

This makes it possible to use FILTERXML() with an XPath expression (see XPather.com ), which means I can consume REST APIs that return XML from within LibreOffice spreadsheets. This is basically how TECTRA Ltd’s various spreadsheet templates pull information from various internal microservices for customer information, product item code / inventory status / pricing, past invoicing details per customer, order status, etc.

Finally, here’s a StackOverflow explanation of more Google Translate API query parameters that also clarifies the &dt=t part of the query: Google Translate API Query Parameters

Screenshots