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

Solution 1

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.

Solution 2

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.

Comments

[Fuhrmanator]

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.

[George]

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.

[putoshop]

@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.

[Kalin]

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.

[vstepaniuk]

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 { }