Error "Result was not expanded automatically, please insert more rows": how to add rows automatically?

user_1992004 0
Viewed 5845

I use an ImportRange formula to get data from multiple tabs into single one. The formula looks like

=QUERY({IMPORTRANGE("123","B1!A2:D");IMPORTRANGE("123","B2!A2:D")},"where Col1 is not null", 0)

Is it there a way to add rows to table automatically if they are needed? With formula or setting - not by app script.

google-sheets-formula google-sheets
1 Answers

I had the same problem

Added another "arrayformula" to my original formula:

=INDEX(QUERY(...))

Made some tests and appeared all array-like formulas will add new rows automatically, including query. This means adding a new array function to your original formula will force the engine to recalculate again and add new rows. In general, this case looks like a bug / not expected behavior. The reason may be in the limit of resources the formula has, and it stops the work if calculations are heavy.

Limit test: Sequence

This formula will work:

=SEQUENCE(50500)

This will produce an error:

=SEQUENCE(50501)

After you add a new row manually, the formula works again.

Adding new formulas like INDEX(SEQUENCE(50501)) does not fix the error.

Limit test: array-like functions

I've also tested these functions:

QUERY

{Array}

SEQUENCE

Offset

Sort

Index

ArrayFormula

All of them stopped working after row 50501 including.

Possible Script solution

Possible to get the number of rows from the original formula.

=ROWS(SEQUENCE(50501))

Next, create a trigger to execute every N minutes to add new rows to your sheet.

Source

All tests were made by date and in my environment. Your own tests may vary.

user_53724000
Related