How to Use IMPORTRANGE in Google Sheets: A Step‑by‑Step Guide

1. Understanding IMPORTRANGE Basics

IMPORTRANGE is a Google Sheets‑only function that pulls a range of cells from another spreadsheet (or another tab in the same file) into the current sheet.

IMPORTRANGE(spreadsheet_url, range_string)

When the formula runs for the first time, Sheets will display #REF! and ask you to grant permission to the source file. After you click Allow access, the data appears and updates automatically.

Quick example

Source sheet Sheet1 (cells A2:C5) contains:

Destination sheet formula:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1a2b3cD4e5F6g7H8i9J0kLmNoPqrStuVwXyZ/edit","Sheet1!A2:C5")

Result (starting in the cell where the formula is entered):

Key points

1. Sheets‑only – No direct equivalent in Excel (Power Query or VBA are alternatives).
2. Permissions – First call requires manual approval; later calls are automatic.
3. Dynamic updates – Source changes flow through automatically, usually after a short delay rather than instantly.
4. Raw values only – Formatting, data validation, and conditional formatting are not transferred.
5. Performance – Large ranges can slow the sheet; limit the range or use a named range when possible.

2. Setting Up the Source Spreadsheet

Before you can pull data, the source file must be ready for external access.

1. Create or open the source file
2. In Google Drive click New → Google Sheets and give it a clear name (e.g., 2024‑Sales‑Data).

3. If you’re using an existing file, verify that you have edit rights (view‑only works for import but you can’t modify the data later).

4. Organize the data in a clean table

5. Put column headers in row 1.
6. Avoid blank rows/columns inside the range you’ll share; they can cause #REF! errors.

Example layout:

1. (Optional) Define a named range
2. Select the block (e.g., A1:C100).
3. Choose Data → Named ranges, name it SalesData, and click Done.

4. Using a named range makes the IMPORTRANGE formula easier to read and protects you from column‑letter shifts.

5. Copy the spreadsheet’s URL or ID

6. The ID is the string between /d/ and /edit.

7. Example:

   https://docs.google.com/spreadsheets/d/1aBcD_EfGhIjKlMnOpQrStUvWxYz1234567890/edit#gid=0 → ID = 1aBcD_EfGhIjKlMnOpQrStUvWxYz1234567890.

8. Set sharing permissions

9. Click Share → add the email of the destination‑sheet owner or set Anyone with the link → Viewer for a more open setup.
10. The first time the destination file calls IMPORTRANGE, the user will see a Connect button; after clicking, the connection is saved.

3. Using IMPORTRANGE in Your Destination Sheet

Now that the source is ready, import the data with a single formula.

Step‑by‑step example

1. Copy the source URL (or just the ID).
2. Identify the range you need. Suppose the source sheet is named SalesData and you want rows 2‑6, columns A‑C: "SalesData!A2:C6".
3. Enter the formula in any cell of the destination sheet:

gs =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aBcDeFgHiJkLmNoPqRsTuVwXyZ1234567890/edit#gid=0", "SalesData!A2:C6")

1. Grant access when the #REF! prompt appears. Click Allow access (or Connect) and the data will populate.

Sample source and expected result

The formula above returns the exact same 5‑row table in the destination sheet.

Quick‑reference table

4. Managing Permissions and Errors

Common error messages

One‑time permission workflow

1. In the destination sheet, type a test formula:

gs =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aBcD_EfGhIJKlMnOPqrstuVwxYz/edit","Sheet1!A1")

1. Click the Allow access button that appears.
2. After this connection is established, any other IMPORTRANGE calls that reference the same URL will work automatically—no further clicks required.

Tips to avoid pitfalls

• Keep the destination area empty and unmerged; existing data or merged cells stop the array from expanding.
• Limit the imported range (e.g., A2:C500) to keep performance snappy.
• Use named ranges in the source to protect against column insertions that would otherwise break the formula.

5. Advanced Tips: Combining IMPORTRANGE with Other Functions

Because IMPORTRANGE returns an array, you can nest it inside most Google Sheets functions to build dynamic dashboards.

Example: Pull → Clean → Summarize

Source sheet Sales:

Dashboard formula (in the destination sheet):

=QUERY(
  IMPORTRANGE("https://docs.google.com/spreadsheets/d/FILE_ID/edit","Sales!A1:E"),
  "select Col3, sum(Col5) where Col3 is not null group by Col3 label sum(Col5) 'Total Sales'",
  1)

Result:

6. FAQ

What is IMPORTRANGE and how does it work?

IMPORTRANGE is a Google Sheets function that imports a rectangular range of cells from another spreadsheet (or another tab) into the current sheet. It takes the source file’s URL (or ID) and an A1‑style range string, returns the values as an array, and updates automatically when the source changes.

Do I need to grant permission every time I use IMPORTRANGE?

No. Permission is required only the first time a destination file accesses a particular source URL. After you click Allow access (or Connect) once, all subsequent IMPORTRANGE calls to that same source work automatically.

Can I use IMPORTRANGE with multiple sheets at once?

Yes. You can import several ranges from the same or different source files by writing separate IMPORTRANGE calls, or by stacking them inside functions like ARRAYFORMULA, FILTER, or QUERY. Each call still follows the same two‑argument syntax, and you can combine them to create cross‑sheet dashboards.

End of guide.