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

conditional formattingformulasgoogle sheetsgoogle-sheets-queryimportrange

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?

Best Answer

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).