LO Document has become unusable .....?

English
#1

Hi All,

Hoping someone can help. I have hit a problem with LibreOffice.

I sent a working version of my LO document (.ods format) to a colleague who added some raw data to the sheet. I got it back and was working on it all day yesterday, saving interim copies as I went along throughout the day.

The sheet was getting slower and slower as time went on (it is quite a large and complicated spreadsheet) until I suddenly got the “pizza wheel of death” (Apple Mac). I had to force quit, and ever since then, every copy I made thoughout the day, takes forever to load, and once it does, anything I do, including simply trying to navigate around the sheet, effectively freezes LibreOffice.

I’ve tried doing a “Save As…” immediately upon open, but that just never completes, and as best as I can tell, it’s “crashed” LO. I’ve waited for 15 minutes sometimes hoping it will clear, but I always end up having to force quit.

Smaller, simpler, totally unrelated documents seem to work fine. Even the original I sent my colleague, and the first working version he sent me back, appear to be functioning as expected.

All other “Save As…” copies exhibit the same problem, including the very earliest.

Anyone got any ideas?

Thanks in advance.

CT

Is the new LET function more efficient?
#2

I am not sure that I could add anything, I don’t have a clear understanding of LET.
If your colleague uses Excel then it could be a result of internal conversion to and from .ods
Sometimes copying everything to a new spreadsheet removes some corruption but for complex sheets it might be tricky.
Have a look in Navigator for unexpected items.
It might be quicker to go back to the original emailed spreadsheet and add the raw data by pasting as unformatted text then rebuilding today’s work.

#3

Hey Earnest,

Thanks for the reply.

I’m not sure that what I’m experiencing is directly related to LET, as I’ve had LET implemented in a couple of key areas of the sheet for a few days now, but I wouldn’t put it past that being related to the issue somehow.

He’s definitely on the same build of LO as me, well he fresh installed about a week ago, so there is some justified assumption in that statement.

Yeah, that’s what I’m trying to avoid. Could be a major headache.

I fear you may be right. A whole day’s work down the chute. A hard pill to swallow. Damn it!!

The daft thing is, I can get the sheet to open, but as soon as I try to “Save As…”, it’s goodnight Irene, so it’s something to do with what I added yesterday infecting the system, even across several iterations of earlier copies that were working fine as I went along.

It’s like a corruption of a user pref or some such?? I dunno. Frustrating. I wonder what timezone @mikekaganski is in??

Thanks for the reply.

Regards.

CT

#4

Please test in safe mode, Menu/Help/Restart in Safe Mode

#5

Thanks Mariosv,

I’m trying that as we speak. Not sure which settings to “check” in the safe mode restart box. Experimenting with that now.

Cheers.

CT

#6

Continue in Safe Mode. Is the file still crashing? If not the it is User Profile, if it still crashes then it is the file, probably.

#7

Thanks Earnest,

So, I managed to get a “Save As…” to go through on a recent version of the file. Not sure what settings made it work in the Safe mode. I tried it with several different settings in a row, so maybe there’s a cumulative effect of rewriting certain prefs or some such.

The file is loading under normal restart now, but feels very creaky. Saves take forever, but they do work eventually for now. :angry:

I’m wondering if there’s some effect of having “round trip’d” it to my colleague and back?? Is that possible do you think?

One strange thing I noticed was, I’d been struggling with getting some conditional formatting to “take” on some data columns using “Formula is…”. I was busy with a million other chores in the sheet, so I knew I’d come back and unpick that problem later. The very first time I opened his returned file, I saw one of the columns had my conditional formatting suddenly show up, but it subsequently stopped working again. Given my current problems, that’s just one of those things that makes you go “Hmmmmm…”

Thanks again for the feedback/help.

Cheers.

CT

#8

As it is working now: can you post an anonymized copy here?

#9

OpenCL and Skia/Vulkan are turned off, see First steps to take before submitting a bug - The Document Foundation Wiki

It still sounds like an issue with the file but you could click Tools > Options > LibreOffice > View and tick the box Force Skia software rendering, allow LibreOffice to restart and see if there is an improvement in performance.

#10

Guys,

I need to run some chores for a couple of hours. I will be back to this when I get home again.

MS777. Are you a formularisation expert by chance? Know anything about LET functions??

CT

#11

… LET functions are quite new for me, too. But I have ~20 y experience in finding problems in OO documents
Before you submit a bug, it is always good to exclude that the bug is connected to some specific properties of your installation …

#12

