IMPORTRANGE Function – Google Sheets

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.

Purpose

IMPORTRANGE creates a one-way connection between two Google Sheets spreadsheets.

Syntax

=IMPORTRANGE(spreadsheet_url, range_string)

• spreadsheet_url – The web address of the source spreadsheet
• range_string – The cells to import from the source spreadsheet

Video Tutorial

Example

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.

Source Spreadsheet

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.

Spreadsheet URL

Now select and copy the spreadsheet_url.

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 – 1kiT_Y9yg0yZKQV5OQjcTyEMSNmWhQkxYS85HvzdKy7I.

Destination Spreadsheet

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.

=IMPORTRANGE("1kiT_Y9yg0yZKQV5OQjcTyEMSNmWhQkxYS85HvzdKy7I","H6")

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.

Allowing Access

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.

Notes

• This function can be slow. To increase its speed:
  • Limit chaining multiple IMPORTRANGEs
  • Reduce the number of cells imported
  • Limit the number of spreadsheets imported
• 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.