0

I have a google sheets document that keeps adding rows to the sheet. It slows down my document tremendously because of scripts I run against the document. I only need around 100 rows, but it continues to add several hundred to a thousand plus. Is there a way to limit this with a hard number or set a script to automatically delete any row after 100.

This is a log of orders I am maintaining.

I had considered using a script to

getMaxRows() - getLastRow() + 25 //for future blank rows.

However, I could not get it to function properly. My scripting abilities are limited.

Something akin to.

var Max = getMaxRows()
var Last = getLastRows()
start = 2;
end = Max-Last+25 

sheet.deleteRows(start, end);
TheMaster
  • 45,448
  • 6
  • 62
  • 85
Zachary Kiefer
  • 9
  • 1
  • 2
  • 2
    Before limiting the number of rows of your spreadsheet you should understand what is causing the insertion of new rows as there is the risk that you will be hiding a problem and perhaps that will not make your solution to work as fast as you expect. – Rubén Feb 13 '19 at 19:04

1 Answers1

1

Issue:

You're probably using a badly written auto-iterating array formula function, where the end result of the formula is more than the number of cells in the sheet. The function will automatically create space for the result by inserting rows.

Example:

  • A simple example of a self-iterating arrayformula is provided below. In a "1000 row" new sheet, If you type in this formula, New rows will be inserted upto a total of 50,000 rows, but it varies depending on the device/ browser you use.

A1: 

=ARRAYFORMULA(ROW(A1:A1001))

  • Formula Explanation:

    • ROW(A1:A1001) creates a array of 1 to 1001 and returns them to the sheet
    • But there's no space in the sheet
    • One more row is needed
    • Sheets automatically adds 1 row after row 1000
    • But, If a row is inserted after A1000, The reference A1001 in ROW(A1:A1001) automatically becomes A1002, because a new row is added between A1:A1001.
    • Now, The formula says, =ARRAYFORMULA(ROW(A1:A1002)), so, 1002 numbers, So, we still need a extra row. A new row is added, reference changes again, a new row is needed again and so forth until a hard limit like 50,000 is reached and your formula stops iterating and becomes a #REF error.

Solution(s):

  • Create a copy of your sheet and delete each array formula one by one followed by deleting the rows each time to pin the rogue formula.

  • Pay attention to expanding array formulas like those using SPLIT.

  • Use sane arrayformula methods:

    • Avoid: IF(ROW(A1:A100)=1,"Header",...) Use: {"Header";...}
    • Avoid: Open ended ranges; Use Close ended ranges using INDEX/COUNTA
TheMaster
  • 45,448
  • 6
  • 62
  • 85