Skip to content
Sheet ErrorSheet ErrorSheet Error

Fix IMPORTRANGE “Result too large” in Google Sheets

IMPORTRANGE can fail after you already clicked Allow access. The tooltip is Result too large. The pull asked for more data than one import request will return (Sheets caps received data at 10 MB per request). That is a size problem, not a permission problem.

You need two spreadsheets you own for this page. A 10 MB grid will not fit in a three-cell demo, so the recreate is a working small pull, then the range change that blows the cap on a real source, then the shrink.

Recreate it on two sheets you own

Section titled “Recreate it on two sheets you own”
  1. Source. Open a spreadsheet that already has a wide or tall tab — an export, a dump, a year of rows. If you only have a blank file, this error will not appear yet; keep a small range for the control below. Copy the spreadsheet id from the URL (the string between /d/ and /edit).
  2. Destination. Open a second blank spreadsheet. In A1 enter =IMPORTRANGE("SOURCE_ID","Sheet1!A1:B2"), pasting your id and the real tab name. If A1 is #REF! with You need to connect these sheets, that is IMPORTRANGE access. Click Allow access. Wait until the small range spills. That control proves the pair is connected.
  3. Widen until it fails. Change only the range string. Typical patterns that trip the cap:
    • An open-ended block on a fat tab: Sheet1!A:ZZ (thousands of empty columns still count once they are in the request).
    • A closed but huge block you actually filled: Sheet1!A1:Z50000.
  4. When the pull is over the cap, the destination cell is an error and the tooltip is Result too large. That is usually #ERROR! in the cell, not the Allow-access #REF!. Read the tooltip. Do not click Allow access again.

If your source is still tiny, the widen step will keep working. You have the control formula. The rest of this page is what to do when Result too large is already on the sheet — or how to avoid it before you paste A:ZZ.

The exact cell count that fails is not a published round number. Heavy cells (long strings, many unique values) hit the 10 MB cap sooner than a grid of small integers. Treat “tens of thousands of cells per formula” as a working budget, not a guarantee.

What is actually wrong

Section titled “What is actually wrong”
  • One IMPORTRANGE request downloads the whole range you named, then spills it. The cap is on that download. Sheets is not refusing the formula syntax.
  • Sheet1!A:ZZ is a common way to get here on a tab that “only has twenty used columns.” Open columns in the range string still sit in the import.
  • Wrapping the huge import in IFERROR(...,"") hides the size error the same way it hides a dead range. The data is still too big. Remove the wrapper and read the tooltip.
  • This is not You need to connect these sheets. After Allow access, later formulas to the same source do not ask again. Result too large happens with permission. See IMPORTRANGE access only if the tooltip is the connect prompt.
  • This is not a deleted tab or a #REF! after you delete a cell. See after you delete. The range string is still readable in the formula bar.
  • This is not QUERY select A on an import array (#N/A / no column). That is Col1 vs A. Fix column names after the import is small enough to run.

Chained imports (file C pulls file B which pulls file A) make each refresh heavier. Limit the chain. Pull a summary from the source instead of the raw dump when you only need a total.

Fix

Section titled “Fix”

Leave the failing formula in A1 if you already have Result too large, so you can compare. Put each shrink in a neighboring cell. Paste your id wherever the formulas say SOURCE_ID. Use the real tab name.

Narrow the range string. Import the used rectangle, not the tab. In C1 enter =IMPORTRANGE("SOURCE_ID","Sheet1!A1:C5000") (adjust to the columns and last row you actually need). Closed bounds. No A:ZZ. If you do not know the last row, check it on the source (Ctrl+Down on the key column) and write that row number.

Wrap QUERY so the result is smaller. Import only the columns and rows the destination uses. In E1 enter:

=QUERY(IMPORTRANGE("SOURCE_ID","Sheet1!A1:F5000"),"select Col1, Col2, Col6 where Col6 is not null",1)

QUERY on IMPORTRANGE uses Col1, Col2, … — not A, B. See Col1 vs A if that wrapper is #N/A. The where clause is what drops empty rows. select * on the same huge range does not shrink anything.

Split the pull, then stack. Two requests under the cap can replace one request over it.

  • Rows: =VSTACK(IMPORTRANGE("SOURCE_ID","Sheet1!A1:C8000"),IMPORTRANGE("SOURCE_ID","Sheet1!A8001:C16000"))
  • Columns: =HSTACK(IMPORTRANGE("SOURCE_ID","Sheet1!A1:M8000"),IMPORTRANGE("SOURCE_ID","Sheet1!N1:Z8000"))

Each IMPORTRANGE is its own request. QUERY around a split piece still numbers columns from that piece (Col1 is the left column of that range, not of the original tab).

Shrink on the source, then import the result. If the destination only needs a total, a last-row snapshot, or a filtered extract, compute that on the source tab and IMPORTRANGE the small output. Pulling a million rows to SUM them in the destination is the slow version of the same number.

Do not “retry” with the same A:ZZ string. Do not turn on iterative calculation. Do not wrap Result too large in IFERROR and call it fixed. Name a smaller range, or several smaller ranges, until the tooltip is gone and the spill matches the source cells you actually needed.