google sheetsgoogle-sheets-queryimportrange
I'm trying to import a column from a separate sheet and add a query to it.
Im using:
=QUERY(ImportRange("URL","SHEETNAME!c:f"),"select Col1 where Col4 contains 'statements' ")
I've been using this for years and its always ALWAYS worked. Now it suddenly doesn't work. The importing works but the query is not working and it's actually taking the data and transposing it… Any ideas why my formula isn't working?
you got #ERROR! because you wrapped Sheet1!J2 and Sheet1!K2 between '"& &"' ...try:
=COUNT(QUERY(IMPORTRANGE("14WxbiYeiY7fX6-V0f2MM4SraphCoa6EHD03F_6NmZ3g",
"Customer_Feedback_Report!A2:L250"),
"select Col9 where Col8 = '"&Sheet1!A2&"' and Col4 > date
'"&TEXT(Sheet1!J2, "yyyy-mm-dd")&"' and Col4 <= date
'"&TEXT(Sheet1!K2, "yyyy-mm-dd")&"' ", 1))
=QUERY(IMPORTRANGE("key/url", "Total Summary!A:K"), "where Col1='ATT SLC'
or Col1='ATT Orem'
or Col1='ATT Scottsdale'")
Best Answer
When the output of
querychanges in a strange way just because of different data, there are two likely reasons:Mis-identification of header rows.
querytries to guess what rows contain data, and what rows are headers for that data. Solution: provide the third argument with the correct number of header rows,query(range, "select ...", 1).Mixed data types. Each data column is interpreted as being of some type: text, numbers, dates. If these are mixed together, the type is determined by majority. The other entries are either converted to majority type, or discarded altogether. The solution is to avoid mixed data.
In this case, it was the first reason.