Google sheets query does not work on first row of data imported using importrange

0
votes

I have the following Google sheet which aggregates data across its different tabs to the master sheet

https://docs.google.com/spreadsheets/d/1kvf_Cxj6Cpob0CTMu9ySk4iY1M2sfF-XByJqTS4iI8M/edit?usp=sharing

Post this I import data from the above master in the sheet below using importrange. As I need to filter specific to a case I use a query with a where clause

https://docs.google.com/spreadsheets/d/1I0Hurstm_Ky2cf_n_1BW0Dq6Rd52neIf47lBFLDEB2g/edit?usp=sharing

The issue is that the where clause does not work in the sheet names b2b as shown. But if I add a blank row in Tax Report Master B2B between header and data the same query works

Currently the query is trying to fetch data only with warehouse set to DEL2 but it still fetches the row with warehouse ID ZNUV. I am not sure if this is something wrong with the data or the function. The query used is given below

QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1kvf_Cxj6Cpob0CTMu9ySk4iY1M2sfF-XByJqTS4iI8M/edit#gid=1559648689","master!A2:CH"),"SELECT * WHERE (Col75 = 'DEL2')",1)

1
google-sheets

1 Answers

0
votes

Solution

You are using the header index parameter incorrectly in your QUERY formula. The header index specify which row will be parsed as header on the data range: This row will be returned in the query result no matter what the query is. That's why you are getting a the first row of your master sheet event though it doesn't respect your query's where clause.

If you want to use 1 as a header index parameter you should include the master sheet headers in your data range. Otherwise use 0.

Reference

Query formula