0

I have a Google Sheet which is becoming unresponsive on startup for 2-3 minutes. I have disabled all formulas and scripts and the freezing actually starts AFTER the "calculating formulas" loading bar (top-right) finishes. Is it possible that somebody has hidden a script ? How can I find it ? Shouldn't all scripting projects be visible to everyone ? Could the hidden script come from an add-on someone installed ?

Some background info:

  1. I noticed that copies don't not have the issue, so the culprit is something that does not get copied over when making a copy (ex: triggers). There are no third party apps using it that I know of , since nobody in the company codes in apps script (except me) so it's not like that sheet is being used somewhere and the copy not.

  2. on one account it loads fast now (after I toggled make available offline on/off - now it's off. On all other accounts it's slow.)

  3. Also it looks like a front-end javascript error, not a loading error since the tab simply becomes unresponsive.

  4. On view only accounts the issue is also not present.

    Update: #2 worked so far on two accounts but it seems a bit scary to use this since we're afraid data might become out of sync..

emanuel cristian Mocean
  • 317
  • 3
  • 16
  • 1
    Try removing all the editors and add them back. Try checking tools> script editor>view> executions> clear Ran as Me "filters" at the top – TheMaster Apr 29 '20 at 20:35
  • Try what the @Master said and before adding the editors back test if the same issue persists. Also, check the bounded scripts to your spreadsheet to make sure you do not have an unwanted piece of code (go to **Tools -> Script Editor**). Let me know how that goes. – Mateo Randwolf Apr 30 '20 at 07:26
  • I tried watching the executions (thanks @TheMaster) and I noticed there are 2 add-ons running on startup (Power Tools and Sheet Go). I'll suggest disabling those. Removing editors is also not within my prerogatives, but I'll suggest it. For now they have agreed on the "side effect fix" of toggling on/off once the "Make Available Offline" button. But they are afraid of syncing issue (seeing old data), even though I left that option unchecked after toggling it. Are those fears legit ? – emanuel cristian Mocean Apr 30 '20 at 12:13
  • @emanuel As long as you have consistent internet, I don't think there's anything to fear. – TheMaster Apr 30 '20 at 13:37
  • thanks again @TheMaster. Any idea which are the things which don't get ported when making a copy ? The copy opens blazing fast (5 seconds) while the original opens slow (even with the offline trick still takes more than 30 seconds). I know triggers are one of the things that don't get copied , but I already ruled them out. I also ruled out formulas by commenting. (We cannot just use the copy since there are many docs linking to the original) – emanuel cristian Mocean Apr 30 '20 at 15:23
  • Triggers don't get copied. How did you rule them out? If addons does something, they take time. Other than that, revisions don't get copied. But it's highly unlikely that that's the issue – TheMaster Apr 30 '20 at 16:38
  • I ruled triggers out by disabling all of them. The sheet still loads slow even if there are no triggers (also no formulas and no onOpen scripts). – emanuel cristian Mocean Apr 30 '20 at 18:14
  • Just FYI , one sheet user claims the freezing started around the period when per cell history was introduced. That would make sense because our version is history is huge with lots of members doing edits simultaneously and the copy doesn't have that revision. – emanuel cristian Mocean May 01 '20 at 18:27
  • Hi ! Have you tried disabling the version history (you could store it somewhere before doing so that relevant historical data does not get deleted )? Have you found a solution to your original question of *disabling hidden google sheet scripts*? If you, could you please formalise it into an anwer? Thanks! – Mateo Randwolf May 05 '20 at 07:16
  • @MateoRandwolf I haven't found a solution to the original question. We are just going to start using a copy. There is no way to disable version history (except by making a copy) – emanuel cristian Mocean May 05 '20 at 15:02
  • Ok that sounds like a good workaround for this issue plus I do not think there are any other ways for finding if scripts are using this sheet apart from the ones mentioned in the comment section. Could you formalise it into an answer so that anyone with similar issues finds it easily? – Mateo Randwolf May 06 '20 at 14:11
  • ok, since it's passed quite a long time and there are no other advices I think I'll just do that. – emanuel cristian Mocean May 06 '20 at 16:50
  • 1
    I wasn't notified of your reply because you didn't tag me(Not that I have anything more to offer). You can however give feedback to Google of the issue. – TheMaster May 06 '20 at 17:35

1 Answers1

2

According to the comments I received all bound scripts should be visible in the scripts.google.com > executions and there is no way to track any 3rd party apps using the sheet. Since disabling formulas and scripts didn't help my solution was to just make a copy. I am guessing that the huge "version history" is the culprit, especially because the unresponsiveness started soon after Google published it's "cell history" feature.

emanuel cristian Mocean
  • 317
  • 3
  • 16