This function allows you to import data from one Google Sheets spreadsheet to another one, as long as you have access to it.
=importrange(“spreadsheet_url”,”data_range”)
Where
spreadsheet_url
Points to the specific Google Sheets spreadsheet to import data. It can be found in the address bar of the tab where the sheet you want to import from is open:
You can simply copy the URL to your spreadsheet and it will still work.
data_range
refers to the range of data you want to import. It can either refer to the reference to the range of values you want to import:
sheet!range
Or you can use the named range (you will learn how to set one in Google Sheets in the next section).
If the Google Sheets spreadsheet is made of multiple words separated by spaces, the sheet name is enclosed in single quotation marks.
Both spreadsheet_url and data_range should be enclosed in double quotes.
You must have access to the Google Sheets source spreadsheet. If the spreadsheet you are including is also yours, a prompt will pop up, asking you to connect the sheets. Click Allow access.
Afterward, the data will be loaded.
Step 1: Copy the URL and the range of the data you want to import, then add them to the function in your target sheet.
Step 2: Press enter. A #REF! error will appear, saying “You need to connect these sheets.” Click Allow access.
You have now imported data:
This example requires you to have Editor access level to the source Google Sheets spreadsheet.
Step 1: Select the range where you want to set a specific name.
Step 2: Right click on the range.
Step 3: Find and select Define named range. It may be hidden under View more cell actions.
Step 4: The Named ranges sidebar will appear. Type the name you want to give to the range. Click Done.
You have now set a named range in the source sheet.
You can check the screencap here:
You can now use the named range in the function. Instead of using the name of the sheet and the reference to the range, you can just use the named range:
=importrange(“spreadsheet_url”,”named_range”)
It works just as well:
Yes! Google Sheets automatically updates the function. It can take a few seconds for the updated data to load. If it does not, you can do the following solutions:
Solution 1: Press F5. This will reload Google Sheets.
Solution 2: Edit the range of the function. Sometimes the new rows added to the source sheet are not included in the range specified.
The Google Sheets guide does not specify a limit in the size of the range you can import through this function. However, the effective range that you can use is effectively limited by how much data your desktop or mobile device can handle and by how long it can take for Google Sheets to recalculate the entries. The latter is important because it ensures that the data you import is accurate up-to-date.
If you need to import more data and the single function you use throws the “Result too big”, use the following solution:
={importrange(spreadsheet_url,range1);importrange(spreadsheet_url,range2);importrange(spreadsheet_url,range3);...}
This cuts the range to more manageable sizes.
You can filter incoming data by combining it with QUERY function, another robust function in Google Sheets:
=QUERY( importrange("spreadsheet_url","data_range"), "sql_query_commands")
A set of query commands will serve to filter the data and give you only the information you need.
Learn more about this technique here..
Formatting is not included; only the values are imported through the function.
Yes, you can include multiple instances in the same sheet. When combining them, make sure that the imported ranges do not overlap. When an overlap occurs, it will throw an error:
If you want to make sure they do not overlap like shown above, you can use the same solution in the previous section for getting the “Result too big!”.
IMPORTRANGE function is a handy function for importing data from other Google Sheets spreadsheets to your own. It is a simple function but is very robust and can be combined with other functions such as QUERY function for filtering capabilities and better results.
We hope this article has helped you and given you a better understanding of how to use IMPORTRANGE in Google Sheets. You might also like our articles on how to use the Google Sheets IMPORTHTML function and how to use IMPORTXML in Google Sheets.
On a side note, we also recommend reading our guide on how to send an email from Google Sheets when a row is added and trying our renewal tracker.
-How to Import Multiple Sheets with IMPORTRANGE
-Google Sheets Import Functions
