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

Advanced parameters

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

See also

• getGridId()

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

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

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

Advanced parameters

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

See also

• getGridId()

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

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., '#ffffff').

 
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., '#ffffff').

 
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

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

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 (like '#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 (like '#ffffff' or 'white')

getFontColors()

Returns the font colors of the cells in the range in CSS notation (like '#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 line style of the cell in the top-left corner of the range ('underline', 'line-through', or 'none').

 
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 line style of the cells in the range ('underline', 'line-through', or 'none').

 
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' or 'normal' 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

• getRow()

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

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

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

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

Return

Range — the range for chaining

setBackground(color)

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

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

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

Parameters

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

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 (like '#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

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

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

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

Return

Range — the range, for chaining

setFontColor(color)

Sets the font color in CSS notation (like '#ffffff' or 'white').

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

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

Parameters

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 (like '#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

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

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

Return

Range — the range for chaining

setFontLine(fontLine)

Sets the line style of the given range ('underline', 'line-through', or 'none').

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

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

Parameters

Return

Range — the range for chaining

setFontLines(fontLines)

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

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

 // The size of the two-dimensional array must match the size of the range.
 var fontLines = [
   ["underline", "line-through", "none"]
 ];

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

Parameters

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

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-dimensional array must match the size of the range.
 var fontSizes = [
   [16, 20, 24]
 ];

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

Parameters

Return

Range — the range for chaining

setFontStyle(fontStyle)

Set the font style for the given range ('italic' or 'normal').

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

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

Parameters

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-dimensional array must match the size of the range.
 var fontStyles = [
   ["italic", "normal"]
 ];

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

Parameters

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

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-dimensional array must match the size of the range.
 var fontStyles = [
   [ "bold", "bold", "normal" ]
 ];

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

Parameters

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

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

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-dimensional array must match 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

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-dimensional array must match 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

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

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-dimensional array must match the size of the range.
 var horizontalAlignments = [
   [ "left", "right", "center" ]
 ];

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

Parameters

Return

Range — the range for chaining

See also

• setHorizontalAlignment(alignment)

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

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-dimensional array must match 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

Return

Range — the range for chaining

See also

• setNote(note)

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

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-dimensional array must match the size of the range.
 var formats = [
   [ "0.000", "0,000,000", "$0.00" ]
 ];

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

Parameters

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

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-dimensional array must match the size of the range.
 var values = [
   [ "2.000", "1,000,000", "$2.99" ]
 ];

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

Parameters

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

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-dimensional array must match the size of the range.
 var alignments = [
   [ "top", "middle", "bottom" ]
 ];

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

Parameters

Return

Range — the range for chaining

See also

• setVerticalAlignment(alignment)

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

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-dimensional array must match the size of the range.
 var wraps = [
   [ true, true, false ]
 ];

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

Parameters

Return

Range — the range for chaining

See also

• setWrap(isWrapEnabled)

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

Return

Range — the range, for chaining