Thứ Sáu, Tháng Hai 7, 2025
spot_img
HomeFinanceHow to use the IMPORTRANGE function in Google Sheets

How to use the IMPORTRANGE function in Google Sheets

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.

Google Sheets data table

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")

Enter the Google Sheets IMPORTRANGE function

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.

Allow the IMPORTRANGE function to access Google Sheets

The data results from the worksheet you selected are displayed in another worksheet as shown below.

IMPORTRANGE function results access Google Sheets

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.

You can import any cell range from one worksheet like this to another by copying the URL or worksheet key
You can import any cell range from one worksheet like this to another by copying the URL or worksheet key

3. In the new spreadsheet, import “=IMPORTRANGE(“ – without quotes.

Xem thêm  How to block calls and voice messages on Telegram

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")
This example uses the spreadsheet key instead of the full URL
This example uses the spreadsheet key instead of the full URL

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.

You only need to allow access to the spreadsheet once
You only need to allow access to the spreadsheet once

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") 

IMPORTRANGE function with Google Sheets QUERY function

The results will only display the NV1 score column.

Google Sheets conditional QUERY function

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 QUERY function adds Google Sheets conditions

The results will display the names of schools with NV1 scores lower than 40 points.

Results with conditions Google Sheets

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.

Copy URL
Copy URL

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.

Xem thêm  How to register for the Study and Follow Uncle Ho 2022 exam
Short key
Short key

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”.

Request access permission
Request access permission

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.

Results of the IMPORTRANGE function in Google Sheets
Results of the IMPORTRANGE function in Google Sheets

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.

Named Range example
Named Range example

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

Menu to name the range
Menu to 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.

Xem thêm  3 mobile apps provide creative photo effects
Error in cell when using IMPORTRANGE function on Google Sheets
Error in cell when using IMPORTRANGE function on Google Sheets

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.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisment -
Google search engine

Most Popular

Recent Comments