IMPORTRANGE #REF!: "You need to connect these sheets"

#REF!
You need to connect these sheets. Allow Access.

You don't have permissions to access that sheet

Why it happens

The Google Docs Editors Help page for IMPORTRANGE states the rule directly: spreadsheets "must be explicitly granted permission to pull data from other spreadsheets using IMPORTRANGE. The first time the destination sheet pulls data from a new source sheet, the user will be prompted to grant permission."

So the #REF! is not a broken formula. It is a permission gate between one pair of files: the destination spreadsheet (the one holding the formula) and the source spreadsheet (the one named in the URL). The same help page describes two situations:

• You own the source spreadsheet. A few seconds after you finish the formula, a #REF! error appears with "You need to connect these sheets. Allow Access." Clicking Allow Access grants the permission.
• You do not own the source spreadsheet. A message about not having permission to access that sheet appears instead. You open the source spreadsheet's URL in your browser, request access, and wait for the owner to grant it.

Once access is granted, Google says "any editor on the destination spreadsheet can use IMPORTRANGE to pull from any part of the source spreadsheet". That access stays in place until the user who granted it is removed from the source file. Google also notes that the access given to the destination sheet counts against the source sheet's 600-user share limit. The Drive sharing help gives the same figure: a single file can be shared with up to 600 individual email addresses.

How to fix it

1. Check the formula shape. The syntax from Google is:

   =IMPORTRANGE(spreadsheet_url, range_string)
   

   Both arguments must be either text in quotation marks or a reference to a cell that holds the text. A complete formula looks like this:

   =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "Sheet1!A1:C10")
   

   If the URL sits in A2 and the range text in B2, the cell-reference form is:

   =IMPORTRANGE(A2, B2)
   

2. Connect the two files. Select the cell showing #REF!. If you own the source, click Allow Access in the prompt. You only do this once per source file for this destination file; other IMPORTRANGE formulas in the same destination that point at the same source then work without another prompt.

3. If you do not own the source, get access first. Paste the source URL into your browser's address bar, request access, and wait for the owner to grant it. The Drive help lists three roles a file can be shared with: Viewer, Commenter and Editor. Google's IMPORTRANGE page does not name a minimum role, so ask the owner for whichever access they are prepared to give, then return to step 2.

4. Ask the owner about download and copy restrictions. Google's IMPORTRANGE page warns that if "Disable options to download, print, and copy" is turned on in a source spreadsheet, new IMPORTRANGE formulas cannot export data from it. Formulas set up before the setting was turned on keep working. In the Drive help on limiting sharing, the owner finds this under Share, then Settings, then the checkboxes under "People who can download, copy, and print".

5. Check the range string. Google defines it as text in the format "[sheet_name!]range", for example "Sheet1!A2:B6" or "A2:B6". The sheet name is optional; without it, IMPORTRANGE imports from the given range of the first sheet in the source file. If you meant a different tab, add its name. Google's samples also accept a named range and a table reference:

   =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "Sales_total")
   =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "DeptSales[Sales Amount]")
   

   The range string has to name a tab, range, named range or table that exists in the source file.

6. Keep the spreadsheet URL whole. Google describes the first argument as "the URL of the spreadsheet from where data will be imported", and its samples use the full https://docs.google.com/spreadsheets/d/... address. Copy it from the source file's address bar rather than retyping it.

Check that it worked

• The #REF! disappears and the cell fills with the imported values from the range you asked for.
• Write a second IMPORTRANGE in the same destination file pointing at a different range of the same source. It should load without asking you to connect the sheets again, because the permission belongs to the pair of files, not to a single formula.
• Change a value in the source range. Google says an update to the source causes open receiving documents to refresh and show a green loading bar, with a delay that grows when several files are chained together.

If the result loads but shows the wrong data, check whether the range string leaves out the tab name, so that it reads from the first sheet instead of the tab you meant (step 5).

When IMPORTRANGE shows Loading... or #ERROR! instead of #REF!

Once the files are connected, Google's help page documents other messages that are not about permission.

"Loading..." that does not finish. When import functions create too much traffic, the cell can show "Loading..." with the detail "Loading data may take a while because of the large number of requests. Try to reduce the amount of IMPORTHTML, IMPORTDATA, IMPORTFEED, or IMPORTXML functions across spreadsheets you've created." Google says the limit is enforced on the creator of the document, and counts all import functions across the open documents that person created; an edit by a collaborator can also count against the quota. The recommended fix is to reduce churn: if the URL or range argument changes often, each change can trigger new external calls.

Large ranges. IMPORTRANGE is an external data function, capped at 10 MB of received data per request. Google's advice is to import less: put the summarising calculation (a SUM, for example) in the source file and import only its result, rather than importing every row and calculating locally. The guide to optimising data references adds that IMPORTRANGE always goes through the internet, even when you own both files. As general advice on formula speed, the same guide recommends closed ranges such as A1:B6 over open ranges such as A:B.

#ERROR! about volatile functions. If the formula refers, directly or indirectly, to a cell that uses NOW, RAND or RANDBETWEEN, Google says you get #ERROR! with:

This function is not allowed to reference a cell with NOW, RAND, or RANDBETWEEN.

TODAY is the one exception. The documented fix is to copy the volatile result, paste it back using special paste with Values only, and point the import at those static values.

Cycles. If file A imports from file B and file B imports from file A, Google says each keeps trying to reference the other and never produces output. Break the loop so data flows in one direction.

The "You need to connect these sheets" error clears with one click on Allow Access when you own the source file, or once its owner grants you access. After that, check the range string, especially the tab name, and keep imports small, given the 10 MB cap per request and the traffic limit on import functions. If you also work with the same kinds of lookup in Excel, these Excel pages cover the errors there: XLOOKUP returns #N/A when the value exists when XLOOKUP cannot find a value you can see in the data, Excel's #SPILL! error on dynamic array formulas, and VLOOKUP not working when VLOOKUP returns an error or the wrong value. They describe Excel behaviour from Microsoft's documentation; Google Sheets has its own versions of these functions, so do not assume each rule carries over.