Manipulating Rows and Columns

Working with Columns

Hiding/showing columns

You can hide and show a column on the sheet, using the hideColumn method. It takes the following parameters:

• columnId - (number, array) the column id(s)
• state - (boolean) true - to hide the column, false - to show it
• page - (string) optional, the name of the sheet. If not specified, the method is applied to the current sheet

$$("ssheet").hideColumn(2, true, "Sheet1");

If the columnId parameter isn't specified, the method hides the selected column. The method can also accept an array of ids as the first parameter. In this case all the columns from the range will be hidden:

$$("ssheet").hideColumn([2, 5], true, "Sheet1");

Inserting a new column

To insert a new column to the left of the specified one, use the insertColumn method. The method takes the ID of the column, before which a new column will be inserted as a parameter.

$$("ssheet").insertColumn(3);

If the column id isn't specified, a new column will be inserted before the selected one.

Deleting a column

You can also delete a particular column or a range of columns by passing their id(s) to the deleteColumn method.

$$("ssheet").deleteColumn(3);
// or
$$("ssheet").deleteColumn([1, 3]);

Without the parameter, the method deletes the selected column.

Getting the column config

It's possible to get the configuration object of a column by using the getColumn. You need to pass the column id as a parameter. You can also pass the name of the sheet as the second parameter, if needed.

var config = $$("ssheet").getColumn(5, "Sheet1");

The method returns the column's configuration object.

Checking the column's visibility

You can check the visibility of a column by using the isColumnVisible method. The method takes the id of a column as a parameter:

var isVisible = $$("ssheet").isColumnVisible(2);

The method returns true, if the column is visible and false if it's hidden.

Working with Rows

Hiding/showing rows

You can hide and show a row in the sheet, using the hideRow method. It takes the following parameters:

• rowId - (number, array) the row id(s)
• state - (boolean) true - to hide the row, false - to show it
• page - (string) optional, the name of the sheet. If not specified, the method is applied to the current sheet

$$("ssheet").hideRow(3, true, "Sheet1");

If the rowId parameter isn't specified, the method hides the selected row. The method can also accept an array of ids as the first parameter. In this case all the rows from the range will be hidden:

$$("ssheet").hideRow([3, 7], true, "Sheet1");

Inserting a new row

To insert a new row above the specified one, use the insertRow method. The method takes the ID of the row, above which a new row will be inserted as a parameter.

$$("ssheet").insertRow(3);

If the row id isn't specified, a new row will be inserted above the selected one.

Deleting a row

You can also delete a particular row or a range of rows by passing their id(s) to the deleteRow method.

$$("ssheet").deleteRow(3);
// or
$$("ssheet").deleteRow([1, 3]);

Without the parameter, the method deletes the selected row.

Getting the row's data

It's possible to get the data of a row by using the getRow. You need to pass the row id as a parameter. You can also pass the name of the sheet as the second parameter, if needed.

var data = $$("ssheet").getRow(5, "Sheet1");

The method returns an object with the row's data.

Checking the row's visibility

You can check the visibility of a row by using the isRowVisible method. The method takes the id of a row as a parameter:

var isVisible = $$("ssheet").isRowVisible(2);

The method returns true, if the row is visible and false if it's hidden.

Frozen Rows and Columns

Freezing Columns

You can "freeze" a column with data to make it always visible on the screen independently of horizontal scrolling.

To freeze columns, use the freezeColumns method. Columns are fixed starting from the very left one, so you should pass the amount of columns to freeze, starting from 0 (zero) as the first parameter. You can also pass the name of the sheet as the second parameter, if needed:

$$("ssheet").freezeColumns(2, "Sheet1");

If invoked without the first parameter, the method will unfreeze the frozen columns, if any.

Freezing Rows

There is also a possibility to freeze rows, to make some of them remain at the very top of the sheet.

To freeze rows, apply the freezeRows method. As the first parameter you should pass the number of rows that should be frozen, starting from 0. You can also pass the name of the sheet as the second parameter, if needed:

$$("ssheet").freezeRows(3, "Sheet1");

Without the first parameter the method unfreezes the frozen rows, if there are any.

Grouping Rows and Columns

