Skip to content

Object reference

Range

A Range represents one cell such as A2 or a rectangular block of rows and columns in a Sheet. Use getValue() for one cell and getValues() or setValues() for a 2D array of rows.

SpreadsheetApp.Range

Start here

Get this object first

Get a range by A1 notation or row and column numbers from a Sheet tab, or call SpreadsheetApp.getActiveRange() when a menu action uses the current selection.

Code.gs 
function logRangeNotation() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange('A2:C10');
  Logger.log(range.getA1Notation());
}

Then use it

Common actions

Choose an action, then open its example only when you need the code.

Read one cell with getValue

This reads the value in cell A2 and logs it without changing the sheet.

SpreadsheetApp.RangeSpreadsheetApp.Sheet
Show example code
Code.gs 
function logFirstCellValue() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const value = sheet.getRange('A2').getValue();
  Logger.log(String(value));
}

Read a block of rows with getValues

This reads a 2D array of rows and columns in one call and logs the row count without changing any cells.

SpreadsheetApp.RangeSpreadsheetApp.Sheet
Show example code
Code.gs 
function logRangeRowCount() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const values = sheet.getRange(2, 1, 5, 3).getValues();
  Logger.log('Read ' + values.length + ' rows and ' + values[0].length + ' columns.');
}

Write a 2D array of values with setValues (this updates cells)

This checks that D2:E3 is blank and then writes two rows of two columns starting at D2. The range dimensions must match the outer and inner array lengths.

SpreadsheetApp.RangeSpreadsheetApp.Sheet
Show example code
Code.gs 
function writeSampleStatusRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const rows = [
    ['Checked', new Date()],
    ['Pending', new Date()]
  ];
  const targetRange = sheet.getRange(2, 4, rows.length, rows[0].length);
  if (!targetRange.isBlank()) {
    throw new Error('D2:E3 already has values. Clear D2:E3 or choose an empty range before writing.');
  }
  targetRange.setValues(rows);
  Logger.log('Wrote ' + rows.length + ' rows.');
}

Technical reference

Exact names and signatures

Generated runtime and signature facts stay separate from the beginner actions above. Open method details only when you need them.

At a glance

Service
SpreadsheetApp
Reference symbol
SpreadsheetApp.Range
Object
Range
Runtime
From signature data

Open the Google reference source

Methods and signatures

Browse 176 methods

activate

From signature data Google source
  • activate(): SpreadsheetApp.Range

activateAsCurrentCell

From signature data Google source
  • activateAsCurrentCell(): SpreadsheetApp.Range

addDeveloperMetadata

From signature data Google source
  • addDeveloperMetadata(key: string): SpreadsheetApp.Range
  • addDeveloperMetadata(key: string, visibility: SpreadsheetApp.DeveloperMetadataVisibility): SpreadsheetApp.Range
  • addDeveloperMetadata(key: string, value: string): SpreadsheetApp.Range
  • addDeveloperMetadata(key: string, value: string, visibility: SpreadsheetApp.DeveloperMetadataVisibility): SpreadsheetApp.Range

applyColumnBanding

From signature data Google source
  • applyColumnBanding(): SpreadsheetApp.Banding
  • applyColumnBanding(bandingTheme: SpreadsheetApp.BandingTheme): SpreadsheetApp.Banding
  • applyColumnBanding(bandingTheme: SpreadsheetApp.BandingTheme, showHeader: boolean, showFooter: boolean): SpreadsheetApp.Banding

applyRowBanding

From signature data Google source
  • applyRowBanding(): SpreadsheetApp.Banding
  • applyRowBanding(bandingTheme: SpreadsheetApp.BandingTheme): SpreadsheetApp.Banding
  • applyRowBanding(bandingTheme: SpreadsheetApp.BandingTheme, showHeader: boolean, showFooter: boolean): SpreadsheetApp.Banding

autoFill

From signature data Google source
  • autoFill(destination: SpreadsheetApp.Range, series: SpreadsheetApp.AutoFillSeries): void

