IMPORTRANGE function

IMPORTRANGE retrieves a specified range of cells from another Google Sheet and displays it in the current sheet.

=IMPORTRANGE(spreadsheet_url, range_string)

Generate a IMPORTRANGE formula

Describe what you need. The generator will reach for IMPORTRANGE where IMPORTRANGE is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How IMPORTRANGE reads its arguments
spreadsheet_urlrequiredrange_stringrequiredIMPORTRANGE →
ArgumentRequiredDescription
spreadsheet_urlRequiredThe full URL of the Google Sheet to import from; must be a valid, accessible spreadsheet URL or returns #REF!.
range_stringRequiredA string specifying the range to import (e.g., 'Sheet1!A1:E5'); if the range doesn't exist, returns #N/A.

Returns

An array of cells matching the range specified in the source spreadsheet.

Availability

Excel: Not available · Google Sheets: Supported

Worked examples

1. Import supplier list for all ingredients

IngredientSupplierUnit
Olive OilItalian Imports CoL
TomatoesFarm Fresh Ltdkg
BasilOrganic Herbsg
MozzarellaDairy Pluskg
FlourMilano Flourkg
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aB2cD3eF4gH5iJ6kL7mN8oP9qR0sT1uV/edit", "Stock!A1:C6")

Result: A 6×3 array containing ingredient names, their suppliers, and units of measure

IMPORTRANGE fetches the Stock sheet columns A–C (rows 1–6) from the remote spreadsheet. The function pulls both headers and data, displaying the full supplier and unit information for your restaurant inventory in a single formula.

2. Check basil stock and expiry details

IngredientSupplierUnitQtyExpiry Date
BasilOrganic Herbsg2.52026-09-25
=FILTER(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aB2cD3eF4gH5iJ6kL7mN8oP9qR0sT1uV/edit", "Stock!A1:E6"), IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aB2cD3eF4gH5iJ6kL7mN8oP9qR0sT1uV/edit", "Stock!A1:A6")="Basil")

Result: A filtered array showing only the basil row with all stock details

IMPORTRANGE retrieves the complete stock data, and FILTER narrows it to rows where the ingredient name equals 'Basil'. This quickly shows your basil quantity (2.5 g) and expiry date (2026-09-25) without manually searching the source sheet.

3. Sort inventory by expiry date to see urgent stock

IngredientSupplierUnitQtyExpiry Date
BasilOrganic Herbsg2.52026-09-25
MozzarellaDairy Pluskg302026-10-10
TomatoesFarm Fresh Ltdkg1502026-10-30
FlourMilano Flourkg1002026-11-20
Olive OilItalian Imports CoL502027-01-15
=SORT(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aB2cD3eF4gH5iJ6kL7mN8oP9qR0sT1uV/edit", "Stock!A1:E6"), 5)

Result: All stock rows sorted by expiry date, earliest-expiring items first

IMPORTRANGE pulls the full 6-row dataset, and SORT reorders by column 5 (Expiry Date) in ascending order. Basil expires first (Sep 25), followed by Mozzarella (Oct 10), making it easy to prioritize which ingredients to use or order first.

Common errors

Which IMPORTRANGE error are you seeing?
IMPORTRANGE returned an error#REF!
Verify the URL is copied correctly from the browser's address bar, confirm the source spreadsheet exists, and check that you have View/Edit access permission to it.
#N/A
Open the source spreadsheet and verify the sheet name and range bounds; update the range_string to match actual data, such as 'Stock!A1:E6'.
#VALUE!
Ensure the spreadsheet_url is a complete quoted URL from a Google Sheet, and the range_string uses the format 'SheetName!A1:C5' with an exclamation mark separating sheet name from range.
ErrorWhy it happensHow to fix it
#REF!The spreadsheet URL is invalid, the spreadsheet has been deleted, or the file is no longer accessible because sharing permissions were removed.Verify the URL is copied correctly from the browser's address bar, confirm the source spreadsheet exists, and check that you have View/Edit access permission to it.
#N/AThe range_string references a sheet or range that doesn't exist in the source spreadsheet (e.g., 'Stock!Z100:Z200' when the sheet has fewer rows and columns).Open the source spreadsheet and verify the sheet name and range bounds; update the range_string to match actual data, such as 'Stock!A1:E6'.
#VALUE!The range_string is malformed or the spreadsheet_url is not a properly quoted string (e.g., missing quotes, incomplete URL, or invalid syntax like 'Sheet1A1:C5' instead of 'Sheet1!A1:C5').Ensure the spreadsheet_url is a complete quoted URL from a Google Sheet, and the range_string uses the format 'SheetName!A1:C5' with an exclamation mark separating sheet name from range.

Tips and when to use something else

  • IMPORTRANGE requires the source spreadsheet to be shared with you; if access is restricted or link sharing is disabled, the formula returns #REF!. Always verify the source file's sharing settings.
  • For live dashboards, combine IMPORTRANGE with QUERY or FILTER to transform imported data without needing to duplicate or copy entire sheets manually.
  • If you only need one or two cells, IMPORTRANGE is overkill—use IMPORTRANGE; if you need complex lookups or joins, pair it with VLOOKUP or QUERY instead of importing everything.
  • IMPORTRANGE updates automatically whenever the source sheet changes, making it ideal for shared inventory tracking. However, refreshes depend on Google Sheets calculation frequency, typically within seconds.

Related functions

QUERYQuery a dataset using SQL-like syntax to filter, sort, and extract specific rows and columns that match your conditions.FILTERReturns filtered rows from an array based on a boolean criteria array, retaining the original order and all columns.SORTSORT arranges array data in ascending or descending order by a specified column, returning a sorted copy as a dynamic array.VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.UNIQUEReturns all distinct values from an array, removing duplicates by row or column; use it to extract clean, unique item lists from repetitive data.

Frequently asked questions

Why does IMPORTRANGE show #REF! even though I copied the URL correctly?
The most common cause is insufficient permissions. The source spreadsheet must be shared with your Google account, and link sharing must be enabled in the spreadsheet settings. If you're trying to import from a file you don't own, ask the owner to grant you Viewer access with 'Anyone with the link can view' enabled, or share it directly with your email.
Can IMPORTRANGE pull data from a shared drive or team folder?
Yes, IMPORTRANGE works across Google Drive locations—personal drives, shared drives, Team Drives, and shared folders. As long as the spreadsheet is shared with your account and you have access, the full URL from the browser's address bar will work in IMPORTRANGE.
Does IMPORTRANGE update in real time when the source sheet changes?
Yes, IMPORTRANGE refreshes automatically whenever the source sheet is edited. Changes typically appear within a few seconds, depending on Google Sheets' recalculation schedule. This makes it ideal for live inventory dashboards or multi-user tracking systems.
Is there a size limit on how much data IMPORTRANGE can pull?
IMPORTRANGE can import large datasets, but the total is subject to Google Sheets' limit of 5 million cells per sheet. For very large imports, use QUERY with IMPORTRANGE to filter and reduce the dataset, which improves calculation performance and prevents hitting sheet limits.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17