How to Use IMPORTRANGE in Google Sheets

To use IMPORTRANGE in Google Sheets, you’ll need to include the URL of the original spreadsheet and the target range you want to include in your new spreadsheet. Here’s my video guide:

Below, I’ll discuss the syntax of the function and show step-by-step guidance on how to use it.

How to Use IMPORTRANGE in Google Sheets

Accessing data from one workbook to another in Google Sheets is not as simple as it would be for any other spreadsheet software. This is mainly due to the online nature of Google Sheets.

And it makes sense, keeping in mind data security issues.

But Google does allow you to access and use data from an external worksheet, as long as you have the right permission and access. It provides this facility through the IMPORTRANGE Google Sheets function.

If you’ve ever seen the Google Sheets IMPORTRANGE function call before, you might have felt intimidated by the (seemingly) sheer length and complexity of it.

But to be honest, its syntax is way simpler than it seems. In this tutorial we will take a closer look at the IMPORTRANGE Google spreadsheet function and understand how it works, using a simple example.

Syntax of the IMPORTRANGE Google Sheets Function

The formula syntax of the IMPORTRANGE function in Google Sheets is:

=IMPORTRANGE(spreadsheet_url, range_string)

The function takes two parameters:

• spreadsheet_url : This is the URL of the source spreadsheet.
• range_string: This is a string containing information about the range of cells to import.

Let us understand these parameters in a little more detail.

Using a Spreadsheet URL or Workbook key

This is the first parameter of the IMPORTRANGE function. It contains the Spreadsheet URL or Google key of the Workbook that you want to import from in quotation marks.

Below is where you can find the Google Sheets URL

And you can also use the workbook key instead of using the entire URL in the IMPORTRANGE function.

This parameter is usually a string value. It can either contain the URL of the source sheet (enclosed in double-quotes), or a reference to a cell containing the URL of the source spreadsheet (without double quotes).

The spreadsheet URL is the entire location that you see on your browser’s location bar when your source spreadsheet is open.

Here’s an example:

https://docs.Google.com/spreadsheets/d/1f2gd-I4u3eGzCvABadsVBa8b4vFD5Nw4AlIG-LaILkI/edit#gid=0

The first part of the URL is the main Google Sheets URL. This is followed by the “d” folder, followed by a really large string of alphanumeric characters. This is the Workbook key, or the ID that Google spreadsheets use to identify the workbook.

To specify the first parameter of the IMPORTRANGE function, you can either use the full URL of the spreadsheet or just the spreadsheet key. Either one is enough to identify the spreadsheet that you want to import from.

Related Reading: How to Import XML Files into Google Sheets

The Range String Parameter

The Range String is the second parameter of the Import Range formula. This is also a string value, and has the following format:

"[sheet_name!]range"

As you can see, the range_string contains two parts:

• sheet_name: This is the name of the spreadsheet you want to import data from.
• range: This is the range of cells that you want to import from the source sheet. This component can either be a reference to a range of cells, a single cell, or a named range.

Note that the sheet_name component has been represented in square brackets because this part of range_string is optional. If you don’t provide this component, IMPORTRANGE will, by default, import the first sheet of the specified workbook.

Here’s are some examples of range strings:

• “MySheet!A1:B10” : A range_string like this is used when you want to import cells A1 to B10 from the worksheet named “MySheet”.
• “A1:B10”: A range_string like this is used when you want to import cells A1 to B10 from the first sheet in the source workbook.
• “MySheet!A5”: A range_string like this is used when you want to import just one cell, A5 from the worksheet named “MySheet”.

Now let’s take a look at some IMPORTRANGE function Google Sheets examples.

Example 1: How to Use IMPORTRANGE in Google Sheets With a Spreadsheet URL or Spreadsheet Key

To demonstrate how to use the IMPORTRANGE function, let’s try to import the cells A1 to B7 from a sheet named “Employees”, belonging to an external workbook.

Here are the steps that you need to follow:

