There are specific scenarios you should consider using IMPORTRANGE within the FILTER function in Google Sheets.

Usually, I suggest using QUERY with IMPORTRANGE to import data from one Google Sheets file to another with a filter applied.

But I won’t recommend it in case of mixed data types in a single column in the source data.

Assume you have a column in the to-be-imported table containing the text “postponed” in a few cells and date values in most of the other cells.

The QUERY will consider this column a date data type because it’s the majority data type and will ignore the texts. So in your output, you will see fewer rows.

The solution is using IMPORTRANGE within the FILTER function. But when doing so, one serious concern was the performance of repetitive calculations.

Many experts suggest the following syntax, which calculates the importrange more than once. It will slow down the performance of your file.

=filter(importrange,index(importrange,0,1)=criterion)

Note:- The INDEX returns the column to apply the condition.

Importing Data From One Sheet to Another in Google Sheets

Example:

One tab is named expenses - May in the content - pages file.

How do we import the data in this file in columns A, B, and C to another file?

Here is how.

IMPORTRANGE Syntax: IMPORTRANGE(spreadsheet_url, range_string)

1. Copy and paste the above syntax in cell A1 in a new file.
2. Go to the expenses - May tab in the content - pages file and copy the URL from the address bar.
3. In the A1 syntax, replace spreadsheet_url with the copied URL and range_string with the tab name expenses - May.
4. Insert double quotes around the URL and the tab name.
5. Replace expenses - May with 'expenses - May'!A1:C in the A1 syntax so that it becomes a proper range to import.
6. Now place the equal sign in front of the syntax so that Sheets can recognize it as a formula.
7. Hit the “Enter” key.

The Sheet will start importing data, and for the first time, you will greet with a #REF error.

Hover over it and grant your permission to import the content.

Filtering Imported Data: The Old School Way

This part is just for your understanding of how repetitive calculation affects your Sheet.

We have imported all the contents from one Sheet (file) to another. How do we apply a filter with the import?

For example, how do I import only the category “stationery” from one Sheet to another in Google Sheets?

As per the above example, here is the old-school approach.

Generic Formula (One Criterion):

=filter(import_range_formula,index(import_range_formula,0,2)="Stationery")

Replace the import_range_formula with the original formula that you can find under the subtitle “Importing Data From One Sheet to Another in Google Sheets” above.

You only copy the formula after the equal sign and paste.

As you can see, you want to use the IMPORTRANGE two times within the FILTER function.

Sheets need to import the same data twice, which will affect the performance.

If you want to add one more condition from another column, for example, the date column, then the situation becomes even worse.

Generic Formula (Two Criteria):

=filter(import_range_formula,index(import_range_formula,0,2)="Stationery",index(import_range_formula,0,1)=date(2023,5,24))

Note:- The highlighted numbers represent the column numbers (relative positions of columns in the selected range) in the source file.

The Proper Way to Use IMPORTRANGE Within the FILTER Function

Google Sheets now have the LET function, which helps to avoid repetitive calculations and improve formula performance.

You may assign a name to the IMPORTRANGE formula using LET and call it within the FILTER function.

It avoids repetitive calculation of the IMPORTRANGE formula.

For example, let’s import only the data for the category that matches “stationery” as earlier.

Here we used LET to assign the name “import” and call it once in the FILTER.

What about multiple criteria? I mean, filter “stationery” for the date 24/05/2023?

The first part of the formula is the same. Replace the filtering part with the below one.

filter(import,index(import,0,2)="Stationery",index(import,0,1)=date(2023,5,24))

With the help of the LET function, we can avoid using multiple IMPORTRANGE formulas when using it within the FILTER function to filter out rows.

It can dramatically improve formula performance.