autoFillToNeighbor

From signature data Google source
  • autoFillToNeighbor(series: SpreadsheetApp.AutoFillSeries): void

breakApart

From signature data Google source
  • breakApart(): SpreadsheetApp.Range

canEdit

From signature data Google source
  • canEdit(): boolean

check

From signature data Google source
  • check(): SpreadsheetApp.Range

clear

From signature data Google source
  • clear(): SpreadsheetApp.Range
  • clear(options: SpreadsheetApp.ClearOptions): SpreadsheetApp.Range

clearContent

From signature data Google source
  • clearContent(): SpreadsheetApp.Range

clearDataValidations

From signature data Google source
  • clearDataValidations(): SpreadsheetApp.Range

clearFormat

From signature data Google source
  • clearFormat(): SpreadsheetApp.Range

clearNote

From signature data Google source
  • clearNote(): SpreadsheetApp.Range

collapseGroups

From signature data Google source
  • collapseGroups(): SpreadsheetApp.Range

copyFormatToRange

From signature data Google source
  • copyFormatToRange(gridId: number, column: number, columnEnd: number, row: number, rowEnd: number): void
  • copyFormatToRange(sheet: SpreadsheetApp.Sheet, column: number, columnEnd: number, row: number, rowEnd: number): void

copyTo

From signature data Google source
  • copyTo(destination: SpreadsheetApp.Range): void
  • copyTo(destination: SpreadsheetApp.Range, copyPasteType: SpreadsheetApp.CopyPasteType, transposed: boolean): void
  • copyTo(destination: SpreadsheetApp.Range, options: SpreadsheetApp.CopyOptions): void

copyValuesToRange

From signature data Google source
  • copyValuesToRange(gridId: number, column: number, columnEnd: number, row: number, rowEnd: number): void
  • copyValuesToRange(sheet: SpreadsheetApp.Sheet, column: number, columnEnd: number, row: number, rowEnd: number): void

createDataSourcePivotTable

From signature data Google source
  • createDataSourcePivotTable(dataSource: SpreadsheetApp.DataSource): SpreadsheetApp.DataSourcePivotTable

createDataSourceTable

From signature data Google source
  • createDataSourceTable(dataSource: SpreadsheetApp.DataSource): SpreadsheetApp.DataSourceTable

createDeveloperMetadataFinder

From signature data Google source
  • createDeveloperMetadataFinder(): SpreadsheetApp.DeveloperMetadataFinder

createFilter

From signature data Google source
  • createFilter(): SpreadsheetApp.Filter

createPivotTable

From signature data Google source
  • createPivotTable(sourceData: SpreadsheetApp.Range): SpreadsheetApp.PivotTable

createTextFinder

From signature data Google source
  • createTextFinder(findText: string): SpreadsheetApp.TextFinder

deleteCells

From signature data Google source
  • deleteCells(shiftDimension: SpreadsheetApp.Dimension): void

expandGroups

From signature data Google source
  • expandGroups(): SpreadsheetApp.Range

getA1Notation

From signature data Google source
  • getA1Notation(): string

getBackground

From signature data Google source
  • getBackground(): string

getBackgroundObject

From signature data Google source
  • getBackgroundObject(): SpreadsheetApp.Color

getBackgroundObjects

From signature data Google source
  • getBackgroundObjects(): SpreadsheetApp.Color[][]

getBackgrounds

From signature data Google source
  • getBackgrounds(): string[][]

getBandings

From signature data Google source
  • getBandings(): SpreadsheetApp.Banding[]

getCell

From signature data Google source
  • getCell(row: number, column: number): SpreadsheetApp.Range

getColumn

From signature data Google source
  • getColumn(): number

getDataRegion

From signature data Google source
  • getDataRegion(): SpreadsheetApp.Range
  • getDataRegion(dimension: SpreadsheetApp.Dimension): SpreadsheetApp.Range

getDataSourceFormula

