16

I want to bring in data from a separate sheet and filter it.

I can get IMPORTRANGE to work like this:

=IMPORTRANGE("URL","A:J)

But that doesn't filter. I can only get FILTER to work in the same sheet like this:

=FILTER(Sheet1!A:J, C:C="Yes")

Is there anyway to bring these two functions together and FILTER data from another spreadsheet into a new sheet, based on column C info?

The reason for doing this is to then use the auto notification setting, so whenever someone enters "yes" into the sheet I can be notified.

1
  • 1
    Shouldn't that be =IMPORTRANGE("URL","A:J")? (You seem to be missing a quote.)
    – ale
    Mar 27, 2017 at 12:38

2 Answers 2

25

Use query instead of filter for this:

=query(importrange("URL","A:J"), "select * where Col3 = 'Yes'")

The command query can refer to the columns of the array that it receives in an abstract way: Col1, Col2, and so forth, without being tied to their placement within the sheet. So, it filters the thing "in the cloud" and then puts the result into the sheet.

In contrast, filter can only refer to columns by the cell names like C:C. This means that the array must first be placed into the sheet, and then be filtered in place according to its own values. This runs into circular dependency.

2
  • user 79865 answers is really helpful. Just adding in that you can use the Google Charts query language to modify the query to something different. For example: =query(importrange("URL","A:J"), "select * where (Col3 like '%Yes%')"). Reference document: support.google.com/docs/answer/3093343?hl=en. Jul 14, 2020 at 20:13
  • QUERY may have issues when you have mixed data types, like phone numbers in various formats (which is what prevented this from being a solution to one of my problems). See support.google.com/docs/thread/5599833/…. Use FILTER instead if that's the case.
    – Vincent
    Oct 28, 2021 at 14:45
5

Yes, you can bring the two together like so:

=filter(IMPORTRANGE("URL","A:J"),INDEX(IMPORTRANGE("URL","A:J"),0,3)="Yes")

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