IMPORTRANGE in Google Sheets: Share Data Between Spreadsheets Safely

IMPORTRANGE is Google Sheets' built-in connector for pulling data from one spreadsheet into another. Instead of manually copying data between files — creating out-of-sync duplicates — IMPORTRANGE creates a live link that updates whenever the source sheet changes.

Basic Syntax

=IMPORTRANGE(spreadsheet_url, range_string)

• spreadsheet_url — The full URL of the source spreadsheet, enclosed in double quotes.
• range_string — A text string specifying the sheet name and range, e.g., "Sheet1!A1:D100".

Your First IMPORTRANGE Formula

Suppose you want to pull inventory data from another file. Copy the full URL from the browser address bar of the source file:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aBcDeFgHiJkLmNoPqRsTuVwXyZ/edit", "Inventory!A1:E200")

On first use, Google Sheets will display an "Allow access" prompt. You must click it once to grant your destination file read permission to the source file. After granting access, the formula loads the data.

Security note: Access is granted at the file level — any formula in your file can now read from that source URL. Revoking access requires the source file owner to manage permissions from the source side.

Using the Spreadsheet ID Instead of the Full URL

You can shorten the formula by using only the spreadsheet ID — the long string of characters between /d/ and /edit in the URL:

=IMPORTRANGE("1aBcDeFgHiJkLmNoPqRsTuVwXyZ", "Inventory!A1:E200")

Both formats work identically. The ID form is cleaner and more portable.

Storing the URL in a Cell

For maintainability, store the spreadsheet URL or ID in a dedicated cell (e.g., cell A1):

=IMPORTRANGE(A1, "Inventory!A1:E200")

When the source file changes, update only A1 rather than hunting through every IMPORTRANGE formula.

Combining IMPORTRANGE with QUERY

One of the most powerful spreadsheet patterns is importing data AND filtering/sorting it in the same formula.

Pull only rows from the North region, sorted by revenue descending:

=QUERY(
  IMPORTRANGE("spreadsheet_id", "Sales!A1:F500"),
  "SELECT Col1, Col2, Col4 WHERE Col2 = 'North' ORDER BY Col4 DESC",
  1
)

Note: Because IMPORTRANGE returns a virtual array, you must reference columns using Col1, Col2, Col3 notation rather than sheet letters A, B, C.

Pulling Data from Multiple Sheets in One File

To import from a specific tab, include the tab name followed by an exclamation mark:

=IMPORTRANGE("spreadsheet_id", "January 2024!A1:D100")

Use the exact tab name as it appears in the source file.

Performance Considerations

IMPORTRANGE has known operational characteristics:

• Refresh delay: Changes in the source file may take a few minutes to appear in the destination. It is not instantaneous.
• Cell limits: Google recommends keeping IMPORTRANGE results under 100,000 cells per formula. Large imports cause significant loading time.
• Recalculation: Every time the destination file opens, IMPORTRANGE re-fetches data, which can slow initial load.

Reducing Overhead

Instead of importing all columns, import only the columns you need:

=QUERY(IMPORTRANGE("id","Data!A:Z"), "SELECT Col1, Col3, Col6", 1)

Stacking Multiple IMPORTRANGE Results with VSTACK

Google Sheets supports VSTACK to combine multiple IMPORTRANGE arrays vertically:

=VSTACK(
  IMPORTRANGE("file1_id", "Sheet1!A2:D100"),
  IMPORTRANGE("file2_id", "Sheet1!A2:D100")
)

This creates a single unified table from multiple source files — ideal for consolidating monthly files or regional branch reports.

Common Errors and Solutions

Best Practices

1. Grant access explicitly before sharing the destination file so colleagues do not see #REF! errors.
2. Import the minimum range needed — avoid importing entire empty columns like A:Z.
3. Use QUERY on top of IMPORTRANGE to avoid importing irrelevant rows.
4. Document sources — add a comment or helper cell noting which file the IMPORTRANGE pulls from.
5. Test imports on a dedicated tab before building reports on top of them.