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

问题描述

Google Spreadsheet API的另一个问题。我试图使用Query（）函数来获取来自我们数据库的来自华沙的所有客户。然而，Google API似乎在解析我的查询时遇到了很大的问题。我检查了几次，一切都正常 - 尝试了分号，不同的撇号和列名 - 它仍然不起作用。 I型片材单元的代码：

  =查询（IMPORTRANGE（ 0ArsOaWajjzv9dEdGTUZCWFc1NnFva05uWkxETVF6Q0E; Kuchnia波兰A3：G40！ ）;select B where E contains'Warszawa'）
  

并且得到像这样的错误： p>

 错误：无效的查询：列[E]在表中不存在。 
  

我110％确定该列存在且电子表格键可以正常工作）如果您使用 Query 函数并直接引用矩形，则可以使用的单元格，然后使用大写字母来引用列。但是，如果您针对数组公式的输出使用 Query ，例如从 ImportRange 返回，那么您而是需要使用 Col1 ， Col2 ，...来引用列。所以，你的示例查询字符串将需要阅读：

 选择Col2其中Col5包含'Warszawa'
  

工作示例：



  =查询（IMPORTRANGE（ 0AtEH2Kw9zfIodEQ2dFNFam9sdEFEZlVnNEV3ZEZMZEE， 数据A：G）， 选择COL3，通过计数（COL4）递减标签计数（COL4） '计数' 计数（COL4）基团由COL3顺序）
  

我不知道为什么它不会让您使用标题行中的名称。

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:

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

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

解决方案

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.

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