Google-sheets – Import Google Spreadsheet range if a column contains a particular value

google sheetsgoogle-sheets-queryimportrangeworksheet-function

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?

Best Answer

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

enter image description here

Number

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

enter image description here

String

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

enter image description here

Example

I've created an example file for you:

Related Topic