Conditional Cells Formatting

You can specify particular conditions for cell formatting based on comparison of a cell value with entered values.

Formatting can be applied to a selected range of cells as well.

Each condition for a cell formatting consists of 5 parameters:

• the row id
• the column id
• the comparison mode. Includes the following values:
  • greater
  • less
  • greater or equal
  • less or equal
  • equal
  • not equal
  • between
  • not between
  • contains
  • not contains
  • begins with
  • not begins with
  • ends with
  • not ends with
• the number to compare the cell value with
• the CSS style that will be applied to the cell if its value complies with the condition

The ways of specifying conditions for formatting of cells are described in the section Conditional Formatting API.

Related sample:  Conditional styling

How to Store CSS Styles Used in Conditions

Firstly, you need to specify all the CSS styles you want to use for conditional formatting in the style section of your page:

<style>
    .custom_bold {
        font-weight: bold;
    }
    .custom_color {
        color: red
    }
    .custom_bgcolor {
        background: #f9cb9c;
    }
    ...
</style>

Then, place all the necessary condition styles into the conditionStyle config. It is an array that contains a set of styles that will be applied to a cell, if its value corresponds to a particular condition.

Each element of the array presents an object with two properties:

• name - (string) the name of the condition style
• css - (string) the name of the corresponding CSS style

conditionStyle:[
    {name: 'bold', css: "custom_bold"},
    {name: 'italic', css: "custom_italic"},
    {name: 'red', css: "custom_color"},
    {name: 'highlight', css: "custom_bgcolor"},
    {name: 'green', css: "custom_less"}
]

Conditional Formatting API

You can set conditions for cells' formatting directly in a data source.

For this purpose, use the conditions module of the data object. You can specify a set of conditions in one array:

data.conditions = [
    [3,6,"greater",100,"custom_bgcolor"],
    [3,7,"less",1000,"custom_less"]
];

The conditions collection also allows you to manage cells formatting:

• add/delete cell formatting
• get cell formatting conditions
• remove formatting from all spreadsheet cells at once

Check the full list of API methods available for the conditions module.

Add formatting to a cell

You can add formatting to a cell by using the conditions.add() method. The method takes the following parameters:

• rowId - (number) the row id
• columnId - (number) the column id
• condition - (string) the comparison mode that will be applied (">","<","=","!=" (not equal),"<>" (between))
• value - (number/string) the value, or a reference to a cell the value of which should be compared to the value of the specified cell
• style - (string) the style that will be applied to the cell, if its value meets the condition.

$$("ssheet").conditions.add(3,6,"greater",100,"custom_bgcolor");
 
// or
$$("ssheet").conditions.add(4,6,"less","=B4","custom_bgcolor");

Delete formatting from a cell

To remove the applied formatting from a particular cell, use the conditions.remove() method. You need to pass the following parameters to it:

• rowId - (number) the row id
• columnId - (number) the column id.

$$("ssheet").conditions.remove(rowId,columnId);

Get the conditions of cell formatting

It is possible to get the conditions according to which formatting has been applied to a cell. For this, pass the row id and the column id of a cell into the conditions.get() method. Optionally, you can also pass the name of the sheet as the third parameter.

The method will return an array with three elements:

• condition - (string) the applied comparison mode ("greater","less","equal","not equal","between")
• value - (number) the value that the cell value was compared with
• style - (string) the style applied to the cell

var cellStyle = $$("ssheet").conditions.get(3, 6, "Sheet1"); 
// -> [">",100,"custom_bgcolor"]

Clear all formatting

You can clear formatting from all cells in the spreadsheet. Use the conditions.clear() method for this purpose:

$$("ssheet").conditions.clear();

You can pass the name of the sheet as a parameter of the method.

Related sample:  Spreadsheet: Conditional Formats

Back to top