Edit

Share via

Read or write to an unbounded range using the Excel JavaScript API

Use these guidelines to understand how entire-column and entire-row addresses behave, and apply patterns that reduce errors and memory usage. For the complete list of properties and methods that the Range object supports, see Excel.Range class.

Key points

  • "Unbounded" means entire columns (like A:F) or entire rows (such as 2:2).
  • Cell-level properties (like values, text, numberFormat, or formulas) come back as null for unbounded reads.
  • You can't set cell-level properties on an unbounded range. This returns an error.
  • Narrow to the used cells first with getUsedRange().
  • Prefer explicit bounds (like A1:F5000) for faster calculation speeds and lower memory use.

The following are examples of unbounded ranges.

  • Range addresses comprised of entire columns.
    • C:C
    • A:F
  • Range addresses comprised of entire rows.
    • 2:2
    • 1:4

Read an unbounded range

When you request an unbounded range (for example, getRange('C:C')), the response returns null for cell-level properties like values, text, numberFormat, and formula. Other properties (address, cellCount) are still valid.

Write to an unbounded range

You can't set cell-level properties like values, numberFormat, or formula on an unbounded range because the request is too large. For example, the next code sample fails because it sets values for an unbounded range.

// Invalid: Attempting to write cell-level data to unbounded columns.
let range = context.workbook.worksheets.getActiveWorksheet().getRange("A:B");
range.values = [["Due Date"]]; // This throws an error.

Next steps

See also