Sync Two Google Sheets Automatically: IMPORTRANGE vs. Apps Script

If two Google Sheets spreadsheets need to stay in sync, IMPORTRANGE is the first thing most people try — and it works fine until the source sheet gets big or the import chain gets long, at which point it silently stalls or throws “Loading data may take a while.” The reliable fix for anything beyond a small, single-hop import is a scheduled Apps Script that pushes the data across instead of pulling it live. Below is a working push-sync script, the exact thresholds where IMPORTRANGE breaks down, and a decision framework for which one to use.

Quick verdict: Use IMPORTRANGE for a single-hop, moderate-size read where near-real-time freshness doesn’t matter. Switch to an Apps Script push sync once you’re chaining sheets, importing more than roughly 250K–350K cells, or the destination needs to stay accurate even when nobody has the source sheet open.

IMPORTRANGE vs. Apps Script Push Sync: Side by Side

Neither option is strictly better — they fail in different places. The table below is built from Google’s own IMPORTRANGE documentation and the official Apps Script quotas reference, both re-checked on 2026-09-18.

Why Does IMPORTRANGE Stop Updating?

Three mechanisms cause most “IMPORTRANGE not updating” reports, per Google’s own IMPORTRANGE documentation: the 10MB per-request data cap, hourly-only auto-refresh while the doc is open, and cumulative lag when sheets are chained through multiple IMPORTRANGE hops. None of these are bugs — they’re documented limits.

• 10MB per-request cap. Google states IMPORTRANGE “must download the entire range to your computer” and is “capped at 10MB of received data per request.” Wide, formula-heavy source sheets hit this well before they hit any row-count limit.
• Hourly auto-refresh. “IMPORTRANGE automatically checks for updates every hour while the document is open, even if the formula and spreadsheet don’t change.” If the destination sheet isn’t open, it doesn’t refresh at all until someone opens it.
• Chain delay. If sheet B imports from sheet A, and sheet C imports from sheet B, an edit in A has to propagate through two hops before C sees it. Google’s own guidance: “limit chains of IMPORTRANGE across multiple sheets” and never create a cycle (A imports from B while B imports from A — this produces a permanent #REF!, not a sync).

The Apps Script Alternative: One-Way Push Sync

Instead of the destination pulling data on a formula refresh cycle, the source spreadsheet pushes its data to the destination whenever it changes (or on a fixed schedule). The script below does three things a naive setValues() copy doesn’t: it skips the write entirely when nothing changed (saving execution quota), it clears the destination first so deleted source rows don’t leave stale data behind, and it uses LockService so two rapid edits can’t run the sync twice at once and corrupt the target range. If you only need to move matching rows rather than mirror an entire sheet, see our related guide on copying rows to another Google Spreadsheet without duplicates.

function pushSyncToTarget() {
  var lock = LockService.getScriptLock();
  try {
    lock.waitLock(30000); // give a concurrent run up to 30s to finish first
  } catch (e) {
    Logger.log('Sync already running elsewhere, skipping this run: ' + e);
    return;
  }

  try {
    var SOURCE_ID = 'PASTE_SOURCE_SPREADSHEET_ID_HERE';
    var SOURCE_SHEET = 'Sheet1';
    var TARGET_ID = 'PASTE_TARGET_SPREADSHEET_ID_HERE';
    var TARGET_SHEET = 'Sheet1';

    var sourceSheet = SpreadsheetApp.openById(SOURCE_ID).getSheetByName(SOURCE_SHEET);
    var targetSheet = SpreadsheetApp.openById(TARGET_ID).getSheetByName(TARGET_SHEET);

    var sourceValues = sourceSheet.getDataRange().getValues();
    var targetValues = targetSheet.getDataRange().getValues();

    // Skip the write entirely if nothing changed -- avoids burning
    // execution time on no-op runs when onChange fires for unrelated edits.
    if (JSON.stringify(sourceValues) === JSON.stringify(targetValues)) {
      return;
    }

    // Clear first: if source lost rows, a plain setValues() would leave
    // the old extra rows sitting in the target untouched.
    targetSheet.clearContents();
    if (sourceValues.length > 0 && sourceValues[0].length > 0) {
      targetSheet
        .getRange(1, 1, sourceValues.length, sourceValues[0].length)
        .setValues(sourceValues);
    }
  } finally {
    lock.releaseLock();
  }
}

function createSyncTrigger() {
  // Run this once manually from the editor to install the watcher.
  // Re-running it is safe -- it removes any duplicate trigger first.
  var sourceId = 'PASTE_SOURCE_SPREADSHEET_ID_HERE';
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === 'pushSyncToTarget') {
      ScriptApp.deleteTrigger(t);
    }
  });
  ScriptApp.newTrigger('pushSyncToTarget')
    .forSpreadsheet(SpreadsheetApp.openById(sourceId))
    .onChange()
    .create();
}

