Edit

Share via

Read or write to a large range using the Excel JavaScript API

Use these patterns to read or write large ranges, while avoiding resource limit errors.

  • Split big ranges into smaller blocks. Don't load or write everything at once.
  • Load only what you need (for example, just values instead of values,numberFormat,formulas).
  • Use getSpecialCells and RangeAreas to work with scattered cells instead of a large range.
  • If you encounter a limit error, retry with a smaller block size.
  • Apply formatting after the data is in place.

When to split a large range

Scenario Sign you should split the range Approach
Reading millions of cells Timeout or resource error Read in row or column blocks. Start with 5k–20k rows.
Writing a large result set Single values write fails Write in row blocks (with same column count for each block).
Sparse updates Many distant cells Build combined address string with getRanges and RangeAreas.
Writing data and formatting Formatting slows Excel Write values first, format afterward.

Defer formatting & calculations

Formatting and calculation-heavy operations, such as conditional formats or formula writes, add time on large areas. Consider:

  • First write raw values (plain numbers or text), then add formulas or formats in a second pass.
  • Use setDirty only on necessary recalculation scopes.
  • Limit conditional formats to used rows instead of entire column references (such as A2:A5000 instead of A:A) with getUsedRange.

Next steps

See also