Google 电子表格查询错误 - 列不存在 [英] Google spreadsheet Query Error - column doesn't exist

问题描述

Google 电子表格 API 的另一个问题.我正在尝试使用 QUERY() 函数来从我们的数据库中获取所有来自华沙的客户.然而，谷歌 API 似乎在解析我的查询时有一个大问题.我检查了几次，一切正常.试过分号、不同的撇号和列名——它仍然不起作用.我在工作表单元格中键入此代码:

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'")

并得到这样的错误:

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

而且我 110% 确定该列存在并且电子表格键没有问题.;)

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

推荐答案

如果您使用 Query 函数直接引用单元格的矩形，那么您使用大写字母来引用列.但是，如果您对数组公式的输出使用 Query，例如从 ImportRange 返回，那么您需要使用 Col1, Col2, ... 引用列.因此，您的示例查询字符串需要读取:

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'"

工作示例:

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

这篇关于Google 电子表格查询错误 - 列不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！