2

I have an app script attached to my Google Spreadsheet. There is a function that does a bunch of stuff withing the spreadsheet. Here is what it does.

  1. Create a new Sheet
  2. Set column header for new sheet
  3. Copy data in new sheet
  4. Set Formulas
  5. Delete Extra columns
  6. Delete Extra rows
  7. Add A row in an existing sheet(Sheet 1)
  8. Add a column in an existing sheet(Sheet 2)
  9. Add Formula in new column in Sheet 2
  10. Edit Value in an existing sheet's cell (Sheet 3).

After these 10 steps are complete, the spreadsheet works normally but after 10-15 secs, the sheet will freeze. To get it to work, I need to refresh the page.

I experimented and I found out that if I eliminate Step 6 i.e "Delete Extra rows", the freeze behavior is eliminated. Why does it freeze on this particular step? How do I fix this? I cannot not delete extra rows. Here is the exact piece of code where it freezes.

var unwanted = newAssignmentSheet.getMaxRows() - (parseInt(i) + 6);
newAssignmentSheet.deleteRows(i+7, unwanted); //FREEZES HERE

Point to note :

If I implement Step 6 in isolation, the Spreadsheet does not freeze.

nupac
  • 2,459
  • 7
  • 31
  • 56
  • How much work are the formulas doing? Could they be recalculating after you delete the row? – Spencer Easton Jan 28 '15 at 13:12
  • If you do all those steps manually, does it work fine? Need to isolate whether the issue is the code, or the spreadsheet itself. If it turns out to be a bug, you can report it. Unfortunately, you might have a lot of work to do to try to simplify being able to recreate the problem so someone else can verify and test it. – Alan Wells Jan 28 '15 at 15:38
  • @SpencerEaston the formulas are the basic Max, Min, Avg and Median formulas provided by the SpreadsheetApp and the range provided are cells from rows that are not deleted so I dont think they are getting recalculated. – nupac Jan 28 '15 at 16:22
  • @SandyGood if I manually perform these steps it works. The only difference being is it takes about 10 secs to complete all these step using the script but a minute-ish when I do it manually so maybe its the speed at which I perform all these tasks. I am pretty sure I will be able to replicate this issue on a new sheet. All you need to do is perform multiple modification to the spreadsheet out of which atleast one step is deleting rows. The interesting part is if I remove the "deleting rows step" and add multiple extra steps(i added 5), it does not crash. – nupac Jan 28 '15 at 16:26

1 Answers1

0

I discovered that if you leave at least one empty row below the last contingent cell, the spreadsheet will not become unresponsive.

Terence Hoefdraad
  • 11
  • 3