Import Google Spreadsheet range if a column contains a particular value

Question

I want to import a range of data from one Google Spreadsheet (1) into another (2), but only when Column J in spreadsheet 1 contains a 'yes'.

I'm using the following formula:

=query(ImportRange("0AgbNxxZ5QnpGdEU2R3BLVDlhd0t6VlhCbS1kQWNYT2c","Renewals!B:C"),"select Col2, Col3, Col5 where Col10='Yes' ")

(which I've hashed together having looked on forums & YouTube!)

But it keeps coming back with errors: Col10 does not exist, etc.

How can I fix it?

Answer 1

There are two things that you need to take into consideration.

Consideration

1. The IMPORTRANGE function requires two parameters (strings):
   • spreadsheet key
   • data range
     The data range needs to correspond to the number of columns you're about to use. In your case, you selected only column B and column C. Therefore, going beyond col2 will end up in failure.
2. There are two distinct ways to use a parameter:
   • numbers
   • strings

Data

Number

=QUERY(                                                // data
  IMPORTRANGE(
    "0AluAYY6ZHeWYdE5XampzQVl4dVpxZ3FoVW9mUkdwQkE",    // spreadsheet key 
    "DATA!A:C"                                         // datarange
  ), 
  "SELECT Col1 WHERE Col3=" & C2, 0)                   // query
)

String

=QUERY(                                                // data
  IMPORTRANGE(
    "0AluAYY6ZHeWYdE5XampzQVl4dVpxZ3FoVW9mUkdwQkE",    // spreadsheet key 
    "DATA!A:C"                                         // datarange
  ), 
  "SELECT Col1 WHERE Col2=" & "'" & B2 & "'"           // query
)

Example

I've created an example file for you:

• Test data for QUERY
• Result for QUERY

Answer 2

Short answer

Replace Renewals!B:C by Renewals!A:J

Explanation

The problem is that the second parameter of the QUERY() function is doing references to column 10 while the columns specified by the range in the IMPORTRANGE() function only include two.

The solution could be
1. To change the range parameter of the IMPORTRANGE() to include enough columns.
2. To change the select parameter of the QUERY() function to make reference only to the available columns.

As the question said that the is required that the result only include rows where column J values are Yes, then the solution is 1.