Google spreadsheet Query Error - column doesn't exist

Question

Another problem with Google Spreadsheet API. I'm trying to use QUERY() function in order to get all customers from our database who are from Warsaw. Google API seems however to have a big problem with parsing my query. I've checked it few times and everything is OK. Tried semicolons, different apostrophes and column names—it still won't work. I type this code in the sheet cell:

=QUERY(IMPORTRANGE("0ArsOaWajjzv9dEdGTUZCWFc1NnFva05uWkxETVF6Q0E"; "Kuchnia polska!A3:G40"); "select B where E contains 'Warszawa'")

And get error like this:

Invalid query: Column [E] does not exist in table.

And I'm 110% sure that the column exists and the spreadsheet key is OK. ;)

score 38 · Answer 1 · answered Nov 28 '12 at 03:26

38

If you are using the Query function with a direct reference to a rectangle of cells, then you use capital letters to refer to the columns. However, if you are using Query against the output of an array formula, such as the return from ImportRange, then you instead need to use Col1, Col2, ... to refer to the columns. So, your example query string would need to read:

"select Col2 where Col5 contains 'Warszawa'"

Working example:

=Query(ImportRange("0AtEH2Kw9zfIodEQ2dFNFam9sdEFEZlVnNEV3ZEZMZEE", "data!A:G"), "select Col3, count(Col4) group by Col3 order by count(Col4) desc label count(Col4) 'count'")

I've no idea why it doesn't just let you use names from a header row.

answered Nov 28 '12 at 03:26

George

3,027
1
24
17

3

Thanks! Curious where you found this hint! FYI, this is also a problem when you do nestings of queries/sorts/transposes such as `=query(transpose(query(A1:B3;"select * where B>=2"));"select A")` (it says Column [A] doesn't exist). But it works if I use Col1. – Fuhrmanator Feb 16 '13 at 16:04
2

I was having problems querying an imported range, so I googled for `google spreadsheet query importrange` and landed on an example which used the `Col1 ... Coln` convention. – George Feb 18 '13 at 22:08
@George. Is there any settings that I need to do on the source spreadsheet? I have successfully imported data but only numbers are imported, cells that should contain strings are blank. – putoshop Dec 10 '13 at 11:47
2

For some reason, I get the error "NO_COLUMN: Col1" when I try to apply query referring to a single cell that contains a `=filter...` formula. – Kalin Nov 21 '16 at 21:15
Yeah, and if you need to convert an array, referenced by A, B, C, to the one referenced by Col1, Col2, Col3. just enclose it in `{ }` – vstepaniuk Oct 12 '20 at 08:40

score 3 · Answer 2 · edited Jun 13 '18 at 12:54

Alternatively you can try the FILTER function in this case and then you don't need to bother with SQL. :)

=FILTER(ImportRange("0ArsOaWajjzv9dEdGTUZCWFc1NnFva05uWkxETVF6Q0E"; "Kuchnia polska!B3:B40"); ImportRange("0ArsOaWajjzv9dEdGTUZCWFc1NnFva05uWkxETVF6Q0E"; "Kuchnia polska!e3:e40")="Warszava")

Although I admit that it's not so pretty because of the two importRange functions.

Hope it helped anyway.

Google spreadsheet Query Error - column doesn't exist

2 Answers2

Linked