5

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?

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

  • 1
    Thank you @pnuts. I have edited my question to better express exactly what I'm looking for. In the meantime I will mess around with the formula you have suggested. I'll post back latter if I got it to work the way I'd like. Thanks again! Take care and God Bless! – JWCole Dec 1 '14 at 1:14
  • 1
    If importrange is nested within query on the first try, there is no prompt for granting access -- only an error about lack of data is thrown. Apparently, one should first use importrange on its own, allow access, and then proceed to use it in a query. – user79865 May 23 '15 at 7:19
0

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'")

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.