If you work with Google Sheets often enough, you will inevitably need to get data from one spreadsheet to another.
The IMPORTRANGE function in Google Sheets will help us link data from different spreadsheets, to search for data quickly, and retrieve data according to the requirements displayed in the function. So you can use the IMPORTRANGE function to quickly extract data and link data in Google Sheets data tables.
Using the IMPORTRANGE function in Google Sheets is also simple and can be combined with some functions in Google Sheets. The following article will guide you how to use the IMPORTRANGE function in Google Sheets.
IMPORTRANGE function structure in Google Sheets
Function structure IMPORTRANGE Google Sheets
=IMPORTRANGE(“spreadsheet_url”; “range_string”)
In there:
- Spreadsheet key: is a long string of numbers and letters in the URL for a given spreadsheet.
- Spreadsheet url: is the address link of a certain spreadsheet file.
- Range string: is the exact name of the spreadsheet from which the data is taken followed by '!' and the range of cells you want to get data from.
We will take an example with the Grade 10-2019 Benchmark data table, with the request to extract data from cell B2 to cell C29 in the Page 1 spreadsheet.
You open a completely new spreadsheet in Google Sheets to get the spreadsheet data you need. First you need Copy the link of the Grade 10-2019 Standard Score document.
Next in the new spreadsheet interface, we enter the function formula as below and press Enter.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/19AGre5-Fvin6Z4D0bW6e4iq6yYX13kar0EIvXAVh-VQ/edit#gid=887314864"; "Page1!B2:C29")
Immediately you will see #REF newspaper!. You need to tap on this notification and tap Allow access to allow the IMPORTRANGE function to access data from another worksheet.
The data results from the worksheet you selected are displayed in another worksheet as shown below.
How to use the IMPORTRANGE function in Google Sheets
1. With only two arguments, using the IMPORTRANGE function is usually quite simple. Let's say you have a spreadsheet and you want to import it into a new spreadsheet.
2. Click the URL in the address bar at the top of the browser and copy it. Alternatively, you can just copy the spreadsheet key from within the URL.

