Google Sheets IMPORTRANGE: Permission, Limits and Fixes

Two arguments, both text

Google's help page gives the syntax as IMPORTRANGE(spreadsheet_url, range_string). The first argument is the address of the source spreadsheet. The second is a string in the form "[sheet_name!]range", such as "Sheet1!A2:B6" or just "A2:B6".

Both can sit in quotation marks or come from a cell. If you leave out the sheet name, Sheets reads the first sheet of the source. Google also shows two other range forms: a table reference such as "DeptSales[Sales Amount]" and a named range such as "Sales_total".

A typed example from the help page is IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A1:C10"). The short form IMPORTRANGE(A2,"B2") reads the address from cell A2 of the current sheet.

Allow Access: the permission step that trips everyone

Sheets will not hand over data from a source until the source has said yes. The first time a destination pulls from a new source, you are prompted.

If you own the source, the cell shows #REF! with the message "You need to connect these sheets." Click Allow Access and the data loads. If someone else owns it, you see "You don't have permissions to access that sheet." Request access and wait for the owner to grant it.

• Once access is granted, any editor of the destination can pull from any part of the source, not only the range you first used.
• Access lasts until the user who granted it is removed from the source.
• Access given to the destination counts against the 600-user share limit of the source.
• If the source has Disable options to download, print, and copy switched on, new IMPORTRANGE formulas cannot export data. Formulas that already existed keep working.

A worked example: monthly totals from a team file

Suppose a team keeps orders on a sheet named Orders with 20,000 rows, and your report file needs the monthly totals only. The source has a summary block in F1:G13: 12 months in column F and totals in column G.

Import just that block with =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "Orders!F1:G13"). That is 26 cells instead of 20,000 rows times every column.

If you only need one number, such as a yearly total in G14, import Orders!G14 and you move a single cell. Google gives the same advice in its own words: compute a sum in the source and import the single result instead of 1,000,000 rows.

Wrap the call when you also want to filter. =QUERY(IMPORTRANGE(A2,"Orders!F1:G13"), "select Col1, Col2 where Col2 > 5000", 1) imports once and filters in the destination.

10MB per request, and an hour between rechecks

IMPORTRANGE is an external data function. It needs an internet connection and it downloads the whole range, so a slow network slows it. Google caps each request at 10MB of received data. If a range is too big, shrink it or summarise in the source first.

Freshness works on a timer. While a document is open, IMPORTRANGE checks for updates every 1 hour, even if nothing changed. A document that is opened, or was opened within the past 5 minutes, reloads. Deleting, reading or overwriting cells with the same formula triggers a reload too, but simply opening and reloading the document does not.

Any update to the source refreshes every open receiving document, and the formula waits for the source to finish its calculations, even if the imported range needs none.

Why NOW, RAND and RANDBETWEEN break it

Import functions cannot reference NOW, RAND or RANDBETWEEN, directly or through another cell. You get #ERROR! with the message "This function is not allowed to reference a cell with NOW, RAND, or RANDBETWEEN."

TODAY is the exception, because it updates no more than once per day. If you need the result of a volatile function, copy it, use Special paste, then Values only, and point the import at the pasted values.

Loading... and the usage limit

Too much traffic produces a Loading... cell and the error "Loading data may take a while because of the large number of requests." Google's wording mentions IMPORTHTML, IMPORTDATA, IMPORTFEED and IMPORTXML next to IMPORTRANGE.

The limit is counted against the creator of the document, across all of their open documents, and collaborators' edits can count against that creator's quota. A common cause is an argument that changes often. If the argument in =IMPORTRANGE(arg) changes frequently, many external calls are made.

Chains, cycles and the size of the file

Chained imports (sheet A to B to C) pass updates along, and each layer adds delay. Google asks you to limit chains and the number of receiving sheets, since every receiver reads from the source. Avoid cycles, where two spreadsheets import from each other, because they loop without producing output.

A whole Sheets file can hold up to 20 million cells or 100MB, according to Google's Drive limits page. For large datasets Google points to Connected Sheets, which has scheduled refresh, and to Apps Script, which can run on edit or on a schedule.

Common mistakes

Leaving the quotation marks off the URL or range text is the usual first slip, and Google's format also needs the exclamation mark between the sheet name and the range. Importing a whole wide range when only 13 rows matter spends the 10MB request budget for nothing. A report that imports the same source on 15 tabs makes 15 readers of one file, and Google asks you to limit the number of receiving sheets.

If a #REF! message appears and you are not sure whether it is a permission or a range problem, Crade can read the screen and tell you which message it is.

Who it suits and who should look elsewhere

IMPORTRANGE suits a report that needs a small, steady slice of one or two source files. It is a poor fit for a source with a million rows, and for anyone who needs updates faster than the hourly check. In those cases Google's own alternatives, Apps Script and Connected Sheets, are named for the job.