I’m having trouble moving data from one Google Sheet to another using the IMPORTRANGE function. Even though I own both sheets, the system keeps requesting authorization from the original sheet owner. The function returns a reference error instead of importing the data. I’ve tried refreshing the sheet and re-entering the formula, but the permission prompt won’t go away. Has anyone encountered this issue before? What steps should I take to properly authorize the connection between my own sheets? I just want to pull specific cell ranges from my source sheet into my destination sheet without these access problems.
ugh this bug drove me nuts too! make sure you’re logged into the same google account for both sheets - sounds obvious but i had multiple accounts and that was messing things up. also try incognito mode, sometimes cached permissions get weird.
This happens because IMPORTRANGE treats each function call as a separate request, even if you own both sheets. Google Sheets needs explicit permission for new connections - it’s a security thing. Just click “Allow access” when the dialog pops up, then wait a bit for the data to load. If you missed the prompt, don’t edit the existing formula - add IMPORTRANGE in a completely new cell instead. I ran into this same issue consolidating quarterly reports. Sometimes clearing your browser cache helps if the authorization gets stuck. Once you’ve authorized it though, the connection sticks for future IMPORTRANGE calls between those sheets.
Been there, done that. IMPORTRANGE is a pain when you need reliable data syncing.
The permission thing happens because Google treats each IMPORTRANGE like a new API call. Even with your own sheets, it’s clunky and breaks randomly.
I wasted hours fighting authorization loops until I switched to automation. Now I set up workflows that handle data transfers automatically. No permission prompts, no broken formulas when someone moves a sheet.
Last project had 8 sheets feeding a master dashboard. Instead of managing multiple IMPORTRANGE functions, I built one workflow that pulls from all sources and updates everything in real time. Way cleaner and actually works.
You can add data validation, formatting, and notifications when transfers complete. Much better than hoping IMPORTRANGE cooperates.
Check out Latenode for this: https://latenode.com
Had the same headache last month setting up automated dashboards. Here’s what finally worked: keep both sheets open in the same browser session before running IMPORTRANGE. The auth dialog gets wonky if you’re jumping between tabs or browsers. Check for popup blockers too - extensions screwed me over at first. Try using the full Google Sheets URL instead of just the sheet ID in your formula. Once you get that first connection working, you can copy the formula to other cells and it’ll keep the authorization. Whole thing’s way more finicky than it should be for your own sheets.
totally feel ya! just hit the auth prompt when it pops up, even if it’s ur own sheet. it’s a bit of a hassle, but once u do that, try the formula again, and it should work! fingers crossed!