IMPORT RANGE Function in Google Sheets
Updated: Aug 2, 2023
Connecting two Google Sheets can be hugely beneficial when you need to collate data across multiple sheets into one document. This is possible, and you’ll be pleased to know, simple to achieve using the IMPORTRANGE function in Google Sheets.
If you’re anything like me, you’ll have multiple sheets containing data or information relevant to each sheet respectively; however, there are occasions when it’s useful or even necessary to bring that data together on one sheet to visualise various metrics in one place. The IMPORTRANGE function in Google Sheets does a marvellous job taking care of this with minimal effort.
Syntax
The syntax only has two arguments, the spreadsheet_url and the range_string.
IMPORTRANGE(spreadsheet_url, range_string)
spreadsheet_url - This is the URL of the spreadsheet from which data will be imported.
range_string - This defines the name of the specific sheet within the spreadsheet you're importing and the range of cells you wish to collect from within that sheet written as a string.
IMPORTRANGE Function
There are a few advantages to using this function in Google Sheets.
Firstly, you can import an entire sheet from another spreadsheet or just part of it, so, for example, if you only need one cell or one column, that’s absolutely fine.
Secondly, you can combine functions within this IMPORTRANGE function to perform further aggregation; for example, you could collect an entire column and perform a SUM or an AVERAGE on that data to retrieve a single result.
Let’s jump in and see some examples of what’s possible.
Using this example spreadsheet we covered in this VLOOKUP tutorial, we will collect and display some data in another spreadsheet.
Spreadsheet URL
The URL of this VLOOKUP example sheet in its entirety is:
We can use the URL as you see it above within the IMPORTRANGE function, or we can use its ID to make things cleaner. The ID is shown below in red:
https://docs.google.com/spreadsheets/d/1BbV5EQ9SQzb85CX5qx7IGXoAd7RaQ4zBasziR4XPM8g/edit#gid=1326002530 You’ll always find the ID after the d/ and before the /edit part of the link.
Let's begin by typing out the function in the sheet we want to pull the data into.
=IMPORTRANGE(
We need to specify the spreadsheet_url of the target spreadsheet as the first argument, which must be wrapped within quotes. To begin, we’ll just enter the whole URL. Notice that the URL is green in colour; this is correct since we have used the quotes “ “
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1BbV5EQ9SQzb85CX5qx7IGXoAd7RaQ4zBasziR4XPM8g/edit#gid=1326002530"
Range String
Next, we can move into the second part of the argument to define the range_string, but it’s essential to add a comma to separate these two arguments.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1BbV5EQ9SQzb85CX5qx7IGXoAd7RaQ4zBasziR4XPM8g/edit#gid=1326002530",The range_string tells the IMPORTRANGE function where to find the data in this spreadsheet.
You may have multiple tabs or sheets in the target spreadsheet, so it’s crucial to explicitly define which sheet we need. For example, the sheet name is Sheet1 in this VLOOKUP example spreadsheet, so we’ll begin by typing this out as the second argument, which also needs to be wrapped in quotes.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1BbV5EQ9SQzb85CX5qx7IGXoAd7RaQ4zBasziR4XPM8g/edit#gid=1326002530","Sheet1You’ll need need to include an exclamation point between the sheet name and the range like this:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1BbV5EQ9SQzb85CX5qx7IGXoAd7RaQ4zBasziR4XPM8g/edit#gid=1326002530","Sheet1!Now, we need to decide what range we wish to collect from the target sheet; let’s just collect everything for now. We can do this by simply starting from cell A1 and ending with column Z; we don’t need to specify how many columns since we will leave the Z open-ended, like this:
"Sheet1!A1:Z"You could be more specific if you wish and specify an actual endpoint by including the row number with the Z, although it’s optional.
"Sheet1!A1:Z1000"The complete formula looks like this:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1BbV5EQ9SQzb85CX5qx7IGXoAd7RaQ4zBasziR4XPM8g/edit#gid=1326002530","Sheet1!A1:Z")Our IMPORTRANGE Function sheet displays the data from the target VLOOKUP Function sheet. For example, you can see the import range function in cell A1.
Errors Encountered
Collecting the entire sheet is probably excessive, to be honest; it’s recommended only to collect just the data you need. We collected every cell in the target sheet, even the blank cells. This may create an issue if you want to add other data on the main sheet importing the data.
If we change any cell in the main sheet, it breaks the IMPORTRANGE function. This is because the IMPORTRANGE function reserves all cells for the imported data. So when something is added, it would need to overwrite the data entered in our cell F9.
Choosing a Range
This generally is only an issue if you’re importing the entire sheet. If we were more specific and only imported a smaller range of data, we’ll be able to edit other cells without breaking anything. So let’s have a look at a smaller range of cells.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1BbV5EQ9SQzb85CX5qx7IGXoAd7RaQ4zBasziR4XPM8g/edit#gid=1326002530","Sheet1!A1:D51")I’ve changed the range only to collect the main table, which goes to column D, row 51:
"Sheet1!A1:D51"Now the function works even with our added data in cell F9 because the main sheet only reserves the range A1:D51; anything outside this range will be unaffected.
Importing a Single Table
As previously mentioned, we can choose to import any range we like. Here is an example of importing just the summary table in the range F1:G6 from the target sheet.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1BbV5EQ9SQzb85CX5qx7IGXoAd7RaQ4zBasziR4XPM8g/edit#gid=1326002530","Sheet1!F1:G6")
Importing a Single Cell
We can import a single cell like this:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1BbV5EQ9SQzb85CX5qx7IGXoAd7RaQ4zBasziR4XPM8g/edit#gid=1326002530","Sheet1!B2")
Importing a Single Row
Or we can import a single row:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1BbV5EQ9SQzb85CX5qx7IGXoAd7RaQ4zBasziR4XPM8g/edit#gid=1326002530","Sheet1!A2:D2")
You’ll notice only the range at the end that needs to be adjusted depending on what you’re looking to import.
Importing a Single Column
Importing a single column can be helpful if you need to compare sales from a previous period, for example, and the process is the same.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1BbV5EQ9SQzb85CX5qx7IGXoAd7RaQ4zBasziR4XPM8g/edit#gid=1326002530","Sheet1!D2:D51")
Aggregating an Imported Range
The handy thing about a column of data is that you can combine this import function to aggregate the data into a single result. For example, looking at this revenue column, we could SUM this by adding a SUM function to the beginning of the formula; just remember to close the SUM function by adding a parenthesis at the end of the formula.
=SUM(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1BbV5EQ9SQzb85CX5qx7IGXoAd7RaQ4zBasziR4XPM8g/edit#gid=1326002530","Sheet1!D2:D51"))
Here is an AVERAGE function for the same data.
=AVERAGE(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1BbV5EQ9SQzb85CX5qx7IGXoAd7RaQ4zBasziR4XPM8g/edit#gid=1326002530","Sheet1!D2:D51"))
Now you can see the benefit that the IMPORTRANGE function can bring, particularly when you need to aggregate the data.
Using the spreadsheet ID
As alluded to earlier, you can keep things cleaner by simply using the ID of the sheet rather than the whole URL. The last example shows the entire URL as the spreadsheet_url argument.
=AVERAGE(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1BbV5EQ9SQzb85CX5qx7IGXoAd7RaQ4zBasziR4XPM8g/edit#gid=1326002530","Sheet1!D2:D51"))If we just use the ID for the spreadsheet_url argument, I think you’ll agree that it makes the formula easier to read and, as a result, is much shorter and cleaner. However, everything behaves the same, so I would advocate getting into the habit of using the ID.
=AVERAGE(IMPORTRANGE("1BbV5EQ9SQzb85CX5qx7IGXoAd7RaQ4zBasziR4XPM8g","Sheet1!D2:D51"))Allowing Access
Google Sheets used to show a #REF! Error when you first submit the function. This is because you had to hover your mouse over the cell to see a prompt to allow access to the target sheet, but I’ve not seen this recently, so I suspect they are verifying ownership of the target sheet in the background, which saves you the hassle. If you see it, you just need to click the ‘Allow access’ button once, and your data will arrive as expected.
Named Ranges
If you create named ranges in the target sheet, you can also use these within the IMPORTRANGE function, which does make life even easier. Here is an example of the summary table I named in the original target sheet. You can see I specified the range as F1:G6 and named it Summary.
Then we can use this named range as the range_string argument.
=IMPORTRANGE("1BbV5EQ9SQzb85CX5qx7IGXoAd7RaQ4zBasziR4XPM8g","Summary")
Formatting
Although you cannot change any of the data you imported due to the limitation mentioned earlier about reserving the cells for the imported data, you can format it to your heart's content.
I would encourage you to try the IMPORTRANGE function to familiarise yourself, as you’re bound to find use cases for it at some point. Here is the Import Range Function example spreadsheet used in this tutorial; make a copy of it and see what you can accomplish.
Comments