CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
Zachary Kiefer
Zachary Kiefer

Reputation: 9

Limit the number of row in Google Sheets

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);

Upvotes: 0

Views: 8956

Answers (1)

TheMaster
TheMaster

Reputation: 50382

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

Upvotes: 1

Related Questions