1. Open the workbook containing the source spreadsheet (‘Employee Details 2020’ in our example).
2. In the location bar of your browser, you can see the URL of the workbook. You can either select the entire URL (as shown in image 1) or select just the workbook key (as shown in image 2).
   

   

    

   

3. Copy the selected URL or workbook key by pressing CTRL+C in the keyboard.
4. Next, open your target workbook.
5. Click on the cell where you want the range of imported cells to start displaying. In our example, we want to start displaying the cells from A1 onwards.
6. Type the formula: =IMPORTRANGE(“<<url_here>>”,”<<source_sheet_name_here>>!<<range_here>>”).Replace <<url_here>>, <<source_sheet_name_here>> and <<range_here>> with the appropriate values. In our case, we will enter =IMPORTRANGE(“1Hw9qK3r4egNP7EHsYH2kcpBOAdGn64iW-WEIZ59rtF4″,”Employees!A1:B7”)
   

   

7. Press the return key.
8. Give it some time to process.
9. When using the IMPORTRANGE function for the first time, you will get a #REF, as shown below:
   

   

10. When you hover over the cell, you will get a prompt asking you if you want to allow access to the sheet.
11. Click on the button that says “Allow Access”.
    

    

12. You should now see your data range from the source sheet displayed at your desired point in the target sheet.
    

    

Note: You will get the #REF error (from step 9) only the first time that you import from the source sheet. The next time you use this function, it will not display this error or ask for permission again. This access remains in effect until the user who granted access is removed from the source.

It’s also important to make sure that there are enough empty cells in the target sheet to accommodate the result of the formula.

In this way, the IMPORTRANGE function can be used to access data from other workbooks. This becomes especially helpful when you want to pull together data from multiple workbooks into a single sheet.

For example, you could import employee details of different company branches into one cumulative sheet with the help of IMPORTRANGE.

We hope we have been successful in helping you understand what the IMPORTRANGE Google Sheets function does and how to use it. We encourage you to try it out for yourself and see how convenient and time-saving it can be.

Example 2: How to Do IMPORTRANGE in Google Sheets With a Named Range

That first example gives a deep dive into using IMPORTRANGE to pull in data from another workbook. But you can also use the import range Google Sheets function with named ranges, to do so:

1. Name the range in the source sheet
2. Use the name you set as the range_string of the IMPORTRANGE formula Google Sheets

For example, if you named the data range “NamedRange1” your formula may look something like this:

=IMPORTRANGE("1G_XEiSnUu0o8kmbkNVlQgTIjoE5lGqErRK3TlNfR1oI","'NamedRange1")

That can simplify what you need to type in the formula, which saves time when you need to duplicate similar IMPORTRANGE functions across multiple cells.

Why Is IMPORTRANGE Not Working? (Common Issues and How to Fix)

Unfortunately, there is no one-size-fits-all solution to an IMPORTRANGE internal error, but never fear, follow our list for potential causes and fixes for this issue.

• Don’t call the same data more than once. Use the IMPORTRANGE function in a dedicated tab and copy it over to others as needed
• Check for empty rows in the source sheet. If the source sheet has tens of thousands of empty rows it will cause errors, so delete them first
• Temporarily turn off Iterative Calculations on both sheets
• Check for volatile functions that auto-update like TODAY(), NOW(), and RAND() and temporarily disable them.
• Make sure you are using a stable internet connection

Google Sheets IMPORTRANGE Drawbacks

However convenient the Google Sheet import range may be, it still has a number of drawbacks:

• Sometimes the formula returns an error without the error source
• It’s also hard to keep track of the formulas if you import data to multiple sheets
• It takes a long time to load data whenever you update a sheet
• It imports the data raw therefore you can’t retain the formats that you used such as font, highlights, font sizes and so on.
• Since the IMPORTRANGE function uses an importrange range string you can’t import an entire sheet, you must select a specific range to import.

