If you don’t want to give others direct access to your Google Sheets file but still want to share the data, you can use the IMPORTRANGE function. This function imports data from another Google Sheets file, using the file’s URL and sheet name with range. Any changes you make to the source sheet will be reflected in the imported ranges in the destination sheet.
However, it is important to note that you should not make changes to the imported range of cells in the destination file. Doing so will break the formula.
You can also use the IMPORTRANGE function to import data from one sheet to another within a file. However, I do not recommend doing this, as it is more efficient to use ARRAYFORMULA or QUERY to accomplish this task.
I usually use the “within one sheet” feature to experiment with new things in IMPORTRANGE, as I don’t need two files to test.
In this tutorial, we will learn from basic to advanced use of the IMPORTRANGE function in Google Sheets.
IMPORTRANGE Function Syntax and Arguments
Syntax: IMPORTRANGE(spreadsheet_url, range_string)
Arguments at a Glance:
spreadsheet_url
: The URL of the spreadsheet from which the data will be imported.
Note: You can hardcode the URL within the formula, or it can be a cell reference containing the URL. In the former case, i.e., when you hardcode, you must place double quotation marks on both sides. We will see this with IMPORTRANGE examples below.
range_string
: The cell range to import in the following text format “sheet_name!range” (e.g. "Sheet1!B1:N100"
or "B1:N100"
)
If you omit sheet_name
, the formula will import the data from the first sheet in the source file.
Here, the above note is also applicable. However, instead of “URL,” read “cell range,”
Copying the Spreadsheet URL
Here are the steps to copy the spreadsheet URL to use within the IMPORTRANGE function in Google Sheets:
- Open the source file (the sheet from which you want to import data) in your favorite browser, such as Safari, Chrome, etc.
- Click within the address bar to select the URL. If you see the URL in the address bar highlighted, that means it’s selected.
- Press Ctrl+C on Windows or ⌘+C on Mac. Alternatively, you can right-click and select Copy.
Basic Use of Google Sheets IMPORTRANGE Function
We have already seen the purpose of the IMPORTRANGE function in Google Sheets. As the function name suggests, it is used to import data from Google Sheets.
What are the practical uses of importing data?
The practical uses of importing data vary from user to user. However, one common use case is to keep our master Google Sheets file intact and share a portion of content from it.
We will see more advanced uses of IMPORTRANGE later. For now, here is a basic example to get you familiar with the syntax.
Example
There are two Google Sheets: File 1, masterfile
and File 2, Result sheet
.
Our data is in the masterfile
, and the data range is Class Data!A:F
. We want to copy this data into the resultsheet
.
Approach 1
- Open
masterfile
and copy the sheet URL as explained above. - Then go to the destination file named
resultsheet.
- In cell A1, enter
=IMPORTRANGE("
and paste the copied URL. Close the quotation marks. Place a comma, and enter the range to copy within quotation marks.
Example:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1OiMbGNDOkzHu5DWaUvFSj_5cdq3rhvqu-ocWeXhbflo/edit#gid=0","Class Data!A:F")
The formula may return a #REF error instead of importing data.
Hover your mouse pointer over it and click on “Allow access.”
Approach 2
- Copy the URL from the source sheet.
- Paste the copied URL in cell A1 in the destination sheet.
- Enter the range reference
Class Data!A:F
in cell A2. - Enter the following IMPORTRANGE formula in cell B2.
Don’t forget to “Allow access.”
=IMPORTRANGE(A1,A2)
This is one of the fastest ways to use an IMPORTRANGE formula.
If clicking “Allow access” does not solve the IMPORTRANGE #REF! error, what should I do?
There are certain things you can do to solve the #REF error problem in the IMPORTRANGE function used in Google Sheets.
If you are the owner of the source sheet, make sure that you are using the correct URL.
If you are not the owner of the source sheet, ask the owner of the sheet to share the sheet with you.
Hover your mouse pointer over the error and read the tooltip. The error may associate with the range_string
parameter.
If you have checked everything and have the necessary permission to import and still having problems, do the following:
- Reload the spreadsheet.
- Clear the cache and cookies in your browser.
Similar: Different Error Types in Google Sheets and How to Correct It
IMPORTRANGE Function Advanced Usage Tips in Google Sheets
The IMPORTRANGE function can be used with other functions to perform more complex data manipulation tasks. Some of the functions that you can combine with IMPORTRANGE are VLOOKUP, FILTER, SUMIF, and QUERY.
In this advanced IMPORTRANGE usage, the QUERY function stands out from the rest as it can perform advanced data manipulation in Google Sheets. For example, you could use the IMPORTRANGE function to import data from a source sheet, and then use the QUERY function to filter, sort, or aggregate the data.
- How to Use Query with Importrange in Google Sheets.
- How to Use IMPORTRANGE Function with Conditions in Google Sheets.
When you use an IMPORTRANGE formula as the range in lookup functions such as FILTER and XLOOKUP, use the LET function to assign a name to it. This will make your formulas more readable and easier to maintain.
Please see an example of this in my latest tutorial titled IMPORTRANGE Within FILTER Function in Google Sheets. You can also find another example at How to Vlookup Importrange in Google Sheets.
Here are some advanced tips and tricks for working with the IMPORTRANGE function.
- How to Freeze Cell in Importrange in Google Sheets.
- Importrange Named Ranges in Google Sheets.
- Dynamic Sheet Names in Importrange in Google Sheets.
- Relative Cell Reference in Importrange in Sheets.
- Sumif Importrange in Google Sheets – Examples.
Conclusion
Do you want to know how to use the IMPORTRANGE function to import data from one sheet to another within the same Google Sheets spreadsheet?
The URL-based import method is not necessary in this case, as the data is being pulled from different sheets within the same file.
Instead, you can use the following array formula:
={'Class Data!A:F'!A:F}
This will import the range A1:F from the Class Data
sheet into another sheet in the same file.
Hi,
I have a Google Sheet from where I import the columns using Query and Importrange functionality into another Google Sheet.
I mark some comments in the new Google Sheet against the rows that are imported. But when the new rows get amended (using Query and Importrange) the comments marked for these also are shuffled. Any way I can lock those comments or those rows?
Hi, Ameya Raje,
That’s only possible with a common ID column as detailed in this guide – Align Imported Data with Manually Entered Data in Google Sheets.
Hi,
Is there a way that I could stop an importrange from updating? or someway to only reload the data with a button press or a checkbox tic? I have a file with a schedule that I am mirroring to another workbook and I don’t want others to see the changes before they’re ready.
Hi, Andrew,
We can control the import with a tickbox inserting in the source file. I’ll test it myself and update you very soon.
How to Control Reloading of Importrange from the Source File in Google Sheets
Hi!
Is there a way to import data from one sheet to another sheet only when the checkbox is marked?
If so, how do I do it?
Thanks for all your help, these tutorials are great!
Hi, Lili,
Checkbox marked on which Sheet. Source sheet or the Sheet containing the Importrange formula?
Please clarify.
Best,
Hello,
I have multiple spreadsheets that are tabbed “Wk1” – “Wk52”, and I’m using importrange within the sheets.
However, I am trying to figure out a way to make the range_string part of the formula grab a cell within the sheet that I have the formula in so that it knows what tab I want data from.
Not sure if this is possible?
So if I have a cell D3 with the value 21 in my current sheet (because I want data from Wk21), how could I correctly input the formula? I’m trying similar to this so far, but with no luck –
=importrange("url","Wk"D3"!A2")
Hi, William,
Your formula to get dynamic tab names in Importrange must be as follows.
=importrange("url","Wk"&D3&"!A2")
I think you have missed my tutorial Dynamic Tab Names in Importrange.
There you can find a similar solution using named ranges.
Best,
Hi There,
Importrange shows only the header in the Query. When I delete the query and manually type value everything is pulled to another sheet.
This is the query.
=QUERY('WO Start Rev 03 -1'!A:J, "SELECT A,C,I,F WHERE C MATCHES '"&JOIN("|",C:C)&"'")
This is the importrange formula.
=IF(ISERROR(ImportRange("URL","QA")),
IF(ISERROR(ImportRange("URL","QAB")),ImportRange("URL","QAC"),
ImportRange("URL","WO!Q:q")),ImportRange("URL","WO!q:Q"))
Hi, Desmond LEE,
The Query formula must be in this pattern. See the TEXTJOIN function used instead of JOIN.
=QUERY(A1:D,"SELECT A,B,C,D WHERE C MATCHES "&"'"&textjoin("|",true,G1:G)&"'")
When you replace A1:D (data) with the above importrange, you must change the column identifiers in SELECT clause and as well as in WHERE clause.
For example, you must use the formula as below.
=QUERY(importrange formula here,"SELECT Col1,Col2,Col3,Col4 WHERE Col3 MATCHES "&"'"&textjoin("|",true,G1:G)&"'")
I have updated my post to include additional resources at the bottom. Please go thru’ that too for some awesome importrange related tips.
Best,
Hi,
Is there a way to ensure that the font/size/color/bold/underline etc. import along with the content in the tab and ranges?
I used the importrange according to your example but it defaulted to the regular font, size etc.
Thanks!
Hi, Mel,
If you want to import data and also keep the ‘characteristics’ of the data, you can follow the below workaround.
1. Go to the Source file (File A).
2. Click File > Make a copy. It will make a duplicate copy of the source file (Copy of File A).
3. Delete all the contents in this ‘Copy of File A’.
4. Import the content from ‘File A’ to ‘Copy of File A’.
Best,
I using Importrange with Vlookup and am getting the #REF! error as it is evaluating “to an out of bounds range.”
My formula is as follows (note I have substituted “key” for the actual key)
=VLOOKUP(A3&"", IMPORTRANGE("key","Duane!A2:P"),8,FALSE)
The only thing I can think of is that it is a permissions issue but I can’t get the “allow access” bubble to pop up. Is there something else I may be missing?
Thanks!
Hi, Tom,
The problem is the Importrange is not importing the data. So Vlookup has no column 8 to return value.
First use importrange individually. Allow permission and then use it inside Vlookup as the range.
Additionally, I have Vlookup + Importrange tutorial.
How to Vlookup Importrange in Google Sheets [Formula Examples]
Best,
Hello! Thanks much for the guide. Is there a way to allow the person with whom we share the document by IMPORTRANGE to edit the sheet and both have live updates? Like in cloud? Thanks.
Hi, Maria,
I fear I didn’t understand your question correctly. I guess you are talking about sharing Google Sheets file with edit access.
You can share any Google Sheets file with Edit access. To do that look for the blue button labeled as Share on your Google Sheets. It’s on the top right-hand side of your screen.
You can also share a file in just view or comment mode. You can see that options once you click on the said blue button.
There is one more method in file sharing in Google Sheets but not official. It’s COPY mode.
Share Google Sheet Files on Copy Mode
Hi,
Is there a way to use IMPORTRANGE over multiple cells ?
If sheet is protected how we can use importrange function
Hi, Suyog,
I don’t think it will affect the importrange. In my testing, the Importrange function works well with a protected sheet.
Have you faced any issue? If then, what the error message says?
Thanks for sharing, this is great! Is there a way to ‘lock’ this formula on the content you’re really looking for. For example, if I’m using the import range function on cell C5 in doc ‘123’ and the editor of doc ‘123’ adds a row to the top of doc ‘123’, it would bump the cell I need to C6. Any way I can ensure the import range function is smart enough to update my formula as things in the source sheet shift?
Hi, Tori,
Good question! I have this already addressed in an earlier tutorial. Here you go.
https://infoinspired.com/google-docs/spreadsheet/freeze-cell-in-importrange-in-google-sheets/
I hope it could be useful.
Cheers!
Hi,
I have a question. Could we draw a chart by directly using Importrange Data? Because I have many files but would like to show the chart in one specific sheet.
Thank you in advance 🙂
Hi, Songthang,
You can create a chart from the imported data. I don’t find any issue in that.