ranges

a module to work with named ranges in formulas

object ranges;

Example

// add new range named "MYRANGE"
$$("ssheet").ranges.add("MYRANGE", "B2:C2", "Sheet 2");

Related samples

Details

Via this module you can add, name and manipulate cell ranges. For more information refer to the main article.

The ranges module has several methods to work with ranges:

add()

Used to add a new named range globally or to the active/specified sheet. The method takes the following parameters:

  • name (string) - range name
  • range (string) - range of cells to be included
  • scope (string, boolean) - visibility scope. It can be:
    • sheet name, to add a range visible in this sheet only
    • true, to add a range globally (visible throughout Spreadsheet)
    • not defined, to add a range to the active sheet.
// add "MYRANGE" range to Sheet 1
$$("ssheet").ranges.add("MYRANGE", "B2:C2", "Sheet 1");

Optionally, you can state the sheet name in the range.

// adds a range that takes data from Sheet1 to Sheet2
spreadsheet.ranges.add("MYRANGE", "Sheet1!A1:A3", "Sheet2");

getCode()

Used to get the range of cells. The method takes the following parameters:

  • name (string) - range name
  • scope (string, boolean) - visibility scope. It can be:
    • sheet name, to get the local range from this sheet
    • true, to get the global range
    • not defined, to get the local range from the active sheet.
// "Sheet 1!B2:C2"
$$("ssheet").ranges.getCode("MYRANGE", "Sheet 1");

getRanges()

Used to get all the existing named ranges. The method takes a single parameter:

  • scope (string, boolean) - visibility scope. It can be:
    • sheet name, to get all ranges visible in this sheet
    • true, to get the global ranges only
    • not defined, to get all ranges visible in the active sheet.
// [{name:"K",range:"Sheet 1!C3:D3", global: true},...]
$$("ssheet").ranges.getRanges("Sheet 1");

The flag global in the range object shows whether the range is visible globally.

remove()

Used to delete a specified named range. The method takes the following parameters:

  • name (string) - range name
  • scope (string, boolean) - visibility scope. It can be:
    • sheet name, to remove a local range from this sheet
    • true, to remove a global range
    • not defined, to remove a local range from the active sheet

To remove the range from the active sheet, do not pass the 2nd parameter.

// remove a global range named "globalRange"
$$("ssheet").ranges.remove("globalRange", true);

clear()

Used to remove all global or local named ranges. The method takes a single parameter:

  • scope (string, boolean) - scope to delete ranges from:
    • sheet name, to remove all local ranges from it
    • true, to remove all global ranges from Spreadsheet
    • not defined, to remove all local ranges from the active sheet.
// deletes all ranges from Sheet 3
$$("sheet").ranges.clear("Sheet 3");

parse()

Used to parse new ranges. Unlike the add() method, parse() adds several ranges at once. The method takes the following parameters:

  • data (array) - a 2D array where each inner array can include the following elements:
    • name - name for the range
    • range - cells to be included in the range
    • global - to parse the range globally.
  • scope (string, boolean) - visibility scope. It can be:
    • sheet name, to parse local ranges to it
    • true, to parse all the ranges globally
    • not defined, to parse local ranges to the active sheet.
// parse new ranges to Sheet 2
$$("sheet").ranges.parse([
  ["firstRange", "A1:A3"], //local range for Sheet 2
  ["secondRange", "B3:B7", true] //global range
], "Sheet 2");

Note that you can define ranges with sheet name ("Sheet1!C1:C5") or without ("C1:C5"). If a range is defined without a sheet name, it takes data from:

  • the particular sheet, if specified by the 2nd element of the parse method
  • the active sheet, otherwise.

Spreadsheet stores ranges together with the sheet name, so getCode(), getRanges() and serialize() return them in the "Sheet1!C1:C5" form.

serialize()

Used to get array(s) with range data of a specified sheet.

  • scope (string, boolean) - visibility scope. It can be:
    • sheet name, to serialize all ranges visible in this sheet
    • true, to serialize the global ranges only
    • not defined, to serialize all ranges visible in the active sheet.
$$("sheet").ranges.serialize("Sheet 1");
/*
The method returns a 2D array like:
[
    ["firstRange", "Sheet 1!A1:E3", false],
    ["secondRange", "Sheet 1!B2:C2", false],
]
*/

The third element of the resulting array defines whether the range is visible globally.

Back to top
Join Our Forum
We've retired comments here. Visit our forum for faster technical support, connect with other developers, and share your feedback there.