ranges

a module to work with named ranges in formulas

Example

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

Related samples

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 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 sheet name in the range.

// adds rangethat takes data from Sheet 1 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.

// "B2:C2"
$$("ssheet").ranges.getCode("MYRANGE", "Sheet 1");

getRanges()

Used to get all the existing named ranges. The method takes the only 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:"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 the only 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 the Sheet 3
$$("sheet").ranges.clear("Sheet 3");

parse()

Used to parse new ranges. Comparing with the add() method, parse() is used to add more than 1 ranges. The method takes the following parameters:

• data (array) - 2D array where each inner array can include 3 elements:
  • name - name for the range
  • range - cells to be included to the range
  • global - to parse range globally.
• scope (string, boolean) - visibility scope. It can be:
  • sheet name, to parse local ranges to it
  • 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 range is defined without a sheet name it will take data from:

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

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", "A1:E3", false],
    ["secondRange", "B2:C2", false],
]
*/

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

Back to top