Fix Google Sheets IMPORTRANGE Error: "You need to connect these sheets. Allow access to import data from the other spreadsheet."

The Error

You've written an IMPORTRANGE formula, but instead of data, the cell shows an error. Hover over it and you'll see:

You need to connect these sheets. Allow access to import data from the other spreadsheet.

That stops the import cold. Every cell referencing that formula displays #REF! until you fix it.

Why This Happens

Google Sheets locks down cross-spreadsheet access by design. The first time IMPORTRANGE tries to pull data from another file, the destination sheet has no permission to read from the source. Someone with edit access to the destination must click Allow — once is enough, but that click is mandatory.

You'll hit this error when:

• It's the first time using IMPORTRANGE with a particular source spreadsheet
• The source spreadsheet URL or ID changed
• The person who originally granted access lost their access to the source
• The source spreadsheet moved to a different Google Drive or organization
• You copied a spreadsheet containing an IMPORTRANGE formula — copies don't inherit permissions

Fix 1: Click "Allow Access" (Works 90% of the Time)

Start here. Most of the time, this is all you need.

• Open the destination spreadsheet — the one with the IMPORTRANGE formula.
• Click the cell showing the error.
• A small popup appears near the cell: "You need to connect these sheets" with an Allow access button.
• Click Allow access.
• Give it a few seconds. The cell refreshes and your data appears.

Heads up: You need at least view access to the source spreadsheet, plus edit access to the destination. With only view access to the destination, the button won't show up for you.

Fix 2: Verify the IMPORTRANGE Formula Syntax

If the Allow access popup never appears — or clicking it does nothing — look at the formula itself.

=IMPORTRANGE("spreadsheet_url_or_id", "sheet_name!range")

Common mistakes to check:

• The URL must be the full link — shortened URLs don't work. The spreadsheet ID alone is fine too.
• If someone renamed a tab, the formula still uses the old sheet name. Update it.
• Missing quotes around the range string will break the formula silently.

A working formula using the full URL:

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

Or using just the spreadsheet ID (shorter and cleaner):

=IMPORTRANGE("1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms", "Sheet1!A1:D100")

Fix 3: Check Your Access to the Source Spreadsheet

Clicking Allow access isn't enough if you can't actually open the source spreadsheet. The permission grant only works if you currently have view access to the source.

• Copy the source spreadsheet URL from your IMPORTRANGE formula.
• Paste it into a new browser tab and try to open it.
• "You need to request access" or a 403 page means that's your real problem — not the formula.
• Ask the spreadsheet owner to share it with at least Viewer permission, then go back and click Allow access again.

Worth knowing: the original person who granted access doesn't have to stay involved forever. The authorization is stored at the spreadsheet level. But someone with current view access to the source must have clicked Allow at some point.

Fix 4: Re-grant Permission After Copying a Spreadsheet

Copied spreadsheets don't carry over IMPORTRANGE permissions. The copy is treated as a brand-new file, so Google Sheets requires a fresh authorization.

• Open the copied spreadsheet.
• Find the cell with the IMPORTRANGE formula.
• Click it and look for the Allow access popup.
• No popup? Click the cell, press F2 to enter edit mode, then press Enter to re-evaluate the formula.
• The permission popup should appear now. Click Allow access.

Fix 5: Use a Service Account or Shared Drive (For Teams)

Tying IMPORTRANGE access to one person is a reliability risk. If that person leaves the project or loses source access, imports can break silently — sometimes days later when nobody's watching.

More resilient setups for team environments:

• Share the source spreadsheet with a Google Group instead of individuals. Any group member can then grant IMPORTRANGE access from their account.
• On Google Workspace, move both spreadsheets into a Shared Drive. Permissions attach to the drive, not individual users.
• For Apps Script or API automation, use a service account with explicit access to the source. It won't expire when someone changes teams.

Verification Steps

After applying the fix:

• The cell should show imported data instead of the error.
• Still showing #REF!? Hover over the cell — the error text may have changed. A different message (wrong range, misspelled sheet name) means a new problem to solve.
• Go to Data → Data connectors in the destination spreadsheet. The source should be listed there as a connected spreadsheet.
• Edit a value in the source and confirm the destination reflects the change within 1–2 minutes.

Prevention

• Keep a record of linked spreadsheets. A cell comment or a dedicated "Links" tab listing source spreadsheet IDs takes two minutes to set up and saves hours of debugging when someone reorganizes files.
• Use named ranges in the source. Define a named range (e.g., SalesData) in the source spreadsheet instead of hardcoding Sheet1!A1:D100. Named ranges survive tab renames and minor structural changes.
• Walk new team members through permission prompts on day one. New editors who open a shared spreadsheet for the first time may see stale IMPORTRANGE errors — they need to click Allow access themselves before data loads for them.
• Don't rely on a single owner's access for production dashboards. Use Shared Drives or Google Groups so the import doesn't go dark the moment one person's permissions change.