From signature data Google source
  • getDataSourceFormula(): SpreadsheetApp.DataSourceFormula | null

getDataSourceFormulas

From signature data Google source
  • getDataSourceFormulas(): SpreadsheetApp.DataSourceFormula[]

getDataSourcePivotTables

From signature data Google source
  • getDataSourcePivotTables(): SpreadsheetApp.DataSourcePivotTable[]

getDataSourceTables

From signature data Google source
  • getDataSourceTables(): SpreadsheetApp.DataSourceTable[]

getDataSourceUrl

From signature data Google source
  • getDataSourceUrl(): string

getDataTable

From signature data Google source
  • getDataTable(): Charts.DataTable
  • getDataTable(firstRowIsHeader: boolean): Charts.DataTable

getDataValidation

From signature data Google source
  • getDataValidation(): SpreadsheetApp.DataValidation | null

getDataValidations

From signature data Google source
  • getDataValidations(): (SpreadsheetApp.DataValidation | null)[][]

getDeveloperMetadata

From signature data Google source
  • getDeveloperMetadata(): SpreadsheetApp.DeveloperMetadata[]

getDisplayValue

From signature data Google source
  • getDisplayValue(): string

getDisplayValues

From signature data Google source
  • getDisplayValues(): string[][]

getFilter

From signature data Google source
  • getFilter(): SpreadsheetApp.Filter | null

getFontColor

From signature data Google source
  • getFontColor(): string

getFontColorObject

From signature data Google source
  • getFontColorObject(): SpreadsheetApp.Color

getFontColorObjects

From signature data Google source
  • getFontColorObjects(): SpreadsheetApp.Color[][]

getFontColors

From signature data Google source
  • getFontColors(): string[][]

getFontFamilies

From signature data Google source
  • getFontFamilies(): string[][]

getFontFamily

From signature data Google source
  • getFontFamily(): string

getFontLine

From signature data Google source
  • getFontLine(): string

getFontLines

From signature data Google source
  • getFontLines(): string[][]

getFontSize

From signature data Google source
  • getFontSize(): number

getFontSizes

From signature data Google source
  • getFontSizes(): number[][]

getFontStyle

From signature data Google source
  • getFontStyle(): string

getFontStyles

From signature data Google source
  • getFontStyles(): string[][]

getFontWeight

From signature data Google source
  • getFontWeight(): string

getFontWeights

From signature data Google source
  • getFontWeights(): string[][]

getFormula

From signature data Google source
  • getFormula(): string

getFormulaR1C1

From signature data Google source
  • getFormulaR1C1(): string

getFormulas

From signature data Google source
  • getFormulas(): string[][]

getFormulasR1C1

From signature data Google source
  • getFormulasR1C1(): string[][]

getGridId

From signature data Google source
  • getGridId(): number

getHeight

From signature data Google source
  • getHeight(): number

getHorizontalAlignment

From signature data Google source
  • getHorizontalAlignment(): string

getHorizontalAlignments

From signature data Google source
  • getHorizontalAlignments(): string[][]

getLastColumn

From signature data Google source
  • getLastColumn(): number

getLastRow

From signature data Google source
  • getLastRow(): number

getMergedRanges

From signature data Google source
  • getMergedRanges(): SpreadsheetApp.Range[]

getNextDataCell

From signature data Google source
  • getNextDataCell(direction: SpreadsheetApp.Direction): SpreadsheetApp.Range

getNote

From signature data Google source
  • getNote(): string

getNotes

From signature data Google source
  • getNotes(): string[][]

getNumColumns

From signature data Google source
  • getNumColumns(): number

getNumRows

From signature data Google source
  • getNumRows(): number

getNumberFormat

From signature data Google source
  • getNumberFormat(): string

getNumberFormats

From signature data Google source
  • getNumberFormats(): string[][]

getRichTextValue

From signature data Google source
  • getRichTextValue(): SpreadsheetApp.RichTextValue | null

getRichTextValues

