Using FILTER or IMPORTRANGE to bring in and filter data from another spreadsheet

Question

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.

Answer 1

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.

Answer 2

Yes, you can bring the two together like so:

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