/docs/community?hl=en
This content is likely not relevant anymore. Try searching or browse recent questions.
Exporting a Google sheet, IFS() and CONCAT() doesn't work in LibreOffice (.ods) 0 Recommended Answers 4 Replies 0 Upvotes
1 Update
$0 Updates
1 Recommended Answer
$0 Recommended Answers
1 Relevant Answer
$0 Relevant Answers
0
When I export (download) a Google sheet which contains formulas with IFS() or CONCAT(), the formulas are not correctly recognized in LibreOffice 7.0.3.1 (x64). Instead of the uppercase IFS(...), the function name is in lowercase (ifs(...)), and I have to manually change something in the formula to make the function work (e.g. replace a parenthesis, replace the lowercase text with uppercase text, etc).
IF() and other functions on the other hand works just fine.

Unpacking the .ods file and taking a look at the included content.xml, the "table:formula=" property contains lowercase ifs() and concat() strings, but an uppercase IF(), which as described works just fine in an exported file, so I suspect this is a bug while exporting?

I've created a simple test sheet here:
https://docs.google.com/spreadsheets/d/1lNxZ9pkXicLSQp4_xnAVACwWEqZ_ic_KP8OHM4tlsGg/edit?usp=sharing
Relevant Answer Relevant Answers (0)
All Replies (4)
Relevant Answer
With the help of erAck from the LibreOffice support forum it seems to become clear that this is indeed a bug on Google's site when exporting to .ods.
When I replace the table:formula="of:=ifs(...)" string with table:formula="of:=COM.MICROSOFT.IFS(...)" in the content.xml within the exported downloaded .ods file, I could successfully open the file with the formula working (and the same is true for CONCAT).

In erAcks words:
"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."

List of LibreOffice OpenFormula Extensions
marked this as an answer
Relevant Answer
Good research Spoon.  It would be best if you made Google aware of it. (there are no G employees here on the forum)
 
If you'd like to influence future versions of Google Sheets, Feature Requests (and bug reports) are encouraged. You can submit your idea using the HELP > Help Sheets Improve / Report a Problem menu. I know that those submissions are valued by the Sheets team.
marked this as an answer
Relevant Answer
Thanks, I'll try my luck there. Googling for a way to report a Google bug didn't actually produce any meaningful results (how ironic), hence I posted here.
marked this as an answer
Relevant Answer
That's funny Spoon ;-)

As for luck, I should point out that  you will not receive a response from your message via the In-Product feedback tool. It's simply Google's way of keeping ear to the ground, but it's not a customer service interaction. It's still worth your while to report and document as best you can. You particular situation is not something that we hear about on the forum. Point is, if you don't report it, they'll never know about it. ODS is an underappreciated export format!

Best,
Lance
marked this as an answer
This question is locked and replying has been disabled.
Discard post? You will lose what you have written so far.
Write a reply
10 characters required
Failed to attach file, click here to try again.
Discard post?
You will lose what you have written so far.
Personal information found

We found the following personal information in your message:

This information will be visible to anyone who visits or subscribes to notifications for this post. Are you sure you want to continue?

A problem occurred. Please try again.
Create Reply
Edit Reply
Delete post?
This will remove the reply from the Answers section.
Notifications are off
Your notifications are currently off and you won't receive subscription updates. To turn them on, go to Notifications preferences on your Profile page.
Report abuse
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

Reported post for abuse
Unable to send report.
Report post
What type of post are you reporting?
Google takes abuse of its services very seriously. We're committed to dealing with such abuse according to the laws in your country of residence. When you submit a report, we'll investigate it and take the appropriate action. We'll get back to you only if we require additional details or have more information to share.

Go to the Legal Help page to request content changes for legal reasons.

Reported post for abuse
Unable to send report.
This reply is no longer available.
/docs/threads
//accounts.google.com/ServiceLogin
You'll receive email notifications for new posts at
Unable to delete question.
Unable to update vote.
Unable to update subscription.
You have been unsubscribed
Deleted
Unable to delete reply.
Removed from Answers
Removed from Updates
Marked as Recommended Answer
Marked as Update
Removed recommendation
Undo
Unable to update reply.
Unable to update vote.
Thank you. Your response was recorded.
Unable to undo vote.
Thank you. This reply will now display in the answers section.
Link copied
Locked
Unlocked
Unable to lock
Unable to unlock
Pinned
Unpinned
Unable to pin
Unable to unpin
Marked
Unmarked
Unable to mark
Reported as off topic
Known Issue
Fixed
Marked Fixed
Unmarked Fixed
Unable to mark fixed
Unable to unmark fixed
/docs/profile/0