Google Sheets IMPORTRANGE #REF! error? Check which access it needs

Open the destination sheet in a desktop browser. Select the cell with the formula and look for Allow access in the error message. Before connecting the files, check who can edit the destination. Once connected, those editors can use IMPORTRANGE to pull from other parts of the same source spreadsheet, even if your formula imports only a small range.

Open the exact source link from the formula. If it opens under another Google account, switch to the account you use to edit the destination and try again. Ask the source owner to share it with that account if necessary. If the files belong to different organizations, sharing rules can still block access. If the message specifically says an admin has not allowed imports from the URL, send the error and source link to your Workspace administrator.

If the access prompt is gone and the import still fails, check the source tab name and start with a small range. For example, try importing just the header row into an empty area of the destination. If that works, expand to the rows and columns you need. For a large report, summarize the data in the source first so the destination imports a smaller result.