Class Range

Range

Access and modify spreadsheet ranges. A range can be a single cell in a sheet or a group of adjacent cells in a sheet.

Methods

MethodReturn typeBrief description
activate()RangeSets the specified range as the active range, with the top left cell in the range as the current cell.
activateAsCurrentCell()RangeSets the specified cell as the current cell.
addDeveloperMetadata(key)RangeAdds developer metadata with the specified key to the range.
addDeveloperMetadata(key, visibility)RangeAdds developer metadata with the specified key and visibility to the range.
addDeveloperMetadata(key, value)RangeAdds developer metadata with the specified key and value to the range.
addDeveloperMetadata(key, value, visibility)RangeAdds developer metadata with the specified key, value, and visibility to the range.
applyColumnBanding()BandingApplies a default column banding theme to the range.
applyColumnBanding(bandingTheme)BandingApplies a specified column banding theme to the range.
applyColumnBanding(bandingTheme, showHeader, showFooter)BandingApplies a specified column banding theme to the range with specified header and footer settings.
applyRowBanding()BandingApplies a default row banding theme to the range.
applyRowBanding(bandingTheme)BandingApplies a specified row banding theme to the range.
applyRowBanding(bandingTheme, showHeader, showFooter)BandingApplies a specified row banding theme to the range with specified header and footer settings.
autoFill(destination, series)voidFills the destinationRange with data based on the data in this range.
autoFillToNeighbor(series)voidCalculates a range to fill with new data based on neighboring cells and automatically fills that range with new values based on the data contained in this range.
breakApart()RangeBreak any multi-column cells in the range into individual cells again.
canEdit()BooleanDetermines whether the user has permission to edit every cell in the range.
check()RangeChanges the state of the checkboxes in the range to “checked”.
clear()RangeClears the range of contents and formats.
clear(options)RangeClears the range of contents, format, data validation rules, and/or comments, as specified with the given advanced options.
clearContent()RangeClears the content of the range, leaving the formatting intact.
clearDataValidations()RangeClears the data validation rules for the range.
clearFormat()RangeClears formatting for this range.
clearNote()RangeClears the note in the given cell or cells.
collapseGroups()RangeCollapses all groups that are wholly contained within the range.
copyFormatToRange(gridId, column, columnEnd, row, rowEnd)voidCopy the formatting of the range to the given location.
copyFormatToRange(sheet, column, columnEnd, row, rowEnd)voidCopy the formatting of the range to the given location.
copyTo(destination)voidCopies the data from a range of cells to another range of cells.
copyTo(destination, copyPasteType, transposed)voidCopies the data from a range of cells to another range of cells.
copyTo(destination, options)voidCopies the data from a range of cells to another range of cells.
copyValuesToRange(gridId, column, columnEnd, row, rowEnd)voidCopy the content of the range to the given location.
copyValuesToRange(sheet, column, columnEnd, row, rowEnd)voidCopy the content of the range to the given location.
createDataSourcePivotTable(dataSource)DataSourcePivotTableCreates an empty data source pivot table from the data source, anchored at the first cell in this range.
createDataSourceTable(dataSource)DataSourceTableCreates an empty data source table from the data source, anchored at the first cell in this range.
createDeveloperMetadataFinder()DeveloperMetadataFinderReturns a DeveloperMetadataFinderApi for finding developer metadata within the scope of this range.
createFilter()FilterCreates a filter and applies it to the specified range on the sheet.
createPivotTable(sourceData)PivotTableCreates an empty pivot table from the specified sourceData anchored at the first cell in this range.
createTextFinder(findText)TextFinderCreates a text finder for the range, which can find and replace text in this range.
deleteCells(shiftDimension)voidDeletes this range of cells.
expandGroups()RangeExpands the collapsed groups whose range or control toggle intersects with this range.
getA1Notation()StringReturns a string description of the range, in A1 notation.
getBackground()StringReturns the background color of the top-left cell in the range (for example, '#ffffff').
getBackgroundObject()ColorReturns the background color of the top-left cell in the range.
getBackgroundObjects()Color[][]Returns the background colors of the cells in the range.
getBackgrounds()String[][]Returns the background colors of the cells in the range (for example, '#ffffff').
getBandings()Banding[]Returns all the bandings that are applied to any cells in this range.
getCell(row, column)RangeReturns a given cell within a range.
getColumn()IntegerReturns the starting column position for this range.
getDataRegion()RangeReturns a copy of the range expanded in the four cardinal Directions to cover all adjacent cells with data in them.
getDataRegion(dimension)RangeReturns a copy of the range expanded Direction.UP and Direction.DOWN if the specified dimension is Dimension.ROWS, or Direction.NEXT and Direction.PREVIOUS if the dimension is Dimension.COLUMNS.
getDataSourceFormula()DataSourceFormulaReturns the DataSourceFormula for the first cell in the range, or null if the cell doesn't contain a data source formula.
getDataSourceFormulas()DataSourceFormula[]Returns the DataSourceFormulas for the cells in the range.
getDataSourcePivotTables()DataSourcePivotTable[]Gets all the data source pivot tables intersecting with the range.
getDataSourceTables()DataSourceTable[]Gets all the data source tables intersecting with the range.
getDataSourceUrl()StringReturns a URL for the data in this range, which can be used to create charts and queries.
getDataTable()DataTableReturn the data inside this object as a DataTable.
getDataTable(firstRowIsHeader)DataTableReturn the data inside this range as a DataTable.
getDataValidation()DataValidationReturns the data validation rule for the top-left cell in the range.
getDataValidations()DataValidation[][]Returns the data validation rules for all cells in the range.
getDeveloperMetadata()DeveloperMetadata[]Gets the developer metadata associated with this range.
getDisplayValue()StringReturns the displayed value of the top-left cell in the range.
getDisplayValues()String[][]Returns the rectangular grid of values for this range.
getFilter()FilterReturns the filter on the sheet this range belongs to, or null if there is no filter on the sheet.
getFontColorObject()ColorReturns the font color of the cell in the top-left corner of the range.
getFontColorObjects()Color[][]Returns the font colors of the cells in the range.
getFontFamilies()String[][]Returns the font families of the cells in the range.
getFontFamily()StringReturns the font family of the cell in the top-left corner of the range.
getFontLine()StringGets the line style of the cell in the top-left corner of the range ('underline', 'line-through', or 'none').
getFontLines()String[][]Gets the line style of the cells in the range ('underline', 'line-through', or 'none').
getFontSize()IntegerReturns the font size in point size of the cell in the top-left corner of the range.
getFontSizes()Integer[][]Returns the font sizes of the cells in the range.
getFontStyle()StringReturns the font style ('italic' or 'normal') of the cell in the top-left corner of the range.
getFontStyles()String[][]Returns the font styles of the cells in the range.
getFontWeight()StringReturns the font weight (normal/bold) of the cell in the top-left corner of the range.
getFontWeights()String[][]Returns the font weights of the cells in the range.
getFormula()StringReturns the formula (A1 notation) for the top-left cell of the range, or an empty string if the cell is empty or doesn't contain a formula.
getFormulaR1C1()StringReturns the formula (R1C1 notation) for a given cell, or null if none.
getFormulas()String[][]Returns the formulas (A1 notation) for the cells in the range.
getFormulasR1C1()String[][]Returns the formulas (R1C1 notation) for the cells in the range.
getGridId()IntegerReturns the grid ID of the range's parent sheet.
getHeight()IntegerReturns the height of the range.
getHorizontalAlignment()StringReturns the horizontal alignment of the text (left/center/right) of the cell in the top-left corner of the range.
getHorizontalAlignments()String[][]Returns the horizontal alignments of the cells in the range.
getLastColumn()IntegerReturns the end column position.
getLastRow()IntegerReturns the end row position.
getMergedRanges()Range[]Returns an array of Range objects representing merged cells that either are fully within the current range, or contain at least one cell in the current range.
getNextDataCell(direction)RangeStarting at the cell in the first column and row of the range, returns the next cell in the given direction that is the edge of a contiguous range of cells with data in them or the cell at the edge of the spreadsheet in that direction.
getNote()StringReturns the note associated with the given range.
getNotes()String[][]Returns the notes associated with the cells in the range.
getNumColumns()IntegerReturns the number of columns in this range.
getNumRows()IntegerReturns the number of rows in this range.
getNumberFormat()StringGet the number or date formatting of the top-left cell of the given range.
getNumberFormats()String[][]Returns the number or date formats for the cells in the range.
getRichTextValue()RichTextValueReturns the Rich Text value for the top left cell of the range, or null if the cell value is not text.
getRichTextValues()RichTextValue[][]Returns the Rich Text values for the cells in the range.
getRow()IntegerReturns the row position for this range.
getRowIndex()IntegerReturns the row position for this range.
getSheet()SheetReturns the sheet this range belongs to.
getTextDirection()TextDirectionReturns the text direction for the top left cell of the range.
getTextDirections()TextDirection[][]Returns the text directions for the cells in the range.
getTextRotation()TextRotationReturns the text rotation settings for the top left cell of the range.
getTextRotations()TextRotation[][]Returns the text rotation settings for the cells in the range.
getTextStyle()TextStyleReturns the text style for the top left cell of the range.
getTextStyles()TextStyle[][]Returns the text styles for the cells in the range.
getValue()ObjectReturns the value of the top-left cell in the range.
getValues()Object[][]Returns the rectangular grid of values for this range.
getVerticalAlignment()StringReturns the vertical alignment (top/middle/bottom) of the cell in the top-left corner of the range.
getVerticalAlignments()String[][]Returns the vertical alignments of the cells in the range.
getWidth()IntegerReturns the width of the range in columns.
getWrap()BooleanReturns whether the text in the cell wraps.
getWrapStrategies()WrapStrategy[][]Returns the text wrapping strategies for the cells in the range.
getWrapStrategy()WrapStrategyReturns the text wrapping strategy for the top left cell of the range.
getWraps()Boolean[][]Returns whether the text in the cells wrap.
insertCells(shiftDimension)RangeInserts empty cells into this range.
insertCheckboxes()RangeInserts checkboxes into each cell in the range, configured with true for checked and false for unchecked.
insertCheckboxes(checkedValue)RangeInserts checkboxes into each cell in the range, configured with a custom value for checked and the empty string for unchecked.
insertCheckboxes(checkedValue, uncheckedValue)RangeInserts checkboxes into each cell in the range, configured with custom values for the checked and unchecked states.
isBlank()BooleanReturns true if the range is totally blank.
isChecked()BooleanReturns whether all cells in the range have their checkbox state as 'checked'.
isEndColumnBounded()BooleanDetermines whether the end of the range is bound to a particular column.
isEndRowBounded()BooleanDetermines whether the end of the range is bound to a particular row.
isPartOfMerge()BooleanReturns true if the cells in the current range overlap any merged cells.
isStartColumnBounded()BooleanDetermines whether the start of the range is bound to a particular column.
isStartRowBounded()BooleanDetermines whether the start of the range is bound to a particular row.
merge()RangeMerges the cells in the range together into a single block.
mergeAcross()RangeMerge the cells in the range across the columns of the range.
mergeVertically()RangeMerges the cells in the range together.
moveTo(target)voidCut and paste (both format and values) from this range to the target range.
offset(rowOffset, columnOffset)RangeReturns a new range that is offset from this range by the given number of rows and columns (which can be negative).
offset(rowOffset, columnOffset, numRows)RangeReturns a new range that is relative to the current range, whose upper left point is offset from the current range by the given rows and columns, and with the given height in cells.
offset(rowOffset, columnOffset, numRows, numColumns)RangeReturns a new range that is relative to the current range, whose upper left point is offset from the current range by the given rows and columns, and with the given height and width in cells.
protect()ProtectionCreates an object that can protect the range from being edited except by users who have permission.
randomize()RangeRandomizes the order of the rows in the given range.
removeCheckboxes()RangeRemoves all checkboxes from the range.
removeDuplicates()RangeRemoves rows within this range that contain values that are duplicates of values in any previous row.
removeDuplicates(columnsToCompare)RangeRemoves rows within this range that contain values in the specified columns that are duplicates of values any previous row.
setBackground(color)RangeSets the background color of all cells in the range in CSS notation (such as '#ffffff' or 'white').
setBackgroundObject(color)RangeSets the background color of all cells in the range.
setBackgroundObjects(color)RangeSets a rectangular grid of background colors (must match dimensions of this range).
setBackgroundRGB(red, green, blue)RangeSets the background to the given color using RGB values (integers between 0 and 255 inclusive).
setBackgrounds(color)RangeSets a rectangular grid of background colors (must match dimensions of this range).
setBorder(top, left, bottom, right, vertical, horizontal)RangeSets the border property.
setBorder(top, left, bottom, right, vertical, horizontal, color, style)RangeSets the border property with color and/or style.
setDataValidation(rule)RangeSets one data validation rule for all cells in the range.
setDataValidations(rules)RangeSets the data validation rules for all cells in the range.
setFontColor(color)RangeSets the font color in CSS notation (such as '#ffffff' or 'white').
setFontColorObject(color)RangeSets the font color of the given range.
setFontColorObjects(colors)RangeSets a rectangular grid of font colors (must match dimensions of this range).
setFontColors(colors)RangeSets a rectangular grid of font colors (must match dimensions of this range).
setFontFamilies(fontFamilies)RangeSets a rectangular grid of font families (must match dimensions of this range).
setFontFamily(fontFamily)RangeSets the font family, such as "Arial" or "Helvetica".
setFontLine(fontLine)RangeSets the font line style of the given range ('underline', 'line-through', or 'none').
setFontLines(fontLines)RangeSets a rectangular grid of line styles (must match dimensions of this range).
setFontSize(size)RangeSets the font size, with the size being the point size to use.
setFontSizes(sizes)RangeSets a rectangular grid of font sizes (must match dimensions of this range).
setFontStyle(fontStyle)RangeSet the font style for the given range ('italic' or 'normal').
setFontStyles(fontStyles)RangeSets a rectangular grid of font styles (must match dimensions of this range).
setFontWeight(fontWeight)RangeSet the font weight for the given range (normal/bold).
setFontWeights(fontWeights)RangeSets a rectangular grid of font weights (must match dimensions of this range).
setFormula(formula)RangeUpdates the formula for this range.
setFormulaR1C1(formula)RangeUpdates the formula for this range.
setFormulas(formulas)RangeSets a rectangular grid of formulas (must match dimensions of this range).
setFormulasR1C1(formulas)RangeSets a rectangular grid of formulas (must match dimensions of this range).
setHorizontalAlignment(alignment)RangeSet the horizontal (left to right) alignment for the given range (left/center/right).
setHorizontalAlignments(alignments)RangeSets a rectangular grid of horizontal alignments.
setNote(note)RangeSets the note to the given value.
setNotes(notes)RangeSets a rectangular grid of notes (must match dimensions of this range).
setNumberFormat(numberFormat)RangeSets the number or date format to the given formatting string.
setNumberFormats(numberFormats)RangeSets a rectangular grid of number or date formats (must match dimensions of this range).
setRichTextValue(value)RangeSets the Rich Text value for the cells in the range.
setRichTextValues(values)RangeSets a rectangular grid of Rich Text values.
setShowHyperlink(showHyperlink)RangeSets whether or not the range should show hyperlinks.
setTextDirection(direction)RangeSets the text direction for the cells in the range.
setTextDirections(directions)RangeSets a rectangular grid of text directions.
setTextRotation(degrees)RangeSets the text rotation settings for the cells in the range.
setTextRotation(rotation)RangeSets the text rotation settings for the cells in the range.
setTextRotations(rotations)RangeSets a rectangular grid of text rotations.
setTextStyle(style)RangeSets the text style for the cells in the range.
setTextStyles(styles)RangeSets a rectangular grid of text styles.
setValue(value)RangeSets the value of the range.
setValues(values)RangeSets a rectangular grid of values (must match dimensions of this range).
setVerticalAlignment(alignment)RangeSet the vertical (top to bottom) alignment for the given range (top/middle/bottom).
setVerticalAlignments(alignments)RangeSets a rectangular grid of vertical alignments (must match dimensions of this range).
setVerticalText(isVertical)RangeSets whether or not to stack the text for the cells in the range.
setWrap(isWrapEnabled)RangeSet the cell wrap of the given range.
setWrapStrategies(strategies)RangeSets a rectangular grid of wrap strategies.
setWrapStrategy(strategy)RangeSets the text wrapping strategy for the cells in the range.
setWraps(isWrapEnabled)RangeSets a rectangular grid of word wrap policies (must match dimensions of this range).
shiftColumnGroupDepth(delta)RangeChanges the column grouping depth of the range by the specified amount.
shiftRowGroupDepth(delta)RangeChanges the row grouping depth of the range by the specified amount.
sort(sortSpecObj)RangeSorts the cells in the given range, by column and order specified.
splitTextToColumns()voidSplits a column of text into multiple columns based on an auto-detected delimiter.
splitTextToColumns(delimiter)voidSplits a column of text into multiple columns using the specified string as a custom delimiter.
splitTextToColumns(delimiter)voidSplits a column of text into multiple columns based on the specified delimiter.
trimWhitespace()RangeTrims the whitespace (such as spaces, tabs, or new lines) in every cell in this range.
uncheck()RangeChanges the state of the checkboxes in the range to “unchecked”.