Yesterday, while testing ‘=LET()’, something inexplicable happened to me when I tried to enter something. LO automatically converted this function into a Matrix{=…} function block, which has since been missing ‘LET()’.
So far I haven’t been able to get a ‘=LET()’ function to work.

#14

Hey ms777,

Thanks for the offer to take a look at my misbehaving file. Link below. It was slightly too big (4.3mb) to upload here.

I’m not entirely sure how, as I’ve been flailing around with a million restarts and forced quits over the past 24 hours with it, but I’ve managed to get it working through (I think) running it in safe mode. It’s still pretty crotchety though, Scrolls like mollases. “Calculates” (can’t run with AutoCalculate on, heaven forbid) and “Saves” taking a long time.

That’s what I’m trying to ascertain. I am maybe too much of a newb to recognise what I should be looking for, but anyway. it will be very interesting to hear back how well/if it runs on your machine as a comparison test.

A couple of days ago I introduced my first attempt at a LET function. That’s in column BR “Final Exit Event Flag”. That didn’t seem to cause any undue crash tendencies (maybe slow down behaviour, not sure), but yesterday I also introduced another LET function to column AD “Buy Flag Row [in MR]”. I am suspicious that this is around the time when the trouble started.

Thanks again for the offer of help. Much appreciated.

Regards.

CT

#15

Hey Earnest,

It looks very much like:

did the opposite and made matters worse, but I have just read through the Wiki document and ohers linked there too, and I’m on the trail of trying to swap out my current user profile etc.

ms777 above is also hopefully going to take a look at my file as well, so maybe that will zone in on the specific problem here.

If you feel inspired and have the time, feel free to download my file from the post above too.

Cheers.

CT

#16

Hey Koyotak,

Thanks for the additional info.

As I mentioned to ms777 above, I am very suspicious that the LET function may be the cause of my problems. Amongst other things I’m trying, I’m just about to try ridding my sheet of all LET functions now as a test.

I wonder if LET is not quite ready for primetime!!?!

Cheers.

CT

#18

CT-Shares Backtester for ms777-2.ods (167.8 KB)
Hi,

Have a look at this. I have removed the bottom 100.000 lines. I hope I have not removed some lines carrying data. My gut feeling is that the size is the problem. Yor content.xml contained 128 MByte. I have a deeper look tomorrow

#19

Hey ms777,

Thanks for this.

Being a bit of a beginner, I couldn’t work out if the amount of data I was testing with was a small, normal, or astronomical amount of information for the average spreadsheet. This is my first serious attempt at building anything like this, so my inexperience certainly shows.

Great! I’ll take an informed gut feeling at this stage.

Yes, well the final section over on the righthand side (MR Master Reference candle times @ 30 sec) WAS 57K odd rows long, so it’s obviously not a typical “real world” example to cut it down as much as you have done, but it’s an excellent experimental scenario, especially if the amount of data looks excessive to you.

What’s the content.xml size? Is that the raw data? Is 128 Mb a lot, or a little?

I’m just about to embark on a test to put the bloody thing on a diet!!

I’ve been using ChatGPT to help with certain approaches to things I’m less than optimally skilled to handle, and it has mentioned several times that it thinks my Dynamic Named Ranges should be converted away from using OFFSET and COUNTA, as they are “Volatile”. Would that explain the length of time my Calculate (F9)'s take and file Saves take??

Any thoughts on this or other approaches to slimming the damn thing down some??

Thanks again for your help. Much appreciated.

CT

#20

Hi,

I did a little further clean up:
I have removed some 50,000 empty columns on the right side
There was an orphan object on the left side. You can see that in the Navigator pane. I removed that, too

I did not try to understand the logic behind all formula, it is too complex. But here some observations:
You use a mixture of normal functions and array functions (like ={…}, e.g. in col V). I am not sure if array functions are necessary. I recommend to use them only if you are exactly knowing what you are doing.
In your Named Ranges there is a mixture of upper row bounds, e.g. 187, 3617, 97989, 1048576. Maybe this is partly caused by me cutting the rows, but you should look over that. Please note that the handling of complete columns (e.g. B:B) in LO is not possible like in Excel

All office documents are zip files. If you copy your .ods, rename it to .zip, open, you will find the content.xml I was mentioning

Good luck,

ms777

CT-Shares Backtester for ms777-3.ods (182.7 KB)

P.S.: There is no indication that the LET function is the culprit …

#21

As of today, 2025-04-20, that is possible. It was implemented several versions ago.

#22

Ýou are right, thanks for pointing that out. I completely missed that …

@Clearwell_T: That will probably simplify some of your Named Ranges / Formula …

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.