1

I know calculations cannot be turned to manual like in Excel but there is a situation where you can have a series of named ranges adjusted and cause an infinite row expansion that will make the sheet completely unusuable to even use the UI to try to stop it. Is there no "safe mode"?

CodeCamper
  • 6,609
  • 6
  • 44
  • 94
  • You can provide feedback through the sheets help section. – TheMaster Dec 09 '19 at 20:48
  • Does this answer your question? [ArrayFormula is breaking the getLastRow() funtion. Possible workarounds?](https://stackoverflow.com/questions/46883862/arrayformula-is-breaking-the-getlastrow-funtion-possible-workarounds) – TheMaster Dec 09 '19 at 20:55
  • @TheMaster give me a link to your spreadsheet and with one copy and paste I can make it so bad your Chrome crashes. I can't believe there is no way to turn off calculations at least some sort of safe recovery mode... it is so easy to create an infinite looping situation. – CodeCamper Dec 09 '19 at 21:22
  • You don't need to explain the issue. I already understand the issue and have suggested solutions/workarounds. – TheMaster Dec 09 '19 at 21:30
  • @TheMaster yes, thank you for the workarounds but I think I meant for this question to be concerning unforseen formula editing causing permanently uneditable sheet due to infinite loops. – CodeCamper Dec 09 '19 at 22:25
  • AFAIK, There does seem to be a hard limit of 50k rows or something like that. I'm sure you can delete the rows or you can access the previous version from the version history or if worse comes, through drive api. As I said, send direct feedback to Google. That's the best way. – TheMaster Dec 09 '19 at 22:27

1 Answers1

0

You can check the Current limitations, as it says your script, depending on your type of account, you can run it from 6 minutes/execution to 30 minutes/execution. That would stop your script to enter into an infinite loop.

Also, the Usage Limits for the Sheets API states the maximum requests you will be able to do:

This version of the Google Sheets API has a limit of 500 requests per 100 seconds per project, and 100 requests per 100 seconds per user. Limits for reads and writes are tracked separately. There is no daily usage limit.

Furthermore, there is a limit to the number of cells you can have which is 5 million as it says here.

alberto vielma
  • 2,302
  • 2
  • 8
  • 15
  • The problem is when you have too many in cell formulas that spread across millions of cells and won't stop calculating... – CodeCamper Dec 11 '19 at 05:18
  • I recommend you to visit [Public Issue Tracker](https://issuetracker.google.com/), there you can submit your issues(probably a bug) about the ***expaning array formula*** – alberto vielma Dec 11 '19 at 08:37