Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://developers.google.com/apps-script/reference/spreadsheet/range below:

Class Range | Apps Script

Stay organized with collections Save and categorize content based on your preferences.

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

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

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

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

Range — This range, for chaining.

Sets the specified cell as the current cell.

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

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

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

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

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

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

Range — This range, for chaining.

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

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

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

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

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

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

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

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

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

Banding — The new banding.

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

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

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

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

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

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

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

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

Banding — The new banding.

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

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

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

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

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

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

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

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

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

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

Banding — The new banding.

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

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

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

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

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

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

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

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

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

Banding — The banding.

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

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

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

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

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

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

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

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

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

Banding — The new banding.

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

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

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

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

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

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

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

Banding — The new banding.

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

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

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

const sheet = SpreadsheetApp.getActiveSheet();

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

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

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

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

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

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

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

const sheet = SpreadsheetApp.getActiveSheet();

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

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

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

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

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

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

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

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

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

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

Range — This range, for chaining.

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

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

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

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

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

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

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

Boolean — true if the user has permission to edit every cell in the range; false otherwise.

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

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

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

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

Range — This range, for chaining.

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

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

Clears the range of contents and formats.

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

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

Range — This range, for chaining.

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

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

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

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

Range — This range, for chaining.

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

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

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

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

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

Range — This range, for chaining.

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

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

Clears the data validation rules for the range.

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

Range — This range, for chaining.

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

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

Clears formatting for this range.

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

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

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

Range — This range, for chaining.

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

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

Clears the note in the given cell or cells.

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

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

Range — This range, for chaining.

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

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

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

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

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

Range — This range, for chaining.

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

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

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

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

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

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

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

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

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

• getGridId()

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• getGridId()

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

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

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

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

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

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

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

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

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

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

DataSourcePivotTable — The newly created data source pivot table.

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

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

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

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

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

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

DataSourceTable — The newly created data source table.

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

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

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

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

// Creates a new filter and applies it to the range A1:C20 on the active sheet.
function createFilter() {
  range.createFilter();
}
// Gets the filter and applies criteria that only shows cells that aren't empty.
function getFilterAddCriteria() {
  const filter = range.getFilter();
  const criteria =
      SpreadsheetApp.newFilterCriteria().whenCellNotEmpty().build();
  filter.setColumnFilterCriteria(2, criteria);
}

Use this method to create filters for

sheets, the default type of sheet. Grid sheets are sheets that aren't connected to a database. To create other types of filters, refer to the following:

• Create a pivot table filter with PivotTable.addFilter(sourceDataColumn, filterCriteria)
• Create a filter for a sheet connected to a database with DataSourceSheet.addFilter(columnName, filterCriteria)
• Create a filter for a pivot table connected to a database with DataSourcePivotTable.addFilter(columnName, filterCriteria)

Filter — The new filter.

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

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

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

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

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

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

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

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

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

PivotTable — The newly created PivotTable.

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

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

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

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

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

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

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

TextFinder — The TextFinder for the range

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

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

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

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

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

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

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

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

Range — This range, for chaining.

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

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

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

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

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

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

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

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

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

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

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

String — The color code of the background.

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

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

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

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

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

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

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

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

Returns the background colors of the cells in the range.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Returns a given cell within a range.

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

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

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

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

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

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

Returns the starting column position for this range.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

DataSourceFormula — The DataSourceFormula for the cell.

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

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

Returns the DataSourceFormulas for the cells in the range.

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

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

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

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

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

DataSourceFormula[] — An array of DataSourceFormulas.

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

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

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

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

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

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

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

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

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

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

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

Gets all the data source tables intersecting with the range.

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

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

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

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

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

DataSourceTable[] — A list of data source tables.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Return the data inside this object as a DataTable.

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

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

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

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

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

DataTable — the data as a datatable.

Return the data inside this range as a DataTable.

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

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

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

DataTable — The data as a datatable.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

String — The displayed value in this cell.

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

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

Returns the rectangular grid of values for this range.

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

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

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

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

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

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

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

Filter — The filter.

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

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

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

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

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

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

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

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

Returns the font colors of the cells in the range.

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

const results = range.getFontColorObjects();

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

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

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

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

Returns the font families of the cells in the range.

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

const results = range.getFontFamilies();

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

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

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

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

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

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

Logger.log(range.getFontFamily());

String — The font family of the cell.

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

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

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

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

Logger.log(range.getFontLine());

String — The font line.

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

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

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

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

const results = range.getFontLines();

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

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

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

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

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

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

Logger.log(range.getFontSize());

Integer — The font size in point size.

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

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

Returns the font sizes of the cells in the range.

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

const results = range.getFontSizes();

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

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

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

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

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

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

Logger.log(range.getFontStyle());

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

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

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

Returns the font styles of the cells in the range.

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

const results = range.getFontStyles();

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

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

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

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

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

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

Logger.log(range.getFontWeight());

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

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

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

Returns the font weights of the cells in the range.

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

const results = range.getFontWeights();

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

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

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

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

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

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

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

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

String — The formula for the cell.

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

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

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

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

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

String — The formula in R1C1 notation.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Logger.log(range.getGridId());

Integer — The grid ID of the parent sheet.

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

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

Returns the height of the range.

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

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

Integer — The height of the range.

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

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

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

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

Logger.log(range.getHorizontalAlignment());

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

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

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

Returns the horizontal alignments of the cells in the range.

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

const results = range.getHorizontalAlignments();

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

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

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

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

Returns the end column position.

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

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

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

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

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

Returns the end row position.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Returns the note associated with the given range.

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

Logger.log(range.getNote());

String — The note associated with the given cell.

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

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

Returns the notes associated with the cells in the range.

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

const results = range.getNotes();

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

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

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

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

Returns the number of columns in this range.

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

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

Integer — The number of columns in this range.

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

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

Returns the number of rows in this range.

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

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

Integer — The number of rows in this range.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Integer — The row position of the range.

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

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

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

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

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

Integer — The row position of the range.

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

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

• getRow()

Returns the sheet this range belongs to.

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

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

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

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

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

Sheet — The sheet that this range belongs to.

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

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

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

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

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

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

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

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

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

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