The IMPORTRANGE function imports a range of cells from another spreadsheet. It aggregates data from different sources while allowing those sources to remain in separate files.
This function is not needed if both sheets are in the same file. Use cell references if you are linking between two sheets in the same spreadsheet.
IMPORTRANGE creates a one-way connection between two Google Sheets spreadsheets.
spreadsheet_url– The web address of the source spreadsheet
range_string– The cells to import from the source spreadsheet
First, let’s get some terminology down. A spreadsheet refers to an entire file with its own URL and sharing permissions, while a sheet refers to a tab or worksheet inside a file. One spreadsheet can have multiple sheets. These examples focus on connecting two spreadsheets.
First, let’s start with your source spreadsheet. This is the spreadsheet that contains the data we want to import.
We only want cell
H6 from the source spreadsheet. The detail in columns
A:E will not be visible in the destination spreadsheet.
Now select and copy the
Select the text of the web address as shown above. This text is the
spreadsheet_url you will use in the
IMPORTRANGE function. You can select the entire address or just the spreadsheet key, which we do in this tutorial. The key is the long string of random characters between the slashes –
Go to the spreadsheet into which you will be importing your data.
In this case, we have an empty cell at
C5 for the incoming value.
Entering the Formula
Now enter the formula.
Be sure to surround both the
spreadsheet_url and the
range_string cell reference in quotes. In this example, we reference the only sheet in the destination file. If you need to specify a sheet, add the sheet name, an exclamation point, then the cell reference as such
=Sheet2!H6 to the end of the
range_string if the
range_string was in a sheet called
Sheet2. See more examples of that syntax in the post about cell references.
⚠️ A clever user could modify the
IMPORTRANGE function by changing the cell reference. Consider moving sensitive data to another sheet with a random name in the source spreadsheet.
Next, you must give the destination spreadsheet permission to access the source spreadsheet.
You can do so by clicking the blue button that says Allow access.
Completed IMPORTRANGE Function
Now we have finished our
IMPORTRANGE function. The destination spreadsheet shows only summary numbers and hides the detail from the source.
- This function can be slow. To increase its speed:
- Limit chaining multiple
- Reduce the number of cells imported
- Limit the number of spreadsheets imported
- Limit chaining multiple
- Consider protecting worksheets or ranges in the same file to limit edit access if view-only is acceptable.
- Each spreadsheet can have different users with different levels of permission.