Importing IFS() and CONCAT() from Google Sheets into Calc

[sp00n]

Neither of these two functions seem to correctly import into LibreOffice 7.0.3.1 (x64). When I open the downloaded .ods (or .xlsx) sheet, the formulas show up as lowercase (so e.g. “ifs(…)” instead of “IFS(…)”) and only display a #NAME value.
When I replace the lowercase function names with their uppercase counterparts the formulas do work just fine.

I’m not sure if this is a bug with LibreOffice or Google Sheets, as when I unpack the .ods file and take a look at the included content.xml file, the “table:formula=” property also contains lowercase ifs() and concat() values, but an uppercase IF(), which works just fine in an exported file.

Taking a look at the .xlsx file however, after unpacking it and opening the included sheet1.xml file, the “< f>” tag value contains uppercase IFS(…) and CONCAT(…) values. But it’s still not working when I open the .xlsx file in LibreOffice, the formulas are still displaying as lowercase and the value as #NAME.

[anon73440385]

Might be a language thing but can’t figure out without a sample file.Google encodes languages differently; e.g. English simply as {en} while LibreOffice has en-US, en-GB, en-ZA,… (or their respective telling name). Since LibreOffice function names depend on the language settings (or the translation of function names used in a document into the language of your current environment) everything in turn depends on a unambiguous recognition of the document’s language.

[sp00n]

Here’s a sample Google Sheet:

Export Test #2

It’s English <-> English, so both the English Google Sheets and an English LibreOffice version. I just downloaded from a German localized Google Sheet version (where the IFS() and CONCAT() formulas actually aren’t translated, in contrast to IF(), which becomes WENN()), and it still wouldn’t work for those two.

[anon73440385]

Ok - I see, not a language thing and a simple remove+add of the last closing parenthesis ) makes the the formula functional (This is just like text formatted cells behave, if you enter a formula and remove the text formatting by using CTRL+M. You need to perform any edit operation for the formula recognition to be triggered once more).

From my pov a bug (esp. since it is the same, if downloading as an `.ods` file from Google)

[erAck]

IFS() and CONCAT() are not defined in the Open Document Format OpenFormula (ODFF) standard and as such are an extension to the standard. Google exports them wrongly as ifs() and concat() (as if they were covered by the standard) instead of COM.MICROSOFT.IFS() and COM.MICROSOFT.CONCAT() and hence are imported as unknown functions.

You may want to complain at Google about that.

If you re-edit the formula in an English function names using UI then the functions are recognized and accepted.

[erAck]

Fwiw, there’s a List of LibreOffice OpenFormula Extensions.

[sp00n]

Indeed that seems to be the case. I’ve replaced the table:formula="of:=ifs(...)" with table:formula="of:=COM.MICROSOFT.IFS(...)" in the content.xml within the .ods file, and could successfully open the file with the formula working (the same is true for CONCAT).

I had also created a post in the Google support section, but have not yet received any answer there:
https://support.google.com/docs/thread/92777310

[erAck]

Note that you somehow duplicated the extension list URI in that forum post so it doesn’t link to the wiki page mentioned.

However, if you create a Google problem report as indicated there, could you please link back to it here? Thanks.

[sp00n]

Yeah, I just fixed that. I’m currently writing the problem report and will include a link both to here and the Google support post.