How To Use IMPORTRANGE Function In Google Sheets?

If you are working or collaborating on Google Sheets and wish to integrate data between spreadsheets, you can use the IMPORTRANGE formula in Google Sheets. Before you continue, you may want to look through our related article How to reference another sheet in Google Sheets, as referencing is a crucial part when using the IMPORTRANGE formula.

This article will explain the IMPORTRANGE Google Sheets syntax formula, how to import a specific range of data from one Google Sheet to another, how to import data from several sheets or tabs into a Google Sheet, and how to combine the Google Sheet QUERY function and VLOOKUP function with IMPORTRANGE.

Alternatively, you can use Sheetgo to instantly create connections between your files and set up automatic data updates. You can easily filter, split, or merge your data and control who has access to what without having to share the whole file with your team or worry about complex formulas and related errors.

Syntax of the IMPORTRANGE formula

The IMPORTRANGE formula syntax is very straightforward, but we’ll explain each parameter below:

=IMPORTRANGE("spreadsheet_url","range_string")

• “spreadsheet_url”: The URL or ID of the spreadsheet you wish to import data from.
• “range_string”: Specifies the data you wish to import; it should include the name of the sheet and the range of cells.

How to use IMPORTRANGE in Google Sheets?

It should first be noted that in order to use the IMPORTRANGE function in Google Sheets you need to know the URL. All files saved on your Google Drive have a unique URL, which is what allows you to ‘refer’ to the spreadsheet. Depending on the data you wish to refer to, you can specify the page or range within the importing spreadsheet.

Let’s say we want to import students scores from file ‘Assignment Scores’ into ‘Full Student List’:

1. 1. Go to the Google Drive folder where your exporting spreadsheet is, and right click to “Get link”.

1. 2. Make sure you click on “Copy link” too.

1. 3. Another simpler and faster way is to open the exporting spreadsheet and click on the address or URL bar to select the link.

1. 4. Now that the link is copied, go to the importing spreadsheet and paste it in the spreadsheet_url variable of the IMPORTRANGE formula.

1. 5. Then, the range_string can be added in two ways. First, you simply select the range you wish to import and then copy the range from the far-left. Here, E2:E41.

1. 6. A second way is to name the range and save it for further use. For this, you would need to click on the arrow next to E2:E41 and click on “Manage named ranges”.

1. 7. Add the name of your range to the field in the pop-up window. In this example, I'll name it 'Score'. Check your range and name are correct before clicking Done.

1. 8. Now, try typing in ‘Score’ as your range_string. Remember to add speech marks on either side as shown below.

1. 9. Now press the return key. You may get a message asking you to connect these sheets. Simply click on “Allow access” to connect them.

NOTE: Don’t forget that if multiple users are working on Google Sheets, you will need to allow access in order for the IMPORTRANGE to function properly.

1. 10. Once you allow access to the other file, it should start loading. Depending on the amount of data you imported, it may take more than a few seconds.

We would like to remind users that the IMPORTRANGE function does not import the data with formatting; it will only import the values.

How to use IMPORTRANGE from multiple sheets?

You can continue to use the IMPORTRANGE formula to import a range from another tab in that same Google spreadsheet or from multiple Google sheets.

1. 1. Let’s say the exporting file has two tabs, Sheet 1 and Sheet 2, each containing scores from two different assignments. As before, you want to import the cells containing scores for both to another Google sheet.

1. 2. Note that, in this case, the IMPORTRANGE formula will have to include the other tab as well. We can do this by adding curly brackets {} to start and end the formula. The syntax also becomes a little more complex.

={IMPORTRANGE("spreadsheet", “range_string”), IMPORTRANGE("spreadsheet", "range_string")}

1. 3. Below, you will see how the data has been imported horizontally, given that we used “,” (in red) instead of “;” to separate both IMPORTRANGE formulas.

={IMPORTRANGE("spreadsheet", “range_string”), IMPORTRANGE("spreadsheet", "range_string")}

NOTE: If you want to import vertically, i.e. the two sets of ranges in one same column, you simply need to substitute the comma “,” for a semicolon “;”:

={IMPORTRANGE("spreadsheet", “range_string”); IMPORTRANGE("spreadsheet", "range_string")}

1. 4. Make sure that the sheet or tab name is correctly input. It is one of the most common errors and you will receive an error message indicating that the formula cannot find the range or the sheet for the imported range you selected. It may also simply return an error message like this.

How to combine IMPORTRANGE and other functions in Google Sheets?

There may be situations in which you want to filter the data imported into your Google Sheet. In this case, there are two formulas you can combine with IMPORTRANGE; QUERY and VLOOKUP.

How to combine Google Sheets QUERY and IMPORTRANGE?

You can combine the QUERY function with IMPORTRANGE for the following reasons:

• Import a specific range.
• Disregard specific rows.
• Modify column names.
• Format values in cells and columns.
• Filter data, sort data.
• Perform any operation related based on arithmetic, aggregate, and scalar actions.

The combined syntax is:

=QUERY(IMPORTRANGE("spreadsheet", "range_string"), "query_string")

How to combine Google Sheets VLOOKUP and IMPORTRANGE?

You can combine VLOOKUP and IMPORTRANGE in Google Sheets to import data according to a search criteria.

The combined syntax is:

=VLOOKUP(search_key,IMPORTRANGE("spreadsheet","range_string"),index,[sorted_boolean])

This formula is a bit more complex as it requires understanding the following parameters:

• search_key: This would refer to the cell range you will perform the search criteria on.
• index: The column index to return the value from
• sorted_boolean: The TRUE (sorted)/FALSE boolean indicates whether the indexed column is sorted or not.

Use Sheetgo to automatically import data to Google Sheets

As you have seen, you can use IMPORTRANGE to import data to Google Sheets. However, there are a few limitations when using this function, such as the complexity of formulas, file and size limits, loss of formatting, processing time, and privacy concerns. Fortunately, there is an alternative: Sheetgo. 

Check out our blog post to learn more about IMPORTRANGE’s limitations and how you can avoid them with Sheetgo.

Conclusion

We recommend using the IMPORTRANGE function for sporadic data imports. For collaborative work that requires constant data imports or more advanced integration between multiple spreadsheets, you might need to use third-party software. This is because having too many IMPORTRANGE formulas within a Google Sheet can increase loading time and hinder your team’s productivity levels. Nonetheless, we have also illustrated the syntax for IMPORTRANGE used in combination with QUERY and VLOOKUP.

One of the main advantages of using Google Sheets is that the IMPORTRANGE formula will update automatically. However, users have witnessed a set of drawbacks or common errors that we would like to remind you of:

• You must keep your Google Sheets files open in order for the IMPORTRANGE formula to work. Otherwise, you will find that data is not updating properly.
• You must save uploaded Excel files as Google Sheets so that the IMPORTRANGE formula does not delay the loading process.

Instead, sign up for Sheetgo for scalable automation solutions, avoiding these limitations and accessing additional advanced features.