IMPORTRANGE Not Updating? Here's Why It Stops and How to Fix It Permanently

IMPORTRANGE stops refreshing silently. No red error cell. No warning banner. The data just sits there looking current while the source sheet has moved on. A Stack Overflow question about force-refreshing IMPORTRANGE has collected 255,648 views. This problem affects a large number of Google Sheets users and Google's documentation barely acknowledges it.

This guide covers every known cause of IMPORTRANGE failing to update, how to diagnose which one you are hitting, and the exact fix for each. It also covers when IMPORTRANGE is the wrong tool entirely and what to use instead.

What IMPORTRANGE Actually Does (and How Caching Breaks It)

IMPORTRANGE pulls a range of cells from one Google Sheet into another. Under the hood, Google runs the cross-sheet fetch in the background and caches the result. The formula does not re-execute every time you open the sheet or every time the source data changes. Google decides when to refresh it, and that decision is not fully documented.

In practice, IMPORTRANGE refreshes on a schedule that appears to be somewhere between every 30 minutes and a few hours under normal conditions. When any of the six failure modes below occur, the refresh either stops entirely or silently returns the last cached value without indicating that the data is stale.

This is the core problem: IMPORTRANGE has no built-in freshness indicator. A cell showing data from three days ago looks identical to a cell that refreshed five minutes ago.

Cause 1: Source Sheet Was Deleted, Renamed, or Moved

What happens: IMPORTRANGE uses the spreadsheet URL (or ID) plus a sheet name and range. If the source spreadsheet is deleted, the formula errors out. If the source sheet tab is renamed or the range reference becomes invalid, the formula may freeze on the last cached value rather than showing an error.

How to diagnose:

1. Copy the spreadsheet URL from your IMPORTRANGE formula.
2. Open it in a new browser tab.
3. Check whether the sheet tab name in your formula matches an actual tab in the source spreadsheet.
4. If the spreadsheet no longer exists, you will see a Google 404 page.

Fix:

• If the tab was renamed: update the formula to use the new tab name. The formula structure is =IMPORTRANGE("spreadsheet_url", "NewTabName!A:Z").
• If the spreadsheet was deleted or is no longer accessible: you need a new source. There is no way to recover data from a deleted spreadsheet via IMPORTRANGE.
• If the spreadsheet was moved to a different Drive folder: the URL and ID remain the same, so the formula should still work. The move does not cause failures.

Prevention: Never use a tab name in your IMPORTRANGE formula that is likely to change. If the source team regularly renames their tabs, ask them to create a stable "export" tab with a fixed name.

Cause 2: Permission Was Revoked

What happens: When you first use IMPORTRANGE, Google prompts you to "Allow access" to the source spreadsheet. This permission is stored per Google account. If the source spreadsheet owner changes the sharing settings, restricts access to specific people, or removes your account, the permission is revoked and IMPORTRANGE stops refreshing.

How to diagnose:

1. Click on the cell containing the IMPORTRANGE formula.
2. Look for a small blue "Allow access" prompt or a #REF! error with the message "You need permission to access the spreadsheet."
3. If the cell shows your old data with no error, try clicking into the cell and pressing Enter to force a re-evaluation.

Fix:

• Ask the source spreadsheet owner to restore your access. Once they share the spreadsheet with your Google account (at least Viewer level), return to your sheet and click "Allow access" if prompted, or delete and re-enter the formula to re-trigger the permission check.
• If you are the source spreadsheet owner and you accidentally restricted access, adjust the sharing settings under File > Share > Share with people and groups.

Note: IMPORTRANGE requires at least Viewer access on the source spreadsheet. The "Allow access" prompt only appears once per spreadsheet per Google account. After that, the permission is stored silently.

Cause 3: Too Many IMPORTRANGE Calls in One Sheet

What happens: Google Sheets has an undocumented limit on the number of IMPORTRANGE formulas per spreadsheet. The practical limit appears to be around 50 IMPORTRANGE calls across all sheets in a single spreadsheet file. Beyond this, some formulas silently stop refreshing while others continue working normally.

This is the most frustrating cause because there is no error message and no official documentation of the limit. Teams discover it when they have been adding IMPORTRANGE formulas over time and suddenly some of them stop updating.

How to diagnose:

