The #REF! with "You need to connect these sheets" is Google Sheets asking for permission. Click the cell that shows the error, press Allow access, and the data usually loads. If it still fails, the usual causes are a wrong sheet name or range inside the formula, or no permission to open the source file.
What does the formula look like?
IMPORTRANGE takes two arguments: the source file and the range to pull. A working example is =IMPORTRANGE("full link of the source file", "Expenses!A1:D200"). The link goes in quotes, and so does the range, which is the sheet name, an exclamation mark, then the cells.
- You can paste the full link of the source file, or only the long identifier that sits inside it.
- If the sheet name has spaces or symbols, wrap it in single quotes inside the range text, like "'April log'!A1:D200".
- Copy the sheet name exactly from the tab, including any spaces at the end.
How do I use the Allow access button?
The first time a file pulls from a source it has never touched, the cell shows #REF!. Click that cell. A small box appears with the message that you need to connect the sheets, and an Allow access button. Press it. The two files are now linked, and the same pair will not ask again.
If there is no button, the pull is failing for another reason, so read the message in the box. It usually tells you whether the problem is the link, the range or your permission.
Why does IMPORTRANGE still fail after allowing access?
| Cause | What you see | Fix |
|---|---|---|
| Wrong sheet name | A message about being unable to parse the range | Copy the tab name exactly; add single quotes if it has spaces |
| Range outside the sheet | A message that the range is invalid | Ask for a range that fits inside the data, such as A1:D200 for a sheet that has 200 rows |
| No access to the source | Message that you do not have permission | Ask the owner to give your account at least view access |
| Wrong link | Message that the file cannot be found | Open the source file in the browser and copy its address again |
| Source deleted or moved to trash | The same not-found style message | Restore the file or point to the new copy |
| Internal error | A short error with no detail | Wait a minute and reload; try a smaller range; retry in case the service was busy |
How can I test it in three steps?
- Open the source link in the same browser, signed in as the same Google account you use in the destination file. If it will not open, the problem is permission, not the formula.
- In a blank cell of the destination, test a tiny pull, =IMPORTRANGE("full link", "Sheet1!A1"), with the right sheet name. One cell is enough. Press Allow access if asked.
- Widen the range a little at a time. When it breaks, the last edit you made is the cause, so you know whether it was a typo in the range or a size problem.
What if the data loads but looks wrong?
A pulled block spills into the cells below and to the right of the formula. If something already sits there, the formula shows an error that says the array result was not expanded. Clear the area, or put the formula on a blank tab.
Also remember that the pulled cells are formulas, not values. If the source owner renames the sheet or removes the columns you pulled, your file breaks at once. For a monthly sheet shared with a spouse or a CA, keep the source tab names stable and avoid inserting columns in the middle of a range that someone else pulls from.
A note on privacy
When you share your file with someone, they see the formula, and with it the link to the source file. They cannot open the source without permission, but they can see the pulled values. Be sure you are happy with them seeing every cell in the range you pull.
Fix list
- Click the #REF! cell and press Allow access.
- Check sheet name and range text character by character.
- Confirm the same Google account can open the source.
- Test one cell, then widen the range.
- Keep a values-only copy of any month you report from.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


