IMPORTRANGE Google Sheets: Instructions and tips

importrange google sheets featured image

Written by Valentine Schelstraete

Mar 19, 2021

The IMPORTRANGE Google Sheets function is the only way to integrate data between spreadsheets without third-party add-ons in Google Sheets. For experienced spreadsheet users, it’s an easy formula, but it needs a lot of attention. For the inexperienced, it can get confusing in a hurry.

Below we’ve described its application, implementation, and the pros and cons. Is it a valuable formula for your situation? It’s hard to know, but after reading you’ll have a better idea of whether or not IMPORTRANGE fits your needs.

Application

The objective of IMPORTRANGE is to import values from cells in one spreadsheet into another. You must have access to the other spreadsheet and know its URL.

As a result of Google Sheets’ cloud based infrastructure, IMPORTRANGE’s has a very simple functionality. Because every file has a unique URL, you can reference individual files by including the file’s “key” (its URL modifier). You then add specificity by referencing a page and a range within the target file.

IMPORTRANGE is a great solution for one-time and sporadic data imports from one sheet to another. On the other hand, it’s not recommended if you need to perform multiple imports in multiple spreadsheets.

Syntax

=IMPORTRANGE(spreadsheet_url, range_string)

How to use IMPORTRANGE in Google Sheets

To import a range of data from a spreadsheet, you need two variables: the spreadsheet’s url, and the range within that spreadsheet you want to import.

1. Spreadsheet URL

First copy the complete URL of the spreadsheet that has the data you’d like to import. If you’d like the formula to be visually shorter however, you can copy the spreadsheet key only. This is the unique identifier that Google Sheets uses for each spreadsheet.

On top of that, the spreadsheet reference must be in quotes in your formula, or you can reference a cell containing the desired spreadsheet’s URL.

Complete URL
https://docs.google.com/spreadsheets/d/1sqWKxq4mbsJVqy2mZbeV2H7MWF9TZ2V-yU6Ne0NGL0g/edit?usp=sharing
Spreadsheet key
1sqWKxq4mbsJVqy2mZbeV2H7MWF9TZ2V-yU6Ne0NGL0g/edit?usp=sharing

In the example below I chose to only copy the spreadsheet key in order to keep the formula short:

=IMPORTRANGE(“1sqWKxq4mbsJVqy2mZbeV2H7MWF9TZ2V-yU6Ne0NGL0g”,range_string)

2. Range string

The second variable of the function is the range string. Type the cell range that you intend to import from your external spreadsheet. Optionally you can include the sheet you wish to import from. If left out, IMPORTRANGE always defaults to the first sheet of the spreadsheet.

Your range must be written as a string — meaning it must be contained within quotation marks — or it must reference a cell containing the desired range written as a string.

IMPORTRANGE google sheets 1

The selected range above exactly represents “Sheet1!A1:B25”. In this case, the format for the entire function should be:

=IMPORTRANGE(“1sqWKxq4mbsJVqy2mZbeV2H7MWF9TZ2V-yU6Ne0NGL0g”,”Sheet1!A1:B25”)

After typing out the entire function, simply press ‘Enter’.

The first time you execute the function you will be asked to link your two sheets. Click “Allow access” to do so.

IMPORTRANGE google sheets 2

As a result, the function initiates the import of data and displays it in your sheet.

The data transfer occurs nearly instantly if you are importing a small range of data, but might take a few extra seconds if you are importing a larger dataset.

QUERY and IMPORTRANGE

The QUERY function is another powerful function native to Google Sheets. You can use these two functions in conjunction with one another by using IMPORTRANGE as the dataset in your QUERY function parameters:

=QUERY(IMPORTRANGE(“1sqWKxq4mbsJVqy2mZbeV2H7MWF9TZ2V-yU6Ne0NGL0g”, “sheet1!A1:C10”), “select * where B contains ‘denied'”)

This formula will result in an error message, however, because when integrating the query and IMPORTRANGE formula, you need to refer to the columns with their column number rather than their letter name.

=QUERY(IMPORTRANGE(“1sqWKxq4mbsJVqy2mZbeV2H7MWF9TZ2V-yU6Ne0NGL0g”, “sheet1!A1:C10”), “select * where Col2 contains ‘denied'”)

Combinine QUERY with IMPORTRANGE to save time when working with multiple spreadsheets, as you only pull in the exact data you need. It also allows you to format the data within your query instead of afterwards, which saves you an additional step!

Why use IMPORTRANGE Google Sheets

Strong points

  • Quickly implement it without adding or downloading anything.
  • It’s possible to choose the exact data range that you want to import from within a sheet.
  • You can place the formula in any cell in your sheet.
  • Data updates in real-time when the spreadsheet is open.

Weak points

  • There’s no easy way to organize or visualize connections. If you have to include a lot of these functions, it becomes messy and error prone.
  • It’s easy to forget where they are in your spreadsheet and from where you are importing the data.
  • If you cascade updates through multiple sheets, when you look at the data in the last spreadsheet in the chain there will be long delays as the data to load. You need to wait for IMPORTRANGE to execute through the whole sequence. This delay can result in incorrect data or errors because there is no way of verifying that all of the data has been updated through the chain (without opening every sheet to look). This is dangerous if you are using this data to make decisions or putting it in reports.
  • You cannot import the format of the source cells.
  • It’s not possible to choose the data update frequency.

Conclusion

The IMPORTRANGE Google Sheets function is a quick solution to import small volumes of data. It’s great to bring information from one spreadsheet to another for spot analysis.

However, it might not be the right choice if connecting spreadsheets is a routine in your work. When you depend on these connections for analysis, reports, and decision making, we strongly recommend using an application designed for data transfer, like Sheetgo.

IMPORTRANGE vs Sheetgo

Sheetgo takes the main principle of IMPORTRANGE and expands on its functionality. You can customize every aspect of your cross-sheet connections and you don’t need to do any heavy formula-writing.

Whilst IMPORTRANGE in Google Sheets is limited to 50 connections per spreadsheet, Sheetgo has unlimited potential. Create complex networks of interconnected spreadsheets without worrying about the delicate formulas related to IMPORTRANGE.

Sheetgo also connects to non-Google spreadsheet files like .xls, .xlsx, and .csv files.

Workflow view Sheetgo - Importrange Google Sheets

Take a look at our IMPORTRANGE vs Sheetgo blog post if you want to know more about the difference between the two.

Here you’ll find a comparison between both functionalities and which suits you best.

Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.

You may also like…

Share This