From signature data Google source
  • getRichTextValues(): SpreadsheetApp.RichTextValue[][]

getRow

From signature data Google source
  • getRow(): number

getRowIndex

From signature data Google source
  • getRowIndex(): number

getSheet

From signature data Google source
  • getSheet(): SpreadsheetApp.Sheet

getTextDirection

From signature data Google source
  • getTextDirection(): SpreadsheetApp.TextDirection

getTextDirections

From signature data Google source
  • getTextDirections(): SpreadsheetApp.TextDirection[][]

getTextRotation

From signature data Google source
  • getTextRotation(): SpreadsheetApp.TextRotation

getTextRotations

From signature data Google source
  • getTextRotations(): SpreadsheetApp.TextRotation[][]

getTextStyle

From signature data Google source
  • getTextStyle(): SpreadsheetApp.TextStyle

getTextStyles

From signature data Google source
  • getTextStyles(): SpreadsheetApp.TextStyle[][]

getValue

From signature data Google source
  • getValue(): unknown

getValues

From signature data Google source
  • getValues(): unknown[][]

getVerticalAlignment

From signature data Google source
  • getVerticalAlignment(): string

getVerticalAlignments

From signature data Google source
  • getVerticalAlignments(): string[][]

getWidth

From signature data Google source
  • getWidth(): number

getWrap

From signature data Google source
  • getWrap(): boolean

getWrapStrategies

From signature data Google source
  • getWrapStrategies(): SpreadsheetApp.WrapStrategy[][]

getWrapStrategy

From signature data Google source
  • getWrapStrategy(): SpreadsheetApp.WrapStrategy

getWraps

From signature data Google source
  • getWraps(): boolean[][]

insertCells

From signature data Google source
  • insertCells(shiftDimension: SpreadsheetApp.Dimension): SpreadsheetApp.Range

insertCheckboxes

From signature data Google source
  • insertCheckboxes(): SpreadsheetApp.Range
  • insertCheckboxes(checkedValue: unknown): SpreadsheetApp.Range
  • insertCheckboxes(checkedValue: unknown, uncheckedValue: unknown): SpreadsheetApp.Range

isBlank

From signature data Google source
  • isBlank(): boolean

isChecked

From signature data Google source
  • isChecked(): boolean

isDataValid

From signature data Google source
  • isDataValid(): boolean

isDataValidForAll

From signature data Google source
  • isDataValidForAll(): boolean[][]

isEndColumnBounded

From signature data Google source
  • isEndColumnBounded(): boolean

isEndRowBounded

From signature data Google source
  • isEndRowBounded(): boolean

isPartOfMerge

From signature data Google source
  • isPartOfMerge(): boolean

isStartColumnBounded

From signature data Google source
  • isStartColumnBounded(): boolean

isStartRowBounded

From signature data Google source
  • isStartRowBounded(): boolean

merge

From signature data Google source
  • merge(): SpreadsheetApp.Range

mergeAcross

From signature data Google source
  • mergeAcross(): SpreadsheetApp.Range

mergeVertically

From signature data Google source
  • mergeVertically(): SpreadsheetApp.Range

moveTo

From signature data Google source
  • moveTo(destination: SpreadsheetApp.Range): void

offset

From signature data Google source
  • offset(rowOffset: number, columnOffset: number): SpreadsheetApp.Range
  • offset(rowOffset: number, columnOffset: number, numRows: number): SpreadsheetApp.Range
  • offset(rowOffset: number, columnOffset: number, numRows: number, numColumns: number): SpreadsheetApp.Range

protect

From signature data Google source
  • protect(): SpreadsheetApp.Protection

randomize

From signature data Google source
  • randomize(): SpreadsheetApp.Range

removeCheckboxes

From signature data Google source
  • removeCheckboxes(): SpreadsheetApp.Range

removeDuplicates

From signature data Google source
  • removeDuplicates(): SpreadsheetApp.Range
  • removeDuplicates(columnsToCompare: number[]): SpreadsheetApp.Range

