Google Apps Script

Class Range

Access and modify spreadsheet ranges. This class allows users to access and modify ranges in Google Sheets. A range can be a single cell in a sheet or a range of cells in a sheet.

Methods

MethodReturn typeBrief description
activate()RangeMake this range the active range.
breakApart()RangeBreak any multi-column cells in the range into individual cells again.
clear()RangeClears the range of contents, formats, and data-validation rules.
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.
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, 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.
getA1Notation()StringReturns a string description of the range, in A1 notation.
getBackground()StringReturns the background color of the top-left cell in the range (i.e., "#ff0000").
getBackgrounds()String[][]Returns the background colors of the cells in the range (i.e., "#ff0000").
getCell(row, column)RangeReturns a given cell within a range.
getColumn()IntegerReturns the starting column position for this 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.
getFontColor()StringReturns the font color of the cell in the top-left corner of the range, in CSS notation (e.g.
getFontColors()String[][]Returns the font colors of the cells in the range in CSS notation (e.g.
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 LineStyle of of the cell in the top-left corner of the range (underline, line-through, etc.).
getFontLines()String[][]Gets the LineStyle of of the cells in the range (underline, line-through, etc.).
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/normal/oblique) 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.
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 formatting of the top-left cell of the given range.
getNumberFormats()String[][]Returns the number formats 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.
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 the wrapping policy of the cell in the top-left corner of the range.
getWraps()Boolean[][]Returns the wrapping policy of the cells in the range.
isBlank()BooleanReturns true if the range is totally blank.
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.
setBackground(color)RangeSets the background color of all cells in the range in CSS notation (e.g.
setBackgroundRGB(red, green, blue)RangeSets the background to the given RGB color.
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.
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 (e.g.
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 LineStyle of the given range (underline, line-through, etc.).
setFontLines(fontLines)RangeSets a rectangular grid of font lines (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/normal/oblique).
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).
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).
setWrap(isWrapEnabled)RangeSet the cell wrap of the given range.
setWraps(isWrapEnabled)RangeSets a rectangular grid of word wrap policies (must match dimensions of this range).
sort(sortSpecObj)RangeSorts the cells in the given range.

Detailed documentation

activate()

Make this range the active range.

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

 var range = sheet.getRange("A1:D10");
 range.activate();
 

Return

Range — the range for chaining


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 -> Unmerge.

Return

Range — the range for chaining


clear()

Clears the range of contents, formats, and data-validation rules.

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

 var range = sheet.getRange("A1:D10");
 range.clear();
 

Return

Range — the range for chaining


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 will be cleared.

 
// The code below will clear range C2:G8 in the active sheet, but preserve the format,
 // data-validation rules, and comments.
 SpreadsheetApp.getActiveSheet().getRange(2, 3, 6, 4).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

Return

Range — the range for chaining


clearContent()

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

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

 var range = sheet.getRange("A1:D10");
 range.clearContent();
 

Return

Range — the range for chaining


clearDataValidations()

Clears the data-validation rules for the range.

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

Return

Range — the range, for chaining


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.

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

 var range = sheet.getRange("A1:D10");
 range.clearFormat();
 

Return

Range — the range for chaining


clearNote()

Clears the note in the given cell or cells.

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

 var range = sheet.getRange("A1:D10");
 range.clearNote();
 

Return

Range — the range for chaining


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 will be repeated or truncated accordingly. Note that this method is formatting only. For a detailed description of the gridId parameter, see getGridId().

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

 var 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.copyFormatToRange(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

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 will be repeated or truncated accordingly. Note that this method is formatting only.

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

 var range = source.getRange("B2:D4");

 // This copies the data 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

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 will copy the first 5 columns over to the 6th column.
 var sheet = SpreadsheetApp.getActiveSheet();
 var 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

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 will copy only the values of the first 5 columns over to the 6th column.
 var 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

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 will be repeated or truncated accordingly. For a detailed description of the gridId parameter, see getGridId().

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

 var 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

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 will be repeated or truncated accordingly.

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

 var 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

getA1Notation()

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

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];
 var 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


getBackground()

Returns the background color of the top-left cell in the range (i.e., "#ff0000").

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

 var cell = sheet.getRange("B5");
 Logger.log(cell.getBackground());
 

Return

String — the color code of the background


getBackgrounds()

Returns the background colors of the cells in the range (i.e., "#ff0000").

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

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

Return

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


getCell(row, column)

Returns a given cell within a range.

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

 var 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, B2
 var 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


getColumn()

Returns the starting column position for this range.

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

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

Return

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


getDataSourceUrl()

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

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

 var dataSourceUrl = range.getDataSourceUrl();

 // Note that this doesn't build an EmbeddedChart, so we can't just use
 // Sheet#insertChart(). If we want to do that, we should use
 // sheet.newChart().addRange() instead.
 var chart = Charts.newBarChart()
     .setOption("title", "Your Title Here")
     .setDataSourceUrl(dataSourceUrl)
     .build();

 var app = UiApp.createApplication();
 app.add(chart);
 ss.show(app);
 

Return

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


getDataTable()

Return the data inside this object as a DataTable.

Return

DataTable — the data as a datatable.


getDataTable(firstRowIsHeader)

Return the data inside this Range as a DataTable.

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

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

 // Note that this doesn't build an EmbeddedChart, so we can't just use
 // Sheet#insertChart(). If we want to do that, we should use
 // sheet.newChart().addRange() instead.
 var chart = Charts.newBarChart()
     .setDataTable(datatable)
     .setOption("title", "Your Title Here")
     .build();

 var app = UiApp.createApplication();
 app.add(chart);
 ss.show(app);
 

Parameters

NameTypeDescription
firstRowIsHeaderBooleanwhether to treat the first row as a header

Return

DataTable — the data as a datatable


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.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var rule = cell.getDataValidation();
 if (rule != null) {
   var criteria = rule.getCriteriaType();
   var 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


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.
 var oldDates = [new Date('1/1/2013'), new Date('12/31/2013')];
 var newDates = [new Date('1/1/2014'), new Date('12/31/2014')];
 var sheet = SpreadsheetApp.getActiveSheet();
 var range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
 var rules = range.getDataValidations();

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

     if (rule != null) {
       var criteria = rule.getCriteriaType();
       var 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


getFontColor()

Returns the font color of the cell in the top-left corner of the range, in CSS notation (e.g. #ffffff or white).

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

 Logger.log(range.getFontColor());
 

Return

String — the font color in CSS notation (e.g. #ffffff or white)


getFontColors()

Returns the font colors of the cells in the range in CSS notation (e.g. #ffffff or white).

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

 var results = range.getFontColors();

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

Return

String[][] — a two-dimensional array of font colors associated with cells in the range


getFontFamilies()

Returns the font families of the cells in the range.

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

 var results = range.getFontFamilies();

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

Return

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


getFontFamily()

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

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

 Logger.log(range.getFontFamily());
 

Return

String — the font family of the cell


getFontLine()

Gets the LineStyle of of the cell in the top-left corner of the range (underline, line-through, etc.).

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

 Logger.log(range.getFontLine());
 

Return

String — the font line


getFontLines()

Gets the LineStyle of of the cells in the range (underline, line-through, etc.).

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

 var results = range.getFontLines();

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

Return

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


getFontSize()

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

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

 Logger.log(range.getFontSize());
 

Return

Integer — the font size in point size


getFontSizes()

Returns the font sizes of the cells in the range.

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

 var results = range.getFontSizes();

 for (var i in results) {
   for (var 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


getFontStyle()

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

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

 Logger.log(range.getFontStyle());
 

Return

String — the font style of the text in the cell


getFontStyles()

Returns the font styles of the cells in the range.

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

 var results = range.getFontStyles();

 for (var i in results) {
   for (var 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


getFontWeight()

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

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

 Logger.log(range.getFontWeight());
 

Return

String — the font weight of the text in the cell


getFontWeights()

Returns the font weights of the cells in the range.

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

 var results = range.getFontWeights();

 for (var i in results) {
   for (var 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


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.

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

 // This assumes we have a function in B5 that sums up
 // B2:B4
 var 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


getFormulaR1C1()

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

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

 var range = sheet.getRange("B5");
 var formula = range.getFormulaR1C1();
 Logger.log(formula);
 

Return

String — the formula in R1C1 notation


getFormulas()

Returns the formulas (A1 notation) for the cells in the range. Entries in the 2D array will be an empty string for cells with no formula.

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

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

Return

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


getFormulasR1C1()

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

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

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

Return

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


getGridId()

Returns the grid ID of the range's parent sheet. The first sheet created in a spreadsheet has a grid ID of 0, and each sheet created thereafter increments the ID. The ID does not necessarily correspond to a sheet's tab position because sheets may have been reordered or deleted. If a sheet is deleted, the grid ID for subsequent sheets continues to increment, and the grid ID of the deleted sheet is not reused.

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

 Logger.log(range.getGridId());
 

Return

Integer — the grid ID of the parent sheet


getHeight()

Returns the height of the range.

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

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

Return

Integer — the height of the range


getHorizontalAlignment()

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

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

 Logger.log(range.getHorizontalAlignment());
 

Return

String — the horizontal alignment of the text in the cell


getHorizontalAlignments()

Returns the horizontal alignments of the cells in the range.

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

 var results = range.getHorizontalAlignments();

 for (var i in results) {
   for (var 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


getLastColumn()

Returns the end column position.

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

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

Return

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


getLastRow()

Returns the end row position.

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

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

Return

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


getNote()

Returns the note associated with the given range.

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

 Logger.log(range.getNote());
 

Return

String — the note associated with the given cell


getNotes()

Returns the notes associated with the cells in the range.

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

 var results = range.getNotes();

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

Return

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


getNumColumns()

Returns the number of columns in this range.

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

 var range = sheet.getRange("B2:D5");
 Logger.log(range.getNumColumns());
 

Return

Integer — the number of columns in this range


getNumRows()

Returns the number of rows in this range.

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

 var range = sheet.getRange("B2:D5");
 Logger.log(range.getNumRows());
 

Return

Integer — the number of rows in this range


getNumberFormat()

Get the number formatting of the top-left cell of the given range. The number format corresponds to a formatting created when a user clicks Format -> Number.

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

 var cell = sheet.getRange("C4");
 Logger.log(cell.getNumberFormat());
 

Return

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


getNumberFormats()

Returns the number formats for the cells in the range.

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

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

Return

String[][] — a two-dimensional array of number formats


getRow()

Returns the row position for this range.

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

 var range = sheet.getRange("B2");
 Logger.log(range.getRow());
 

Return

Integer — the row position of the range


getRowIndex()

Returns the row position for this range.

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

 var range = sheet.getRange("B2");
 Logger.log(range.getRowIndex());
 

Return

Integer — the row position of the range

See also


getSheet()

Returns the sheet this range belongs to.

Return

Sheet — the sheet that this range belongs to


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 will return an empty string.

Return

Object — the value in this cell


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 will be represented by an empty string in the array. Remember that while a range index starts at 1, 1, the JavaScript array will be indexed from [0][0].

 
// The code below will get the values for the range C2:G8
 // in the active spreadsheet.  Note that this will be a javascript array.
 var values = SpreadsheetApp.getActiveSheet().getRange(2, 3, 6, 4).getValues();
 Logger.log(values[0][0]);
 

Return

Object[][] — a two-dimensional array of values


getVerticalAlignment()

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

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

 Logger.log(range.getVerticalAlignment());
 

Return

String — the vertical alignment of the text in the cell


getVerticalAlignments()

Returns the vertical alignments of the cells in the range.

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

 var results = range.getVerticalAlignments();

 for (var i in results) {
   for (var 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


getWidth()

Returns the width of the range in columns.

Return

Integer — the number of columns in the range


getWrap()

Returns the wrapping policy of the cell in the top-left corner of the range. true if the text wraps, false if the text does not.

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

 Logger.log(range.getWrap());
 

Return

Boolean — whether the text in this cell wraps or not


getWraps()

Returns the wrapping policy of the cells in the range.

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

 var results = range.getVerticalAlignments();

 for (var i in results) {
   for (var j in results[i]) {
     var 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


isBlank()

Returns true if the range is totally blank.

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

 Logger.log(range.isBlank());
 

Return

Boolean — whether the range is blank or not


merge()

Merges the cells in the range together into a single block.

 
var sheet = SpreadsheetApp.getActiveSheet();

  // The code below will 2-dimensionally merge the cells in A1 to B3
  sheet.getRange('A1:B3').merge();
 

Return

Range — the range for chaining


mergeAcross()

Merge the cells in the range across the columns of the range.

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

 // The code below will merge cells C5:E5 into one cell
 var range1 = sheet.getRange("C5:E5");
 range1.mergeAcross();

 // The code below will create 2 horizontal cells, F5:H5 and F6:H6
 var range2 = sheet.getRange("F5:H6");
 range2.mergeAcross();
 

Return

Range — the range for chaining


mergeVertically()

Merges the cells in the range together.

 
var sheet = SpreadsheetApp.getActiveSheet();

  // The code below will vertically merge the cells in A1 to A10
  sheet.getRange('A1:A10').mergeVertically();

  // The code below will create 3 merged columns: B1 to B10, C1 to C10, and D1 to D10
  sheet.getRange('B1:D10').mergeVertically();
 

Return

Range — the range for chaining


moveTo(target)

Cut and paste (both format and values) from this range to the target range.

 
// The code below will move the first 5 columns over to the 6th column
 var sheet = SpreadsheetApp.getActiveSheet()
 sheet.getRange("A1:E").moveTo(sheet.getRange("F1"));
 

Parameters

NameTypeDescription
targetRangea target range to copy this range to; only the top-left cell position is relevant

offset(rowOffset, columnOffset)

Returns a new range that is offset from this range by the given number of rows and columns (which can be negative). The new range will be the same size as the original range.

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

 var cell = sheet.getRange("A1");

 // newCell references B2
 var newCell = cell.offset(1, 1);
 

Parameters

NameTypeDescription
rowOffsetIntegernumber of rows down from the range's top-left cell; negative values represent rows up from the range's top-left cell
columnOffsetIntegernumber of columns right from the range's top-left cell; negative values represent columns left from the range's top-left cell

Return

Range — the range for chaining


offset(rowOffset, columnOffset, numRows)

Returns 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.

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

 var cell = sheet.getRange("A1");

 // newCell references B2:B3
 var newRange = cell.offset(1, 1, 2);
 

Parameters

NameTypeDescription
rowOffsetIntegernumber of rows down from the range's top-left cell; negative values represent rows up from the range's top-left cell
columnOffsetIntegernumber of columns right from the range's top-left cell; negative values represent columns left from the range's top-left cell
numRowsIntegerthe height in rows of the new range

Return

Range — the range for chaining


offset(rowOffset, columnOffset, numRows, numColumns)

Returns 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.

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

 var cell = sheet.getRange("A1");

 // newCell references B2:C3
 var newRange = cell.offset(1, 1, 2, 2);
 

Parameters

NameTypeDescription
rowOffsetIntegernumber of rows down from the range's top-left cell; negative values represent rows up from the range's top-left cell
columnOffsetIntegernumber of columns right from the range's top-left cell; negative values represent columns left from the range's top-left cell
numRowsIntegerthe height in rows of the new range
numColumnsIntegerthe width in columns of the new range

Return

Range — the range for chaining


setBackground(color)

Sets the background color of all cells in the range in CSS notation (e.g. #ffffff or white).

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

 var range = sheet.getRange("B2:D5");
 range.setBackground("red");
 

Parameters

NameTypeDescription
colorStringcolor code in CSS notation (e.g. #ffffff or white)

Return

Range — the range for chaining


setBackgroundRGB(red, green, blue)

Sets the background to the given RGB color. This is a convenience wrapper for the setBackground call that takes a string color.

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

 var cell = sheet.getRange("B2");

 // Sets the background to white
 cell.setBackgroundRGB(255, 255, 255);

 // Sets the background to red
 cell.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

Range — the range for chaining


setBackgrounds(color)

Sets a rectangular grid of background colors (must match dimensions of this range). The colors are in CSS notation (e.g. #ffffff or white).

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

 var colors = [
   ["red", "white", "blue"],
   ["#FF0000", "#FFFFFF", "#0000FF"] // These are the hex equivalents
 ];

 var cell = sheet.getRange("B5:D6");
 cell.setBackgrounds(colors);
 

Parameters

NameTypeDescription
colorString[][]a two-dimensional array of colors in CSS notation (e.g. #ffffff or white)

Return

Range — the range for chaining


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

Sets the border property. Valid values are true (on), false (off) and null (no change).

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

 var cell = sheet.getRange("B2");
 // Sets borders on the top and bottom, but leaves the left and right unchanged
 cell.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

Range — the range for chaining


setDataValidation(rule)

Sets one data-validation rule for all cells in the range.

 // Set the data-validation rule for cell A1 to require a value from B1:B10.
 var cell = SpreadsheetApp.getActive().getRange('A1');
 var range = SpreadsheetApp.getActive().getRange('B1:B10');
 var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
 cell.setDataValidation(rule);
 

Parameters

NameTypeDescription
ruleDataValidationthe data-validation rule to set

Return

Range — the range, for chaining


setDataValidations(rules)

Sets the data-validation rules for all cells in the range. This method takes a two-dimensional array of data validations, indexed by row then by column. The array dimensions must correspond to the range dimensions.

// Set the data-validation rules for Sheet1!A1:B5 to require a value from Sheet2!A1:A10.
 var destinationRange = SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange('A1:B5');
 var sourceRange = SpreadsheetApp.getActive().getSheetByName('Sheet2').getRange('A1:A10');
 var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange).build();
 var rules = destinationRange.getDataValidations();
 for (var i = 0; i < rules.length; i++) {
   for (var j = 0; j < rules[i].length; j++) {
     rules[i][j] = rule;
   }
 }
 destinationRange.setDataValidations(rules);
 

Parameters

NameTypeDescription
rulesDataValidation[][]a two-dimensional array of data-validation rules to set

Return

Range — the range, for chaining


setFontColor(color)

Sets the font color in CSS notation (e.g. #ffffff or white).

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

 var cell = sheet.getRange("B2");
 cell.setFontColor("red");
 

Parameters

NameTypeDescription
colorStringthe font color in CSS notation (e.g. #ffffff or white)

Return

Range — the range for chaining


setFontColors(colors)

Sets a rectangular grid of font colors (must match dimensions of this range). The colors are in CSS notation (e.g. #ffffff or white).

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

 var colors = [
   ["red", "white", "blue"],
   ["#FF0000", "#FFFFFF", "#0000FF"] // These are the hex equivalents
 ];

 var cell = sheet.getRange("B5:D6");
 cell.setFontColors(colors);
 

Parameters

NameTypeDescription
colorsObject[][]a two-dimensional string array of background colors

Return

Range — the range for chaining


setFontFamilies(fontFamilies)

Sets a rectangular grid of font families (must match dimensions of this range). Examples of font families are "Arial" or "Helvetica".

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

 var fonts = [
   ["Arial", "Helvetica", "Verdana"],
   ["Courier New", "Arial", "Helvetica] // These are the hex equivalents
 ];

 var cell = sheet.getRange("B2:D3");
 cell.setFontFamilies(fonts);
 

Parameters

NameTypeDescription
fontFamiliesObject[][]a two-dimensional string array of font families

Return

Range — the range for chaining


setFontFamily(fontFamily)

Sets the font family, such as "Arial" or "Helvetica".

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

 var cell = sheet.getRange("B2");
 cell.setFontFamily("Helvetica");
 

Parameters

NameTypeDescription
fontFamilyStringthe font family

Return

Range — the range for chaining


setFontLine(fontLine)

Sets the LineStyle of the given range (underline, line-through, etc.).

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

 var cell = sheet.getRange("B2");
 cell.setFontLine("line-through");
 

Parameters

NameTypeDescription
fontLineStringany of "underline", "overline", "line-through" or "blink"

Return

Range — the range for chaining


setFontLines(fontLines)

Sets a rectangular grid of font lines (must match dimensions of this range).

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

 // The size of the two dimension array must match up with
 // the size of the range
 var fontLines = [
   ["underline", "overline", "line-through"]
 ];

 var range = sheet.getRange("B2:D2");
 range.setFontLines(fontLines);
 

Parameters

NameTypeDescription
fontLinesObject[][]a two dimension array of font line styles

Return

Range — the range for chaining


setFontSize(size)

Sets the font size, with the size being the point size to use.

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

 var cell = sheet.getRange("B2");
 cell.setFontSize(20);
 

Parameters

NameTypeDescription
sizeIntegera font size in point size

Return

Range — the range for chaining


setFontSizes(sizes)

Sets a rectangular grid of font sizes (must match dimensions of this range). The sizes are in points.

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

 // The size of the two dimension array must match up with
 // the size of the range
 var fontSizes = [
   [16, 20, 24]
 ];

 var range = sheet.getRange("B2:D2");
 range.setFontSizes(fontSizes);
 

Parameters

NameTypeDescription
sizesObject[][]a two dimension array of sizes

Return

Range — the range for chaining


setFontStyle(fontStyle)

Set the font style for the given range (italic/normal/oblique).

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

 var cell = sheet.getRange("B2");
 cell.setFontStyle("italic");
 

Parameters

NameTypeDescription
fontStyleStringany of "italic", "normal" or "oblique".

Return

Range — the range for chaining


setFontStyles(fontStyles)

Sets a rectangular grid of font styles (must match dimensions of this range).

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

 // The size of the two dimension array must match up with
 // the size of the range
 var fontStyles = [
   ["italic", "normal", "oblique"]
 ];

 var range = sheet.getRange("B2:D2");
 range.setFontSizes(fontStyles);
 

Parameters

NameTypeDescription
fontStylesObject[][]a two dimension array of font styles

Return

Range — the range for chaining


setFontWeight(fontWeight)

Set the font weight for the given range (normal/bold).

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

 var cell = sheet.getRange("B2");
 cell.setFontWeight("bold");
 

Parameters

NameTypeDescription
fontWeightStringthe font weight - normal or bold

Return

Range — the range for chaining


setFontWeights(fontWeights)

Sets a rectangular grid of font weights (must match dimensions of this range). An example of a font weight is "bold".

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

 // The size of the two dimension array must match up with
 // the size of the range
 var fontStyles = [
   [ "bold", "bold", "normal" ]
 ];

 var range = sheet.getRange("B2:D2");
 range.setFontWeights(fontStyles);
 

Parameters

NameTypeDescription
fontWeightsObject[][]a two-dimensional array of font weights

Return

Range — the range for chaining


setFormula(formula)

Updates the formula for this range. The given formula must be in A1 notation.

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

 var cell = sheet.getRange("B5");
 cell.setFormula("=SUM(B3:B4)");
 

Parameters

NameTypeDescription
formulaStringa string representing the formula to set for the cell

Return

Range — the range for chaining


setFormulaR1C1(formula)

Updates the formula for this range. The given formula must be in R1C1 notation.

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

 var cell = sheet.getRange("B5");
 // This sets the formula to be the sum of the 3 rows above B5
 cell.setFormulaR1C1("=SUM(R[-3]C[0]:R[-1]C[0])");
 

Parameters

NameTypeDescription
formulaStringa string formula

Return

Range — the range for chaining


setFormulas(formulas)

Sets a rectangular grid of formulas (must match dimensions of this range). The given formulas must be in A1 notation. This method takes a two-dimensional array of formulas, indexed by row, then by column. The array dimensions must correspond to the range dimensions.

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

 // This sets the formulas to be a row of sums, followed by a row of
 // averages right below
 // The size of the two dimension array must match up with
 // the size of the range
 var formulas = [
   ["=SUM(B2:B4)", "=SUM(C2:C4)", "=SUM(D2:D4)"],
   ["=AVERAGE(B2:B4)", "=AVERAGE(C2:C4)", "=AVERAGE(D2:D4)"]
 ];

 var cell = sheet.getRange("B5:D6");
 cell.setFormulas(formulas);
 

Parameters

NameTypeDescription
formulasString[][]a two-dimensional string array of formulas

Return

Range — the range for chaining


setFormulasR1C1(formulas)

Sets a rectangular grid of formulas (must match dimensions of this range). The given formulas must be in R1C1 notation.

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

 // This creates formulas for a row of sums, followed by a row of
 // averages
 var sumOfRowsAbove = "=SUM(R[-3]C[0]:R[-1]C[0])";
 var averageOfRowsAbove = "=AVERAGE(R[-4]C[0]:R[-2]C[0])";

 // The size of the two dimension array must match up with
 // the size of the range
 var formulas = [
   [sumOfRowsAbove, sumOfRowsAbove, sumOfRowsAbove],
   [averageOfRowsAbove, averageOfRowsAbove, averageOfRowsAbove]
 ];

 var cell = sheet.getRange("B5:D6");
 // This sets the formula to be the sum of the 3 rows above B5
 cell.setFormulasR1C1(formulas);
 

Parameters

NameTypeDescription
formulasString[][]a two dimension array of formulas in R1C1 format

Return

Range — the range for chaining


setHorizontalAlignment(alignment)

Set the horizontal (left to right) alignment for the given range (left/center/right).

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

 var cell = sheet.getRange("B2");
 cell.setHorizontalAlignment("center");
 

Parameters

NameTypeDescription
alignmentStringthe alignment - left, center or right

Return

Range — the range for chaining


setHorizontalAlignments(alignments)

Sets a rectangular grid of horizontal alignments. see setHorizontalAlignment(alignment)

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

 // The size of the two dimension array must match up with
 // the size of the range
 var horizontalAlignments = [
   [ "left", "right", "center" ]
 ];

 var range = sheet.getRange("B2:D2");
 range.setHorizontalAlignments(horizontalAlignments);
 

Parameters

NameTypeDescription
alignmentsObject[][]a two-dimensional array of alignments

Return

Range — the range for chaining

See also


setNote(note)

Sets the note to the given value.

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

 var cell = sheet.getRange("B2");
 cell.setNote("This is a note");
 

Parameters

NameTypeDescription
noteStringthe note value to set for the range

Return

Range — the range for chaining


setNotes(notes)

Sets a rectangular grid of notes (must match dimensions of this range).

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

 // The size of the two dimension array must match up with
 // the size of the range
 var notes = [
   ["it goes", "like this", "the fourth, the fifth"],
   ["the minor fall", "and the", "major lift"]
 ];


 var cell = sheet.getRange("B2:D3");
 cell.setNotes(notes)
 

Parameters

NameTypeDescription
notesObject[][]a two-dimensional array of notes

Return

Range — the range for chaining

See also


setNumberFormat(numberFormat)

Sets the number or date format to the given formatting string. Date formats follow the java.text.SimpleDateFormat specification.

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

 var cell = sheet.getRange("B2");
 // Always show 3 decimal points
 cell.setNumberFormat("0.000");
 

Parameters

NameTypeDescription
numberFormatStringa number format string

Return

Range — the range for chaining


setNumberFormats(numberFormats)

Sets a rectangular grid of number or date formats (must match dimensions of this range). The values are formatting strings, see setNumberFormat(numberFormat). Date formats follow the java.text.SimpleDateFormat specification.

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

 // The size of the two dimension array must match up with
 // the size of the range
 var formats = [
   [ "0.000", "0,000,000", "$0.00" ]
 ];

 var range = sheet.getRange("B2:D2");
 range.setNumberFormats(formats);
 

Parameters

NameTypeDescription
numberFormatsObject[][]a two dimension array of number formats

Return

Range — the range for chaining


setValue(value)

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

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

 var cell = sheet.getRange("B2");
 cell.setValue(100);
 

Parameters

NameTypeDescription
valueObjectthe value for the range

Return

Range — the range for chaining


setValues(values)

Sets a rectangular grid of values (must match dimensions of this range).

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

 // The size of the two dimension array must match up with
 // the size of the range
 var values = [
   [ "2.000", "1,000,000", "$2.99" ]
 ];

 var range = sheet.getRange("B2:D2");
 range.setValues(values);
 

Parameters

NameTypeDescription
valuesObject[][]a two dimension array of values

Return

Range — the range for chaining


setVerticalAlignment(alignment)

Set the vertical (top to bottom) alignment for the given range (top/middle/bottom).

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

 var cell = sheet.getRange("B2");
 cell.setVerticalAlignment("middle");
 

Parameters

NameTypeDescription
alignmentStringthe alignment - top, middle or bottom

Return

Range — the range for chaining


setVerticalAlignments(alignments)

Sets a rectangular grid of vertical alignments (must match dimensions of this range).

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

 // The size of the two dimension array must match up with
 // the size of the range
 var alignments = [
   [ "top", "middle", "bottom" ]
 ];

 var range = sheet.getRange("B2:D2");
 range.setVerticalAlignments(alignments);
 

Parameters

NameTypeDescription
alignmentsObject[][]a two-dimensional array of alignments

Return

Range — the range for chaining

See also


setWrap(isWrapEnabled)

Set the cell wrap of the given range. Cells with wrap enabled (the default) will resize to display their full content. Cells with wrap disabled will display as much as possible in the cell without resizing or running to multiple lines.

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

 var cell = sheet.getRange("B2");
 cell.setWrap(true);
 

Parameters

NameTypeDescription
isWrapEnabledBooleanwhether to wrap text or not

Return

Range — the range for chaining


setWraps(isWrapEnabled)

Sets a rectangular grid of word wrap policies (must match dimensions of this range). Cells with wrap enabled (the default) will resize to display their full content. Cells with wrap disabled will display as much as possible in the cell without resizing or running to multiple lines.

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

 // The size of the two dimension array must match up with
 // the size of the range
 var wraps = [
   [ true, true, false ]
 ];

 var range = sheet.getRange("B2:D2");
 range.setWraps(wraps);
 

Parameters

NameTypeDescription
isWrapEnabledObject[][]a two-dimensional array of whether to wrap text in a cell or not

Return

Range — the range for chaining

See also


sort(sortSpecObj)

Sorts the cells in the given range. Sorts the cells in a given range, by column and order specified.

 
var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];
 var range = sheet.getRange("A1:C7");

 // Sorts by the values in the first column (A)
 range.sort(1);

 // Sorts by the values in the second column (B)
 range.sort(2);

 // Sorts descending by column B
 range.sort({column: 2, ascending: false});

 // Sorts descending by column B, then ascending by column A
 // Note the use of an array
 range.sort([{column: 2, ascending: false}, {column: 1, ascending: true}]);

 // For rows that are sorted in ascending order, the "ascending" parameter is
 // optional, and just an integer with the column can be used instead. Note that
 // in general, keeping the sort specification consistent results in more readable
 // code. We could have expressed the earlier sort as:
 range.sort([{column: 2, ascending: false}, 1]);

 // Alternatively, if we wanted all columns to be in ascending order, we would use
 // the following (this would make column 2 ascending)
 range.sort([2, 1]);
 // ... which is equivalent to
 range.sort([{column: 2, ascending: true}, {column: 1, ascending: true}]);
 

Parameters

NameTypeDescription
sortSpecObjObjectthe column(s) to sort by, see example code

Return

Range — the range, for chaining

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.