Grouping allows you to organize rows or columns into collapsible ranges, making it easier to navigate and manage large sheets. Groups can be nested up to 8 levels deep.

Related sample:  Spreadsheet: Grouping API

Data structure

Groups are stored inside table.groups of each sheet's content:

{
  "table": {
    "groups": {
      "row": [{ "start": 3, "end": 8, "level": 1, "open": true }],
      "column": [{ "start": 4, "end": 7, "level": 0, "open": false }]
    }
  }
}

Each group object is an array of objects (groups) which has the following properties:

• start - (number) the starting column/row of the group
• end - (number) the ending column/row of the group
• level - (number) the nesting level of the group
• open - (boolean) defines whether the group is open

Groups support up to 8 nesting levels; the level field is zero-indexed. Groups are open by default.

Grouping Columns

Adding a column group

You can group a range of columns using the addColumnGroup method. It takes the following parameters:

• columnId - (number,array) optional, a column ID or a range of column IDs [start, end]
• page - (string) optional, a page name

$$("ssheet").addColumnGroup([2, 5], "Sheet1");

If the columnId parameter isn't specified, the method adds a group for the selected column(s).

Removing a column group

To remove one level of grouping from a column range, use the removeColumnGroup method. It takes the following parameters:

• columnId - (number,array) optional, a column ID or a range of column IDs [start, end]
• page - (string) optional, a page name

$$("ssheet").removeColumnGroup([2, 5], "Sheet1");

If the columnId parameter isn't specified, the method removes a grouping level from the selected column(s).

Expanding and collapsing column groups

To expand a column group, use the openColumnGroup method. To collapse it, use the closeColumnGroup method. Both methods accept the same parameters:

• columnId - (number,array) optional, a column ID or a range of column IDs [start, end]
• page - (string) optional, a page name

$$("ssheet").openColumnGroup(3, "Sheet1");
$$("ssheet").closeColumnGroup(3, "Sheet1");

If the columnId parameter isn't specified, the method operates on the group for the selected column(s).

Getting column groups

To get all column groups of a sheet, use the getColumnGroups method. You can optionally pass the page name as a parameter:

const columnGroups = $$("ssheet").getColumnGroups();

The method returns a snapshot array of group objects, each having the following properties: start, end, level, and open.

Grouping Rows

Adding a row group

You can group a range of rows using the addRowGroup method. It takes the following parameters:

• rowId - (number,array) optional, a row ID or a range of row IDs: [start, end]
• page - (string) optional, a page name

$$("ssheet").addRowGroup([3, 7], "Sheet1");

If the rowId parameter isn't specified, the method adds a group for the selected row(s).

Removing a row group

To remove one level of grouping from a row range, use the removeRowGroup method. It takes the following parameters:

• rowId - (number,array) optional, a row ID or a range of row IDs: [start, end]
• page - (string) optional, a page name

$$("ssheet").removeRowGroup([3, 7], "Sheet1");

If the rowId parameter isn't specified, the method removes a grouping level from the selected row(s).

Expanding and collapsing row groups

To expand a row group, use the openRowGroup method. To collapse it, use the closeRowGroup method. Both methods accept the same parameters:

• rowId - (number,array) optional, a row ID or a range of row IDs: [start, end]
• page - (string) optional, a page name

$$("ssheet").openRowGroup(5, "Sheet1");
$$("ssheet").closeRowGroup(5, "Sheet1");

If the rowId parameter isn't specified, the method operates on the group for the selected row(s).

Getting row groups

To get all row groups of a sheet, use the getRowGroups method. You can optionally pass the page name as a parameter:

const rowGroups = $$("ssheet").getRowGroups();

The method returns a snapshot array of group objects, each having the following properties: start, end, level, and open.

Hotkeys

• Alt+Shift+Right - Group: opens a dialog to choose Row or Column, then groups the current selection.
• Alt+Shift+Left - Ungroup: opens a dialog to choose Row or Column, then ungroups the current selection.
• Alt+Shift+Up - Collapse/Close: collapses a group (or multiple groups) at the current selection. Works for both dimensions (rows/columns) at once.
• Alt+Shift+Down - Expand/Open: expands a group (or multiple groups) at the current selection. Works for both dimensions (rows/columns) at once.

Back to top