Functions You Can Use with IMPORTRANGE in Google Sheets

The Import range function is a pretty convenient function, but even more so when you combine it with other functions. You can use it together with functions like the query function, filter, and VLOOKUP.

Query Function

When used with the query function, the IMPORTRANGE function in Google Sheets can extract and manipulate a subset of data from one sheet to another. This allows you to search for specific data from the data that has been imported from another sheet.

Filter Function

When you combine the filter function and import range, the filter function takes the import range as the first argument and any other argument afterward as the conditions. If both your first argument and conditions are in a separate sheet then you can use IMPORTRANGE to refer to them in the Filter formula. The formula would look like below:

=FILTER (IMPORTRANGE("IMPORTING SHEET URL", "Sheet name & range to display"), IMPORTRANGE("IMPORTING SHEET URL", "Sheet name & column with condition 1") = condition1)

VLOOKUP Function

The IMPORTRANGE and VLOOKUP functions combined help you look at data from a different sheet. The syntax for VLOOKUP is:

=VLOOKUP(search_key, range, index, [is_sorted]).

In this case we can put the input range formula in the range part of the VLOOKUP formula:

VLOOKUP(search_key, IMPORTRANGE(spreadsheet_url, range_string), index, [is_sorted])

Tips for Using Google Spreadsheet IMPORTRANGE

To maximize the capabilities of the Google spreadsheet import range function in Google Sheets, I’m including a few tips to consider:

• Make sure that there is no data overlapping the formula. If you do not have enough space for the results of the formula, it will return an error in the cells that have overlapping data.
• Do not use too many chains of IMPORTRANGE across multiple sheets since it can cause delays when you update one sheet.
• Summarize your data before using Google Sheet IMPORTRANGE instead of after so that you can reduce the amount of data you need to transfer

The most important takeaway? I find this function most useful when I’ve already summarized my data. That makes it easier to call exactly what I need without a bunch of extraneous information.

Why Use IMPORTRANGE in Google Sheets

• You can import data quickly and easily
• You can import exact ranges
• You can place it in any cell in your spreadsheet
• Real-time data transfers

Frequently Asked Questions

As usually, I’m including a few of the most common questions I hear from people when they want to use IMPORTRANGE. These are the most common questions, but they’re not the only ones. If I missed anything, please let me know in the comments!

How Do I Use IMPORTRANGE in Google Sheets With Conditions?

There is no native way to use IMPORTRANGE Google Sheets with conditions. But, you can use the QUERY function alongside IMPORTRANGE to apply conditions. You could also consider using VLOOKUP from another sheet.

Is IMPORTRANGE Dynamic? / Does IMPORTRANGE Automatically Update?

Yes IMPORTRANGE in dynamic, any changes you make to the source sheet will show up in the imported data.

What Is the Difference Between IMPORTRANGE and IMPORTDATA?

IMPORTRANGE takes a specific range of data from a spreadsheet. IMPORTDATA scrapes data from a URL and imports it as a .CSV or .TSV file into the spreadsheet.

Is There a Limit on IMPORTRANGE in Google Sheets?

Yes, there is a limit on IMPORTRANGE, there can only be a maximum of 50 cross-workbook reference formulas.

Conclusion: Keep Importing Data

In this article, we’ve shown you how to use IMPORTRANGE  in Google Sheets. However, using IMPORTRANGE in Google Sheets isn’t the only way to import data. There are also plenty of ways to perform queries. If you found this IMPORTRANGE Google Sheets guide useful, you can also find out more on how to import JSON to import data from a public data source.

Here or some other Google Sheets tutorials you may like as well:

• How to Use the FILTER Function in Google Sheets (Examples)
• IFS Function in Google Sheets – Test Multiple Conditions (Examples)
• How to Show Formulas in Google Sheets (instead of the value)
• How to Sum a Column in Google Sheets
• REGEXMATCH Function in Google Sheets
• How to Scrape Websites Using Google Sheets Formulas (Examples)