1. Count all IMPORTRANGE formulas across every tab in your spreadsheet. Use Edit > Find and Replace with "IMPORTRANGE" in the search field and enable "Search all sheets."
2. If the count is above 40, you are likely hitting or approaching the limit.
3. To confirm, temporarily delete a few IMPORTRANGE formulas and see if the remaining ones start updating again.

Fix:

Option A. Consolidate sources. If multiple IMPORTRANGE formulas pull from the same source spreadsheet, combine them into fewer formulas using {IMPORTRANGE(..); IMPORTRANGE(..)} array syntax (note: this uses a semicolon on most locales and a comma or backslash on others). Each formula still counts toward the limit, but combining arrays lets you fetch more data per formula.

Option B. Use a staging sheet. Create an intermediate spreadsheet that consolidates data from all your sources using IMPORTRANGE. Then have your final spreadsheet use a single IMPORTRANGE pointing to the staging sheet. This distributes the IMPORTRANGE count across two spreadsheets.

Option C. Replace IMPORTRANGE with a proper connector. If you are consistently hitting this limit, it is a signal that IMPORTRANGE is not the right architecture. See the section below on when to use a connector instead.

Cause 4: Circular Dependency

What happens: A circular dependency occurs when Sheet A imports from Sheet B, and Sheet B imports from Sheet A (directly or through a chain). Google Sheets detects the cycle and stops recalculating the formulas involved to prevent an infinite loop.

How to diagnose:

1. Click the cell with the IMPORTRANGE formula.
2. Look for a #REF! error with the message "Circular dependency detected."
3. Trace the chain: where does this sheet import from, and where does that sheet import from? Keep tracing until you find the loop.

Fix:

Break the cycle. There must be a clear data flow direction: raw source sheets → processed/aggregate sheets → presentation sheets. No sheet in the chain should import from a sheet that is downstream of it.

If you genuinely need data to flow in both directions between two spreadsheets, you cannot use IMPORTRANGE for both directions. One direction must use a script, a connector, or manual copy-paste.

Cause 5: Cell Reference Error in the Formula

What happens: IMPORTRANGE takes two arguments: the spreadsheet URL (or ID) and the range string. The range string must be a text string in the format "SheetName!A1:Z100". Common mistakes that cause the formula to silently fail:

• Using a cell reference as the sheet name without quoting it correctly
• Specifying a range that exceeds the sheet's actual dimensions
• A typo in the sheet name (IMPORTRANGE is case-insensitive for sheet names but space-sensitive)
• Using a named range that does not exist in the source spreadsheet

How to diagnose:

1. Open the source spreadsheet.
2. Manually navigate to the exact tab name and range you specified in the formula.
3. Confirm the tab exists and the range contains data.
4. Check for trailing spaces in the tab name. They are invisible but break the reference.

Fix:

Correct the range string. The most reliable format is to use the spreadsheet ID (the long string in the URL between /d/ and /edit) rather than the full URL. Use the exact tab name with correct spacing. Use an open-ended column reference like A:Z rather than a fixed row count like A1:Z5000 to avoid the range silently excluding new rows.

Example of a reliable formula:

=IMPORTRANGE("1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms", "Sales Data!A:Z")

Cause 6: Google Sheets Cache Not Refreshing

What happens: IMPORTRANGE results are cached by Google. Even when everything is configured correctly (valid source, valid permissions, correct formula) the cache sometimes does not update on the schedule you expect. This is the most common complaint in the Stack Overflow question with 255,648 views, and it is the hardest to fix because it is partially outside your control.

Google does not publish the exact cache invalidation rules. The refresh interval appears to depend on usage patterns, the size of the imported range, and server load. In practice, IMPORTRANGE can lag anywhere from a few minutes to several hours.

How to diagnose:

This is a diagnosis of exclusion. If the source sheet is accessible, the permission is valid, you have fewer than ~50 IMPORTRANGE formulas, there is no circular dependency, and the range reference is correct (but the data is still not updating) it is a cache issue.

Fixes (in order of reliability):

1. Edit and re-save the formula. Click the cell, press F2 (or click into the formula bar), make no changes, and press Enter. This forces a re-evaluation. It works about 50% of the time.

1. Close and reopen the spreadsheet. Close all browser tabs with the spreadsheet open, wait 30 seconds, then reopen it. Google sometimes delays cache refresh until a fresh session opens the sheet.

