Class RangeList

RangeList

A collection of one or more Range instances in the same sheet. You can use this class to apply operations on collections of non-adjacent ranges or cells.

Methods

MethodReturn typeBrief description
activate()RangeListSelects the list of Range instances.
breakApart()RangeListBreak all horizontally- or vertically-merged cells contained within the range list into individual cells again.
clear()RangeListClears the range of contents, formats, and data validation rules for each Range in the range list.
clear(options)RangeListClears the range of contents, format, data validation rules, and comments, as specified with the given options.
clearContent()RangeListClears the content of each Range in the range list, leaving the formatting intact.
clearDataValidations()RangeListClears the data validation rules for each Range in the range list.
clearFormat()RangeListClears text formatting for each Range in the range list.
clearNote()RangeListClears the note for each Range in the range list.
getRanges()Range[]Returns a list of one or more Range instances in the same sheet.
setBackground(color)RangeListSets the background color for each Range in the range list.
setBackgroundRGB(red, green, blue)RangeListSets the background to the given RGB color.
setBorder(top, left, bottom, right, vertical, horizontal)RangeListSets the border property for each Range in the range list.
setBorder(top, left, bottom, right, vertical, horizontal, color, style)RangeListSets the border property with color and/or style for each Range in the range list.
setFontColor(color)RangeListSets the font color for each Range in the range list.
setFontFamily(fontFamily)RangeListSets the font family for each Range in the range list.
setFontLine(fontLine)RangeListSets the font line style for each Range in the range list.
setFontSize(size)RangeListSets the font size (in points) for each Range in the range list.
setFontStyle(fontStyle)RangeListSet the font style for each Range in the range list.
setFontWeight(fontWeight)RangeListSet the font weight for each Range in the range list.
setFormula(formula)RangeListUpdates the formula for each Range in the range list.
setFormulaR1C1(formula)RangeListUpdates the formula for each Range in the range list.
setHorizontalAlignment(alignment)RangeListSet the horizontal alignment for each Range in the range list.
setNote(note)RangeListSets the note text for each Range in the range list.
setNumberFormat(numberFormat)RangeListSets the number or date format for each Range in the range list.
setShowHyperlink(showHyperlink)RangeListSets whether or not each Range in the range list should show hyperlinks.
setTextDirection(direction)RangeListSets the text direction for the cells in each Range in the range list.
setTextRotation(degrees)RangeListSets the text rotation settings for the cells in each Range in the range list.
setValue(value)RangeListSets the value for each Range in the range list.
setVerticalAlignment(alignment)RangeListSet the vertical alignment for each Range in the range list.
setVerticalText(isVertical)RangeListSets whether or not to stack the text for the cells for each Range in the range list.
setWrap(isWrapEnabled)RangeListSet text wrapping for each Range in the range list.
setWrapStrategy(strategy)RangeListSets the text wrapping strategy for each Range in the range list.

Detailed documentation

activate()

Selects the list of Range instances. The last range in the list is set as the active range.

Note: This provides a way to multi-select a number of ranges.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['D4', 'B2:C4']);
rangeList.activate();

var selection = sheet.getSelection();
// Current cell: B2
var currentCell = selection.getCurrentCell();
// Active range: B2:C4
var activeRange = selection.getActiveRange();
// Active range list: [D4, B2:C4]
var activeRangeList = selection.getActiveRangeList();

Return

RangeList — the list of active ranges 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

breakApart()

Break all horizontally- or vertically-merged cells contained within the range list into individual cells again.

Calling this function on a range list is equivalent to selecting a set of ranges and selecting the Format > Merge > Unmerge Sheets menu item.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A:A', 'C:C']);
rangeList.breakApart();

Return

RangeList — this range list, 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, formats, and data validation rules for each Range in the range list.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A:A', 'C:C']);
rangeList.clear();

Return

RangeList — this range list, 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 comments, as specified with the given options. By default all data is cleared.

// The code below clears the contents of the following ranges A:A and C:C in the active sheet,
// but preserves the format, data validation rules, and comments.
var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A:A', 'C:C']);
rangeList.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

RangeList — this range list, 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 each Range in the range list, leaving the formatting intact.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A:A', 'C:C']);
rangeList.clearContent();

Return

RangeList — this range list, 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 each Range in the range list.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A:A', 'C:C']);
rangeList.clearDataValidations();

Return

RangeList — this range list, 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 text formatting for each Range in the range list.

This clears text formatting for each range, but does not reset any number formatting rules.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A:A', 'C:C']);
rangeList.clearFormat();

Return

RangeList — this range list, 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 for each Range in the range list.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A:A', 'C:C']);
rangeList.clearNote();

Return

RangeList — this range list, 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

getRanges()

Returns a list of one or more Range instances in the same sheet.

Return

Range[] — the list of ranges

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

setBackground(color)

Sets the background color for each Range in the range list. Color is represented in in CSS notation; for example, '#ffffff' or 'white'.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A:A', 'C:C']);
rangeList.setBackground('red');

Parameters

NameTypeDescription
colorStringThe background color code in CSS notation such as '#ffffff' or 'white'; a null value resets the color.

