The IMPORTRANGE Google Sheets function is the only way to integrate data between spreadsheets without third-party add-ons in Google Sheets. This function provides a more streamlined way to easily import data from one Google Sheets file to another. Not only does this save you time, but you also avoid the risk of human error from manual copy and pasting. In a nutshell, IMPORTRANGE is one of the best Google Sheets solutions to automatically transfer spreadsheet data.
With just a single formula, you can import or transfer data from one spreadsheet to another. Whether you need to import last year’s key metrics to compare yearly performance or transfer specific client data to share privately, the IMPORTRANGE is an easy formula for experienced spreadsheet users.
However, it’s important to note that this formula does come with rather complex requirements and a few limitations. For inexperienced users, it can get particularly confusing.
That’s why, in this article, you can learn more about what you can use IMPORTRANGE for, how to correctly implement it, and its pros and cons. You’ll also see how the Sheetgo add-on can be the perfect alternative to IMPORTRANGE to take spreadsheet data management to the next level.
What can I use IMPORTRANGE for?
The objective of IMPORTRANGE is to import values from a range of cells from 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 has 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 cell range within this file.
IMPORTRANGE is a great solution for one-time and sporadic data imports from one sheet to another. You can use IMPORTRANGE to carry out simple data transferral such as:
- Import specific data to create general reports
- Transfer older data to compare with newer data
- Retrieve dynamic data for the latest insights
- Importing multiple, large datasets into a single spreadsheet
- Transferring private data to a shareable file
On the other hand, it’s not recommended if you need to perform multiple imports in multiple spreadsheets.
How to use IMPORTRANGE in Google Sheets
Before we go through a step-by-step on how to use IMPORTRANGE in Google Sheets, it’s important to learn the formula syntax and what each parameter means.
Syntax of IMPORTRANGE
As you can see, 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/
Spreadsheet key
1sqWKxq4mbsJVqy2mZbeV2H7MWF9TZ2V-yU6Ne0NGL0g
In the example below, I chose to only copy the spreadsheet key in order to keep the formula short:
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; it must be contained within quotation marks, or it must reference a cell containing the desired range written as a string.
Now that you know what you need in order to complete the formula, let’s take a look at the function within an example.
Example of how to use IMPORTRANGE in Google Sheets
In the following example, I want to compare sales team performance from 2020 with their performances from 2021, so that I can generate a general performance report.
I can use the IMPORTRANGE formula to transfer the 2020 sales performance column to the 2021 sales sheet, where I can compare the data directly.
The screenshot below shows the 2020 data that I want to transfer. The exact range I want to import is “Sales 2020!C2:C19”.
In this case, the format for the entire function should be:
After typing out the entire function, simply press ‘Enter’ on your keyboard.
The first time you execute the function you will be asked to link your two sheets. Click Allow access to do so.
The function initiates the import of data and displays it in your sheet, as shown below:
Now that I have my two sets of data in one place, I can start calculating key metrics for my performance report, such as the percentage increase in sales between 2020 and 2021.
Please note: 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:
Please note: When integrating the QUERY and IMPORTRANGE formula, you need to refer to the columns with their column number rather than their letter name.
E.g.=QUERY(IMPORTRANGE(“1sqWKxq4mbsJVqy2mZbeV2H7MWF9TZ2V-yU6Ne0NGL0g”, “sheet1!A1:C10”), “select * where Col2 contains ‘denied’”)Read our blog post on how to combine QUERY with IMPORTRANGE to save time when working with multiple spreadsheets.
Why use IMPORTRANGE Google Sheets?
Although IMPORTRANGE does offer a lot of value when it comes to transferring data into spreadsheets quickly, it’s also important to note its disadvantages.
Strong points of IMPORTRANGE
- Implement it quickly without adding or downloading anything.
- Import from any spreadsheet in which you have access to the URL.
- Choose the exact data range that you want to import from within a sheet.
- Place the formula in any cell in your sheet.
- Receive real-time data updates when you open your spreadsheet.
Weak points of IMPORTRANGE
- Unable to organize or visualize connections easily. If you have to include a lot of these functions, it becomes messy and error-prone.
- Easily forget where the formulas are in your spreadsheet and from where you are importing the data.
- Long delays for data to load if you cascade updates through multiple sheets. You need to wait for IMPORTRANGE to execute through the whole sequence.
- Risk of incorrect data or errors with multiple uses of formulas. There is no way of verifying that all of the data has been transferred 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.
- Cannot import the format of the source cells, including font weight, size, color, or background.
- Impossible to determine the frequency of your data transfer.
IMPORTRANGE vs. Sheetgo
Sheetgo takes the main principle of IMPORTRANGE and expands on its functionality, so you can make the most of your spreadsheet data. With the Sheetgo add-on, you can easily connect your different spreadsheets to transfer data automatically – no formula writing or code needed! What’s more, Sheetgo also connects to non-Google spreadsheet files like .xls, .xlsx, and .csv files. As a result, you can transfer data across any spreadsheet file types you have.
Here are just a few of the benefits of using Sheetgo as an IMPORTRANGE alternative:
- Save time: With Sheetgo, there’s no need to manually input a formula. Simply connect your spreadsheets using the add-on, and Sheetgo does the rest for you.
- Avoid human error: With complete automation, there is no risk of incorrect formula-writing. The data transferral will always be accurate, every single time.
- Keep original formatting: Unlike IMPORTRANGE, Sheetgo can effectively transfer font weight, size, color, or background through copy formatting.
- Avoid data transfer errors: Avoid multiple formulas in a Google Sheet that can cause incorrect data. Sheetgo will update automatically without the need to open your spreadsheets.
- Customizable connections: Connect as many spreadsheets (of all types) as you wish within a single workflow.
- Visualize your connections: Sheetgo provides an easy workflow view so you can visualize the connections and easily identify where data is being transferred to.
- Schedule automatic updates: Sheetgo allows you to schedule automatic updates up to every hour. Your spreadsheets will update with the latest imported data automatically – no need to open a spreadsheet to trigger updates.
You can experience all of these benefits and more by installing the Sheetgo add-on inside Google Sheets.
Click on the button below to install the Sheetgo add-on.
Sheetgo add-on
Alternatively, install the add-on inside your Google Sheets file by heading to Extensions > Add-ons > Get add-ons and searching for Sheetgo. Once you have selected the Sheetgo add-on, click Install.
You’ll then need to sign up to Sheetgo before you begin – you can sign up for free straight away!
Once signed in, open the Sheetgo add-on inside your Google Sheets by heading to Extensions > Sheetgo > Start. Now you can begin importing your own data automatically!
How to use Google Sheets IMPORTRANGE
The IMPORTRANGE Google Sheets function is a quick solution to import small volumes of data. With just one formula, you can transfer a specified amount of data into another sheet in no time.
However, it might not be the right choice if connecting spreadsheets is a routine in your work. Although a quick and accessible solution, the formula can become quite complex, especially for inexperienced users. What’s more, IMPORTRANGE does have a few limitations which can impact the accuracy of your data if used incorrectly.
When you depend on these connections for analysis, reports, and decision making, we strongly recommend using an application designed for data transfer, like Sheetgo. Sheetgo integrates with Google Sheets in order to make the most of your data management, so you can easily transfer various data from various spreadsheets – all automatically!
Take a look at our IMPORTRANGE vs Sheetgo blog post if you want to know more about the difference between the two.
Alternatively, check out some of the related blog posts below.
Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.
