Google Sheets IMPORT functions · error message

Google Sheets: You need to connect these sheets

ConfigurationPermissions and access

Last verified

The exact message
#REF! You need to connect these sheets. Allow access

What it means

This is not a failure so much as a one-time consent step. Google requires that a spreadsheet be explicitly granted permission to pull data from another spreadsheet with IMPORTRANGE. Until someone with access clicks Allow access, the formula shows #REF! with this message.

Once granted, Google's help page says the access remains in effect until the user who granted it is removed from the source file. The grant also counts against the source file's 600-user share limit.

What is not wrong: the URL or range. If either were wrong you would see a different message after allowing access.

What it looks like

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1AbC...xyz", "Orders!A1:F500")

#REF!
You need to connect these sheets.
[Allow access]

Why it happens

  • This is the first IMPORTRANGE from this source file into this destination file.
  • The formula was added by a script or by copying a sheet, so no one has clicked the prompt yet.
  • The person who originally granted access was removed from the source file.
  • The source spreadsheet was replaced with a copy that has a new ID.

How to fix it

  1. Click the cell showing #REF!, hover over it, and click Allow access in the tooltip.
  2. If no button appears, make sure you have at least view access to the source file and are signed into that account.
  3. If the formula was written by a script, open the sheet once as a user with access and grant it manually; the prompt cannot be accepted by the formula itself.
  4. If the source file was duplicated, update the URL or ID in the formula to the current file and allow access again.

How to stop it recurring

Grant access from an account that will stay on the source file long term, such as a team or service owner, rather than an individual who may leave. Keep source IDs stable by editing files in place instead of replacing them with copies, and document which files feed which so a broken connection is easy to trace.

Official documentation: support.google.com/docs/answer/3093340. See all Google Sheets error messages or the Google Sheets limits and quotas.

Related errors

Definitions

Quick answers

What does "You need to connect these sheets" mean in Google Sheets?

This is not a failure so much as a one-time consent step. Google requires that a spreadsheet be explicitly granted permission to pull data from another spreadsheet with IMPORTRANGE. Until someone with access clicks Allow access , the formula shows #REF! with this message. Once granted, Google's help page says the access remains in effect until the user who granted it is removed from the source file.

How do I fix "You need to connect these sheets"?

1. Click the cell showing #REF!, hover over it, and click Allow access in the tooltip. 2. If no button appears, make sure you have at least view access to the source file and are signed into that account. 3. If the formula was written by a script, open the sheet once as a user with access and grant it manually; the prompt cannot be accepted by the formula itself. 4. If the source file was duplicated, update the URL or ID in the formula to the current file and allow access again.

When an IMPORT formula cannot read the page

IMPORTXML and IMPORTHTML only see the HTML the server sends. An Autonoly extraction step reads the page in a real browser and writes rows to Google Sheets. Sites that block automated visitors can still block it.

Also see Google Sheets integration.