1

Querying a small table (11x4 cells), results in sheet expanding to 50.000 rows. Is this expected behaviour or a bug?

Shared example spreadsheet here. Spr is read-only, because experimenting with this will quickly create more than 1 million cells. To replicate the behaviour, please copy the spreadsheet to a new spreadsheet, and add "=" To content in cell Sheet1!P11 to activate the query.

Query: =query(A:D,"select A,B",1)

When entering the above query in cell T1, query works as expected, and returns a 11x2 results table. When entering the same query in, for example, cell P11, the query fails, but 50.000 rows are created. Query fails with "#REF". As far as I can see, there is no content in the 50k rows.

Error message on hovering over cell: Result was not automatically expanded. "Please insert more rows(10)." But still 50.000 rows are created. Surely this is not what should happen? I searched on SE for similar issues, but could not find any.

My .05: The query creates a new row beyond the last row in the table. This then causes the query result to expand to another row, which creates yet another row, etc. until max. rows is reached.

Any insights/references/comments greatly appreciated!

Table in text format:

ID First_name1 Sales Sale_date

1 Linsay 2 6/3/2007

2 Paris 26.53 6/3/2007

3 Britney 1 6/3/2007

4 Nicole 1 6/3/2007

5 Linsay 9.16 7/3/2007

6 Paris 1.52 7/3/2007

7 Britney 43.21 7/3/2007

8 8.05 7/3/2007

9 Linsay 17.62 8/3/2007

10 Paris 24.19 8/3/2007

Jack Rambling
  • 27
  • Looks like comments from SO are not migrated, only the question, so I am posting the comments (containing and answer) provided by @AdamL in the comments on SO. – Jack Rambling Nov 19 '14 at 13:57
  • @AdamL posted this comment on SO:"There are two issues here (IMO): 1. you are using a formula inappropriately, and 2. perhaps Google Sheets could manage it better when users use a formula inappropriately. By referencing whole columns, you will get a runaway effect - if there are 1000 rows to start with, then invoking the formula in row 11 will force 10 extra rows, but then there will be 1010 rows referenced, and so on. Sheets will limit the auto-expansion to 50000 rows (or 300 columns, for sideways expansions). There was a period where the behaviour was that you would receive a #REF! error..." – Jack Rambling Nov 19 '14 at 13:58
  • @AdamL "continued: ...but there was no auto-expansion (ie Sheets seemed to recognise there would be a runaway effect). IMO this behaviour is preferable, because creating 50000 rows can quickly cripple complicated spreadsheets. BTW, this isn't specific to QUERY, you can reproduce the same effect by eg =ArrayFormula(ROW(A:A)) entered below row 1." – Jack Rambling Nov 19 '14 at 13:59
  • Then I commented (with a thanks bc I couldn't upvote or accept bc question was already flagged as OT on SO): "Thanks @AdamL ! Do you know of an online reference to this behaviour in the documentation or some other "official" source? Just for completeness. Still seems strange this effect is not prevented, with a clear/unique error message. Is it possible for this question to be taken "off hold", so you can your comments as an answer, so it can be accepted and the question can be marked as resolved?" – Jack Rambling Nov 19 '14 at 14:02

0 Answers0