1. Use the nuclear option (described below). Delete the cell contents and re-enter the formula. This is the most reliable force-refresh.

1. Add a dummy dependency. Add &"" to your range string (it concatenates an empty string and forces re-evaluation without changing the result):

   =IMPORTRANGE("spreadsheet_id", "Sheet1!A:Z"&"")

This works sometimes but is not guaranteed.

1. Use a Google Apps Script trigger. A time-based trigger that touches a cell in the spreadsheet (writes a timestamp, then clears it) can force recalculation. This is hacky but effective for time-sensitive data.

The Nuclear Option: Delete and Re-Enter the Formula

When nothing else works, this does. It forces Google to drop the cache, re-fetch the permission, and pull fresh data from the source.

Steps:

1. Note down your exact IMPORTRANGE formula (copy it somewhere safe).
2. Select the cell (or cells) containing the formula.
3. Press Delete to clear the cell contents.
4. Wait five seconds.
5. Re-enter the formula exactly as it was.
6. If prompted with "Allow access," click it.
7. Wait 10–30 seconds for the data to populate.

The data that populates after re-entry will be current as of the moment the formula runs, not the previous cached value.

Warning: If your IMPORTRANGE fills a large range (thousands of rows), deleting and re-entering it will cause a visible gap in your sheet until the data repopulates. If other formulas depend on the imported range, they will briefly error during this period. Do this during off-hours if your sheet is being actively used.

The Last Refreshed Timestamp Trick

IMPORTRANGE does not stamp its results with a freshness time. But you can approximate it using a workaround.

Add a column to your source spreadsheet that contains the formula =NOW() with the cell formatted as a date-time. When the row data changes, NOW() updates. When IMPORTRANGE pulls that column into your destination sheet, readers can see the timestamp alongside the data.

A cleaner approach: add a dedicated cell in the source sheet with =NOW() labeled "Last updated." Import that cell alongside your data range. Readers can see at a glance how stale the data is.

Limitation: This still relies on IMPORTRANGE refreshing. A very stale IMPORTRANGE will also show a stale timestamp. It is a better user experience than nothing, but it does not solve the underlying refresh problem.

When IMPORTRANGE Is the Wrong Tool

IMPORTRANGE is built for pulling data from one Google Sheet into another. It is not built for:

• Pulling data from external sources (databases, APIs, data warehouses). Use a proper connector.
• Syncing large datasets (tens of thousands of rows at high frequency). IMPORTRANGE is slow and unreliable at this scale.
• Mission-critical reporting where stale data has business consequences. IMPORTRANGE's cache behavior is not predictable enough for this.
• Data that needs a defined refresh schedule. IMPORTRANGE refreshes on Google's terms, not yours.
• Teams that need audit trails or refresh history. IMPORTRANGE has no logging.

If you are hitting IMPORTRANGE's limits: whether that is the formula count cap, the cache unpredictability, or the lack of a refresh schedule. The right move is a dedicated data connector.

Connectors like brooked.io pull data from your source on a defined schedule, write it into Google Sheets on your terms, and surface clear status when a refresh fails. You get the live-ish data experience without fighting Google's caching layer.

Comparison Table: IMPORTRANGE vs. Proper Connectors

Troubleshooting Quick Reference

Bottom Line

IMPORTRANGE stops updating silently because Google's caching layer is opaque, because permission can be revoked without notice, and because there are undocumented limits on how many IMPORTRANGE formulas one spreadsheet can sustain. For each of the six causes, there is a specific fix. The nuclear option (delete and re-enter) solves the majority of cache-related freezes.

If you are regularly fighting IMPORTRANGE to keep data fresh, or pulling from sources outside Google Sheets, you have hit the edge of what IMPORTRANGE was designed to do. A dedicated connector gives you a defined refresh schedule, failure alerts, and no formula count limits.

Stop Fighting IMPORTRANGE and Get Reliable Data Instead

brooked.io pulls data from any source (databases, APIs, data warehouses, other spreadsheets) into Google Sheets on a schedule you control. No more stale data. No silent failures. No formula count limits.

Try brooked.io free and set up your first automated refresh in under 15 minutes.

Related Articles

• How to Connect a Database to Google Sheets Without Writing Code
• Google Sheets Data Freshness: How Often Do External Functions Update?
• Snowflake to Google Sheets: The Complete Guide

Frequently asked questions