setBackground

From signature data Google source
  • setBackground(color: string | null): SpreadsheetApp.Range

setBackgroundObject

From signature data Google source
  • setBackgroundObject(color: SpreadsheetApp.Color): SpreadsheetApp.Range

setBackgroundObjects

From signature data Google source
  • setBackgroundObjects(color: SpreadsheetApp.Color[][]): SpreadsheetApp.Range

setBackgroundRGB

From signature data Google source
  • setBackgroundRGB(red: number, green: number, blue: number): SpreadsheetApp.Range

setBackgrounds

From signature data Google source
  • setBackgrounds(colors: (string | null)[][]): SpreadsheetApp.Range

setBorder

From signature data Google source
  • setBorder(top: boolean | null, left: boolean | null, bottom: boolean | null, right: boolean | null, vertical: boolean | null, horizontal: boolean | null): SpreadsheetApp.Range
  • setBorder(top: boolean | null, left: boolean | null, bottom: boolean | null, right: boolean | null, vertical: boolean | null, horizontal: boolean | null, color: string | null, style: SpreadsheetApp.BorderStyle | null): SpreadsheetApp.Range

setDataValidation

From signature data Google source
  • setDataValidation(rule: SpreadsheetApp.DataValidation | null): SpreadsheetApp.Range

setDataValidations

From signature data Google source
  • setDataValidations(rules: SpreadsheetApp.DataValidation[][]): SpreadsheetApp.Range

setFontColor

From signature data Google source
  • setFontColor(color: string | null): SpreadsheetApp.Range

setFontColorObject

From signature data Google source
  • setFontColorObject(color: SpreadsheetApp.Color): SpreadsheetApp.Range

setFontColorObjects

From signature data Google source
  • setFontColorObjects(colors: SpreadsheetApp.Color[][]): SpreadsheetApp.Range

setFontColors

From signature data Google source
  • setFontColors(colors: unknown[][]): SpreadsheetApp.Range

setFontFamilies

From signature data Google source
  • setFontFamilies(fontFamilies: unknown[][]): SpreadsheetApp.Range

setFontFamily

From signature data Google source
  • setFontFamily(fontFamily: string): SpreadsheetApp.Range

setFontLine

From signature data Google source
  • setFontLine(fontLine: string): SpreadsheetApp.Range

setFontLines

From signature data Google source
  • setFontLines(fontLines: unknown[][]): SpreadsheetApp.Range

setFontSize

From signature data Google source
  • setFontSize(size: number): SpreadsheetApp.Range

setFontSizes

From signature data Google source
  • setFontSizes(sizes: unknown[][]): SpreadsheetApp.Range

setFontStyle

From signature data Google source
  • setFontStyle(fontStyle: string): SpreadsheetApp.Range

setFontStyles

From signature data Google source
  • setFontStyles(fontStyles: unknown[][]): SpreadsheetApp.Range

setFontWeight

From signature data Google source
  • setFontWeight(fontWeight: string | null): SpreadsheetApp.Range

setFontWeights

From signature data Google source
  • setFontWeights(fontWeights: unknown[][]): SpreadsheetApp.Range

setFormula

From signature data Google source
  • setFormula(formula: string): SpreadsheetApp.Range

setFormulaR1C1

From signature data Google source
  • setFormulaR1C1(formula: string): SpreadsheetApp.Range

setFormulas

From signature data Google source
  • setFormulas(formulas: string[][]): SpreadsheetApp.Range

setFormulasR1C1

From signature data Google source
  • setFormulasR1C1(formulas: string[][]): SpreadsheetApp.Range

setHorizontalAlignment

From signature data Google source
  • setHorizontalAlignment(alignment: string): SpreadsheetApp.Range

setHorizontalAlignments

From signature data Google source
  • setHorizontalAlignments(alignments: unknown[][]): SpreadsheetApp.Range

setNote

From signature data Google source
  • setNote(note: string): SpreadsheetApp.Range

