Returns the size of the range this is the rows * columns.
Returns an iterator that allows you to iterate over the individual values within the range.
For more advanced iteration, use ICellRange.entries or ICellRange.forEach.
Creates a 'light' table used for stateful filtering and sorting with-in a sheet.
Optionaloptions: AddAutoTableOptionsAdd a listener to the range.
Optionaloptions: ListenerOptionsA callback to remove. IListener.Remove
Add an ITable at the range.
Optionaloptions: boolean | ITableCollection.AddOptions
If a boolean then this indicates if headers are present.
Evaluates formula across this range and calls callback with the results — then again every
time anything the formula reads changes.
An expression, compiled at this range's top-left, or an already-compiled formula anchored there (see the anchor note below).
Receives one value per cell of this range, read with local coordinates
(getValueAt(0, 0) is this range's top-left).
Optionaloptions: WatchOptionsA callback to remove the watch. IListener.Remove
The difference from createFormula is the lifetime: that one compiles and evaluates once and registers nothing, so its answer silently ages. This one keeps answering.
Block-shaped, always. A rule's operand is a value per cell, not one value for the range: an
absolute reference ($A$1) yields the same value in every cell and a relative one (A1)
yields each cell's own — the same code path, which is why per-cell semantics cost nothing here.
The callback fires on the first evaluation as well as on every change, so a caller never has to evaluate separately and then watch — a sequence with a window in it where a change is lost. An evaluation superseded by a newer one is dropped rather than delivered late, so the callback never goes backwards in time.
The anchor must be this range's top-left, and a compiled formula anchored elsewhere throws rather than being quietly re-sited. A formula means different things read from different cells (ICalculation.IFormula.shift), so silently moving one would answer a question the caller did not ask. To watch a formula belonging to a multi-area rule, fill it first — or use ICellRanges.addWatch, which does that for every area.
The formula is held by the calculation engine like a formula in a cell: its references follow
row and column inserts and deletes, a reference to deleted cells reads #REF!, and undo
restores it. A range the formula discovers at evaluation time (INDIRECT, OFFSET) is watched
too. A volatile formula (NOW, RAND) re-evaluates with the engine's pass and does not re-fire
on its own.
With ICellRange.WatchOptions.arrayFirstCell each cell is evaluated the way a conditional-format or data-validation rule evaluates its formula: as an array-entered formula whose extent is that one cell.
Fill the range to a destination using an array of IAutoFill.Filler.
The range to autoFill including the current range.
Optionaloptions: AutoFillOptionsICellRange for the destination.
// What would land in the last cell, and how big will that cell be?
const preview = sheet.getRange('A1:B4').autoFill('A1:B8', { preview: true });
const last = preview.getCell({ rowIndex: 7, colIndex: 1 });
last.getText(); // the value the fill would write
last.getExtrusions(); // the merged block it would occupy (null if not merged)
Adjusts the column widths and rows heights to fit the content.
Optionaloptions: AutoFitOptionsThe current ICellRange.
Re-evaluates and formulas within the range.
Optionalfull: boolean
If true then all formulas will be recalculated.
Whether insertFrom would go through, asked without performing it.
The source that would be inserted - the same argument insertFrom takes.
Optionaloptions: InsertFromOptions
The same options insertFrom would be given; axis and isCut both change the
answer, so pass the ones the real call will use.
false when insertFrom would refuse, true otherwise.
A drop can be refused for reasons the caller cannot see: a cut whose gap the insert would only
half close, a merge the shift would tear or land inside of, a table it would split. This answers
the same question insertFrom asks itself before it commits, which is what lets an interactive
drag show a drop as refused BEFORE the user commits to it, rather than reporting an error after.
It reports only what can be known up front. A refusal raised deeper in the operation (protection, or content pushed off the end of the sheet) still surfaces as a thrown error.
Clear the range.
OptionalapplyTo: ICell.Content
The type of content to clear. Defaults to all@returns The currentICellRange`.
Optionaloptions: ITransaction.OperationOptionsRemoves the outline levels from the rows and columns of the range. Hidden rows and columns stay hidden.
Optionalaxes: AxisSelection
Which levels to remove. Default IRange.AxisSelection.Both.
Optionaloptions: ITransaction.OperationOptionsThe current ICellRange.
The counterpart of Excel's Range.ClearOutline: both axes over the range's rows and columns,
so entire rows also clear every column's levels unless axes names the rows, and a single
cell clears the whole sheet. To outline a sheet from its formulas see
IOutline.autoOutline.
Copies (or cuts) data and formatting from a specified source to the current range.
The source of the data and formatting to be copied or cut.
This can be either a range address, another range object, or an ISnapshot
created using the getSnapshot method.
Optionaloptions: ICellRange.CopyOptionsA promise that resolve to an ICellRange or 'null' when the copy has completed.
Parses a formula expression and returns a transient formula object anchored at this range's top-left cell.
The formula expression to parse.
Optionaloptions: CreateFormulaOptions
Optional parse options; the expression is storage text unless target is 'display'.
The anchored convenience form: equivalent to
getSheet().createFormula(expression, topLeftCoords), where the anchor is this
range's top-left cell. Nothing is committed — the formula is not registered in the workbook
or the dependency graph.
Two behaviors follow from the sheet form (see ISheet.createFormula):
A2) resolves against THIS range's sheet, never the selected one.Creates a SparseBuilder for efficiently updating sparsely populated cells in a streaming low memory way.
For contiguous or mostly contiguous updates, the ICellRange.updateCells method is simpler and will use ICellRange.SparseBuilder internally.
Optionaloptions: ICellRange.SparseBuilderOptionsOrdering: By default updates must be added in either row-major or column-major order, as specified
by the axis parameter, with major/minor coordinates increasing monotonically. Unless
allowUnorderedWrites is set to `true out of order updates will result in an error.
Performance: Setting axis to IRange.Axis.Column is generally faster as it aligns with the internal data representation.
Performance: Setting allowUnorderedWrites adds a nominal performance overhead for all updates and will do a full apply when an
out of order update is detected.
The default IRange.Axis.Row is provided for compatibility with common spreadsheet APIs.
Validation: Data validation (e.g., for protected cells or boundaries) is performed when the apply method is called.
Transactions:
SparseBuilder only builds a temporary representation of the updates.
No actual changes are made to the sheet until apply is called.doBatch.Delete the cells within the range and shifts the remaining cells to fill the gap.
Optionaloptions: Axis | DeleteOptions
The axis or The direction in which to shift existing cells. Defaults to the axis where the range's dimension is smaller (e.g., shifts down if the range is wider than it is tall). See ICellRange.getDefaultShiftAxis for details on the default behavior. See ITransaction.OperationOptions
The current ICellRange.
Perform a set of operations as a batch.
The callback return type.
Function to perform the operations.
Optionaloptions: string | ITransaction.OperationOptions
Description of the operation or additional options. This will be used for tracking undo/redo and history.
The result of the callback.
Returns an iterator that allows you to iterate over the individual entries within the range,
yielding pairs of IteratorContext and a Value.
Optionaloptions: ICellRange.IteratorOptions<ICellRange.Address>An iterator that yields ICellRange.Entry tuples for each value within the range.
Hide the rows that do not meet the condition.
Optionaloptions: ITransaction.OperationOptionsApply filter to multiple spans in either axis.
Example – hide rows where column C is >= 3:
sheet.getRange('A2:E100').filter({
axis: IRange.Axis.Row, // default but could be column
conditions: [
{
operator: ICondition.BuiltInOperatorNames.GreaterThanEqualTo,
param: 3,
// 0-based index of column C within the range (relative to the range, not the sheet)
index: 2,
},
],
});
@see
{@link IFilter.IFilterable
Returns an iterator of ICells that match the results.
The text to search for or FindOptions
Iterates over the cells within the range, applying the provided callback function to each cell.
This method is a more scalable alternative to getValues or getCells for large or sparse ranges,
as it:
A function that will be called for each non-empty cell in the range. The callback receives:
value: The Scalar at the current cell.context: A IteratorContext object providing additional information and utilities for the cell.{ break: T } to stop the iteration early.Optionaloptions: ICellRange.IteratorOptions<ICellRange.Address>The value returned by the callback function if it explicitly returned to stop the iteration early; otherwise, void.
Returns the coords as a string address (e.g., 'A1' or 'A1:E5').
Optionalr1c1: boolean
If unspecified returns the environment's default.
The size this range's content needs — what getBounds would report if the range had been autoFit. A pure query: nothing is measured onto the model and nothing is committed.
Optionaloptions: BestFitOptions
Measurement options. Default { axes: IRange.AxisSelection.Both }.
autoFit also scans outside the range, to prove a shrink is safe.)Returns the bottom-right corner of the range.
Optionaloptions: ICellRange.GetRangeOptionsReturns the absolute bounds for the range.
Return a 2D array of cells in the shape of the range.
Optionaloptions: ICellRange.GetValuesOptionsA 2D array of ICells.
Returns the column after the current range.
Optionaloptions: ICellRange.GetRangeOptionsICellRange with the top-left corner immediately after the current range.
Returns the column at the specified absolute column index, retaining the current row span.
The absolute column index (0-based). Must be ≥ 0. Not relative to the current range.
Optionaloptions: ICellRange.GetRangeOptionsICellRange with a column width of 1 and the original row height.
Returns the column before the current range.
Optionaloptions: ICellRange.GetRangeOptionsICellRange with the top-left corner immediately before the current range.
Returns the column at the specified delta relative to the start of the current range, retaining the current row span.
Number of columns to move from columnStart. Positive moves right; negative moves left.
Optionaloptions: ICellRange.GetRangeOptionsICellRange with a column width of 1 and the original row height.
getColumnBy(0) is equivalent to getColumnAt(columnStart) — the first column of the current range.
To advance past the end of the range, use getColumnAfter instead.
Returns the number of columns of the range.
Returns the right of the range.
Returns the header range for the columns.
Optionaloptions: ICellHeaderRange.GetRangeOptionsICellHeaderRange for the columns.
Returns the column start.
Returns the IComments on cells within the range.
Optionaloptions: ICellRange.GetItemsOptions<ICommentCollection.GetItemsOptions>
The filters of ICommentCollection.getItems without address — this
range is the address.
Returns the IConditionalFormats that overlap the range.
Optionaloptions: ICellRange.GetItemsOptions<IConditionalFormatCollection.GetItemsOptions>
The filters of IConditionalFormatCollection.getItems without address —
this range is the address.
Overlap, not containment. A format anchored to A1:D10 is returned for a query on B2;
pass fullyContained for the stricter question.
The items are the sheet's — this is a scoped view of
getSheet().getConditionalFormats(), not a copy — and each appears once, however many of
its ranges the query hit. The range only says which items; ask an item where it lives with
IConditionalFormat.getRanges.
The IRange.Coords.
Returns per-feature cell counts for the range.
Optionalfeatures: ICellRange.Feature | readonly ICellRange.Feature[]
The features to count. Defaults to all public features — counting is the discovery call.
getCounts answers "how much of each feature is here"; getStats answers "what
are the values like". Feature occupancy vs value analysis — they do not overlap.
0 — the shape is stable and self-describing, never null.getCounts([Fill, Border]) is two numbers,
not the count of their refined intersection — the refined count is getRegions.getRegions returns or cost as much as computing it.Returns the IDataValidations that overlap the range.
Optionaloptions: ICellRange.GetItemsOptions<IDataValidationCollection.GetItemsOptions>
The filters of IDataValidationCollection.getItems without address —
this range is the address.
Plural, unlike Excel. A cell carries at most one validation there, so Excel's range accessor is singular; here a range may overlap any number of independently anchored validations.
See getConditionalFormats for the overlap and one-entry-per-item rules, which are the same for every ranged item lookup.
Returns the fill this range implies — which cells it would read, which it would cover, the fill types that can be offered for it, and the one to open on.
Optionaldirection: Direction
Which way the series runs. Defaults to the range's own shape: a single row
runs Right, anything else runs Down — or, with a destination, to the way it extends.
Optionaldestination: ICellRange.Address
Fill the range as it is to here — the fill handle's drag. The template is
then the WHOLE range, blanks included, rather than its leading run, and destination must
extend it on exactly one edge as autoFill requires.
IAutoFill.DefaultCriteria, or null where there is nowhere to fill: the range is
one cell deep along direction, or destination does not extend it — which is also how a
surface learns that a direction is not on offer at all.
Error If destination does not share three edges with the range, or direction is
not the way it extends.
Useful for displaying options to the ui — a Series dialog, or a fill menu deciding what to grey. Nothing is filled and nothing is committed; see autoFill to run one, and AutoFill.toFillOptions for the options a chosen IAutoFill.Preset amounts to.
Default IRange.Axis that will be used to shift the range.
Returns a default sort that would be used if no overrides where provided.
Default ISort.DefaultSortCriteria or null if there is not valid sort criteria.
Returns all cells eventually affected by this range, through the full calculation chain.
The range form of ICell.getDependents: a recursive walk downstream giving the total
blast radius of a write here. Resolves null when there are none.
Returns a set of non-overlapping ranges the represents the difference between the current range and the specified range.
The address of the range to subtract from the current range.
Optionaloptions: ICellRange.GetRangeOptionsICellRanges containing the resulting non-overlapping ranges,
or null if:
Returns the cells that directly reference any cell in this range.
The range form of ICell.getDirectDependents — the immediate consumers of anything
this range produces. Resolves null when there are none.
Returns the ranges immediately referenced by the formulas in this range.
The range form of ICell.getDirectPrecedents: the union over every cell, one call rather than a fan-out — the calculation graph is indexed by range, so the cell form is this one over a 1×1.
null when there are none; see ICell.getDirectPrecedents for when it resolves.Returns a range that includes the columns for the entire sheet.
For example, if the range is A1:B2, this method will return A:B.
Optionalshift: boolean
If true this will also shift the columns by the width of the range. default false.
Optionaloptions: ICellRange.GetRangeOptionsICellRange at the repositioned range.
Returns a range that includes the rows for the entire sheet.
For example, if the range is A1:B2, this method will return 1:2.
Optionalshift: boolean
If true this will also shift the rows by the height of the range. default false.
Optionaloptions: ICellRange.GetRangeOptionsICellRange at the repositioned range.
Returns a range that includes the current range up to a content edge boundary.
The direction.
Optionalcell: ICell.Address | GetExtendedOptions
Either the cell to walk from — as ICellRange.GetExtendedOptions.cell, and
the positional form Office Scripts' getExtendedRange takes — or the full
ICellRange.GetExtendedOptions. If neither is given the cell closest to
the edge is used.
ICellRange extended in the given direction.
The result includes the current range, and a boundary with nowhere to go returns the range unchanged — so comparing the two is how you tell there was nothing to extend onto.
ICellRange.GetExtendedOptions.basis picks what the boundary is read from: the range's own content (Ctrl+Arrow, the default) or the lines flanking it (a double-clicked fill handle).
Both bases read the content, merge and table indexes rather than walking cells, so the cost is the blocks involved and not the distance traveled — extending across a million rows costs what extending across ten does.
Returns a range with coordinates adjusted to be fixed (absolute) or flexible (relative).
Optionaloptions: boolean | GetFixedOptions
Either a ICellRange.GetFixedOptions object to
specify individual coordinate settings, or a boolean
to adjust all coordinates at once. If a boolean is provided:
- true sets all coordinates to absolute.
- false sets all coordinates to relative.
ICellRange with updated coordinate references types.
Returns the formula for the range.
When querying the formula this will always be for the top left. However the set values work for the entire range.
Returns the formula object, or null for a cell without one. For formula text across the
whole range use ICellRange.getFormulas, which reads '' for cells without a formula.
Returns the formula text of every cell in the range.
A row-major 2D array in the shape of the range.
= ('=SUM(A1:A3)'), so the result can be
passed straight back to ICellRange.setFormulas.'', not null. This differs from
ICell.getFormulaText and ICellRange.getFormula, which return null.10000 cells, like ICellRange.getValues.Returns the value for the top left InteropScalar.
Returns a 2D array of InteropScalar | values in the shape of the range.
Optionaloptions: ICellRange.GetValuesOptionsICellRange.getValues for options and remarks.
Returns the intersection of the current range and the specified range.
Optionaladdress: ICellRange.Address
The address of the other range to intersect with..
Optionaloptions: ICellRange.GetRangeOptionsICellRange at the intersection of the two ranges, or null if there's no overlap.
Return an array of coords for all merges.
An array of 'IRange.Coords` that contain merges.
Returns the IMovables anchored over the range.
Optionaloptions: ICellRange.GetItemsOptions<IMovableCollection.GetItemsOptions>
The filters of IMovableCollection.getItems without address — this
range is the address.
Returns the number format of the top-left cell.
Reads the top-left cell, the same contract as ICellRange.getValue and
ICellRange.getStyle. A cell without an authored format reads 'General'.
Returns the number format of every cell in the range.
A row-major 2D array in the shape of the range.
'General'.10000 cells, like ICellRange.getValues.Returns a range with the top-left corner moved by the specified row and column offsets.
The number of rows to move the top-left corner up (negative) or down (positive).
The number of columns to move the top-left corner left (negative) or right (positive).
Optionaloptions: ICellRange.GetRangeOptionsICellRange at the repositioned range.
Returns a range repositioned to the specified starting row and column indices.
The new starting row index (inclusive).
The new starting column index (inclusive).
Optionaloptions: ICellRange.GetRangeOptionsICellRange at the repositioned range.
Returns all ranges contributing to this range's values, through the full calculation chain.
The range form of ICell.getPrecedents: a recursive walk upstream to the root data
sources. Resolves null when there are none.
Reach for this before editing. The precedents of a block are what its values depend on, so they are what a change to it can be wrong about.
Returns a 1×1 cell or an explicit range at the specified absolute sheet coordinates.
The absolute row index (0-based, inclusive). Must be ≥ 0.
The absolute column index (0-based, inclusive). Must be ≥ 0.
OptionalrowEnd: number
The absolute ending row index (inclusive). If not provided, defaults to rowStart (1×1 cell).
OptionalcolEnd: number
The absolute ending column index (inclusive). If not provided, defaults to columnStart (1×1 cell).
Optionaloptions: ICellRange.GetRangeOptionsICellRange at the specified coordinates.
All indices are absolute — they are used directly as sheet coordinates and are not relative to the current range. Negative indices are not supported.
When only rowStart and columnStart are supplied the result is always a single cell (1×1),
regardless of the current range's shape. Use getOffsetTo to move the top-left corner
while preserving the original dimensions.
Returns the uniform regions for the requested features within the range.
Optionalfeatures: ICellRange.Feature | readonly ICellRange.Feature[]
The features to partition by. Defaults to ['Contentful'] — never all;
combining features refines the partition (a cross-product), so all-features is the
maximum-fragmentation call. Query one feature at a time; combine only when the relationship
between features is the question.
Optionaloptions: GetRegionsOptions
A region answers where, not what. It carries its address, its shape and the features it was partitioned by, and it bridges to the objects that answer reads: ICellRegion.getCell (the anchor — whose read IS the region's payload, since a region is uniform by construction) and ICellRegion.getRange (the block). Reading is a different verb on a different object, and the hop is the point — it is what keeps a discovery result from answering a question it was never asked.
getRegions combines features into one partition; getCounts counts each
feature separately. The refined count is this method; the cheap discovery is that one.getCounts(Fill) counts the
cells that have a fill and getRegions(Fill) splits them by color; getCounts(Formula)
counts formula cells and getRegions(Formula) splits them by authored R1C1 — so a filled
block is ONE region and the value pasted over the middle of it is a hole. The rule holds
for every feature.A1:D4 is one
Contentful region, and a number format applied in three strokes (or loaded from a file
that stored it in pieces) is one NumberFormat region. The same holds for Fill and the
other content-style features (Font, Alignment, Protection), and each of those splits
only on its own part: a bold column inside a uniform number format does not split the
NumberFormat region. Borders is the exception — a border's edges belong to the block
it was applied to, so two equal boxes side by side are two regions. Refinement is
unaffected — [Contentful, Fill] still partitions by fill and still excludes unoccupied
cells.DateFormat splits by
format string, the same way Fill splits by color; Error splits by error value, backed
by the reconciled error index. Scalar kinds (String/Number/Boolean) are not in this
vocabulary at all — see Feature's own remarks for where they went.Returns a range with the bottom-right corner moved by the specified row and column offsets.
The number of rows to move the bottom-right corner up (negative) or down (positive).
The number of columns to move the bottom-right corner left (negative) or right (positive).
Optionaloptions: ICellRange.GetRangeOptionsICellRange with the resized range.
Resizes the current range to the specified ending row and column indices.
The new ending row index (inclusive).
The new ending column index (inclusive).
Optionaloptions: ICellRange.GetRangeOptionsICellRange with the resized range.
Returns the row after the current range.
Optionaloptions: ICellRange.GetRangeOptionsICellRange with the top-left corner immediately after the current range.
Returns the row at the specified absolute row index, retaining the current column span.
The absolute row index (0-based). Must be ≥ 0. Not relative to the current range.
Optionaloptions: ICellRange.GetRangeOptionsICellRange with a row height of 1 and the original column width.
Returns the row before the current range.
Optionaloptions: ICellRange.GetRangeOptionsICellRange with the top-left corner immediately before the current range.
Returns the row at the specified delta relative to the start of the current range, retaining the current column span.
Number of rows to move from rowStart. Positive moves down; negative moves up.
Optionaloptions: ICellRange.GetRangeOptionsICellRange with a row height of 1 and the original column width.
getRowBy(0) is equivalent to getRowAt(rowStart) — the first row of the current range.
To advance past the end of the range, use getRowAfter instead.
Returns the number of rows of the range.
Returns the bottom of the range.
Returns the header range for the rows.
Optionaloptions: ICellHeaderRange.GetRangeOptionsICellHeaderRange for the rows.
Returns the top of the range.
Returns the Sheet.
Returns a snapshot of the current range's data and formatting suitable for copying or archiving.
Optionaloptions: ICellRange.CopyOptions
Default options when apply the snapshot (Usually copy).
A ISnapshot object representing the range.
Returns the statistics for the range.
Optionaloptions: GetStatsOptions
Iterator options the stats are created with — most usefully
{ onError: 'ignore' } so aggregate reads degrade instead of faulting on error values.
The same override is available per-call on IRangeStats.getAggregates.
{ ignoreHidden: true } measures only what the sheet is showing.
getStats answers "what are the values like"; getCounts answers "how much of
each feature is here". Value analysis (visits values) vs feature occupancy (reads the
indexes) — they do not overlap.
Returns the style for the range.
Returns a range with surrounding data or the entire range if there is no data nearby.
Optionaloptions: GetSurroundingOptionsICellRange containing either nearby content or the entire range.
Returns the ITables that overlap the range.
Optionaloptions: ICellRange.GetItemsOptions<ITableCollection.GetItemsOptions>
The filters of ITableCollection.getItems without address — this range
is the address.
Returns the top-left corner of the range.
Optionaloptions: ICellRange.GetRangeOptions1×1 ICellRange at the top-left corner.
Returns the union of the current range with the specified range.
Optionaladdress: ICellRange.Address
The address of the other range to intersect with.
Optionaloptions: ICellRange.GetRangeOptionsICellRange at the union of the two ranges.
Returns the unlocked ranges (Excel's Allow Edit Ranges) that overlap the range.
Optionaloptions: ICellRange.GetItemsOptions<IProtectedRangeCollection.GetItemsOptions>
The filters of IProtectedRangeCollection.getItems without address —
this range is the address.
See getConditionalFormats for the overlap and one-entry-per-item rules, which are the
same for every ranged item lookup. A cell can also be unlocked by its style
(IStyle.getProtection()); ICell.isEditAllowed answers for both.
Returns the smallest range within the range that contains any used cells.
Optionaloptions: ICellRange.GetRangeOptionsICellRange that contains the smallest range with used cells, or null if the range is empty.
This method returns the range of cells that have been "used," which includes cells with data, as well as certain types of formatting or styling. It focuses on cells that have been interacted with in some form, whether through data entry or other modifications. Merged cells and overflow values may not always be included in this range.
Narrow what counts as used with GetRangeOptions.features: cells with values always
count, and each listed feature widens the extent beyond them — so features: [] is the
values-only fast path (no feature index is consulted) and ['merge'] is values plus merged
spans. The selection is sticky: the returned range carries it.
Returns the value for the top left Scalar.
Returns a 2D array of Scalar | values in the shape of the range.
Optionaloptions: ICellRange.GetValuesOptions
asJSDate Return dates as Javascript dates instead of SerialDates but is slower due to need to create dates.
maxSize Limit the size of the results. defaultValue 10000.A 2D array of values.
Returns an immutable IRange snapshot of the cell range's current values.
IRange is SheetXL's stateless value computation layer — it has no knowledge of
the sheet, and the same logic runs identically in a browser, Node.js, or a Web Worker.
This is the bridge between the live sheet (ICellRange) and pure value computation (IRange):
IRange.ICellRange and IRange for a full guide.
Returns a range that excludes any hidden rows or columns.
Optionaloptions: GetVisibleOptions
Optional parameters that can adjust how visibility is determined (e.g., by ignoring specific visibility rules).
An ICellRanges object that contains the visible portions of the range, excluding any hidden rows or columns.
ICellRanges object with a single range and a length of 1.null.Groups the rows or columns of the range: each one moves one outline level deeper.
Optionalaxis: Axis
Whether to group the rows or the columns. When omitted, entire rows group the rows, entire columns group the columns, and any other range groups both.
Optionaloptions: ITransaction.OperationOptionsThe current ICellRange.
Collapses the groups the range selects, chosen as for showGroupDetails: their details are hidden.
Optionalaxis: Axis
Whether to collapse row groups or column groups. Inferred as for showGroupDetails.
Optionaloptions: ITransaction.OperationOptionsThe current ICellRange.
Insert a new, empty range within the sheet and shifts existing cells to accommodate it.
Optionaloptions: Axis | InsertOptions
The axis or The direction in which to shift existing cells. Defaults to the axis where the range's dimension is smaller (e.g., shifts down if the range is wider than it is tall). See ICellRange.getDefaultShiftAxis for details on the default behavior. See ITransaction.OperationOptions
The current ICellRange.
Inserts the contents of the source into the sheet, shifting existing cells to make space.
The source of the data and formatting to insert.
This can be either a range address, another range object, or an ISnapshot
created using the getSnapshot method.
Optionaloptions: InsertFromOptionsA promise that resolve to an ICellRange or 'null' when the copy has completed.
Returns true if there is any content within the range.
Optionalfeatures: ICellRange.Feature | readonly ICellRange.Feature[]
Restrict the check to specific features. Defaults to any content.
'boolean' indicating if there is content.
Checks if the ranges is empty, meaning it has no values or formatting.
Returns true if the range represents both entire rows and entire columns.
Returns true if the given address represents the same range as the current range.
The address to compare against.
Optionaloptions: ICellRange.GetRangeOptions
Options for getting the range from the address.
Indicates if the range is readonly only.
Not an indicator of if the range is protected but rather if the range is being used in a way that doesn't support write operations. (e.g. the range is being provided as a listener source ICellRange.Event or the range belongs to a historical snapshot.)
Returns true if the range is a single cell.
Merges all cells within the selection ranges.
Optionaloptions: AxisSelection | MergeOptionsThe current ICellRange.
Removes duplicate rows based on specified columns.
Optionaloptions: RemoveDuplicatesOptions
Configuration for the duplicate removal operation, including the columns to consider.
ICellRange containing the rows that remain. This is the original range resized by the
number of rows that were removed (the origin and the columns are unchanged).
This method mimics the "Remove Duplicates" feature in Excel.
The duplicate rows are deleted rather than cleared, so the rows that remain keep their formatting, hyperlinks and comments. Only the range is compacted - the same number of rows is given back at the bottom of the range so that the cells below it stay where they are.
A row with no values is still a row. The first blank row is unique and any that follow are duplicates of it. Blank rows after the last row with a value are left alone.
Selects the current range.
Optionaloptions: ICellRange.SelectOptionsAn async ICellRange.
Sets or clears the comment on the range's first cell.
Comment modifiers, a plain string as the content, or null to remove.
Optionaloptions: ITransaction.OperationOptionsThe current ICellRange.
A comment is on one cell, so a value goes on the range's top-left cell — updating the comment
there if it has one, adding one otherwise — while null clears every comment within the
range. The comment itself is the richer surface: getSheet().getComments() to add, and
ICell.getComments to reach the one on a cell.
Applies a transformation to the cell range's values and writes the result back to the sheet.
A pure function that transforms the cell range's IRange.
Optionaloptions: ITransaction.OperationOptionsConvenience wrapper around getValuesRange and setValues:
setComputedValues(fn) ≡ this.setValues(fn(this.getValuesRange()))
The callback receives the current values as an immutable IRange,
and the returned IRange is written back to the sheet.
Set the formula for a range.
The formula to be set or a callback that will generate a formula.
Optionaloptions: SetValuesOptionsThe current ICellRange.
Sets the formula of each cell in the range.
Formula text in row-major order, each with a leading =.
Optionaloptions: SetValuesOptionsThe same write as ICellRange.setValues: text with a leading = is entered as a
formula, and an array smaller than the range writes only the cells it covers.
Set the hyperlink for a range of values.
A hyperlink update
Optionaloptions: ITransaction.OperationOptionsThe current ICellRange.
Sets one number format on every cell in the range.
The format code, or null to clear it.
Optionaloptions: UpdateOptionsSets the number format of each cell in the range.
Format codes in row-major order.
Optionaloptions: UpdateCellsOptionsAn array smaller than the range writes only the cells it covers, as ICellRange.setValues does.
Set a single value to the entire range.
Optionaloptions: SetValuesOptionsSet the value(s) of one or more cells within a range.
The value to be set or a callback that will generate a value.
Optionaloptions: SetValuesOptionsThe current ICellRange.
null values will clear the corresponding cells.undefined values will be ignored, leaving the existing cell values unchanged.Expands the groups the range selects: those whose summary row or column is in the range or, when there are none, the innermost groups the range meets.
Optionalaxis: Axis
Whether to expand row groups or column groups. Inferred when the range is entire rows or entire columns; required otherwise.
Optionaloptions: ITransaction.OperationOptionsThe current ICellRange.
Sort the given range.
Optionaloptions: boolean | ISort.Options[] | ISort.RangeOptions & ITransaction.OperationOptions
If a boolean then this is sort ascending or descending.
The current ICellRange.
Sort the given range by a single column or row.
Optionaloptions: boolean | Criteria & ITransaction.OperationOptionsParses the text in the selected column into multiple columns based on a delimiter.
Optionaloptions: string | TextToColumnsOptions
Delimiter or Configuration for the split operation, including delimiter and handling of existing data.
ICellRange containing the the newly split data
Returns the address as a string.
Ungroups the rows or columns of the range: each grouped one moves one outline level out. Rows or columns that are not grouped are left alone.
Optionalaxis: Axis
Whether to ungroup the rows or the columns. When omitted, chosen as for group.
Optionaloptions: ITransaction.OperationOptionsThe current ICellRange.
Unmerge all cells within the selection ranges.
Optionaloptions: ITransaction.OperationOptionsThe current ICellRange.
Provides a convenient method for setting values with a range of calls.
Optionaloptions: UpdateCellsOptionsThe current ICellRange.
Returns the values within the range, yielding each value.
Optionaloptions: ICellRange.IteratorOptions<ICellRange.Address>An iterator that yields Scalar each value within the range.
A ICellRange represents a rectangular collection of ICell within an ISheet.
Naming Conventions: Methods usually adhere to the following conventions except when adhering to a idiomatic existing conventions of Typescript (for example
sizeor 'length'):getprefix.toprefix (e.g.,toArray()).