Suitebriar Blog

How to Use IMPORTRANGE in Google Sheets

Written by Meghan Donovan | Jun 11, 2020 2:19:00 AM
 

The IMPORTRANGE Google Sheets function is the only way to integrate data between two different spreadsheets, rather than within one spreadsheet (combining different tabs).

If you are experienced with spreadsheets and working with data, the IMPORTRANGE function is extremely simple to use. That said, it's a function which may cause confusion if you are not a seasoned spreadsheet ninja.

For those who have barely (or never) used sheets in an advanced way before, simply follow the instructions I'll provide today, and you should be merging data and combining sheets in no time.

Let's go!

Why Use IMPORTRANGE?

The main purpose of using IMPORTRANGE is to move values from the cells in one spreadsheet, to another. Instead of simply copying one cell, or combining two tabs, you can import an entire range of data from a completely different spreadsheet.

It is good to note that IMPORTRANGE helps to import data one time when you need it, but it is not the best for performing continuous imports within multiple spreadsheets.

Using Google Sheets IMPORTRANGE

There are two main components to IMPORTRANGE:

1. The key or URL of the source sheet

Instead of copying the complete URL that has the data that you want to import, you can simply use the spreadsheet key in Google Sheets instead.

The spreadsheet key is a unique identifier that Google Sheets uses for each spreadsheet. For a quick method to find the key, simply look in the URL of your browser and you will find a set of letters and numbers directly after “spreadsheets/d”. 

As an example this is the formula you might use: 

=IMPORTRANGE(“1cdrMoGvdz(:o)Ywe2gKOJG9_FhccWa2U0”

2. Range

The range is also going to be a very important component because it dictates which data you want to be imported from the entire data set of the Google Sheet you're referencing with the key.

You should describe the data with the column letter first and row number after. For example: B2.

You will also need to include the tab (if there are multiple tabs in the sheet you're important data from).

For example: Sheet1!B2:H5

This would be importing data from Sheet 1 in the cells B2-H5.

The format for the entire function to read as:

=IMPORTRANGE(“1cdrMoGvdz(:o)Ywe2gKOJG9_FhccWa2U0”,”Sheet1!B2:H5”)

Finally, after you have the function set, simply press Enter and Allow access to import.

Using Query and IMPORTRANGE

You can also use query along with importrange to work with data from another sheet.

This can be done by using the IMPORTRANGE as the dataset in your query parameters.

It is also good to know that you cannot integrate both query and IMPORTRANGE formulas. 

For example, if you input:

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

You will find that you receive an error message.

To fix this simply change the columns to be written with numbers rather than letters:

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

4 Ways IMPORTRANGE is Awesome

  • It works fast
  • You can choose the exact range of data that you want to import
  • You can include any cell on your sheet and import into any cell that you choose
  • Data is updated in real-time when the spreadsheet is open

4 Reasons IMPORTRANGE Isn't Always Ideal

  • It is not always easy to organize and can be messy if you do not have an organizational pattern in your spreadsheet
  • It can be easy to lose track of data, where it is coming from, as well as where it's going
  • You cannot import the format of source cells, just the data
  • The data only updates in real-time when the spreadsheet is open

IMPORTRANGE is a great tool to use when working with small amounts of data.

Not only can you import data and information from one spreadsheet to another, but you can do so quickly.

For those who are tired of importing data manually from one location to the next, this can be a true timesaver, and it can also ensure accuracy and cut down on errors. 

Also for those who want real-time updates regarding their data, IMPORTRANGE makes it very simple with automatic real-time updating.

Whether you need constant data transfer, a new method of organization, or simply want to take the extra taskwork out of your life, then the Google Sheets IMPORTRANGE function is sure to be a great tool for you.