5

I am currently working in the following spreadsheet

https://docs.google.com/spreadsheets/d/13KfjUhWSB-BjGyC1G8f8i8o4SPd1kFFLkjN7D6VY8Lk/edit#gid=993210576

In which I am importing data from another worksheet using IMPORTRANGE, and writing a QUERY to match the cells in column B, which correspond to a specific part number, to their corresponding cut quantity found in Column D of the imported sheet. The query I have written is as follows. 

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1kFK-ZW8QjtsLYY5twdoMNTdqobGNWIV8nAFBRdouE28/edit#gid=473793446", 
 "FABRICATION LOG!A78169:K"), "Select Col3 where Col4 = "&B3&" limit 1", 0)`

And is returning the error message:

Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: WFR332703

I have used ImportRange for the sheet I am linking to in sheet1 of the spreadsheet linked above, and allowed access, so the error is not there.

Sheet1 is there to display the values returned for the IMPORTRANGE so that I can manually look up values I am expecting to get. Now for some of these cells, I expect to not get a value, as these will not be in the sheet I'm importing. But for others, I am expecting a numerical value, which is not being returned. I suspect this may have something to do with the fact that there is a mismatch between Datatypes since the entries in column b are both letters and numbers, but this is only a hunch with no actual facts to back it up. If anyone has any suggestions It'd be greatly appreciated.

player0
  • 124,011
  • 12
  • 67
  • 124
Dan
  • 53
  • 1
  • 1
  • 5

2 Answers2

4

first, paste this into some cell and connect you sheets by allowing access:

=IMPORTRANGE("1kFK-ZW8QjtsLYY5twdoMNTdqobGNWIV8nAFBRdouE28", "FABRICATION LOG!A1")

then use the formula: 

=QUERY(IMPORTRANGE("1kFK-ZW8QjtsLYY5twdoMNTdqobGNWIV8nAFBRdouE28", 
 "FABRICATION LOG!A78169:D"), 
 "select Col3 
  where Col4 = '"&B3&"' 
  limit 1", 0)

if cell B3 is number use:

=QUERY(IMPORTRANGE("1kFK-ZW8QjtsLYY5twdoMNTdqobGNWIV8nAFBRdouE28", 
 "FABRICATION LOG!A78169:D"), 
 "select Col3 
  where Col4 = "&B3&" 
  limit 1", 0)
player0
  • 124,011
  • 12
  • 67
  • 124
  • Sorry, I should have mentioned that I had used importrange in Sheet1 of the worksheet I linked to, and have already allowed access. B3 is a mix of letters and numbers, which I think may be causing the problem in the first place. – Dan Jul 11 '19 at 13:53
  • if its a mix of letters and numbers then use the upper formula – player0 Jul 11 '19 at 13:54
  • 1
    Ah! I did not see the extra set of single quotes. This did solve my initial problem, so I will mark it as solved. Thank you! – Dan Jul 11 '19 at 13:57
  • Hi @player0. How can it be made to work with imported range queried with data validation? – Lod Jul 21 '23 at 17:32
  • 1
    @Lod use: `=ArrayFormula(QUERY({IMPORTRANGE("12JFvpetkje1L4Amed-6LQ24xjZRQ7m_sCihJsAIl34c","Sheet1!A3:C")}, "SELECT * WHERE LOWER(Col2) MATCHES LOWER('"&E1&"') AND LOWER(Col3) CONTAINS LOWER('"&E2&"')", 0))` – player0 Jul 21 '23 at 23:46
  • @player0 works great, many thanks again for the great solution: https://i.imgur.com/Bru1Iph.png Be Well! Previous post with the screenshots only: — Tests here, the 3rd screenshot shows the error, the 2nd show it working with the query of imported data on same output sheet, the 1st shows the imported range from the data spreadsheet formula: https://i.imgur.com/3LBciMg.png https://i.imgur.com/zG1g99r.png https://i.imgur.com/f5PnJfM.png – Lod Jul 22 '23 at 12:53
  • Just as a reference note: 1st I noticed it's best to use `TRIM()` the input of `Col2` and `Col3` in the Data Shpreadsheet just in case of whitespaces that do break the query if present. 2nd I just realized from another example the arrayformula could be omited and it would still work as the query imports the whole columns. – Lod Jul 22 '23 at 15:36
  • @player0 Can zou help with this one: I'm getting `Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col8` from this formula: `'=UNIQUE(FILTER(FLATTEN(query(AC:AI,"SELECT Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15 WHERE Col1 = 1",0)),FLATTEN(query(AC:AI,"SELECT Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15 WHERE Col1 = 1",0))<>""))` – Lod Aug 31 '23 at 19:33
  • Ah, I just got the insight. It needs the input range to be `query(AC:AS` instead of `query(AC:AI`. Sorry for the disturbance. I'll leave it here as future reference in case I face it again. – Lod Aug 31 '23 at 19:35
0

I came here because I had a query like:

=Query(Data, "select * where i < 70", 1)

What fixed it was changing to:

=Query(Data, "select * where I < 70", 1)

The column in the where clause needs to be upper case if its a letter higher than 'd'.

kztd
  • 3,121
  • 1
  • 20
  • 18