We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

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

asked 2021-01-09 16:49:16 +0200

sp00n gravatar image

Neither of these two functions seem to correctly import into LibreOffice (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.

edit retag flag offensive close merge delete


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.

Opaque gravatar imageOpaque ( 2021-01-09 17:20:35 +0200 )edit

Here's a sample Google Sheet: https://docs.google.com/spreadsheets/...

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.

sp00n gravatar imagesp00n ( 2021-01-09 17:34:01 +0200 )edit

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)

Opaque gravatar imageOpaque ( 2021-01-09 17:49:12 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2021-01-11 20:15:59 +0200

erAck gravatar image

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.

edit flag offensive delete link more


erAck gravatar imageerAck ( 2021-01-11 20:43:19 +0200 )edit

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

sp00n gravatar imagesp00n ( 2021-01-11 21:54:52 +0200 )edit

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.

erAck gravatar imageerAck ( 2021-01-11 22:39:06 +0200 )edit

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.

sp00n gravatar imagesp00n ( 2021-01-11 22:48:42 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-01-09 16:49:16 +0200

Seen: 69 times

Last updated: Jan 11