setNotes

From signature data Google source
  • setNotes(notes: string[][]): SpreadsheetApp.Range

setNumberFormat

From signature data Google source
  • setNumberFormat(numberFormat: string): SpreadsheetApp.Range

setNumberFormats

From signature data Google source
  • setNumberFormats(numberFormats: string[][]): SpreadsheetApp.Range

setRichTextValue

From signature data Google source
  • setRichTextValue(value: SpreadsheetApp.RichTextValue): SpreadsheetApp.Range

setRichTextValues

From signature data Google source
  • setRichTextValues(values: SpreadsheetApp.RichTextValue[][]): SpreadsheetApp.Range
From signature data Google source
  • setShowHyperlink(showHyperlink: boolean): SpreadsheetApp.Range

setTextDirection

From signature data Google source
  • setTextDirection(direction: SpreadsheetApp.TextDirection): SpreadsheetApp.Range

setTextDirections

From signature data Google source
  • setTextDirections(directions: SpreadsheetApp.TextDirection[][]): SpreadsheetApp.Range

setTextRotation

From signature data Google source
  • setTextRotation(degrees: number): SpreadsheetApp.Range
  • setTextRotation(rotation: SpreadsheetApp.TextRotation): SpreadsheetApp.Range

setTextRotations

From signature data Google source
  • setTextRotations(rotations: SpreadsheetApp.TextRotation[][]): SpreadsheetApp.Range

setTextStyle

From signature data Google source
  • setTextStyle(style: SpreadsheetApp.TextStyle): SpreadsheetApp.Range

setTextStyles

From signature data Google source
  • setTextStyles(styles: SpreadsheetApp.TextStyle[][]): SpreadsheetApp.Range

setValue

From signature data Google source
  • setValue(value: unknown): SpreadsheetApp.Range

setValues

From signature data Google source
  • setValues(values: unknown[][]): SpreadsheetApp.Range

setVerticalAlignment

From signature data Google source
  • setVerticalAlignment(alignment: string): SpreadsheetApp.Range

setVerticalAlignments

From signature data Google source
  • setVerticalAlignments(alignments: unknown[][]): SpreadsheetApp.Range

setVerticalText

From signature data Google source
  • setVerticalText(isVertical: boolean): SpreadsheetApp.Range

setWrap

From signature data Google source
  • setWrap(isWrapEnabled: boolean): SpreadsheetApp.Range

setWrapStrategies

From signature data Google source
  • setWrapStrategies(strategies: SpreadsheetApp.WrapStrategy[][]): SpreadsheetApp.Range

setWrapStrategy

From signature data Google source
  • setWrapStrategy(strategy: SpreadsheetApp.WrapStrategy): SpreadsheetApp.Range

setWraps

From signature data Google source
  • setWraps(isWrapEnabled: unknown[][]): SpreadsheetApp.Range

shiftColumnGroupDepth

From signature data Google source
  • shiftColumnGroupDepth(delta: number): SpreadsheetApp.Range

shiftRowGroupDepth

From signature data Google source
  • shiftRowGroupDepth(delta: number): SpreadsheetApp.Range

sort

From signature data Google source
  • sort(columnPosition: number): SpreadsheetApp.Range
  • sort(sortSpec: SpreadsheetApp.SortSpec | SpreadsheetApp.SortSpec[]): SpreadsheetApp.Range

splitTextToColumns

From signature data Google source
  • splitTextToColumns(): void
  • splitTextToColumns(delimiter: string): void
  • splitTextToColumns(delimiter: SpreadsheetApp.TextToColumnsDelimiter): void

trimWhitespace

From signature data Google source
  • trimWhitespace(): SpreadsheetApp.Range

uncheck

From signature data Google source
  • uncheck(): SpreadsheetApp.Range

Need the whole workflow?

Return to a task guide

Task guides cover setup, permissions, expected results, and troubleshooting as well as object methods.

Browse Google Sheets tasks