Detailed documentation

activate()

Sets the specified range as the active range, with the top left cell in the range as the current cell.

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
const range = sheet.getRange('A1:D10');
range.activate();

const selection = sheet.getSelection();
// Current cell: A1
const currentCell = selection.getCurrentCell();
// Active Range: A1:D10
const activeRange = selection.getActiveRange();

Return

Range — This range, for chaining.


activateAsCurrentCell()

Sets the specified cell as the current cell.

If the specified cell is present in an existing range, then that range becomes the active range with the cell as the current cell.

If the specified cell is not present in any existing range, then the existing selection is removed and the cell becomes the current cell and the active range.

Note: The specified Range must consist of one cell, otherwise it throws an exception.

// Gets the first sheet of the spreadsheet.
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];

// Gets the cell B5 and sets it as the active cell.
const range = sheet.getRange('B5');
const currentCell = range.activateAsCurrentCell();

// Logs the activated cell.
console.log(currentCell.getA1Notation());

Return

Range — This range, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

addDeveloperMetadata(key)

Adds developer metadata with the specified key to the range.

// Opens the spreadsheet file by its URL. If you created your script from within
// a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets row 2 on the sheet.
const range = sheet.getRange('2:2');

// Adds the key 'NAME' to the developer metadata for row 2.
range.addDeveloperMetadata('NAME');

// Gets the metadata and logs it to the console.
const developerMetaData = range.getDeveloperMetadata()[0];
console.log(developerMetaData.getKey());

Parameters

NameTypeDescription
keyStringThe key for the new developer metadata.

Return

Range — This range, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

addDeveloperMetadata(key, visibility)

Adds developer metadata with the specified key and visibility to the range.

// Opens the spreadsheet file by its URL. If you created your script from within
// a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets row 2 on Sheet1.
const range = sheet.getRange('2:2');

// Adds the key 'NAME' and sets the developer metadata visibility to 'DOCUMENT'
// for row 2 on Sheet1.
range.addDeveloperMetadata(
    'NAME',
    SpreadsheetApp.DeveloperMetadataVisibility.DOCUMENT,
);

// Gets the updated metadata info and logs it to the console.
const developerMetaData = range.getDeveloperMetadata()[0];
console.log(developerMetaData.getKey());
console.log(developerMetaData.getVisibility().toString());

Parameters

NameTypeDescription
keyStringThe key for the new developer metadata.
visibilityDeveloperMetadataVisibilityThe visibility of the new developer metadata.

Return

Range — This range, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

addDeveloperMetadata(key, value)

Adds developer metadata with the specified key and value to the range.

// Opens the spreadsheet file by its URL. If you created your script from within
// a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets row 2 of Sheet1.
const range = sheet.getRange('2:2');

// Adds the key 'NAME' and sets the value to 'GOOGLE' for the metadata of row 2.
range.addDeveloperMetadata('NAME', 'GOOGLE');

// Gets the metadata and logs it to the console.
const developerMetaData = range.getDeveloperMetadata()[0];
console.log(developerMetaData.getKey());
console.log(developerMetaData.getValue());

Parameters

NameTypeDescription
keyStringThe key for the new developer metadata.
valueStringThe value for the new developer metadata.

Return

Range — This range, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

addDeveloperMetadata(key, value, visibility)

Adds developer metadata with the specified key, value, and visibility to the range.

// Opens the spreadsheet file by its URL. If you created your script from within
// a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets row 2 on the sheet.
const range = sheet.getRange('2:2');

// Adds the key 'NAME', sets the value to 'GOOGLE', and sets the visibility
// to PROJECT for row 2 on the sheet.
range.addDeveloperMetadata(
    'NAME',
    'GOOGLE',
    SpreadsheetApp.DeveloperMetadataVisibility.PROJECT,
);

// Gets the updated metadata info and logs it to the console.
const developerMetaData = range.getDeveloperMetadata()[0];
console.log(developerMetaData.getKey());
console.log(developerMetaData.getValue());
console.log(developerMetaData.getVisibility().toString());

Parameters

NameTypeDescription
keyStringThe key for the new developer metadata.
valueStringThe value for the new developer metadata.
visibilityDeveloperMetadataVisibilityThe visibility of the new developer metadata.

Return

Range — This range, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

applyColumnBanding()

Applies a default column banding theme to the range. By default, the banding has header and no footer color.

// Opens the spreadsheet file by its URL. If you created your script from within
// a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets row 2 on the sheet.
const range = sheet.getRange('2:2');

// Applies column banding to row 2.
const colBanding = range.applyColumnBanding();

// Gets the first banding on the sheet and logs the color of the header column.
console.log(
    sheet.getBandings()[0]
        .getHeaderColumnColorObject()
        .asRgbColor()
        .asHexString(),
);

// Gets the first banding on the sheet and logs the color of the second column.
console.log(
    sheet.getBandings()[0]
        .getSecondColumnColorObject()
        .asRgbColor()
        .asHexString(),
);

Return

Banding — The new banding.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

applyColumnBanding(bandingTheme)

Applies a specified column banding theme to the range. By default, the banding has header and no footer color.

// Opens the spreadsheet file by its URL. If you created your script from within
// a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets row 2 on the sheet.
const range = sheet.getRange('2:2');

// Applies the INDIGO color banding theme to the columns in row 2.
const colBanding = range.applyColumnBanding(SpreadsheetApp.BandingTheme.INDIGO);

// Gets the first banding on the sheet and logs the color of the second column.
console.log(
    sheet.getBandings()[0]
        .getSecondColumnColorObject()
        .asRgbColor()
        .asHexString(),
);

Parameters

NameTypeDescription
bandingThemeBandingThemeA color theme to apply to the columns in the range.

Return

Banding — The new banding.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

applyColumnBanding(bandingTheme, showHeader, showFooter)

Applies a specified column banding theme to the range with specified header and footer settings.

// Opens the spreadsheet file by its URL. If you created your script from within
// a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets rows 12-22 on the sheet.
const range = sheet.getRange('12:22');

// Applies the BLUE color banding theme to rows 12-22.
// Sets the header visibility to false and the footer visibility to true.
const colBanding = range.applyColumnBanding(
    SpreadsheetApp.BandingTheme.BLUE,
    false,
    true,
);

// Gets the banding color and logs it to the console.
console.log(
    sheet.getBandings()[0]
        .getSecondColumnColorObject()
        .asRgbColor()
        .asHexString(),
);

// Gets the header color object and logs it to the console. Returns null because
// the header visibility is set to false.
console.log(sheet.getBandings()[0].getHeaderColumnColorObject());

// Gets the footer color and logs it to the console.
console.log(
    sheet.getBandings()[0]
        .getFooterColumnColorObject()
        .asRgbColor()
        .asHexString(),
);

Parameters

NameTypeDescription
bandingThemeBandingThemeA color theme to apply to the columns in the range.
showHeaderBooleanIf true, the banding theme header color is applied to the first column.
showFooterBooleanIf true, the banding theme footer color is applied to the last column.

Return

Banding — The new banding.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

applyRowBanding()

Applies a default row banding theme to the range. By default, the banding has header and no footer color.

// Opens the spreadsheet by its URL. If you created your script from within a
// Google Sheets spreadsheet, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets rows 1-30 on Sheet1.
const range = sheet.getRange('1:30');

// Applies row banding to rows 1-30.
range.applyRowBanding();

// Gets the hex color of the second banded row.
const secondRowColor =
    range.getBandings()[0].getSecondRowColorObject().asRgbColor().asHexString();

// Logs the hex color to console.
console.log(secondRowColor);

Return

Banding — The banding.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

applyRowBanding(bandingTheme)

Applies a specified row banding theme to the range. By default, the banding has header and no footer color.

// Opens the spreadsheet by its URL. If you created your script from within a
// Google Sheets spreadsheet, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets rows 1-30 on Sheet1.
const range = sheet.getRange('1:30');

// Applies the INDIGO row banding theme to rows 1-30.
range.applyRowBanding(SpreadsheetApp.BandingTheme.INDIGO);

// Gets the hex color of the second banded row.
const secondRowColor =
    range.getBandings()[0].getSecondRowColorObject().asRgbColor().asHexString();

// Logs the hex color to console.
console.log(secondRowColor);

Parameters

NameTypeDescription
bandingThemeBandingThemeA color theme to apply to the rows in the range.

Return

Banding — The new banding.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

applyRowBanding(bandingTheme, showHeader, showFooter)

Applies a specified row banding theme to the range with specified header and footer settings.

// Opens the spreadsheet by its URL. If you created your script from within a
// Google Sheets spreadsheet, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets rows 1-30 on Sheet1.
const range = sheet.getRange('1:30');

// Applies the INDIGO row banding to rows 1-30 and
// specifies to hide the header and show the footer.
range.applyRowBanding(SpreadsheetApp.BandingTheme.INDIGO, false, true);

Parameters

NameTypeDescription
bandingThemeBandingThemeA color theme to apply to the rows in the range.
showHeaderBooleanIf true, the banding theme header color is applied to the first row.
showFooterBooleanIf true, the banding theme footer color is applied to the last row.

Return

Banding — The new banding.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

autoFill(destination, series)

Fills the destinationRange with data based on the data in this range. The new values are also determined by the specified series type. The destination range must contain this range and extend it in only one direction. For example, the following fills A1:A20 with a series of increasing numbers based on the current values in A1:A4:

const sheet = SpreadsheetApp.getActiveSheet();

// Has values [1, 2, 3, 4].
const sourceRange = sheet.getRange('A1:A4');
// The range to fill with values.
const destination = sheet.getRange('A1:A20');

// Inserts new values in A5:A20, continuing the pattern expressed in A1:A4
sourceRange.autoFill(destination, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

Parameters

NameTypeDescription
destinationRangeThe range to be auto-filled with values. The destination range should contain this range and extend it in only one direction (upwards, downwards, left, or right).
seriesAutoFillSeriesThe type of autoFill series that should be used to calculate new values. The effect of this series differs based on the type and amount of source data.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

autoFillToNeighbor(series)

Calculates a range to fill with new data based on neighboring cells and automatically fills that range with new values based on the data contained in this range. These new values are also determined by the specified series type.

The calculated destination range considers the surrounding data to determine where the new values should be inserted: if there is data to the immediate left or right of a column that is being auto-filled, new values only extend as far as this adjacent data.

For example, if A1:A20 is filled with a series of increasing numbers and this method is called on the range B1:B4 which contains a series of dates, new values are only inserted into B5:B20. In this way, these new values "stick" to the cells that contain values in column A.

const sheet = SpreadsheetApp.getActiveSheet();

// A1:A20 has values [1, 2, 3, ... 20].
// B1:B4 has values [1/1/2017, 1/2/2017, ...]
const sourceRange = sheet.getRange('B1:B4');

// Results in B5:B20 having values [1/5/2017, ... 1/20/2017]
sourceRange.autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

Parameters

NameTypeDescription
seriesAutoFillSeriesThe type of autoFill series that should be used to calculate new values. The effect of this series differs based on the type and amount of source data.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

breakApart()

Break any multi-column cells in the range into individual cells again.

Calling this function on a range is equivalent to selecting a range and clicking Format > Merge cells > Unmerge.

// Opens the spreadsheet by its URL. If you created your script from within a
// Google Sheets spreadsheet, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets the range A1:C6 on Sheet1.
const range = sheet.getRange('A1:C6');

// Unmerges the range A1:C6 into individual cells.
range.breakApart();

Return

Range — This range, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

canEdit()

Determines whether the user has permission to edit every cell in the range. The spreadsheet owner is always able to edit protected ranges and sheets.

// Opens the spreadsheet by its URL. If you created your script from within a
// Google Sheets spreadsheet, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets the range A1:C6 on Sheet1.
const range = sheet.getRange('A1:C6');

// Logs whether the user has permission to edit every cell in the range.
console.log(range.canEdit());

Return

Booleantrue if the user has permission to edit every cell in the range; false otherwise.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

check()

Changes the state of the checkboxes in the range to “checked”. Ignores the cells in the range which currently do not contain either the checked or unchecked value configured.

// Changes the state of cells which currently contain either the checked or
// unchecked value configured in the range A1:B10 to 'checked'.
const range = SpreadsheetApp.getActive().getRange('A1:B10');
range.check();

Return

Range — This range, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

clear()

Clears the range of contents and formats.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('A1:D10');
range.clear();

Return

Range — This range, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

clear(options)

Clears the range of contents, format, data validation rules, and/or comments, as specified with the given advanced options. By default all data is cleared.

// The code below clears range C2:G7 in the active sheet, but preserves the
// format, data validation rules, and comments.
SpreadsheetApp.getActiveSheet().getRange(2, 3, 6, 5).clear({
  contentsOnly: true
});

Parameters

NameTypeDescription
optionsObjectA JavaScript object that specifies advanced parameters, as listed below.

Advanced parameters

NameTypeDescription
commentsOnlyBooleanWhether to clear only the comments.
contentsOnlyBooleanWhether to clear only the contents.
formatOnlyBooleanWhether to clear only the format; note that clearing format also clears data validation rules.
validationsOnlyBooleanWhether to clear only data validation rules.
skipFilteredRowsBooleanWhether to avoid clearing filtered rows.

Return

Range — This range, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

clearContent()

Clears the content of the range, leaving the formatting intact.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('A1:D10');
range.clearContent();

Return

Range — This range, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

clearDataValidations()

Clears the data validation rules for the range.

// Clear the data validation rules for cells A1:B5.
const range = SpreadsheetApp.getActive().getRange('A1:B5');
range.clearDataValidations();

Return

Range — This range, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

clearFormat()

Clears formatting for this range.

This clears text formatting for the cell or cells in the range, but does not reset any number formatting rules.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('A1:D10');
range.clearFormat();

Return

Range — This range, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

clearNote()

Clears the note in the given cell or cells.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('A1:D10');
range.clearNote();

Return

Range — This range, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

collapseGroups()

Collapses all groups that are wholly contained within the range. If no group is fully within the range, the deepest expanded group that is partially within the range is collapsed.

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
const range = sheet.getActiveRange();

// All row and column groups within the range are collapsed.
range.collapseGroups();

Return

Range — This range, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

copyFormatToRange(gridId, column, columnEnd, row, rowEnd)

Copy the formatting of the range to the given location. If the destination is larger or smaller than the source range then the source is repeated or truncated accordingly. Note that this method copies the formatting only.

For a detailed description of the gridId parameter, see getGridId().

const ss = SpreadsheetApp.getActiveSpreadsheet();
const source = ss.getSheets()[0];

const range = source.getRange('B2:D4');

// This copies the formatting in B2:D4 in the source sheet to
// D4:F6 in the sheet with gridId 1555299895. Note that you can get the gridId
// of a sheet by calling sheet.getSheetId() or range.getGridId().
range.copyFormatToRange(1555299895, 4, 6, 4, 6);

Parameters

NameTypeDescription
gridIdIntegerThe unique ID of the sheet within the spreadsheet, irrespective of position.
columnIntegerThe first column of the target range.
columnEndIntegerThe end column of the target range.
rowIntegerThe start row of the target range.
rowEndIntegerThe end row of the target range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

See also


copyFormatToRange(sheet, column, columnEnd, row, rowEnd)

Copy the formatting of the range to the given location. If the destination is larger or smaller than the source range then the source is repeated or truncated accordingly. Note that this method copies the formatting only.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const source = ss.getSheets()[0];
const destination = ss.getSheets()[1];

const range = source.getRange('B2:D4');

// This copies the formatting in B2:D4 in the source sheet to
// D4:F6 in the second sheet
range.copyFormatToRange(destination, 4, 6, 4, 6);

Parameters

NameTypeDescription
sheetSheetThe target sheet.
columnIntegerThe first column of the target range.
columnEndIntegerThe end column of the target range.
rowIntegerThe start row of the target range.
rowEndIntegerThe end row of the target range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

copyTo(destination)

Copies the data from a range of cells to another range of cells. Both the values and formatting are copied.

// The code below copies the first 5 columns over to the 6th column.
const sheet = SpreadsheetApp.getActiveSheet();
const rangeToCopy = sheet.getRange(1, 1, sheet.getMaxRows(), 5);
rangeToCopy.copyTo(sheet.getRange(1, 6));

Parameters

NameTypeDescription
destinationRangeA destination range to copy to; only the top-left cell position is relevant.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

copyTo(destination, copyPasteType, transposed)

Copies the data from a range of cells to another range of cells.

// The code below copies only the values of the first 5 columns over to the 6th
// column.
const sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange('A:E').copyTo(
    sheet.getRange('F1'),
    SpreadsheetApp.CopyPasteType.PASTE_VALUES,
    false,
);

Parameters

NameTypeDescription
destinationRangeA destination range to copy to; only the top-left cell position is relevant.
copyPasteTypeCopyPasteTypeA type that specifies how the range contents are pasted to the destination.
transposedBooleanWhether the range should be pasted in its transposed orientation.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

copyTo(destination, options)

Copies the data from a range of cells to another range of cells. By default both the values and formatting are copied, but this can be overridden using advanced arguments.

// The code below copies only the values of the first 5 columns over to the 6th
// column.
const sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange('A:E').copyTo(sheet.getRange('F1'), {contentsOnly: true});

Parameters

NameTypeDescription
destinationRangeA destination range to copy to; only the top-left cell position is relevant.
optionsObjectA JavaScript object that specifies advanced parameters, as listed below.

Advanced parameters

NameTypeDescription
formatOnlyBooleandesignates that only the format should be copied
contentsOnlyBooleandesignates that only the content should be copied

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

copyValuesToRange(gridId, column, columnEnd, row, rowEnd)

Copy the content of the range to the given location. If the destination is larger or smaller than the source range then the source is repeated or truncated accordingly.

For a detailed description of the gridId parameter, see getGridId().

const ss = SpreadsheetApp.getActiveSpreadsheet();
const source = ss.getSheets()[0];

const range = source.getRange('B2:D4');

// This copies the data in B2:D4 in the source sheet to
// D4:F6 in the sheet with gridId 0
range.copyValuesToRange(0, 4, 6, 4, 6);

Parameters

NameTypeDescription
gridIdIntegerThe unique ID of the sheet within the spreadsheet, irrespective of position.
columnIntegerThe first column of the target range.
columnEndIntegerThe end column of the target range.
rowIntegerThe start row of the target range.
rowEndIntegerThe end row of the target range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

See also


copyValuesToRange(sheet, column, columnEnd, row, rowEnd)

Copy the content of the range to the given location. If the destination is larger or smaller than the source range then the source is repeated or truncated accordingly.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const source = ss.getSheets()[0];
const destination = ss.getSheets()[1];

const range = source.getRange('B2:D4');

// This copies the data in B2:D4 in the source sheet to
// D4:F6 in the second sheet
range.copyValuesToRange(destination, 4, 6, 4, 6);

Parameters

NameTypeDescription
sheetSheetThe target sheet.
columnIntegerThe first column of the target range.
columnEndIntegerThe end column of the target range.
rowIntegerThe start row of the target range.
rowEndIntegerThe end row of the target range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

createDataSourcePivotTable(dataSource)

Creates an empty data source pivot table from the data source, anchored at the first cell in this range.

This example shows how to create and configure a new data source pivot table.

const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const anchorCell = spreadsheet.getSheets()[0].getRange('A1');
const dataSource = spreadsheet.getDataSources()[0];

const pivotTable = anchorCell.createDataSourcePivotTable(dataSource);
pivotTable.addRowGroup('dataColumnA');
pivotTable.addColumnGroup('dataColumnB');
pivotTable.addPivotValue(
    'dataColumnC',
    SpreadsheetApp.PivotTableSummarizeFunction.SUM,
);
pivotTable.addFilter(
    'dataColumnA',
    SpreadsheetApp.newFilterCriteria().whenTextStartsWith('A').build(),
);

Parameters

NameTypeDescription
dataSourceDataSourceThe data source to create the pivot table from.

Return

DataSourcePivotTable — The newly created data source pivot table.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

createDataSourceTable(dataSource)

Creates an empty data source table from the data source, anchored at the first cell in this range.

This example shows how to create and configure a new data source table.

const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const anchorCell = spreadsheet.getSheets()[0].getRange('A1');
const dataSource = spreadsheet.getDataSources()[0];

const dataSourceTable =
    anchorCell.createDataSourceTable(dataSource)
        .addColumns('dataColumnA', 'dataColumnB', 'dataColumnC')
        .addSortSpec('dataColumnA', true)    // ascending=true
        .addSortSpec('dataColumnB', false);  // ascending=false

Parameters

NameTypeDescription
dataSourceDataSourceThe data source to create the pivot table from.

Return

DataSourceTable — The newly created data source table.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

createDeveloperMetadataFinder()

Returns a DeveloperMetadataFinderApi for finding developer metadata within the scope of this range. Metadata is within the scope of the range only if it is wholly contained within that range. For example, metadata associated with the row ‘3:3’ is not in the scope of a range ‘A1:D5’ but is within the scope of a range ‘1:5’.

// Opens the spreadsheet file by its URL. If you created your script from within
// a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets the range A1:C6.
const range = sheet.getRange('A1:C6');

// Creates a developer metadata finder to search for metadata in the scope of
// this range.
const developerMetaDataFinder = range.createDeveloperMetadataFinder();

// Logs information about the developer metadata finder to the console.
const developerMetaData = developerMetaDataFinder.find()[0];
console.log(developerMetaData.getKey());
console.log(developerMetaData.getValue());
console.log(developerMetaData.getVisibility().toString());

Return

DeveloperMetadataFinder — A developer metadata finder to search for metadata in the scope of this range.


createFilter()

Creates a filter and applies it to the specified range on the sheet. You can't create more than one filter on a sheet. To access and modify your filter after you create it, use getFilter() or Sheet.getFilter().

const ss = SpreadsheetApp.getActiveSheet();
const range = ss.getRange('A1:C20');

// Creates a new filter and applies it to the range A1:C20 on the active sheet.
function createFilter() {
  range.createFilter();
}
// Gets the filter and applies criteria that only shows cells that aren't empty.
function getFilterAddCriteria() {
  const filter = range.getFilter();
  const criteria =
      SpreadsheetApp.newFilterCriteria().whenCellNotEmpty().build();
  filter.setColumnFilterCriteria(2, criteria);
}
Use this method to create filters for Grid sheets, the default type of sheet. Grid sheets are sheets that aren't connected to a database. To create other types of filters, refer to the following:

Return

Filter — The new filter.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

createPivotTable(sourceData)

Creates an empty pivot table from the specified sourceData anchored at the first cell in this range.

// Opens the spreadsheet file by its URL. If you created your script from within
// a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets cell A1 as a range in order to place the pivot table.
const range = sheet.getRange('A1');

// Gets the range of the source data for the pivot table.
const dataRange = sheet.getRange('E12:G20');

// Creates an empty pivot table from the specified source data.
const pivotTable = range.createPivotTable(dataRange);

// Logs the values from the pivot table's source data to the console.
console.log(pivotTable.getSourceDataRange().getValues());

Parameters

NameTypeDescription
sourceDataRangeThe data to create the pivot table from.

Return

PivotTable — The newly created PivotTable.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

createTextFinder(findText)

Creates a text finder for the range, which can find and replace text in this range.

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
const range = sheet.getActiveRange();

// Creates  a text finder for the range.
const textFinder = range.createTextFinder('dog');

// Returns the first occurrence of 'dog'.
const firstOccurrence = textFinder.findNext();

// Replaces the last found occurrence of 'dog' with 'cat' and returns the number
// of occurrences replaced.
const numOccurrencesReplaced = textFinder.replaceWith('cat');

Parameters

NameTypeDescription
findTextStringThe text to search for.

Return

TextFinder — The TextFinder for the range


deleteCells(shiftDimension)

Deletes this range of cells. Existing data in the sheet along the provided dimension is shifted towards the deleted range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('A1:D10');
range.deleteCells(SpreadsheetApp.Dimension.COLUMNS);

Parameters

NameTypeDescription
shiftDimensionDimensionThe dimension along which to shift existing data.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

expandGroups()

Expands the collapsed groups whose range or control toggle intersects with this range. The control toggle location is the index at which the control toggle is shown, directly before or after the group depending on settings. If there is more than one group at the same location, the shallowest group is expanded.

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
const range = sheet.getActiveRange();

// All row and column groups within the range are expanded.
range.expandGroups();

Return

Range — This range, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getA1Notation()

Returns a string description of the range, in A1 notation.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange(1, 1, 2, 5);

// Logs "A1:E2"
Logger.log(range.getA1Notation());

Return

String — The string description of the range in A1 notation.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getBackground()

Returns the background color of the top-left cell in the range (for example, '#ffffff').

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const cell = sheet.getRange('B5');
Logger.log(cell.getBackground());

Return

String — The color code of the background.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getBackgroundObject()

Returns the background color of the top-left cell in the range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const cell = sheet.getRange('B5');
Logger.log(cell.getBackgroundObject().asRgbColor().asHexString());

Return

Color — The background color of the top-left cell in the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getBackgroundObjects()

Returns the background colors of the cells in the range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('B5:C6');
const bgColors = range.getBackgroundObjects();
for (const i in bgColors) {
  for (const j in bgColors[i]) {
    Logger.log(bgColors[i][j].asRgbColor().asHexString());
  }
}

Return

Color[][] — A two-dimensional array of background colors.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getBackgrounds()

Returns the background colors of the cells in the range (for example, '#ffffff').

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('B5:C6');
const bgColors = range.getBackgrounds();
for (const i in bgColors) {
  for (const j in bgColors[i]) {
    Logger.log(bgColors[i][j]);
  }
}

Return

String[][] — A two-dimensional array of color codes of the backgrounds.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getBandings()

Returns all the bandings that are applied to any cells in this range.

// Opens the spreadsheet file by its URL. If you created your script from within
// a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Sets a range.
const range = sheet.getRange('A1:K50');

// Gets the banding info for the range.
const bandings = range.getBandings();

// Logs the second row color for each banding to the console.
for (const banding of bandings) {
  console.log(banding.getSecondRowColor());
}

Return

Banding[] — All the bandings that are applied to any cells in this range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getCell(row, column)

Returns a given cell within a range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('B2:D4');

// The row and column here are relative to the range
// getCell(1,1) in this code returns the cell at B2
const cell = range.getCell(1, 1);
Logger.log(cell.getValue());

Parameters

NameTypeDescription
rowIntegerThe row of the cell relative to the range.
columnIntegerThe column of the cell relative to the range.

Return

Range — A range containing a single cell at the specified coordinates.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getColumn()

Returns the starting column position for this range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('B2:D4');
// Logs "2.0"
Logger.log(range.getColumn());

Return

Integer — The range's starting column position in the spreadsheet.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getDataRegion()

Returns a copy of the range expanded in the four cardinal Directions to cover all adjacent cells with data in them. If the range is surrounded by empty cells not including those along the diagonals, the range itself is returned. This is similar to selecting the range and typing Ctrl+A in the editor.

// Assume the active spreadsheet is blank.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
sheet.getRange('C2').setValue(100);
sheet.getRange('B3').setValue(100);
sheet.getRange('D3').setValue(100);
sheet.getRange('C4').setValue(100);
// Logs "B2:D4"
Logger.log(sheet.getRange('C3').getDataRegion().getA1Notation());

Return

Range — The range's data region or a range for the entire spreadsheet.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getDataRegion(dimension)

Returns a copy of the range expanded Direction.UP and Direction.DOWN if the specified dimension is Dimension.ROWS, or Direction.NEXT and Direction.PREVIOUS if the dimension is Dimension.COLUMNS. The expansion of the range is based on detecting data next to the range that is organized like a table. The expanded range covers all adjacent cells with data in them along the specified dimension including the table boundaries. If the original range is surrounded by empty cells along the specified dimension, the range itself is returned. This method is similar to selecting the range and typing Ctrl+Space for columns or Shift+Space for rows in the editor.

// Assume the active spreadsheet is blank.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
sheet.getRange('C2').setValue(100);
sheet.getRange('B3').setValue(100);
sheet.getRange('D3').setValue(100);
sheet.getRange('C4').setValue(100);
// Logs "C2:C4"
Logger.log(
    sheet.getRange('C3')
        .getDataRegion(SpreadsheetApp.Dimension.ROWS)
        .getA1Notation(),
);
// Logs "B3:D3"
Logger.log(
    sheet.getRange('C3')
        .getDataRegion(SpreadsheetApp.Dimension.COLUMNS)
        .getA1Notation(),
);

Parameters

NameTypeDescription
dimensionDimensionThe dimension along which to expand the range.

Return

Range — The range's data region or a range covering each column or each row spanned by the original range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getDataSourceFormula()

Returns the DataSourceFormula for the first cell in the range, or null if the cell doesn't contain a data source formula.

// Opens the spreadsheet file by its ID. If you created your script from a
// Google Sheets file, use SpreadsheetApp.getActiveSpreadsheet().
// TODO(developer): Replace the ID with your own.
const ss = SpreadsheetApp.openById('abc123456');

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets the range A1 on Sheet1.
const range = sheet.getRange('A1');

// Gets the data source formula from cell A1.
const dataSourceFormula = range.getDataSourceFormula();

// Gets the formula.
const formula = dataSourceFormula.getFormula();

// Logs the formula.
console.log(formula);

Return

DataSourceFormula — The DataSourceFormula for the cell.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getDataSourceFormulas()

Returns the DataSourceFormulas for the cells in the range.

// Opens the spreadsheet file by its ID. If you created your script from a
// Google Sheets file, use SpreadsheetApp.getActiveSpreadsheet().
// TODO(developer): Replace the ID with your own.
const ss = SpreadsheetApp.openById('abc123456');

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets the range A1:B5 on Sheet1.
const range = sheet.getRange('A1:B5');

// Gets an array of the data source formulas in the range A1:B5.
const dataSourceFormulas = range.getDataSourceFormulas();

// Logs the first formula in the array.
console.log(dataSourceFormulas[0].getFormula());

Return

DataSourceFormula[] — An array of DataSourceFormulas.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getDataSourcePivotTables()

Gets all the data source pivot tables intersecting with the range.

// Opens the spreadsheet file by its ID. If you created your script from a
// Google Sheets file, use SpreadsheetApp.getActiveSpreadsheet().
// TODO(developer): Replace the ID with your own.
const ss = SpreadsheetApp.openById('abc123456');

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets the range A1:G50 on Sheet1.
const range = sheet.getRange('A1:G50');

// Gets an array of the data source pivot tables in the range A1:G50.
const dataSourcePivotTables = range.getDataSourcePivotTables();

// Logs the last time that the first pivot table in the array was refreshed.
console.log(dataSourcePivotTables[0].getStatus().getLastRefreshedTime());

Return

DataSourcePivotTable[] — A list of data source pivot tables.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getDataSourceTables()

Gets all the data source tables intersecting with the range.

// Opens the spreadsheet file by its ID. If you created your script from a
// Google Sheets file, use SpreadsheetApp.getActiveSpreadsheet().
// TODO(developer): Replace the ID with your own.
const ss = SpreadsheetApp.openById('abc123456');

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets the range A1:G50 on Sheet1.
const range = sheet.getRange('A1:G50');

// Gets the first data source table in the range A1:G50.
const dataSourceTable = range.getDataSourceTables()[0];

// Logs the time of the last completed data execution on the data source table.
console.log(dataSourceTable.getStatus().getLastExecutionTime());

Return

DataSourceTable[] — A list of data source tables.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getDataSourceUrl()

Returns a URL for the data in this range, which can be used to create charts and queries.

Code.gs

function doGet() {
  const ss = SpreadsheetApp.openById(
      '1khO6hBWTNNyvyyxvob7aoZTI9ZvlqqASNeq0e29Tw2c',
  );
  const sheet = ss.getSheetByName('ContinentData');
  const range = sheet.getRange('A1:B8');

  const template = HtmlService.createTemplateFromFile('piechart');
  template.dataSourceUrl = range.getDataSourceUrl();
  return template.evaluate();
}

piechart.html

<!DOCTYPE html>
<html>
  <head>
    <!--Load the AJAX API-->
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">
      // Load the Visualization API and the corechart package.
      google.charts.load('current', {'packages': ['corechart']});

      // Set a callback to run when the Google Visualization API is loaded.
      google.charts.setOnLoadCallback(queryData);

      function queryData() {
        var query = new google.visualization.Query('<?= dataSourceUrl ?>');
        query.send(drawChart);
      }

      // Callback that creates and populates a data table,
      // instantiates the pie chart, passes in the data and
      // draws it.
      function drawChart(response) {
        if (response.isError()) {
          alert('Error: ' + response.getMessage() + ' ' + response.getDetailedMessage());
          return;
        }
        var data = response.getDataTable();

        // Set chart options.
        var options = {
          title: 'Population by Continent',
          width: 400,
          height: 300
        };

        // Instantiate and draw the chart, passing in some options.
        var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>
  </head>
  <body>
    <!-- Div that holds the pie chart. -->
    <div id="chart_div"></div>
  </body>
</html>

Return

String — A URL for this range as a data source that can be passed to other APIs such as charts.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getDataTable()

Return the data inside this object as a DataTable.

// Opens the spreadsheet file by its ID. If you created your script from a
// Google Sheets file, use SpreadsheetApp.getActiveSpreadsheet().
// TODO(developer): Replace the ID with your own.
const ss = SpreadsheetApp.openById('abc123456');

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets the range A1:B7 on Sheet1.
const range = sheet.getRange('A1:B7');

// Gets the range A1:B7 as a data table. The values in each column must be of
// the same type.
const datatable = range.getDataTable();

// Uses the Charts service to build a bar chart from the data table.
// This doesn't build an embedded chart. To do that, use
// sheet.newChart().addRange() instead.
const chart = Charts.newBarChart()
                  .setDataTable(datatable)
                  .setOption('title', 'Your Chart Title Here')
                  .build();

Return

DataTable — the data as a datatable.


getDataTable(firstRowIsHeader)

Return the data inside this range as a DataTable.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('A1:B7');

// Calling this method with "true" sets the first line to be the title of the
// axes
const datatable = range.getDataTable(true);

// Note that this doesn't build an EmbeddedChart, so you can't just use
// Sheet#insertChart(). To do that, use sheet.newChart().addRange() instead.
const chart = Charts.newBarChart()
                  .setDataTable(datatable)
                  .setOption('title', 'Your Title Here')
                  .build();

Parameters

NameTypeDescription
firstRowIsHeaderBooleanWhether to treat the first row as a header.

Return

DataTable — The data as a datatable.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getDataValidation()

Returns the data validation rule for the top-left cell in the range. If data validation has not been set on the cell, this method returns null.

// Log information about the data validation rule for cell A1.
const cell = SpreadsheetApp.getActive().getRange('A1');
const rule = cell.getDataValidation();
if (rule != null) {
  const criteria = rule.getCriteriaType();
  const args = rule.getCriteriaValues();
  Logger.log('The data validation rule is %s %s', criteria, args);
} else {
  Logger.log('The cell does not have a data validation rule.');
}

Return

DataValidation — The data validation rule for the top-left cell in the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getDataValidations()

Returns the data validation rules for all cells in the range. If data validation has not been set on a given cell, this method returns null for that cell's position in the array.

// Change existing data validation rules that require a date in 2013 to require
// a date in 2014.
const oldDates = [new Date('1/1/2013'), new Date('12/31/2013')];
const newDates = [new Date('1/1/2014'), new Date('12/31/2014')];
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
const rules = range.getDataValidations();

for (let i = 0; i < rules.length; i++) {
  for (let j = 0; j < rules[i].length; j++) {
    const rule = rules[i][j];

    if (rule != null) {
      const criteria = rule.getCriteriaType();
      const args = rule.getCriteriaValues();

      if (criteria === SpreadsheetApp.DataValidationCriteria.DATE_BETWEEN &&
          args[0].getTime() === oldDates[0].getTime() &&
          args[1].getTime() === oldDates[1].getTime()) {
        // Create a builder from the existing rule, then change the dates.
        rules[i][j] = rule.copy().withCriteria(criteria, newDates).build();
      }
    }
  }
}
range.setDataValidations(rules);

Return

DataValidation[][] — A two-dimensional array of data validation rules associated with the cells in the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getDeveloperMetadata()

Gets the developer metadata associated with this range.

// Opens the spreadsheet file by its URL. If you created your script from within
// a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets row 2 on Sheet1.
const range = sheet.getRange('2:2');

// Adds metadata to row 2.
range.addDeveloperMetadata('NAME', 'GOOGLE');

// Logs the metadata to console.
for (const metadata of range.getDeveloperMetadata()) {
  console.log(`${metadata.getKey()}: ${metadata.getValue()}`);
}

Return

DeveloperMetadata[] — The developer metadata associated with this range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getDisplayValue()

Returns the displayed value of the top-left cell in the range. The value is a String. The displayed value takes into account date, time and currency formatting formatting, including formats applied automatically by the spreadsheet's locale setting. Empty cells return an empty string.

// Opens the spreadsheet file by its URL. If you created your script from within
// a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets cell A30 and sets its value to 'Test code.'
const cell = sheet.getRange('A30');
cell.setValue('Test code');

// Gets the value and logs it to the console.
console.log(cell.getDisplayValue());

Return

String — The displayed value in this cell.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getDisplayValues()

Returns the rectangular grid of values for this range.

Returns a two-dimensional array of displayed values, indexed by row, then by column. The values are String objects. The displayed value takes into account date, time and currency formatting, including formats applied automatically by the spreadsheet's locale setting. Empty cells are represented by an empty string in the array. Remember that while a range index starts at 1, 1, the JavaScript array is indexed from [0][0].

// The code below gets the displayed values for the range C2:G8
// in the active spreadsheet.  Note that this is a JavaScript array.
const values =
    SpreadsheetApp.getActiveSheet().getRange(2, 3, 6, 4).getDisplayValues();
Logger.log(values[0][0]);

Return

String[][] — A two-dimensional array of values.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getFilter()

Returns the filter on the sheet this range belongs to, or null if there is no filter on the sheet.

const ss = SpreadsheetApp.getActiveSheet();
const range = ss.getRange('A1:C20');
// Gets the existing filter on the sheet that the given range belongs to.
const filter = range.getFilter();

Return

Filter — The filter.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getFontColorObject()

Returns the font color of the cell in the top-left corner of the range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

Logger.log(range.getFontColorObject().asRgbColor().asHexString());

Return

Color — The font color of the top-left cell in the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getFontColorObjects()

Returns the font colors of the cells in the range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

const results = range.getFontColorObjects();

for (const i in results) {
  for (const j in results[i]) {
    Logger.log(results[i][j].asRgbColor().asHexString());
  }
}

Return

Color[][] — A two-dimensional array of font colors associated with cells in the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getFontFamilies()

Returns the font families of the cells in the range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

const results = range.getFontFamilies();

for (const i in results) {
  for (const j in results[i]) {
    Logger.log(results[i][j]);
  }
}

Return

String[][] — A two-dimensional array of font families associated with cells in the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getFontFamily()

Returns the font family of the cell in the top-left corner of the range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

Logger.log(range.getFontFamily());

Return

String — The font family of the cell.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getFontLine()

Gets the line style of the cell in the top-left corner of the range ('underline', 'line-through', or 'none').

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

Logger.log(range.getFontLine());

Return

String — The font line.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getFontLines()

Gets the line style of the cells in the range ('underline', 'line-through', or 'none').

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

const results = range.getFontLines();

for (const i in results) {
  for (const j in results[i]) {
    Logger.log(results[i][j]);
  }
}

Return

String[][] — A two-dimensional array of font lines associated with cells in the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getFontSize()

Returns the font size in point size of the cell in the top-left corner of the range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

Logger.log(range.getFontSize());

Return

Integer — The font size in point size.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getFontSizes()

Returns the font sizes of the cells in the range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

const results = range.getFontSizes();

for (const i in results) {
  for (const j in results[i]) {
    Logger.log(results[i][j]);
  }
}

Return

Integer[][] — A two-dimensional array of font sizes of text associated with cells in the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getFontStyle()

Returns the font style ('italic' or 'normal') of the cell in the top-left corner of the range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

Logger.log(range.getFontStyle());

Return

String — The font style of the text in the cell.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getFontStyles()

Returns the font styles of the cells in the range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

const results = range.getFontStyles();

for (const i in results) {
  for (const j in results[i]) {
    Logger.log(results[i][j]);
  }
}

Return

String[][] — A two-dimensional array of font styles of text associated with cells in the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getFontWeight()

Returns the font weight (normal/bold) of the cell in the top-left corner of the range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

Logger.log(range.getFontWeight());

Return

String — The font weight of the text in the cell.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getFontWeights()

Returns the font weights of the cells in the range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

const results = range.getFontWeights();

for (const i in results) {
  for (const j in results[i]) {
    Logger.log(results[i][j]);
  }
}

Return

String[][] — A two-dimensional array of font weights of text associated with cells in the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getFormula()

Returns the formula (A1 notation) for the top-left cell of the range, or an empty string if the cell is empty or doesn't contain a formula.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

// This assumes you have a function in B5 that sums up
// B2:B4
const range = sheet.getRange('B5');

// Logs the calculated value and the formula
Logger.log(
    'Calculated value: %s Formula: %s',
    range.getValue(),
    range.getFormula(),
);

Return

String — The formula for the cell.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getFormulaR1C1()

Returns the formula (R1C1 notation) for a given cell, or null if none.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('B5');
const formula = range.getFormulaR1C1();
Logger.log(formula);

Return

String — The formula in R1C1 notation.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getFormulas()

Returns the formulas (A1 notation) for the cells in the range. Entries in the 2D array are empty strings for cells with no formula.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('B5:C6');
const formulas = range.getFormulas();
for (const i in formulas) {
  for (const j in formulas[i]) {
    Logger.log(formulas[i][j]);
  }
}

Return

String[][] — A two-dimensional array of formulas in string format.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getFormulasR1C1()

Returns the formulas (R1C1 notation) for the cells in the range. Entries in the 2D array are null for cells with no formula.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('B5:C6');
const formulas = range.getFormulasR1C1();
for (const i in formulas) {
  for (const j in formulas[i]) {
    Logger.log(formulas[i][j]);
  }
}

Return

String[][] — A two-dimensional array of formulas in R1C1 notation.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getGridId()

Returns the grid ID of the range's parent sheet. IDs are random non-negative int values.

// Log the grid ID of the first sheet (by tab position) in the spreadsheet.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

Logger.log(range.getGridId());

Return

Integer — The grid ID of the parent sheet.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getHeight()

Returns the height of the range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('B2:D4');
// logs 3.0
Logger.log(range.getHeight());

Return

Integer — The height of the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getHorizontalAlignment()

Returns the horizontal alignment of the text (left/center/right) of the cell in the top-left corner of the range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

Logger.log(range.getHorizontalAlignment());

Return

String — The horizontal alignment of the text in the cell.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getHorizontalAlignments()

Returns the horizontal alignments of the cells in the range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

const results = range.getHorizontalAlignments();

for (const i in results) {
  for (const j in results[i]) {
    Logger.log(results[i][j]);
  }
}

Return

String[][] — A two-dimensional array of horizontal alignments of text associated with cells in the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getLastColumn()

Returns the end column position.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('B2:D4');
// Logs "4.0"
Logger.log(range.getLastColumn());

Return

Integer — The range's ending column position in the spreadsheet.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getLastRow()

Returns the end row position.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('B2:D4');
// Logs "4.0"
Logger.log(range.getLastRow());

Return

Integer — The range's ending row position in the spreadsheet.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getMergedRanges()

Returns an array of Range objects representing merged cells that either are fully within the current range, or contain at least one cell in the current range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('A1:B3');

const mergedRanges = range.getMergedRanges();
for (let i = 0; i < mergedRanges.length; i++) {
  Logger.log(mergedRanges[i].getA1Notation());
  Logger.log(mergedRanges[i].getDisplayValue());
}

Return

Range[] — An array of Range objects, representing merged cells overlapping the range.


getNextDataCell(direction)

Starting at the cell in the first column and row of the range, returns the next cell in the given direction that is the edge of a contiguous range of cells with data in them or the cell at the edge of the spreadsheet in that direction. This is equivalent to typing Ctrl+[arrow key] in the editor.

// Assume the active spreadsheet is blank.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('C3:E5');
// Logs "C1"
Logger.log(range.getNextDataCell(SpreadsheetApp.Direction.UP).getA1Notation());

Parameters

NameTypeDescription
directionDirectionThe direction in which to find the next data region edge cell.

Return

Range — The data region edge cell or the cell at the edge of the spreadsheet.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getNote()

Returns the note associated with the given range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

Logger.log(range.getNote());

Return

String — The note associated with the given cell.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getNotes()

Returns the notes associated with the cells in the range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

const results = range.getNotes();

for (const i in results) {
  for (const j in results[i]) {
    Logger.log(results[i][j]);
  }
}

Return

String[][] — A two-dimensional array of notes associated with cells in the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getNumColumns()

Returns the number of columns in this range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('B2:D5');
Logger.log(range.getNumColumns());

Return

Integer — The number of columns in this range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getNumRows()

Returns the number of rows in this range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('B2:D5');
Logger.log(range.getNumRows());

Return

Integer — The number of rows in this range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getNumberFormat()

Get the number or date formatting of the top-left cell of the given range. The returned format patterns are described in the Sheets API documentation.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const cell = sheet.getRange('C4');
Logger.log(cell.getNumberFormat());

Return

String — The number format of the top-left cell of the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getNumberFormats()

Returns the number or date formats for the cells in the range. The returned format patterns are described in the Sheets API documentation.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('B5:C6');
const formats = range.getNumberFormats();
for (const i in formats) {
  for (const j in formats[i]) {
    Logger.log(formats[i][j]);
  }
}

Return

String[][] — A two-dimensional array of number formats.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getRichTextValue()

Returns the Rich Text value for the top left cell of the range, or null if the cell value is not text.

// Gets the Rich Text value of cell D4.
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange('D4:F6');
const richText = range.getRichTextValue();
console.log(richText.getText());

Return

RichTextValue — The Rich Text value of the top left cell in the range, or null if the cell value is not text.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getRichTextValues()

Returns the Rich Text values for the cells in the range.

// Gets the Rich Text values for all cells in range B5:C6
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange('B5:C6');
const values = range.getRichTextValues();

for (let i = 0; i < values.length; i++) {
  for (let j = 0; j < values[i].length; j++) {
    console.log(values[i][j].getText());
  }
}

Return

RichTextValue[][] — A two-dimensional array of Rich Text values.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getRow()

Returns the row position for this range. Identical to getRowIndex().

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('B2');
Logger.log(range.getRow());

Return

Integer — The row position of the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getRowIndex()

Returns the row position for this range. Identical to getRow().

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('B2');
Logger.log(range.getRowIndex());

Return

Integer — The row position of the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

See also


getSheet()

Returns the sheet this range belongs to.

// Opens the spreadsheet file by its URL. If you created your script from within
// a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets the range A1:D10 on Sheet1.
const range = sheet.getRange('A1:D10');

// Gets the sheet that the range belongs to.
const rangeSheet = range.getSheet();

// Gets the sheet name and logs it to the console.
console.log(rangeSheet.getName());

Return

Sheet — The sheet that this range belongs to.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getTextDirection()

Returns the text direction for the top left cell of the range. Returns null if the cell text direction is determined with automatic detection.

// Get the text direction of cell B1.
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange('B1:D4');
Logger.log(range.getTextDirection());

Return

TextDirection — The text direction of the top left cell in the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getTextDirections()

Returns the text directions for the cells in the range. Entries in the 2D array are null for cells using automatic detection.

// Get the text directions for all cells in range B5:C6
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange('B5:C6');
const directions = range.getTextDirections();

for (let i = 0; i < directions.length; i++) {
  for (let j = 0; j < directions[i].length; j++) {
    Logger.log(directions[i][j]);
  }
}

Return

TextDirection[][] — A two-dimensional array of text directions.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getTextRotation()

Returns the text rotation settings for the top left cell of the range.

// Log the text rotation settings for a cell.
const sheet = SpreadsheetApp.getActiveSheet();

const cell = sheet.getRange('A1');
Logger.log(cell.getTextRotation());

Return

TextRotation — The text rotation settings.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getTextRotations()

Returns the text rotation settings for the cells in the range.

const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange('B2:D4');

const results = range.getTextRotations();

for (const i in results) {
  for (const j in results[i]) {
    const rotation = results[i][j];
    Logger.log('Cell [%s, %s] has text rotation: %v', i, j, rotation);
  }
}

Return

TextRotation[][] — A two-dimensional array of text rotations associated with cells in the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getTextStyle()

Returns the text style for the top left cell of the range.

// Get the text style of cell D4.
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange('D4:F6');
const style = range.getTextStyle();
Logger.log(style);

Return

TextStyle — The text style of the top left cell in the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getTextStyles()

Returns the text styles for the cells in the range.

// Get the text styles for all cells in range B5:C6
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange('B5:C6');
const styles = range.getTextStyles();

for (let i = 0; i < styles.length; i++) {
  for (let j = 0; j < styles[i].length; j++) {
    Logger.log(styles[i][j]);
  }
}

Return

TextStyle[][] — A two-dimensional array of text styles.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getValue()

Returns the value of the top-left cell in the range. The value may be of type Number, Boolean, Date, or String depending on the value of the cell. Empty cells return an empty string.

// Opens the spreadsheet file by its URL. If you created your script from within
// a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets the range A1:D10 on Sheet1.
const range = sheet.getRange('A1:D10');

// Gets the value of the top-left cell in the range and logs it to the console.
console.log(range.getValue());

Return

Object — The value in this cell.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getValues()

Returns the rectangular grid of values for this range.

Returns a two-dimensional array of values, indexed by row, then by column. The values may be of type Number, Boolean, Date, or String, depending on the value of the cell. Empty cells are represented by an empty string in the array. Remember that while a range index starts at 1, 1, the JavaScript array is indexed from [0][0].

// The code below gets the values for the range C2:G8
// in the active spreadsheet.  Note that this is a JavaScript array.
const values = SpreadsheetApp.getActiveSheet().getRange(2, 3, 6, 4).getValues();
Logger.log(values[0][0]);
In web apps, a Date value isn't a legal parameter. getValues() fails to return data to a web app if the range contains a cell with a Date value. Instead, transform all the values retrieved from the sheet to a supported JavaScript primitive like a Number, Boolean, or String.

Return

Object[][] — A two-dimensional array of values.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getVerticalAlignment()

Returns the vertical alignment (top/middle/bottom) of the cell in the top-left corner of the range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

Logger.log(range.getVerticalAlignment());

Return

String — The vertical alignment of the text in the cell.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getVerticalAlignments()

Returns the vertical alignments of the cells in the range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

const results = range.getVerticalAlignments();

for (const i in results) {
  for (const j in results[i]) {
    Logger.log(results[i][j]);
  }
}

Return

String[][] — A two-dimensional array of vertical alignments of text associated with cells in the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getWidth()

Returns the width of the range in columns.

// Opens the spreadsheet file by its URL. If you created your script from within
// a Google Sheets file, you can use SpreadsheetApp.getActiveSpreadsheet()
// instead.
// TODO(developer): Replace the URL with your own.
const ss = SpreadsheetApp.openByUrl(
    'https://docs.google.com/spreadsheets/d/abc123456/edit',
);

// Gets Sheet1 by its name.
const sheet = ss.getSheetByName('Sheet1');

// Gets the range A1:D10 on Sheet1.
const range = sheet.getRange('A1:D10');

// Gets the width of the range in number of columns and logs it to the console.
console.log(range.getWidth());

Return

Integer — The number of columns in the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getWrap()

Returns whether the text in the cell wraps. To get more granular wrap strategy, use getWrapStrategy().

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

Logger.log(range.getWrap());

Return

Boolean — Whether the text in this cell wraps or not.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getWrapStrategies()

Returns the text wrapping strategies for the cells in the range.

// Get the text wrapping strategies for all cells in range B5:C6
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange('B5:C6');
const strategies = range.getWrapStrategies();

for (let i = 0; i < strategies.length; i++) {
  for (let j = 0; j < strategies[i].length; j++) {
    Logger.log(strategies[i][j]);
  }
}

Return

WrapStrategy[][] — A two-dimensional array of text wrapping strategies.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getWrapStrategy()

Returns the text wrapping strategy for the top left cell of the range.

// Get the text wrapping strategy of cell B1.
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange('B1:D4');
Logger.log(range.getWrapStrategy());

Return

WrapStrategy — The text wrapping strategy of the top left cell in the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

getWraps()

Returns whether the text in the cells wrap. To get more granular wrap strategy, use getWrapStrategies().

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const range = sheet.getRange('B2:D4');

const results = range.getVerticalAlignments();

for (const i in results) {
  for (const j in results[i]) {
    const isWrapped = results[i][j];
    if (isWrapped) {
      Logger.log('Cell [%s, %s] has wrapped text', i, j);
    }
  }
}

Return

Boolean[][] — A two-dimensional array of vertical alignments of text associated with cells in the range.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

insertCells(shiftDimension)

Inserts empty cells into this range. The new cells retain any formatting present in the cells previously occupying this range. Existing data in the sheet along the provided dimension is shifted away from the inserted range.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];

const range = sheet.getRange('A1:D10');
range.insertCells(SpreadsheetApp.Dimension.COLUMNS);

Parameters

NameTypeDescription
shiftDimensionDimensionThe dimension along which to shift existing data.

Return

Range — This range, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

insertCheckboxes()

Inserts checkboxes into each cell in the range, configured with true for checked and false for unchecked. Sets the value of all cells in the range to false.

const range = SpreadsheetApp.getActive().getRange('A1:B10');

// Inserts checkboxes into each cell in the range A1:B10 configured with 'true'
// for checked and 'false' for unchecked. Also, sets the value of each cell in
// the range A1:B10 to 'false'.
range.insertCheckboxes();

Return

Range — This range, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

insertCheckboxes(checkedValue)

Inserts checkboxes into each cell in the range, configured with a custom value for checked and the empty string for unchecked. Sets the value of each cell in the range to the empty string.

const range = SpreadsheetApp.getActive().getRange('A1:B10');

// Inserts checkboxes into each cell in the range A1:B10 configured with 'yes'
// for checked and the empty string for unchecked. Also, sets the value of each
// cell in the range A1:B10 to
//  the empty string.
range.insertCheckboxes('yes');

Parameters

NameTypeDescription
checkedValueObjectThe checked value for the checkbox data validation.

Return

Range — This range, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

insertCheckboxes(checkedValue, uncheckedValue)

Inserts checkboxes into each cell in the range, configured with custom values for the checked and unchecked states. Sets the value of each cell in the range to the custom unchecked value.

const range = SpreadsheetApp.getActive().getRange('A1:B10');

// Inserts checkboxes into each cell in the range A1:B10 configured with 'yes'
// for checked and 'no' for unchecked. Also, sets the value of each cell in the
// range A1:B10 to 'no'.
range.insertCheckboxes('yes', 'no');

Parameters

NameTypeDescription
checkedValueObjectThe checked value for the checkbox data validation.
uncheckedValueObjectThe unchecked value for the checkbox data validation.

Return

Range — This range, for chaining.

Authorization

Scripts that use this method require authorization with one or more of the following scopes:

  • https://www.googleapis.com/auth/spreadsheets.currentonly
  • https://www.googleapis.com/auth/spreadsheets

isBlank()

Returns true if the range is totally blank.

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const