Skip to content

Object reference

Sheet

A Sheet is one tab inside a Google spreadsheet. Reach it with getSheetByName() or getActiveSheet(), then inspect getLastRow() or read cells with getRange().

SpreadsheetApp.Sheet

Start here

Get this object first

Look up a specific tab by name with getSheetByName(), or call getActiveSheet() when a menu action should work on the tab currently open.

Code.gs 
function logActiveSheetName() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  Logger.log(sheet.getName());
}

Then use it

Common actions

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

Check the last row with content

This reads getLastRow() to find where the used part of the tab ends without changing any cells.

SpreadsheetApp.Sheet
Show example code
Code.gs 
function logLastUsedRow() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  Logger.log('Last used row: ' + sheet.getLastRow());
}

Read all used cells with getDataRange

This checks getLastRow() so a blank tab is skipped, then reads the used area as a 2D array in one call with getDataRange().getValues().

SpreadsheetApp.SheetSpreadsheetApp.Range
Show example code
Code.gs 
function logDataRangeRowCount() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  if (sheet.getLastRow() === 0) {
    Logger.log('Sheet is blank.');
    return;
  }
  const rows = sheet.getDataRange().getValues();
  Logger.log('Data range rows: ' + rows.length);
}

Read data rows below a header row

This checks that the tab has at least one row below row 1 before calling getRange(), avoiding invalid range dimension errors on empty tabs.

SpreadsheetApp.SheetSpreadsheetApp.Range
Show example code
Code.gs 
function logDataRowsBelowHeader() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const numRows = sheet.getLastRow() - 1;
  if (numRows < 1) {
    Logger.log('No data rows below the header row.');
    return;
  }
  const rows = sheet.getRange(2, 1, numRows, sheet.getLastColumn()).getValues();
  Logger.log('Data rows read: ' + rows.length);
}

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.Sheet
Object
Sheet
Runtime
From signature data

Open the Google reference source

Methods and signatures

Browse 128 methods

activate

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

addDeveloperMetadata

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

appendRow

From signature data Google source
  • appendRow(rowContents: unknown[]): SpreadsheetApp.Sheet

asDataSourceSheet

From signature data Google source
  • asDataSourceSheet(): SpreadsheetApp.DataSourceSheet | null

autoResizeColumn

From signature data Google source
  • autoResizeColumn(columnPosition: number): SpreadsheetApp.Sheet

autoResizeColumns

From signature data Google source
  • autoResizeColumns(startingColumn: number, numColumns: number): SpreadsheetApp.Sheet

autoResizeRows

From signature data Google source
  • autoResizeRows(startRow: number, numRows: number): SpreadsheetApp.Sheet

clear

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

clearConditionalFormatRules

From signature data Google source
  • clearConditionalFormatRules(): void

clearContents

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

clearFormats

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

clearNotes

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

collapseAllColumnGroups

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

collapseAllRowGroups

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

copyTo

From signature data Google source
  • copyTo(spreadsheet: SpreadsheetApp.Spreadsheet): SpreadsheetApp.Sheet

createDeveloperMetadataFinder

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

createTextFinder

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

deleteColumn

From signature data Google source
  • deleteColumn(columnPosition: number): SpreadsheetApp.Sheet

deleteColumns

From signature data Google source
  • deleteColumns(columnPosition: number, howMany: number): void

deleteRow

From signature data Google source
  • deleteRow(rowPosition: number): SpreadsheetApp.Sheet

deleteRows

From signature data Google source
  • deleteRows(rowPosition: number, howMany: number): void

expandAllColumnGroups

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

expandAllRowGroups

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

expandColumnGroupsUpToDepth

From signature data Google source
  • expandColumnGroupsUpToDepth(groupDepth: number): SpreadsheetApp.Sheet

expandRowGroupsUpToDepth

From signature data Google source
  • expandRowGroupsUpToDepth(groupDepth: number): SpreadsheetApp.Sheet

getActiveCell

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

getActiveRange

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

getActiveRangeList

From signature data Google source
  • getActiveRangeList(): SpreadsheetApp.RangeList | null

getBandings

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

getCharts

From signature data Google source
  • getCharts(): SpreadsheetApp.EmbeddedChart[]

getColumnGroup

From signature data Google source
  • getColumnGroup(columnIndex: number, groupDepth: number): SpreadsheetApp.Group | null

getColumnGroupControlPosition

From signature data Google source
  • getColumnGroupControlPosition(): SpreadsheetApp.GroupControlTogglePosition

getColumnGroupDepth

From signature data Google source
  • getColumnGroupDepth(columnIndex: number): number

getColumnWidth

From signature data Google source
  • getColumnWidth(columnPosition: number): number

getConditionalFormatRules

From signature data Google source
  • getConditionalFormatRules(): SpreadsheetApp.ConditionalFormatRule[]

getCurrentCell

From signature data Google source
  • getCurrentCell(): SpreadsheetApp.Range | null

getDataRange

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

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[]

getDeveloperMetadata

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

getDrawings

From signature data Google source
  • getDrawings(): SpreadsheetApp.Drawing[]

getFilter

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

getFormUrl

From signature data Google source
  • getFormUrl(): string | null

getFrozenColumns

From signature data Google source
  • getFrozenColumns(): number

getFrozenRows

From signature data Google source
  • getFrozenRows(): number

getImages

From signature data Google source
  • getImages(): SpreadsheetApp.OverGridImage[]

getIndex

From signature data Google source
  • getIndex(): number

getLastColumn

From signature data Google source
  • getLastColumn(): number

getLastRow

From signature data Google source
  • getLastRow(): number

getMaxColumns

From signature data Google source
  • getMaxColumns(): number

getMaxRows

From signature data Google source
  • getMaxRows(): number

getName

From signature data Google source
  • getName(): string

getNamedRanges

From signature data Google source
  • getNamedRanges(): SpreadsheetApp.NamedRange[]

getParent

From signature data Google source
  • getParent(): SpreadsheetApp.Spreadsheet

getPivotTables

From signature data Google source
  • getPivotTables(): SpreadsheetApp.PivotTable[]

getProtections

From signature data Google source
  • getProtections(type: SpreadsheetApp.ProtectionType): SpreadsheetApp.Protection[]

getRange

From signature data Google source
  • getRange(a1Notation: string): SpreadsheetApp.Range
  • getRange(row: number, column: number): SpreadsheetApp.Range
  • getRange(row: number, column: number, numRows: number): SpreadsheetApp.Range
  • getRange(row: number, column: number, numRows: number, numColumns: number): SpreadsheetApp.Range

getRangeList

From signature data Google source
  • getRangeList(a1Notations: string[]): SpreadsheetApp.RangeList

getRowGroup

From signature data Google source
  • getRowGroup(rowIndex: number, groupDepth: number): SpreadsheetApp.Group | null

getRowGroupControlPosition

From signature data Google source
  • getRowGroupControlPosition(): SpreadsheetApp.GroupControlTogglePosition

getRowGroupDepth

From signature data Google source
  • getRowGroupDepth(rowIndex: number): number

getRowHeight

From signature data Google source
  • getRowHeight(rowPosition: number): number

getSelection

From signature data Google source
  • getSelection(): SpreadsheetApp.Selection

getSheetId

From signature data Google source
  • getSheetId(): number

getSheetName

From signature data Google source
  • getSheetName(): string

getSheetProtection

From signature data Google source
  • getSheetProtection(): SpreadsheetApp.PageProtection

getSheetValues

From signature data Google source
  • getSheetValues(startRow: number, startColumn: number, numRows: number, numColumns: number): unknown[][]

getSlicers

From signature data Google source
  • getSlicers(): SpreadsheetApp.Slicer[]

getTabColor

From signature data Google source
  • getTabColor(): string | null

getTabColorObject

From signature data Google source
  • getTabColorObject(): SpreadsheetApp.Color | null

getType

From signature data Google source
  • getType(): SpreadsheetApp.SheetType

hasHiddenGridlines

From signature data Google source
  • hasHiddenGridlines(): boolean

hideColumn

From signature data Google source
  • hideColumn(column: SpreadsheetApp.Range): void

hideColumns

From signature data Google source
  • hideColumns(columnIndex: number): void
  • hideColumns(columnIndex: number, numColumns: number): void

hideRow

From signature data Google source
  • hideRow(row: SpreadsheetApp.Range): void

hideRows

From signature data Google source
  • hideRows(rowIndex: number): void
  • hideRows(rowIndex: number, numRows: number): void

hideSheet

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

insertChart

From signature data Google source
  • insertChart(chart: SpreadsheetApp.EmbeddedChart): void

insertColumnAfter

From signature data Google source
  • insertColumnAfter(afterPosition: number): SpreadsheetApp.Sheet

insertColumnBefore

From signature data Google source
  • insertColumnBefore(beforePosition: number): SpreadsheetApp.Sheet

insertColumns

From signature data Google source
  • insertColumns(columnIndex: number): void
  • insertColumns(columnIndex: number, numColumns: number): void

insertColumnsAfter

From signature data Google source
  • insertColumnsAfter(afterPosition: number, howMany: number): SpreadsheetApp.Sheet

insertColumnsBefore

From signature data Google source
  • insertColumnsBefore(beforePosition: number, howMany: number): SpreadsheetApp.Sheet

insertImage

From signature data Google source
  • insertImage(blobSource: BlobSource, column: number, row: number): SpreadsheetApp.OverGridImage
  • insertImage(blobSource: BlobSource, column: number, row: number, offsetX: number, offsetY: number): SpreadsheetApp.OverGridImage
  • insertImage(url: string, column: number, row: number): SpreadsheetApp.OverGridImage
  • insertImage(url: string, column: number, row: number, offsetX: number, offsetY: number): SpreadsheetApp.OverGridImage

insertRowAfter

From signature data Google source
  • insertRowAfter(afterPosition: number): SpreadsheetApp.Sheet

insertRowBefore

From signature data Google source
  • insertRowBefore(beforePosition: number): SpreadsheetApp.Sheet

insertRows

