If external data in Google Sheets isn’t loading, IMPORTRANGE, IMPORTXML, IMPORTDATA, or GOOGLEFINANCE return #N/A, #REF!, or stay stuck on “Loading…”.
This usually shows up as blank cells, delayed updates, or incomplete imports.
This guide fixes it step by step.
Why the Issue Happens
- Permission not granted (
IMPORTRANGE) - Incorrect URL, range, or query syntax
- Source website blocking requests (
IMPORTXML,IMPORTHTML) - Too many external calls (quota limits)
- Data size too large
- Network or browser issues
- Volatile functions not triggering refresh
- Source file or API temporarily unavailable
Step-by-Step Fixes
Step 1: Grant Access for IMPORTRANGE
Most common issue.
=IMPORTRANGE("FILE_ID", "Sheet1!A:B")
Fix:
- Enter formula
- Click the cell
- Click “Allow access”
Without this, data will not load.
Step 2: Verify URL or File ID
Incorrect ID = no data.
Correct formats:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/FILE_ID/edit", "Sheet1!A:B")
or
=IMPORTRANGE("FILE_ID", "Sheet1!A:B")
Double-check the ID from the source file.
Step 3: Fix Range Syntax
Correct:
"SheetName!A1:C100"
If sheet name has spaces:
"'Sales Data'!A:B"
Wrong syntax breaks import.
Step 4: Test with Simple Import First
Before complex formulas:
=IMPORTRANGE("FILE_ID", "Sheet1!A1")
If this fails, issue is connection—not logic.
Step 5: Handle IMPORTXML / IMPORTHTML Failures
Problem:
- Website blocks scraping
- XPath incorrect
Example:
=IMPORTXML("url", "//table")
Fix:
- Verify URL loads in browser
- Test XPath separately
- Try different XPath
If site blocks requests → cannot be fixed inside Sheets.
Step 6: Reduce Data Size
Large imports fail or slow down.
Wrong:
=IMPORTRANGE("FILE_ID", "Sheet1!A:Z")
Better:
=IMPORTRANGE("FILE_ID", "Sheet1!A1:D1000")
Limit range to required data.
Step 7: Avoid Too Many External Calls
Multiple imports slow or break loading.
Problem:
=IMPORTRANGE(...) repeated many times
Fix:
- Import once in a helper sheet
- Reference locally
Step 8: Force Refresh
External data doesn’t always update automatically.
Fix:
=IMPORTRANGE("FILE_ID", "Sheet1!A:B") + NOW()*0
This forces recalculation.
Step 9: Fix GOOGLEFINANCE Delays
Example:
=GOOGLEFINANCE("NSE:RELIANCE")
Issues:
- Delay in data
- Temporary unavailability
Fix:
- Wait and refresh
- Re-enter formula
Step 10: Check Browser and Network
Sometimes issue is not Sheets.
Fix:
- Hard refresh:
Ctrl + Shift + R - Clear cache
- Try Incognito mode
- Ensure stable internet
Common Mistakes
- Not clicking “Allow access” for IMPORTRANGE
- Using wrong file ID or URL
- Incorrect range syntax
- Importing too much data
- Using too many external formulas
- Expecting instant refresh
- Ignoring site restrictions for IMPORTXML
Pro Tips / Better Alternatives
Use QUERY with IMPORTRANGE
=QUERY(IMPORTRANGE("FILE_ID", "Sheet1!A:C"), "SELECT Col1, Col2", 1)
Reduces data load and improves performance.
Use Helper Sheet for External Data
Best practice:
- One sheet for imports
- Use local references elsewhere
Improves speed and stability.
Schedule Refresh Using Volatile Trigger
Use:
=NOW()
and reference it to trigger updates.
Avoid Full Column Imports
Instead of:
A:Z
Use:
A1:Z1000
Better performance.
Monitor Source Availability
If source file/site is down:
- Import will fail
Always verify source first.
Bottom Line
If external data isn’t loading, fix in this order:
- Grant access (IMPORTRANGE)
- Verify file ID and range syntax
- Test with simple import
- Reduce data size
- Avoid multiple external calls
- Force refresh if needed
Most issues come from permissions, syntax errors, or performance limits.
Fix those, and your external data will load reliably.
Google Sheets Fixes: