Productivity Spot

How to Use IMPORTRANGE Function in Google Sheets (with Examples)

Watch Video – IMPORTRANGE Function in Google Sheets

Do you want to import the data from other Google Sheets into the one in which your working currently?

You can do this easily using the IMPORTRANGE function in Google Sheets.

While it's really easy to connect two workbooks in Excel that reside on your hard drive or a network drive, the only way to connect two Google Sheets is by using the IMPORTRANGE Function.

IMPORTRANGE Function – Syntax


Let's see an example on how to use this function in Google Sheets.

Example – Import specific cells from a Google Sheets Spreadsheet

Suppose you have different Google Sheets that have the test scores of students in different subjects (i.e., one sheet for Math score and one sheet for English and so on).

If you want to combine all these sheets and have the data in the same sheet, you can use the IMPORTRANGE function.

Before you use the formula, you need to get the URL of the Google Sheets from which you want to import the data.

You can find that URL in the address bar of the browser when that Google Sheets document is open.

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

Below is the formula that will give you the import the names of the students and their score in the current sheet:

=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 double quotes). 

When you enter this formula for the first time, you will see the #REF! error in the sheet.

On hovering over the cell, you will see a prompt asking you to ‘Allow access'.

Click on the blue button, and it will give you the result. Note that this happens only once for a URL. Once you have allowed access, it will not ask for it again.

This can be helpful if you're trying to get the data from multiple sheets. For example, in this case, you can get the score of all the subjects from different Google Sheets into a single sheet.

Note that once the sheets are connected (as you allowed access the first time you used the formula), any update done in any of the sheets would automatically be reflected as the result of the formula. For example, if you edit the score of a student, it will automatically change in the sheet which has the formula.

Also, when using the IMPORTRANGE formula, make sure enough empty cells are available to accommodate the result of the formula.

If you have some data in a cell that overlaps with the cell needed for the formula result, the formula will return an error. However, it helps in identifying the error by letting you know the issue when you hover over the cell.

Tips for using IMPORTRANGE Function:

Here some documentation by Google on the IMPORTRANGE function.

You May Also Like the Following Google Sheets Tutorials: