appscript.dev
Guide Intermediate Sheets

Diagnose and fix a slow workbook

Find and remove Northwind spreadsheet bottlenecks — formulas, ranges, references.

Published Oct 3, 2025

A spreadsheet that takes several seconds to react to every edit is not just annoying — it is a sign that the workbook is doing far more calculation than it needs to. The slowness compounds: every collaborator waits, every script that touches the sheet waits, and large recalculations can even time out.

The good news is that slow workbooks almost always come down to a short list of culprits. Once you know what to look for, diagnosing one is mostly a matter of elimination. This guide covers the usual suspects, how to find them in code, and how to fix each one.

Suspects

These four patterns cause the overwhelming majority of slow spreadsheets.

  • Volatile formulas. NOW(), TODAY(), RAND(), RANDBETWEEN(), OFFSET() and INDIRECT() recalculate on every change anywhere in the workbook, not just when their own inputs change. A handful is fine; hundreds drag the whole file down.
  • Full-column references. A reference like A:A tells Sheets to scan every cell in the column — potentially millions of mostly-empty rows. Multiply that across many formulas and the recalculation cost explodes.
  • Massive ARRAYFORMULA. A single ARRAYFORMULA is convenient, but one spanning thousands of rows means thousands of computed cells recalculating together. Several of them stacked is a common hidden cost.
  • External data. IMPORTRANGE, IMPORTHTML, IMPORTXML and IMPORTDATA fetch over the network, are rate-limited, and refresh on their own schedule. They are slow by nature and cannot be sped up — only avoided.
CulpritWhy it is slowTypical fix
Volatile functionsRecalc on every editCache the value on a timer
Full-column refsScans millions of empty cellsBound the range explicitly
Huge ARRAYFORMULAThousands of cells recalc togetherLimit rows, or convert to static values
IMPORT* functionsNetwork fetch, rate-limitedPull with Apps Script on a schedule

Find them

You do not have to hunt through the grid by eye. This function scans every formula in the active sheet and logs any that use a volatile function, with its A1 address so you can jump straight to it.

function findVolatile() {
  const sheet = SpreadsheetApp.getActive().getActiveSheet();

  // getFormulas() returns a 2D array mirroring the grid — empty string
  // for any cell that holds a literal value rather than a formula.
  const formulas = sheet.getDataRange().getFormulas();

  for (let r = 0; r < formulas.length; r++) {
    for (let c = 0; c < formulas[r].length; c++) {
      // Flag the well-known volatile functions, case-insensitively.
      if (/NOW|RAND|OFFSET|INDIRECT/i.test(formulas[r][c])) {
        // Log the cell address and the offending formula so it is
        // easy to locate and review each hit.
        console.log(sheet.getRange(r + 1, c + 1).getA1Notation(), formulas[r][c]);
      }
    }
  }
}

To also catch full-column references, widen the regular expression — a pattern like /\b[A-Z]+:[A-Z]+\b/ matches references such as A:A or B:D. Run the scan sheet by sheet on a large workbook so the output stays readable.

Fixes

Each suspect has a direct remedy. The aim is the same every time: do the expensive work once, not on every edit.

  • Bound your ranges. Replace A:A with a real range such as A2:A1000. Pick a ceiling comfortably above your current row count rather than the whole column. If the data grows past it, raise the bound — that is far cheaper than scanning a million empty rows forever.
  • Cache volatile values. Instead of scattering NOW() through the sheet, put it in one cell and refresh that cell from a time-based script (for example, every few minutes). Every other formula then references that one static cell, so nothing recalculates on unrelated edits.
  • Replace IMPORT* with a script pull. A small Apps Script function can fetch the external data on a schedule and write plain values into the sheet. The values are then static — instant to read, no rate limits, and they update on a cadence you control.
  • Trim oversized ARRAYFORMULAs. Bound them to the rows that actually have data. If the result rarely changes, consider replacing the formula with its computed values entirely.

Watch out for

  • “Fixing” by deleting empty rows. Trimming unused rows helps a little, but it does not address an unbounded A:A reference — the formula will still scan to the new sheet limit. Bound the reference instead.
  • Caching NOW() too aggressively. If downstream logic genuinely needs the current time to the second, a five-minute cache will be wrong. Match the refresh interval to how fresh the value must actually be.
  • Hidden volatility. INDIRECT() is volatile even when it points at a static cell, and many lookup setups bury an OFFSET() inside. The scan above surfaces these — do not trust a visual skim.
  • Blaming the script when the sheet is the problem. If an Apps Script that reads the sheet is slow, check the workbook first. A script waiting on thousands of recalculating cells looks like a slow script but is not one.
  • Re-introducing IMPORTRANGE for convenience. It is tempting because it is one formula, but it brings back the network cost and rate limits. Once you have moved to a script pull, keep it that way.
↑ All guides