Both files are mine, one is shared with four colleagues, and the link worked fine yesterday. This morning every cell in the destination shows #REF and hovering offers the connect button, which I click, and nothing changes. The only thing I did in between was rename a tab in the source file and tidy some columns. Is the authorisation broken or did I break the formula without noticing?
Watch the count as this grows. Each IMPORTRANGE is a separate fetch and they refresh on their own schedule, so a file with dozens of them spends its life recalculating and shows stale numbers in between. I consolidated a file from thirty odd imports down to four and the load time went from painful to unremarkable.
I built a reporting file on twelve separate IMPORTRANGE calls and someone renamed a tab in one source, which killed the lot on a Monday morning. Now I import whole sheets as a wide range like A:Z into hidden staging tabs and do all the slicing locally with QUERY. Fewer strings to break, and when something does break it breaks in one place I can find.
One thing worth knowing before you share this with the team: the permission is tied to accounts, not just to the files. A colleague who can open both files may still see the connect prompt in their own session, and if they do not have edit rights on the destination they cannot resolve it. That is why these dashboards work perfectly for the person who built them and are broken for everyone else.
This explains a complaint from last month that I never got to the bottom of. Thank you.
Put the source URL in a cell and reference that cell in every formula. When the file moves or you copy the whole thing for next year, you change one cell instead of hunting through forty formulas.
If this file is going to be load bearing for a team, at some point stop doing it with formulas. A scheduled script that writes a flat snapshot into one tab is boring, but it does not silently change under people while they are reading it.
@xlookup_lena · 4d ago · 3 replies
You broke the formula. The second argument of IMPORTRANGE is a text string, not a reference, so renaming the tab in the source leaves the destination asking for a sheet that no longer exists, and the error looks identical to the authorisation error. Fix the name in the string and it will come back without any authorising. The genuine auth prompt only appears once per pair of files per account, and once you have granted it, it stays granted.
Reply
Report
@pomodoro_pat · 4d ago
That is exactly it. Two errors that look the same and I spent an hour clicking a button that was never going to help.
Reply
Report
@row_level_sam · 5d ago
The other half of the trap is tab names with spaces or apostrophes, which need quoting inside the string. That one has eaten a morning of mine more than once.
Reply
Report