Return

RangeList — this range list, 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

setBackgroundRGB(red, green, blue)

Sets the background to the given RGB color. This is a convenience wrapper around a setBackground(color) call.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A:A', 'C:C']);
// Sets the background to red for each range in the range list.
rangeList.setBackgroundRGB(255, 0, 0);

Parameters

NameTypeDescription
redIntegerThe red value in RGB notation.
greenIntegerThe green value in RGB notation.
blueIntegerThe blue value in RGB notation.

Return

RangeList — this range list, 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

setBorder(top, left, bottom, right, vertical, horizontal)

Sets the border property for each Range in the range list. The valid values are true (on), false (off) and null (no change).

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A2:B4', 'C1:D4']);
// Sets borders on the top and bottom of the ranges A2:B4 and C1:D4, but leaves the left and
// right unchanged.
rangeList.setBorder(true, null, true, null, false, false);

Parameters

NameTypeDescription
topBooleantrue for border, false for none, null for no change.
leftBooleantrue for border, false for none, null for no change.
bottomBooleantrue for border, false for none, null for no change.
rightBooleantrue for border, false for none, null for no change.
verticalBooleantrue for internal vertical borders, false for none, null for no change.
horizontalBooleantrue for internal horizontal borders, false for none, null for no change.

Return

RangeList — this range list, 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

setBorder(top, left, bottom, right, vertical, horizontal, color, style)

Sets the border property with color and/or style for each Range in the range list. Valid values are true (on), false (off) and null (no change). Color is represented in in CSS notation; for example, '#ffffff' or 'white'.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A2:B4', 'C1:D4']);
// Sets borders on the top and bottom, but leaves the left and right unchanged of the ranges
// A2:B4 and C1:D4. Also sets the color to 'red', and the border to 'DASHED'.
rangeList.setBorder(
    true, null, true, null, false, false, 'red', SpreadsheetApp.BorderStyle.DASHED);

Parameters

NameTypeDescription
topBooleantrue for border, false for none, null for no change.
leftBooleantrue for border, false for none, null for no change.
bottomBooleantrue for border, false for none, null for no change.
rightBooleantrue for border, false for none, null for no change.
verticalBooleantrue for internal vertical borders, false for none, null for no change.
horizontalBooleantrue for internal horizontal borders, false for none, null for no change.
colorStringThe border color in CSS notation like '#ffffff' or 'white', null for default color (black).
styleBorderStyleThe style for the borders, null for default style (solid).

Return

RangeList — this range list, 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

setFontColor(color)

Sets the font color for each Range in the range list. Color is represented in in CSS notation; for example, '#ffffff' or 'white'.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A:A', 'C:C']);
rangeList.setFontColor('red');

Parameters

NameTypeDescription
colorStringThe font color in CSS notation such as '#ffffff' or 'white'; a null value resets the color.

Return

RangeList — this range list, 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

setFontFamily(fontFamily)

Sets the font family for each Range in the range list. The font family is described by a string identifier such as Arial or Roboto.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A:A', 'C:C']);
rangeList.setFontFamily('Roboto');

Parameters

NameTypeDescription
fontFamilyStringThe font family to set; a null value resets the font family.

Return

RangeList — this range list, 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

setFontLine(fontLine)

Sets the font line style for each Range in the range list. The line styles options are 'underline', 'line-through', or 'none'.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A:A', 'C:C']);
rangeList.setFontLine('line-through');

Parameters

NameTypeDescription
fontLineStringThe font line style, either 'underline', 'line-through', or 'none'; a null value resets the font line style.

Return

RangeList — this range list, 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

setFontSize(size)

Sets the font size (in points) for each Range in the range list.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A:A', 'C:C']);
rangeList.setFontSize(20);

Parameters

NameTypeDescription
sizeIntegerA font point size.

Return

RangeList — this range list, 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

setFontStyle(fontStyle)

Set the font style for each Range in the range list. The font style options are 'italic' or 'normal'.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A:A', 'C:C']);
rangeList.setFontStyle("italic");

Parameters

NameTypeDescription
fontStyleStringThe font style, either 'italic' or 'normal'; a null value resets the font style.

Return

RangeList — this range list, 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

setFontWeight(fontWeight)

Set the font weight for each Range in the range list. The font weight options are 'normal' or 'bold'.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A:A', 'C:C']);
rangeList.setFontWeight('bold');

Parameters

NameTypeDescription
fontWeightStringThe font weight, either 'bold' or 'normal'; a null value resets the font weight.

Return

RangeList — this range list, 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

setFormula(formula)

Updates the formula for each Range in the range list. The given formula must be in A1 notation.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A11', 'C11']);
rangeList.setFormula('=SUM(B1:B10)');

Parameters

NameTypeDescription
formulaStringA string representing the formula to set.

Return

RangeList — this range list, 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

setFormulaR1C1(formula)

Updates the formula for each Range in the range list. The given formula must be in R1C1 notation.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A11', 'C11']);
// This sets the formula to be the sum of the 3 rows above B5
rangeList.setFormulaR1C1('=SUM(R[-3]C[0]:R[-1]C[0])');