Setup (do this once)

1. Open the source spreadsheet → Extensions → Apps Script.
2. Delete the placeholder code and paste the script above.
3. Replace PASTE_SOURCE_SPREADSHEET_ID_HERE and PASTE_TARGET_SPREADSHEET_ID_HERE with the IDs from each sheet’s URL (the long string between /d/ and /edit).
4. In the function dropdown at the top, select createSyncTrigger and click Run. Approve the permission prompts — the script needs access to both spreadsheets.
5. Check Triggers (clock icon, left sidebar) — you should see one pushSyncToTarget trigger with event type “On change.”
6. Edit any cell in the source sheet and confirm the target updates within a few seconds.

Use .onChange() rather than .onEdit() here on purpose: an installable onChange trigger fires for row/column insertions and deletions as well as value edits, so a deleted row in the source actually gets removed from the target. A plain onEdit trigger only reliably fires for direct cell edits.

Errors You’ll Hit (and the Fix)

These four are specific to the sync script above. For the wider set of Apps Script failures (permission dialogs, script-level exceptions, and more), see our Apps Script error messages reference.

How Many Syncs Can I Run Before Hitting the Quota?

Free (consumer) Google accounts get 90 minutes of total trigger runtime per day; Google Workspace accounts get 6 hours — confirmed directly from Google’s Apps Script quotas reference on 2026-09-18. That’s a shared budget across every trigger in the project, not just this one.

As a rough illustration only — actual execution time depends entirely on how much data you’re moving — if a single sync run takes about 2 seconds, the 90-minute free-tier budget covers roughly 2,700 runs a day before you’d need a Workspace account or a less chatty trigger (e.g., time-driven every few minutes instead of on every change). Large sheets or wide ranges will push that per-run time up and the ceiling down accordingly; there’s no substitute for checking your own execution time in the Apps Script “Executions” log after the first few real runs. For the full quota table (email limits, UrlFetch calls, simultaneous executions, and more), see our Apps Script quotas and limits reference.

IMPORTRANGE or Apps Script? Decision Framework

Choose IMPORTRANGE if: it’s a single hop (not chained through a third sheet), the imported range is well under the 10MB cap, and it’s acceptable for the destination to only refresh when someone has it open or on the hourly auto-check.

Choose the Apps Script push sync above if: the destination needs to be accurate even when closed, you’re chaining more than one sheet together, the source is large or wide enough that IMPORTRANGE has started throwing the “large number of requests” error, or you need the sync to also remove rows that were deleted upstream.

If you need two-way sync (edits in either sheet should flow to the other), don’t just run this script in both directions — that creates an infinite write loop, since each sync triggers the other sheet’s onChange event. You need a guard column (a “last synced by script” timestamp the script checks and skips) on both ends before attempting two-way sync; that’s outside the scope of the one-way script above.

Related Questions

Does IMPORTRANGE work between a personal Gmail account and a Google Workspace account? Yes, as long as both accounts have at least viewer access to the source spreadsheet and complete the one-time “Allow access” permission prompt — account type doesn’t restrict it.

Will the Apps Script sync survive if I make a copy of the spreadsheet? No. File → Make a copy does not carry over installable triggers or the script’s stored spreadsheet IDs correctly in every case — re-run createSyncTrigger from the copy and double-check the IDs after copying.