From signature data Google source
  • insertRows(rowIndex: number): void
  • insertRows(rowIndex: number, numRows: number): void

insertRowsAfter

From signature data Google source
  • insertRowsAfter(afterPosition: number, howMany: number): SpreadsheetApp.Sheet

insertRowsBefore

From signature data Google source
  • insertRowsBefore(beforePosition: number, howMany: number): SpreadsheetApp.Sheet

insertSlicer

From signature data Google source
  • insertSlicer(range: SpreadsheetApp.Range, anchorRowPos: number, anchorColPos: number): SpreadsheetApp.Slicer
  • insertSlicer(range: SpreadsheetApp.Range, anchorRowPos: number, anchorColPos: number, offsetX: number, offsetY: number): SpreadsheetApp.Slicer

isColumnHiddenByUser

From signature data Google source
  • isColumnHiddenByUser(columnPosition: number): boolean

isRightToLeft

From signature data Google source
  • isRightToLeft(): boolean

isRowHiddenByFilter

From signature data Google source
  • isRowHiddenByFilter(rowPosition: number): boolean

isRowHiddenByUser

From signature data Google source
  • isRowHiddenByUser(rowPosition: number): boolean

isSheetHidden

From signature data Google source
  • isSheetHidden(): boolean

moveColumns

From signature data Google source
  • moveColumns(columnSpec: SpreadsheetApp.Range, destinationIndex: number): void

moveRows

From signature data Google source
  • moveRows(rowSpec: SpreadsheetApp.Range, destinationIndex: number): void

newChart

From signature data Google source
  • newChart(): SpreadsheetApp.EmbeddedChartBuilder

protect

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

removeChart

From signature data Google source
  • removeChart(chart: SpreadsheetApp.EmbeddedChart): void

setActiveRange

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

setActiveRangeList

From signature data Google source
  • setActiveRangeList(rangeList: SpreadsheetApp.RangeList): SpreadsheetApp.RangeList

setActiveSelection

From signature data Google source
  • setActiveSelection(range: SpreadsheetApp.Range): SpreadsheetApp.Range
  • setActiveSelection(a1Notation: string): SpreadsheetApp.Range

setColumnGroupControlPosition

From signature data Google source
  • setColumnGroupControlPosition(position: SpreadsheetApp.GroupControlTogglePosition): SpreadsheetApp.Sheet

setColumnWidth

From signature data Google source
  • setColumnWidth(columnPosition: number, width: number): SpreadsheetApp.Sheet

setColumnWidths

From signature data Google source
  • setColumnWidths(startColumn: number, numColumns: number, width: number): SpreadsheetApp.Sheet

setConditionalFormatRules

From signature data Google source
  • setConditionalFormatRules(rules: SpreadsheetApp.ConditionalFormatRule[]): void

setCurrentCell

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

setFrozenColumns

From signature data Google source
  • setFrozenColumns(columns: number): void

setFrozenRows

From signature data Google source
  • setFrozenRows(rows: number): void

setHiddenGridlines

From signature data Google source
  • setHiddenGridlines(hideGridlines: boolean): SpreadsheetApp.Sheet

setName

From signature data Google source
  • setName(name: string): SpreadsheetApp.Sheet

setRightToLeft

From signature data Google source
  • setRightToLeft(rightToLeft: boolean): SpreadsheetApp.Sheet

setRowGroupControlPosition

From signature data Google source
  • setRowGroupControlPosition(position: SpreadsheetApp.GroupControlTogglePosition): SpreadsheetApp.Sheet

setRowHeight

From signature data Google source
  • setRowHeight(rowPosition: number, height: number): SpreadsheetApp.Sheet

setRowHeights

From signature data Google source
  • setRowHeights(startRow: number, numRows: number, height: number): SpreadsheetApp.Sheet

setRowHeightsForced

From signature data Google source
  • setRowHeightsForced(startRow: number, numRows: number, height: number): SpreadsheetApp.Sheet

setSheetProtection

From signature data Google source
  • setSheetProtection(permissions: SpreadsheetApp.PageProtection): void

setTabColor

From signature data Google source
  • setTabColor(color: string | null): SpreadsheetApp.Sheet

setTabColorObject

From signature data Google source
  • setTabColorObject(color: SpreadsheetApp.Color): SpreadsheetApp.Sheet

showColumns

From signature data Google source
  • showColumns(columnIndex: number): void
  • showColumns(columnIndex: number, numColumns: number): void

showRows

From signature data Google source
  • showRows(rowIndex: number): void
  • showRows(rowIndex: number, numRows: number): void

showSheet

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

sort

From signature data Google source
  • sort(columnPosition: number): SpreadsheetApp.Sheet
  • sort(columnPosition: number, ascending: boolean): SpreadsheetApp.Sheet

unhideColumn

From signature data Google source
  • unhideColumn(column: SpreadsheetApp.Range): void

unhideRow

From signature data Google source
  • unhideRow(row: SpreadsheetApp.Range): void

updateChart

From signature data Google source
  • updateChart(chart: SpreadsheetApp.EmbeddedChart): void

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