The Google Sheets IMPORTRANGE function allows you to import a range of cells from either an external spreadsheet or an existing sheet in a different sheet or spreadsheet.
IMPORTRANGE Syntax
IMPORTRANGE is a straightforward function. According to Google documentation, its syntax is as follows:
IMPORTRANGE(spreadsheet_url, range_string)
For example:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1257d3Ks1E3N7yfFl4xCSCUX2Sm-bJfdtz3WGaAwMaWQ/edit#gid=1493252460", "Sheet 1!A1:Z1000")
How to make IMPORTRANGE work
Even if your formula is correct, you will notice that after a brief ‘loading’, the cell with your formula gives you the #REF! Error. That’s because you need to grant permission to import data from a different spreadsheet.
In order to do so, all you need to do is click the cell. If the formula is correct, Google Sheets will show you the option to grant access if you are either the owner or editor of the external spreadsheet.
If you are not, you need to ask the owners, and either be granted permission or grant them permission to edit yours so that they can authorize the import of data into your spreadsheet.
There you go, you are all set. But if you want more, you can continue reading.
Practical benefits of the IMPORTRANGE function
From a productivity perspective, I see Google Sheets' IMPORTRANGE as the best tool to centralize information.
If you need to gather certain types of data from different sheets, let’s say different client reports that have more or less the same information across each other but your employer has no intention to create a standardized process (this is often the case), you can centralize the core information into one single sheet. From there, you can use that information to generate metrics, KPIs, and even tools to do your job (you can see this in action in the hands-on module of the 'Automate The Sheet Out Of It' course).
By doing this, you can further import those ranges into different spreadsheets, and you know that whenever a problem occurs, you only have your centralized data to look into.
A Better IMPORTRANGE Syntax
As I was writing in the article about Google Sheets IDs, the URL above shows us two things:
- The spreadsheet has its own ID.
- The current sheet that we are using while copying the URL for the IMPORTRANGE formula is mentioned in the URL precisely under ‘gid=1493252460’ where the number is the ID of the sheet.
I understand that the official documentation mentioned ‘spreadsheet_url’, but it seems silly to me to pass the ID of a sheet, and then having to declare the sheet name under ‘range_string’.
I guess that's because I aim to be more organized and proficient to save precious time… after all, why add information that is not necessary?
So I tried to just put the ID of the spreadsheet instead of its URL as the first argument.
Since then, my IMPORTRANGE syntax is as follows:
=IMPORTRANGE(“spreadsheet_ID”, ”Sheet_Name!TopLeft_Cell:BottomRight_Cell”)
Or with a practical example using the URL above:
=IMPORTRANGE(“1257d3Ks1E3N7yfFl4xCSCUX2Sm-bJfdtz3WGaAwMaWQ”, ”Sheet1!A1:C”)
It works like a charm!
But there’s more!
You don’t even need to specify the sheet name. IMPORTRANGE will automatically import the first sheet, no matter the name. And there’s more: if the spreadsheet with the data to be imported has more than one sheet, you can rearrange them, and the first will always be imported through the IMPORTRANGE function. You can even rename them; it doesn’t matter.
The syntax would be this:
=IMPORTRANGE(“1257d3Ks1E3N7yfFl4xCSCUX2Sm-bJfdtz3WGaAwMaWQ”, ”A1:C”)
With just A1:C instead of Sheet1!A:C.
Do you need the IMPORTRANGE function to import data from another tab in the same spreadsheet?
No, you don’t. While you can use it, you need to know that there is a limited number of cells that you can import with IMPORTRANGE, and you should be good at optimizing your resources, especially if you plan to import a lot of data.
If you need to import data from another tab, Google Sheets offers a better function called ARRAYFORMULA::
=ARRAYFORMULA(”Sheet_Name”!TopLeft_Cell:BottomRight_Cell)
For instance:
=ARRAYFORMULA(Settings!A1:B21)
In this example, we assume that the data to import is on a sheet called "Settings" and we want to import the first 21 rows of the first two columns into another tab
Things worth knowing about IMPORTRANGE
- There is a limit on the size of ranges that can be imported; the limit is 10MB, which is first downloaded to your computer.
- IMPORTRANGE immediately pushes any changes made in the source sheet to all receiving sheets, even when they are nested (for instance, when you are importing from another import). Reloading the receiving spreadsheet, on the other hand, won’t affect anything. Additionally, it automatically refreshes every hour.
IMPORTRANGE with formatting
Here I've got a bad news and a good news for you.
I'll start with the bad news. IMPORTRANGE only imports the data of the cells, not their formatting. There's no workaround with that. But here's the good news: you can format your end differently from the source. This might not always be the best approach, but it works well for me, especially as demonstrated in the example in the video below. If you happen to import multiple sheets conditionally, it doesn't matter whether you want to maintain just one style (my preferred approach) or use the source formatting. You can style each instance conditionally.
What can you do with imported data?
Unless you want to consolidate your imported data by selecting all, copying and pasting only the values, as long as you don’t alter the values of the cells of the imported range you can pretty much do whatever you want with it, which reduces your option to formatting only. You can change alignment, size of the font, the font type, cell color etc.
IMPORTRANGE errors and solutions
Although the IMPORTRANGE function is one of the most used by yours truly, when it comes to automating the 9 to 5 job, it is not perfect.
Sometimes it just doesn’t work, and doesn’t even give details on the error.
The good news is that this happens rarely and it solves itself after a while.
If it’s not a Google problem, it usually returns two types of errors:
#ERROR!
#ERROR!
This error is usually a typo or a syntax error in the function; you may have forgotten the comma. If you typed the wrong name or ID of the sheet/spreadsheet, it won’t return this error, nor if you forgot the exclamation mark or the quotation mark.
This type of error is, in fact, very rare and extremely easy to solve, but nonetheless would result in IMPORTRANGE not updating the data.
Other instances where you see #ERROR occur when Google Sheets displays “Error Loading data may take a while because of the large number of requests”. If that happens, it’s because you are using a lot of IMPORT functions across the spreadsheets that you have created (I personally have never seen it so far).
Another reason is when you try to implement other functions within IMPORTRANGE, specifically NOW(), RAND(), and RANDBETWEEN(). You just can’t use them with it due to their frequency of updates.
#REF!
#REF!
This is what you will get the majority of the times you have a problem with your function.
- It occurs when you first import a range from a new spreadsheet (but we have seen how easy to solve this is).
- It also happens if you have made a syntax error other than the comma (please, correct me if I am wrong, I haven’t been able to make the #ERROR! Appear in any other instance).
- It may happen if you have typed an incorrect ID or sheet name or range.
- It happens when Google has a problem and in this case you won’t be able to solve it. Hopefully, unless you see this problem at your first attempt at using this function, you would know that it used to work, and therefore it will return to work eventually (provided that the formula wasn’t modified).
- It also happens if you somehow add or alter data in a cell that is supposed to be generated by the IMPORTRANGE function. You cannot modify data that is imported unless you consolidate the data first (as mentioned previously in this article).
Combining IMPORTRANGE with other functions
Here are a few examples of how you can combine IMPORTRANGE with different functions to make your spreadsheets even more dynamic.
Click the tabs below to see the examples.
VLOOKUP
IMPORTRANGE with VLOOKUP
I love the VLOOKUP function. My favorite usage for it is mapping.
When I need to import data from multiple spreadsheets, perhaps managed by multiple people, I like to create a table with the name of the person who owns or manages the spreadsheet and the ID of the spreadsheet:
Then, I create a dropdown menu from the column with the names of the spreadsheet managers, and I create my VLOOKUP so that it looks for the name selected from the dropdown menu (B1) in the range of my table above.
In this example, it would be a small range, but you can make it bigger if you plan to expand the table in the future (F1:G2), where in F1, I have the name, and in G2, I have the ID of the spreadsheet.
Then, I specify the column with the value that I want to return, which in this case is column 2 (or G), in other words, the ID of the spreadsheet corresponding to whatever name is selected in B1 (my dropdown menu).
Finally, I specify that the content is not sorted with the 0.
VLOOKUP(B1, F1:G2, 2, 0)
By placing this bit of formula within the IMPORTRANGE function instead of the ID/URL (see above for more details) of the spreadsheet that I want to import, it will dynamically generate the ID of the spreadsheet for me.
It works like a charm, and you can check the video below for more details."
QUERY
IMPORTRANGE with QUERY
Even though we're incorporating IMPORTRANGE within the QUERY function in this scenario, I believe it's still valuable to include it here. At the end of the day, we're still importing a range and filtering it before visualizing it.
If there's a function that I love even more than VLOOKUP, it's the QUERY function – an absolute game changer!
Our IMPORTRANGE function here won’t have anything special; I'm using the simplest version of an IMPORTRANGE:
IMPORTRANGE("1fZjlREdabwjmp0Av54aX5kOwAr1ZHwW3LALWvKMJ_fM","A:D")
And I use it in the ‘data’ parameter of the QUERY function, in case you don’t remember:
=QUERY(data, query)
Then, I simply select all with the simplest query possible: “SELECT *”
Just like this:
=QUERY(IMPORTRANGE("1fZjlREdabwjmp0Av54aX5kOwAr1ZHwW3LALWvKMJ_fM", "A:D"), "SELECT *")
If you know how to use the QUERY function, you'll also be able to filter the data as you please. For instance, by showing only records above or below a certain price, or perhaps just the items out of stock, etc.
Check the video tutorial
The video below complements this guide and shows practical uses of the IMPORTRANGE function with conditions.
Conclusion
I really hope you got a lot of value from this article. The IMPORTRANGE function has been one of the pillars of my automations that allowed me to turn my 9 to 5 job into a 9 to 11 one.