Fix Google Sheets Stuck on "Loading..." — Data Not Updating in Real-Time Between Sheets

TL;DR

That endless Loading... spinner usually means a formula — IMPORTRANGE, QUERY, or ARRAYFORMULA — can't fetch or refresh data from another sheet. Three fastest fixes: re-authorize IMPORTRANGE by clicking the cell and hitting Allow access, switch recalculation to File → Settings → On change and every minute, or hard-refresh the tab with Ctrl+Shift+R / Cmd+Shift+R.

What's Actually Happening

Google Sheets has no native push-sync between spreadsheets. Everything is pull-based — formulas reach out and grab data on demand. So when you see Loading... frozen on screen, the formula is stuck mid-fetch. Six things typically cause this:

IMPORTRANGE not authorized — the source spreadsheet hasn't granted access yet. The formula just waits. Forever.
Recalculation set to "On change only" — Sheets won't re-fetch external data unless you edit something in the sheet first.
Circular dependency — Sheet A references Sheet B, Sheet B references Sheet A. Evaluation loops and freezes.
Quota exceeded — Google caps IMPORTRANGE at 50 cross-spreadsheet references per spreadsheet. Hit that limit and extra cells stay stuck.
Stale browser cache — the Sheets web app loads a cached, broken state. Happens more often than you'd expect.
Source sheet deleted or access revoked — IMPORTRANGE loses its connection silently. No error, just Loading....

Fix 1: Re-authorize IMPORTRANGE Access

Start here — this fixes the problem about 70% of the time. Click the cell showing Loading... or #REF!. A popup should appear asking you to Allow access. No popup? Do this:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/SOURCE_SHEET_ID/edit", "Sheet1!A1:D100")

Delete the formula from the cell.
Re-paste it and press Enter.
Click the cell again — Allow access should appear this time.
Click it. Data loads within a few seconds.

Authorization is a one-time handshake per source-destination pair. You won't need to redo it unless access is revoked.

Fix 2: Change the Recalculation Setting

Out of the box, Sheets recalculates only when you make an edit. That means your IMPORTRANGE formula can sit stale for hours if no one touches the sheet. Switch it to refresh automatically:

Open the destination spreadsheet.
Go to File → Settings.
Under the Calculation tab, change Recalculation from On change to On change and every minute.
Click Save settings.

Every minute is the fastest Sheets supports natively. Need faster? Skip to Fix 5.

Fix 3: Hard Refresh the Browser Tab

A stale cache can make Sheets behave like the formula is broken when it's actually fine. Normal refresh (F5) won't cut it — you need to bypass the cache entirely:

Windows/Linux: Ctrl+Shift+R
macOS: Cmd+Shift+R

Sheet loads correctly after that? Cache was the problem. While you're at it, disable any browser extensions — ad blockers and privacy tools frequently intercept Google Workspace requests and silently break things.

Fix 4: Check for Circular Dependencies

Circular references are sneaky. Unlike Excel, Sheets won't always throw a visible error — it just hangs on Loading.... Here's how to track one down:

Go to Tools → Check for errors (if available in your version) or manually trace your formula chain.
Look for any formula in Sheet A referencing Sheet B, while Sheet B references Sheet A.

Break the loop by introducing a helper column with a static value. Or restructure the data flow so it only moves in one direction — source feeds destination, never the other way around.

Fix 5: Replace IMPORTRANGE with Apps Script for Real-Time Sync

IMPORTRANGE updates at most once per minute. For dashboards or data pipelines that need faster refresh, Apps Script with a time-driven trigger is a much better fit:

// In the destination spreadsheet → Extensions → Apps Script
function syncFromSource() {
  const sourceId = 'YOUR_SOURCE_SPREADSHEET_ID';
  const sourceSheet = SpreadsheetApp.openById(sourceId).getSheetByName('Sheet1');
  const destSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Destination');

  const data = sourceSheet.getRange('A1:D100').getValues();
  destSheet.getRange('A1:D100').setValues(data);
}

Wire up a trigger to run it on a schedule:

In the Apps Script editor, click Triggers (the clock icon in the left sidebar).
Add trigger → syncFromSource → Time-driven → Minutes timer → Every minute.
Save. The script runs independently — no browser tab needs to stay open.

This approach also sidesteps the 50-cell IMPORTRANGE quota and handles large datasets (think 5,000+ rows) far more reliably than native formulas.

Fix 6: Stay Under the IMPORTRANGE Limit

Google caps IMPORTRANGE at 50 cross-spreadsheet references per spreadsheet. Exceed that and the overflow cells hang on Loading... with no explanation. Run this script to count how many you're using:

function countImportRange() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const allSheets = sheet.getSheets();
  let count = 0;
  allSheets.forEach(s => {
    const formulas = s.getDataRange().getFormulas().flat();
    count += formulas.filter(f => f.toUpperCase().includes('IMPORTRANGE')).length;
  });
  Logger.log('Total IMPORTRANGE cells: ' + count);
}

Over 50? Consolidate. Pull a wide range with a single IMPORTRANGE — say, columns A through Z from the entire source sheet — then use QUERY or INDEX/MATCH to extract only what each section needs. One import, many consumers.

Verify the Fix Worked

Cells that showed Loading... now display actual data.
Edit a value in the source sheet, wait up to 60 seconds — the destination updates automatically.
No #REF!, #ERROR!, or Loading... cells remain in the affected range.
Using Apps Script? Check Extensions → Apps Script → Executions to confirm the sync function runs on schedule with no failures.

Quick Reference

IMPORTRANGE hangs → click cell → Allow access
Data doesn't refresh automatically → File → Settings → Recalculation: every minute
Browser glitch → Ctrl+Shift+R / Cmd+Shift+R (hard refresh)
Circular reference → trace and break the formula loop
Over 50 IMPORTRANGE cells → consolidate with QUERY on fewer ranges
Need faster than 1-minute sync → Apps Script with a time-driven trigger