Using "IF" or "Query" statement paired with "ImportRange" to bring in data set from one sheet to another

Question

I have a sheet where I input my jobs booked. I want to be able to import information for the first 4 columns (Job Number, Job Name, Salesman, Date booked) if Column F (Permit Required) has a "Yes" in the drop down.

This is what I have: =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1A4SRBoTA1md8YnIVkp87Ngo3Pk9gPpB8YW6BLIiRNy8/edit#gid=1613480212","bOOKINGS!A2:L1000"),"Select Job Number,Job Name,Salesman,Date Booked Where Permit Required (Y/N)= 'yes'")

I receive this error: #VALUE! Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered ' 'Number"" at line 1, Column 12. Was expecting one of: "where... "Group" ... "Pivot"...

In column M, on the sheet I'm importing from, I have my array for drop downs in Columns E-H.

Can I Please get assistance?

Answer 1

When using the Query function, you must refer to fields by their Column ID or index. Try: =query(IMPORTRANGE("<<insert spreadsheet url>>","bOOKINGS!A1:L1000"),"select Col1,Col2, Col3, Col4 where Col6='yes'").

One might think that the Google Visualization API Query Language and Query Language Reference documentation is misleading because it uses headers as field references. But this is because they are referencing SQL - though this is not immediately obvious.

However the common ground for users of the query function are the syntax rules described under Language Clauses. There is only the smallest note in the documentation that mentions this: "(Note that column IDs in spreadsheets are always letters; the column heading text shown in the published spreadsheet are labels, not IDs. You must use the ID, not the label, in your query string.)"

If you wish to persist with using Column Headers with the Query function, several precedents can be found via google. For instance:

• Can I use column headers in a =QUERY?
• Using Query with column headers instead of column letters