Google Sheets download sometimes corrupt

[slithytove]

I’m using LibreOffice 5.2.7.2 (X64) on Win10. This problem is known also to occur on a Mac running a recent version of LO.

I have a Google sheet which is auto-generated by a script. I want to download this into LO Calc and process it further locally. If I download it as a .xslx file and load this into Calc, all is fine. But if I download it as a .ods file, then date/time fields which have a time (HH:MM) of 20:00 appear corrupt and display as 00:00 with no date. This specific time is the only one that I’m aware of that goes wrong; it’s possible there are others, I haven’t tried them all.

Because there is an obvious workaround, this isn’t a serious problem. But I would have expected .ods to work at least as well as .xlsx, so I’m puzzled. It’s not clear whether the bug is in LO or in Google’s .ods translation.

I attach an extract from the content.xml file in the downloaded .ods, named example.ods. Note this is not an ods format file but an xml extract, but this forum apparently doesn’t allow me to upload .xml files. This shows the encoding for two lines of the spreadsheet (I have slightly edited the names for privacy reasons). The first displays correctly, the second does not. I’m not an expert, but there are (to me) no obvious differences in the way these lines are encoded.example.ods

[erAck]

Your LibreOffice version is too old. Google exported spreadsheet documents are known to be broken and contain wrong cell data types (text instead of numbers and dates) in some cases for which workarounds were implemented in later LibreOffice versions. I suggest you upgrade to the latest stable release (currently 6.0.6) or at least 5.4.7

Anyway, in the partial sample you uploaded you can see that the cells’ office:time-value is odd, for the good case it’s PT1037317H00M00.000000419S and for the bad case it’s PT1037324H0-1M59.999999581S which is broken. Apparently Google has a bug with rounding time values.

[slithytove]

Thank you for your reply. I agree I should have updated a while ago. I have installed 6.0.6.2, but it makes no difference. A colleague working on a Mac with 6.0.6.2 also gets this issue.

I also agree that Google encoding date/time fields as text isn’t very sensible. But LO Calc copes with this in most cases. Why should it be data sensitive to fields containing 20:00 and possibly a few other times? It smells like a bug to me.

[erAck]

Please don’t add comments as answers, which they aren’t.

Anyway, in the partial sample you uploaded you can see that the cells’ office:time-value is odd, for the good case it’s PT1037317H00M00.000000419S and for the bad case it’s PT1037324H0-1M59.999999581S which is broken.
Apparently Google has a bug with rounding time values.

Also note that you formatted an entire date+time as just time in both cases (hence the 1037317 respectively 1037324 hours), which isn’t a problem though.