3. In the new spreadsheet, import “=IMPORTRANGE(“ – without quotes.
4. Paste the URL and add quotation marks (“).
5. Enter a comma, add quotation marks (“”), and enter the cell range you want to include. It will look like this: “Sheet1!B1:C6”. Specify here that the example wants the worksheet named “Sheet1” and wants cells B1 through C6.
6. Add a closing parenthesis and press Enter.
7. The complete function will look like this:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Zoq0M0RG-RLYZ9HjOf01ff9eSPIYY3s/edit#gid=1027643093", "Sheet2!A1:C12")

8. However, you may notice that the data is not imported – there is an error #REF! in the replacement box. Click this box and you will see a message that you need to connect the sheets. Click “Allow access” and then the data will appear. You'll only need to do this once for each spreadsheet you import data from.

How to use IMPORTRANGE function with QUERY function
When combining the IMPORTRANGE function with the QUERY function, you can get the exact data according to the conditions that need to be extracted.
We will range the data from cell B2 to cell C29 in spreadsheet Page 1 and only take column NV1. Enter the formula as below and press Enter.
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/19AGre5-Fvin6Z4D0bW6e4iq6yYX13kar0EIvXAVh-VQ/edit#gid=576960759";"Page1!B2:C29");"SELECT Col2")
The results will only display the NV1 score column.
Or you can also get the score value of schools with NV1 scores
We enter the function formula as below and press Enter.
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/19AGre5-Fvin6Z4D0bW6e4iq6yYX13kar0EIvXAVh-VQ/edit#gid=576960759";"Page1!B2:C29");"Sect Col1 where Col2
The results will display the names of schools with NV1 scores lower than 40 points.
Example of using the IMPORTRANGE function in Google Sheets
Use IMPORTRANGE by URL or Spreadsheet Keys
Suppose you have many different Google Sheets sheets containing students' test scores in different subjects (for example, one sheet for Math scores and one sheet for English scores, etc.).
If you want to combine all these sheets and have data in the same sheet, you can use the IMPORTRANGE function.
Step 1: Before using the formula, you need to get the URL of the Google Sheets where you want to import data. You can find that URL in your browser's address bar when that Google Sheets document is open.

Additionally, you can also copy the short key from the URL instead of the full URL (as shown below). This tutorial will use the short key in the formula.

Step 2: Enter the function and paste the URL or short key to add the first argument to the syntax.
Step 3: Enter a comma and specify the range to enter.
Below is a Google Sheets IMPORTRANGE formula that will allow you to import students' names and their scores into the current sheet using the short key from the above URL:
=IMPORTRANGE("1G_XEiSnUu0o8kmbkNVlQgTIjoE5lGqErRK3TlNfR1oI","'Math Score'!A2:B10")
Note that the second argument of this formula is “'Math Score'!A2:B10”. In this argument, you need to specify the sheet name as well as the range (enclosed in quotes).
Step 4: When you enter this formula for the first time, you will see a #REF! in the worksheet. When you hover over the box, you will see a prompt asking you to “Allow access”.

Click on the blue button and it will give you the results. Note that this only happens once per URL. Once you've allowed access, requests won't repeat.

This can be useful if you have to retrieve data from multiple worksheets. For example, in this case, you can pull the scores of all subjects from different Google Sheets into a single worksheet.
Use the IMPORTRANGE function with Named Range
If you have to import ranges from multiple sources, it's easy to get confused. However, you can name the ranges to make things a little easier. Just follow this process:
Step 1: In the worksheet you want to import from, highlight the range and right-click it and navigate to More cell actions > Define named range.

Step 2: In the pop-up menu, name the range.

Step 3: In a new sheet, use the Google Sheets IMPORTRANGE function with a named range range_string. In the example, the command would be as follows:
=IMPORTRANGE("1G_XEiSnUu0o8kmbkNVlQgTIjoE5lGqErRK3TlNfR1oI","'NamedRange1")
Step 4: Press Enterthen click on the error #REF and press Allow Access.
Some notes and tips for using the IMPORTRANGE function in Google Sheets
Once the sheets are connected (because you enabled access the first time you used the formula), any updates made in any sheet will automatically be reflected as the result of the formula. awake. For example, if you edit a student's score, it will automatically change in the sheet with the formula.
When using the IMPORT RANGE formula, make sure there are enough blank cells to contain the result. If you have some data in a cell that overlaps with the cell needed for the formula result, the formula returns an error. However, it helps identify errors by telling you the problem when you hover over the cell.

If you're importing data from multiple worksheets, this can get a bit confusing. It is a good practice to create Named Ranges in the source page and then use the named ranges.
If you want that data to be added to the source page, instead of dragging a specific range, drag the entire column. This will get data from entire columns A and B. Now, if you add more data to the source page, it will automatically be updated in the target page.
Frequently asked questions about the IMPORTRANGE function in Google Sheets
How to use the IMPORTRANGE function in Google Sheets?
1. Find the URL from your browser address bar to enter
2. Enter =IMPORTRANGE( into an empty box and paste the URL inside quotes
3. Enter a comma, then specify the range inside the quotes, for example: “Sheet2!B6:C18″ and press Enter
4. Click on the error #REF and click Allow Access.
What is the IMPORTRANGE function in Google Sheets?
IMPORTRANGE is a function that allows you to transfer data from one spreadsheet to another without having to enter it manually. Using this function is better than copy pasting because it updates automatically.
Does the IMPORTRANGE function automatically update Google Sheets?
Yes, any changes made to the original worksheet will also make changes in the imported data.
Is there a limit to the IMPORTRANGE function in Google Sheets?
Yes, there can be up to 50 workbook cross-reference formulas.
How to filter the IMPORTRANGE function in Google Sheets?
You should use the QUERY function to filter information from imported data.
How to use multiple IMPORTRANGE functions in Google Sheets?
Follow the standard procedure to import a range of cells, but make sure that each time you use the IMPORTRANGE function, there is enough space in the worksheet to display the data.
Can you use the IMPORTRANGE function with formatting?
No, you can only import the data itself.