Parameters

NameTypeDescription
formulaStringA string formula.

Return

RangeList — this range list, 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

setHorizontalAlignment(alignment)

Set the horizontal alignment for each Range in the range list. The alignment options are 'left', 'center', or 'right'.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A:A', 'C:C']);
rangeList.setHorizontalAlignment("center");

Parameters

NameTypeDescription
alignmentStringThe alignment, either 'left', 'center' or 'normal'; a null value resets the alignment.

Return

RangeList — this range list, 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

setNote(note)

Sets the note text for each Range in the range list.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A:A', 'C:C']);
rangeList.setNote('This is a note');

Parameters

NameTypeDescription
noteStringThe note text to set; a null value removes the note.

Return

RangeList — this range list, 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

setNumberFormat(numberFormat)

Sets the number or date format for each Range in the range list.

The accepted formatting patterns are described in the Sheets API date and number formatting guide.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A1:A10', 'C1:C10']);
// Always show 3 decimal points for the specified ranges.
rangeList.setNumberFormat('0.000');

Parameters

NameTypeDescription
numberFormatStringA number format string.

Return

RangeList — this range list, 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

setShowHyperlink(showHyperlink)

Sets whether or not each Range in the range list should show hyperlinks.

var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A1:A10', 'C1:C10']);
// Show hyperlinks for all the ranges.
rangeList.setShowHyperlink(true);

Parameters

NameTypeDescription
showHyperlinkBooleanWhether or not to show the hyperlink.

Return

RangeList — this range list, 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

setTextDirection(direction)

Sets the text direction for the cells in each Range in the range list. If a specified direction is null, the direction is inferred and then set.

// Sets right-to-left text direction each range in the range list.
var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A1:A10', 'C1:C10']);
rangeList.setTextDirection(SpreadsheetApp.TextDirection.RIGHT_TO_LEFT);

Parameters

NameTypeDescription
directionTextDirectionThe desired text direction; if null the direction is inferred before setting.

Return

RangeList — this range list, 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

setTextRotation(degrees)

Sets the text rotation settings for the cells in each Range in the range list. The input corresponds to the angle between the standard text orientation and the desired orientation. An input of zero indicates that the text is set to the standard orientation.

For left to right text direction, positive angles are in the counterclockwise direction, whereas for right to left they are in the clockwise direction.

// Sets the cells in the ranges A1:A10 and C1:C10 to have text rotated up 45 degrees.
var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['A1:A10', 'C1:C10']);
rangeList.setTextRotation(45);

Parameters

NameTypeDescription
degreesIntegerThe desired angle between the standard orientation and the desired orientation. For left to right text, positive angles are in the counterclockwise direction.

Return

RangeList — this range list, 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

setValue(value)

Sets the value for each Range in the range list. The value can be numeric, string, boolean or date. If it begins with '=' it is interpreted as a formula.

var sheet = SpreadsheetApp.getActiveSheet();
// Set value of 100 to each range in the range list.
var rangeList = sheet.getRangeList(['A:A', 'C:C']);
rangeList.setValue(100);

Parameters

NameTypeDescription
valueObjectThe value for the range.

Return

RangeList — this range list, 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

setVerticalAlignment(alignment)

Set the vertical alignment for each Range in the range list. The alignment options are 'top', 'middle' or 'bottom'.

// Sets the vertical alignment to middle for the list of ranges.
var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['D4', 'B2:C4']);
rangeList.setVerticalAlignment("middle");

Parameters

NameTypeDescription
alignmentStringThe alignment, either 'top', 'middle' or 'bottom'; a null value resets the alignment.

Return

RangeList — this range list, 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

setVerticalText(isVertical)

Sets whether or not to stack the text for the cells for each Range in the range list. If the text is stacked vertically, the degree text rotation setting is ignored.

// Sets all cell's in ranges D4 and B2:D4 to have vertically stacked text.
var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['D4', 'B2:C4']);
rangeList.setVerticalText(true);

Parameters

NameTypeDescription
isVerticalBooleanWhether or not to stack the text.

Return

RangeList — this range list, 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

setWrap(isWrapEnabled)

Set text wrapping for each Range in the range list. Cells with wrap enabled resize to display their full content. Cells with wrap disabled display as much as possible in the cell without resizing or running to multiple lines.

// Enable text wrap for the list of ranges.
var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['D4', 'B2:C4']);
rangeList.setWrap(true);

Parameters

NameTypeDescription
isWrapEnabledBooleanWhether to wrap text or not.

Return

RangeList — this range list, 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

setWrapStrategy(strategy)

Sets the text wrapping strategy for each Range in the range list.

// Sets the list of ranges to use the clip wrap strategy.
var sheet = SpreadsheetApp.getActiveSheet();
var rangeList = sheet.getRangeList(['D4', 'B2:C4']);
rangeList.setWrapStrategy(SpreadsheetApp.WrapStrategy.CLIP);

Parameters

NameTypeDescription
strategyWrapStrategyThe desired wrapping strategy.

Return

RangeList — this range list, 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

Send feedback about...

Apps Script
Apps Script
Need help? Visit our support page.