Highlight rows

Highlight worksheet rows to bring attention to important data in your worksheets, or to values that need review.

You can configure a worksheet row to be highlighted automatically with a color if a field's value in that raw matches predefined criteria. For example, you might want to highlight a row when a specific field's value exceeds or falls below a certain number.

Before you begin

This feature is designed for use only by configuration teams. You can configure this feature only through an API request.

Variables definition

In this topic, we use variables instead of real values to define information such as an authorization token or API host. To run a request, replace the following variables with real values:

• {{ApiHost}}—API host for your environment. For details, read Defining the {{ApiHost}} variable .
• {{OAuth2Host}}—OAuth host for your environment. Use this value in the X-Forwarded-Host header when sending a request. For details, read Defining the {{OAuth2Host}} variable .
• {{OAuthToken}}—Keyword and token required for OAuth 2.0 authentication for your environment. Use this value in the Authorization header when sending a request. For details, read Defining the {{OAuthToken}} variable .

Procedure

To conditionally highlight worksheet rows, use a style object when creating or updating a worksheet through an API request.

Request syntax

• To create a worksheet, send a request using one of the following syntax:

  • POST {{ApiHost}}/api/v3/workbook/[workbookID]/worksheet
  • POST {{ApiHost}}/api/v4/workbook/[workbookID]/worksheet

• To update a worksheet, send a request using one of the following syntax:

  • PATCH {{ApiHost}}/api/v3/workbook/[workbookID]/worksheet/[worksheetID]
  • PATCH {{ApiHost}}/api/v4/workbook/[workbookID]/worksheet/[worksheetID]

URI request parameters

A request to create or update a worksheet uses one or both of the following URI parameters.

• [workbookID]—Unique ID of the workbook that contains your worksheet.
• [worksheetID]—Unique ID of your worksheet.

Request headers

A request to create or update a worksheet must include the following headers:

Authorization: {{OAuthToken}}
X-Forwarded-Host: {{OAuth2Host}}
Content-Type: application/json

Request body

A request to create or update a worksheet must include a row JSON document body with a style object. The style object defines the criteria to highlight worksheet rows.

For details about the style object syntax, read the following section.

style object

Use the following syntax for the style object:

{
    "style": {
        "row": {
            "background": [
                {
                    "color": "string",
                    "conditions": [
                        {
                            "field": "string",
                            "operator": "string",
                            "value": "string"
                        }
                    ]
                }
            ]
        }
    }
}

In the style object, define the following data in the JSON format:

• row—Object. Can be empty. Contains the background parameter.

• background—Array of one or multiple objects. Can be empty. Each object defines a highlighting rule. An object that is higher in the script takes priority over objects below. This means that IQ Interface applies the first matching rule to a row and doesn't check any other rules against it.
  Each object in the background array consists of the following key-value pairs:

  • color—String. Defines the HTML color code to use for highlighting a row. Supported colors are defined in the Supported colors section.

  • conditions—Array of one or multiple objects. Each object defines a condition to highlight a row and consists of the following key-value pairs:

    • field—String. System name of the worksheet column.
    • operator—String. Defines the validation type for the column value. Supported operators are defined in the Supported operators section.
    • value—String. Column value.
    • min—Integer. Minimal column value. Use this parameter only if you use the be or nb operator. If you use this parameter, you must also use the max parameter and skip the value parameter.
    • max—Integer. Maximal column value. Use this parameter only if you use the be or nb operator. If you use this parameter, you must also use the min parameter and skip the value parameter.
    Tip

    Objects in the conditions array are combined using the AND logic. This means that IQ Interface applies a rule to a row only if all conditions specified in that rule are met. If any condition fails, IQ Interface doesn't apply this rule to a row and starts verifying the next rule in the background array.

Supported colors

The style object accepts the following HTML color codes:

Supported operators

The style object accepts condition operators listed in the table below.

Sample style objects

• Sample style object with a condition for a string type column:

  {
      "style": {
          "row": {
              "background": [
                  {
                      "color": "#FFB7A3",
                      "conditions": [
                          {
                              "field": "Region",
                              "operator": "bw",
                              "value": "Col"
                          }
                      ]
                  }
              ]
          }
      }
  }
  

• Sample style object with a condition for a datetime type column:

  {
      "style": {
          "row": {
              "background": [
                  {
                      "color": "#FFB7A3",
                      "conditions": [
                          {
                              "field": "OrderDate",
                              "operator": "be",
                              "min": "1725375411000",
                              "max": "1725461811000"
                          }
                      ]
                  }
              ]
          }
      }
  }