Saving changes to sheet freezes in all browsers, all users

jcaf

Hello Community!

 

We are having a major problem with a sheet that our company uses. We've reached out to Smartsheet for help, but the issue is persistent. 

 

Information on the sheet:

It's used by about 15 employees

It has 550 rows and just over 37,400 cells

This issue has been consistent for the past month, November 2019

We are only using a little over 5GB of over 1000GB available to us

 

The issue:

When any of our employees edits a cell and then saves the changes, the sheet will freeze while notifying it is saving changes. This frozen state will go on for 5 - 10 minutes, then it will time out. The changes will not be saved. The only thing that will save, without any issue, are comments made to the sheet.

 

Troubleshooting steps we've tried:

Opening the sheet with different browsers. (Chrome, Firefox, Safari, Edge)

Clearing the cache and cookies from all browsers before they try to access Smartsheet

Disabling any browser extensions (most of our employees do not have any extensions)

Monitoring our network logs for disruptions in service, for on prem employees. Our mobile employees are reporting the same issue.

Smartsheet has refreshed the sheet from their side

Scouring the Community Forums for answers

 

Attempts for remediation:

We are now attempting to create a new instance of the sheet. For the new sheet, we are trying to use the Save As New function, with the option unchecked for Attachments. We have not been able to do this successfully either, as it creates the new sheet WITH attachments, instead of without. We have attempted to create this new instance a few times, with the same results.

 

This sheet is vital to our work. We are looking for any additional help in the matter. 

 

Cheers!

J

 

Paul Newcome

How complex is the sheet? If you have a lot of complex formulas or a lot of formulas (even if they are spread out) running complex tasks, it can bog things down considerably.

.

A few other things that have an impact on sheet functions would be...

 

Using specific row numbers instead of @row when able.

Conditional Formatting (huge impact on sheet speeds).

Cross-sheet references in formulas.

Cell Linking from other sheets.

Automations.

.

What is the sheet used for?

Is it possible to "clean up" old rows by moving them to an "Archive" type of sheet?

Are there old formulas running that aren't needed anymore?

Obsolete Cross-sheet references that could be deleted?

Metrics being pulled that are no longer needed or can be pulled more efficiently?

Cells containing a lot of data?

Formulas that reference a lot of data all at once?

.

Those last two are geared towards the 4,000 character limit per cell.

Manually entered or generated by a formula, you cannot exceed 4,000 characters per cell (including spaces). Exceeding that limit will cause the sheet to crash when you attempt to save it.

 

Another side to it is this...

 

Even if only one letter is displayed, if a formula is looking at multiple cells that allows a string of more than 4,000, it too will crash. For example...

 

Cells 1, 2, 3, 4, and 5 each only contain 1,000 characters.

 

If I were to JOIN those cells together, it would give me 5,000 characters. That will crash a sheet when you try to save it.

=JOIN([Column Name]1:[Column Name]5)

.

Here's the tricky part...

 

=MID(JOIN([Column Name]1:[Column Name]5), 34, 1)

 

Would only generate a single character, but... because the text string it is looking through contains more than 4,000 characters, this too will crash a sheet.

.

So if you have 550 rows and a formula looking down that column in a JOIN function (maybe you are trying to find out if a particular piece of data is found or something), each of those cells could only contain 7 characters.

.

These are just a few things to take into consideration. 

 

I would suggest first seeing if you can move rows to a different sheet such as an Archive or something to that affect. This would lighten the initial load on the sheet. I would also write down my conditional formatting and automation rules then delete them from the sheet. These initial few steps should free things up so that you can look at any formulas and such to see if you can make things more efficient.

 

Once you have cleaned up the sheet, I would really take a look at my automations and conditional formatting to see if there are any that are redundant or unnecessary.

 

Then you can start adding things back in one at a time, saving after each.

jcaf

Thank you so much for your response.

We will start going over the sheet and note all of functions that could be causing impacts, using your suggestions. 

We will let you know how it goes.

 

Cheers!

J

Paul Newcome

Happy to help! 

 

I hope you're able to get it figured out.