Accessing data from one workbook to another in Google Sheets is not as simple as it would be for any other spreadsheet software. This is mainly due to the online nature of Google Sheets.
And it makes sense, keeping in mind data security issues.
But Google does allow you to access and use data from an external worksheet, as long as you have the right permission and access. It provides this facility through its IMPORTRANGE function.
If you’ve ever seen an IMPORTRANGE function call before, you might have felt intimidated by the (seemingly) sheer length and complexity of it.
But to be honest, its syntax is way simpler than it seems. In this tutorial we will take a closer look at the IMPORTRANGE function and understand how it works, using a simple example.
Syntax of the IMPORTRANGE Function
The syntax of the IMPORTRANGE function is:
The function takes two parameters:
- spreadsheet_url : This is the URL of the source spreadsheet.
- range_string: This is a string containing information about the range of cells to import.
Let us understand these parameters in a little more detail.
Spreadsheet URL or Workbook key
This is the first parameter of the IMPORTRANGE function. It contains the Spreadsheet URL or Google key of the Workbook that you want to import from.
Below is where you can find the Google Sheets URL
And you can also use the workbook key instead of using the entire URL in the IMPORTRANGE function.
This parameter is usually a string value. It can either contain the URL of the source sheet (enclosed in double-quotes), or a reference to a cell containing the URL of the source spreadsheet (without double quotes).
The spreadsheet URL is the entire location that you see on your browser’s location bar when your source spreadsheet is open.
Here’s an example:
The first part of the URL is the main Google Sheets URL. This is followed by the “d” folder, followed by a really large string of alphanumeric characters. This is the Workbook key, or the ID that Google spreadsheets use to identify the workbook.
To specify the first parameter of the IMPORTRANGE function, you can either use the full URL of the spreadsheet or just the spreadsheet key. Either one is enough to identify the spreadsheet that you want to import from.
The Range String is the second parameter of the IMPORTRANGE function. This is also a string value, and has the following format:
As you can see, the range_string contains two parts:
- sheet_name: This is the name of the spreadsheet you want to import data from.
- range: This is the range of cells that you want to import from the source sheet. This component can either be a reference to a range of cells, a single cell, or a named range.
Note that the sheet_name component has been represented in square brackets because this part of range_string is optional. If you don’t provide this component, IMPORTRANGE will, by default, import the first sheet of the specified workbook.
Here’s are some examples of range strings:
- “MySheet!A1:B10” : A range_string like this is used when you want to import cells A1 to B10 from the worksheet named “MySheet”.
- “A1:B10”: A range_string like this is used when you want to import cells A1 to B10 from the first sheet in the source workbook.
- “MySheet!A5”: A range_string like this is used when you want to import just one cell, A5 from the worksheet named “MySheet”.
Example – Importing a Range of Cells from another Sheet
To demonstrate how to use the IMPORTRANGE function, let’s try to import the cells A1 to B7 from a sheet named “Employees”, belonging to an external workbook.
Here are the steps that you need to follow:
- Open the workbook containing the source spreadsheet (‘Employee Details 2020’ in our example).
- In the location bar of your browser, you can see the URL of the workbook. You can either select the entire URL (as shown in image 1) or select just the workbook key (as shown in image 2).
- Copy the selected URL or workbook key by pressing CTRL+C in the keyboard.
- Next, open your target workbook.
- Click on the cell where you want the range of imported cells to start displaying. In our example, we want to start displaying the cells from A1 onwards.
- Type the formula: =IMPORTRANGE(“<<url_here>>”,”<<source_sheet_name_here>>!<<range_here>>”).Replace <<url_here>>, <<source_sheet_name_here>> and <<range_here>> with the appropriate values. In our case, we will enter =IMPORTRANGE(“1Hw9qK3r4egNP7EHsYH2kcpBOAdGn64iW-WEIZ59rtF4″,”Employees!A1:B7”)
- Press the return key.
- Give it some time to process.
- When using the IMPORTRANGE function for the first time, you will get a #REF, as shown below:
- When you hover over the cell, you will get a prompt asking you if you want to allow access to the sheet.
- Click on the button that says “Allow Access”.
- You should now see your range of cells from the source sheet displayed at your desired point in the target sheet.
Note: You will get the #REF error (from step 9) only the first time that you import from the source sheet. The next time you use this function, it will not display this error or ask for permission again. This access remains in effect until the user who granted access is removed from the source.
It’s also important to make sure that there are enough empty cells in the target sheet to accommodate the result of the formula.
In this way, the IMPORTRANGE function can be used to access data from other workbooks. This becomes especially helpful when you want to pull together data from multiple workbooks into a single sheet.
For example, you could import employee details of different company branches into one cumulative sheet with the help of IMPORTRANGE.
We hope we have been successful in helping you understand what the IMPORTRANGE function does and how to use it. We encourage you to try it out for yourself and see how convenient and time-saving it can be.
Other Google Sheets tutorials you may find useful: