How to nest IMPORTRANGE into the Query function in New Google Sheets

Question

We have applicants who come in and fill out a form via Google Forms. I have fields that auto-populate from the Response tab into a separate tab that is organized to fit our trackers. I am looking for a string to use with IMPORTRANGE that would add conditions to what is transferred.

For example:

Book 1 Applicant Form Response

   A   B      C             D           E         F          G
1  Jon Doe    xxx-xxx-xxxx  xx/xx/xxxx  Detroit   Michigan   Pass
2  Jon Doe    xxx-xxx-xxxx  xx/xx/xxxx  Detroit   Michigan   Fail
3  Jon Doe    xxx-xxx-xxxx  xx/xx/xxxx  Detroit   Michigan   Pass
4  Jon Doe    xxx-xxx-xxxx  xx/xx/xxxx  Detroit   Michigan   Pass

Book 2 Passed Applicants

   A   B      C             D           E         F          G
1  Jon Doe    xxx-xxx-xxxx  xx/xx/xxxx  Detroit   Michigan   Pass    
2  Jon Doe    xxx-xxx-xxxx  xx/xx/xxxx  Detroit   Michigan   Pass
3  Jon Doe    xxx-xxx-xxxx  xx/xx/xxxx  Detroit   Michigan   Pass

I want to be able to take the informtion from this sheet (Applicant Form Response) in book 1 A:G and auto fill into a different book (Passed Applicants) only if G = Pass.

I understand the range formula:

=IMPORTRANGE("Spread_sheet_key", "Applicant Form Response!A:G")

and my Query function would be (if within the same workbook):

=Query('Applicant Form Response'!A:G, Select * where "G contains 'Pass'")

or something.

With this I am able to transfer all the data from one book to another. As stated above I only want the data of those who passed to transfer.

What other formula would be best to use and how do I nest it within the IMPORTRANGE formula?

Answer 1

Please try something like:

=query(importrange("https://docs.google.com/spreadsheets/$$$$","Applicant Form Response!A:G"),"Select * where Col7 = 'Pass' ")  

where $$$$ is whatever is appropriate to complete the URL for your source (access to which you will need to grant).

Answer 2

It´s important to put emphasis in the fact that when you do this, you have to reference the columns as Col1, Col2, Col3... so on... It will not work to reference them as A, B, C... this is the structure of a query that worked for me:

=query(importrange("http//..the link.../", "Sheet!A:Z"), "SELECT Col1, Col3, Col6, Col12 WHERE Col3 = 'Your search'")