Google Spreadsheet freezes when done running App Script

2
votes

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.

1
google-apps-script
How much work are the formulas doing? Could they be recalculating after you delete the row? – Spencer Easton
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
@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
@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

1 Answers

0
votes

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