当前位置: 动力学知识库 > 问答 > 编程问答 >

Google Sheets BUG: QUERY() doesn't work on a datarange with many columns

问题描述:

I have this query function:

=Query(DataIn!A:LK,"SELECT A,B")

And the output of the query in the first cell is this:

_id 82b60875-11ce-4acf-a6b1-39ed92e5b319 863f5f22-e9ee-4a08-9af2-c7fab2c72891 335dbba5-d6d2-415e-8fca-48bb52f1e8c3 fa2e7cd0-73e2-446d-8038-f5a30ac3ea80 c031aebe-1e3d-4bca-bf91-6d25fe30925b 86f97e07-bde2-4b89-99e3-aed4258a385f 90d07684-31ca-43fb-a59e-6a2c877b8f2f 5d5abba4-4d30-41b2-940d-753e3cc42c7a e113d82d-3584-43bc-91ae-a94abe456da2

I am 100% sure that only '_id' should be there. This only occurs when there is a large datarange, such as A:LK. If I shorten the datarange to A:CA (for example), then the output in the first cell is indeed:

_id

This is an annoying bug. How can I make Google make it go away?

==== EDIT ====

Here is a link to a reproduction of the bug. I think I've scrubbed any data that shouldn't be shown. If you see something that I have missed, please let me know:

https://docs.google.com/spreadsheets/d/15qASa-N-T_a9FPrdwE3-N6nMu6cUtZ-Rx20pQ-l73tU/edit?usp=sharing

网友答案:

I think, I've found some explanation on why query acts like this. If you don't enter the third parameter into query, then sheets try to detect the number of header rows automatically. I discovered that:

  1. It does not depend on number of columns in query
  2. It seems to look only at last column.

Here's is example with data:

And this is what =Query(DataIn!A:D,"SELECT A,B") does:

If you paste any number in cell D2, then you'll have proper query result. Please test it yourself and tell me if I missed something.


Suggested solution

Define number of headers in formula:

=Query(DataIn!A:D,"SELECT A,B", 1)
分享给朋友:
您可能感兴趣的文章:
随机阅读: