Action: Create Conditional Formatting Rule

Google Sheets Operation

The Create Conditional Formatting Rule action applies a new conditional formatting rule to a specified range.

Example User Story

I want to create a conditional formatting rule in Google Sheets so that I can visually highlight cells that meet specific criteria.

Fields

Field Name	Field Type	Required	Description
spreadsheetId	String	Yes	The spreadsheet to apply the rule to.
sheetId	Integer	Yes	Sheet tab ID (0 = first sheet).
startRowIndex	Integer	Yes	First row (0-based, inclusive).
endRowIndex	Integer	Yes	Last row (0-based, exclusive).
startColumnIndex	Integer	Yes	First column (0-based, inclusive).
endColumnIndex	Integer	Yes	Last column (0-based, exclusive).
conditionType	String	Yes	Condition type (e.g. NUMBER_GREATER_THAN).
conditionValues	String	No	Value(s) for the condition.
backgroundColorRed	Float	No	Red component of background color (0.0–1.0).
backgroundColorGreen	Float	No	Green component of background color (0.0–1.0).
backgroundColorBlue	Float	No	Blue component of background color (0.0–1.0).
textColorRed	Float	No	Red component of text color (0.0–1.0).
textColorGreen	Float	No	Green component of text color (0.0–1.0).
textColorBlue	Float	No	Blue component of text color (0.0–1.0).
ruleIndex	Integer	No	Rule priority (0 = highest). Default: 0.

Related Connections

Google Sheets

Related Automations

Example link

Code (Apache v2.0): Operation

{
  "handle": "create-conditional-formatting-rule",
  "state": "inDev",
  "meta": {
    "short": "Create Conditional Formatting Rule",
    "description": "Creates a new conditional formatting rule for a specified range in a Google Sheet."
  },
  "input": [
    { "name": "spreadsheetId",   "type": "String",  "required": true,  "meta": { "description": "The spreadsheet to apply the rule to." } },
    { "name": "sheetId",            "type": "Integer", "required": true,  "meta": { "description": "The ID of the sheet tab (0 = first sheet)." } },
    { "name": "startRowIndex",      "type": "Integer", "required": true,  "meta": { "description": "First row of the range (0-based, inclusive)." } },
    { "name": "endRowIndex",        "type": "Integer", "required": true,  "meta": { "description": "Last row of the range (0-based, exclusive)." } },
    { "name": "startColumnIndex",   "type": "Integer", "required": true,  "meta": { "description": "First column of the range (0-based, inclusive)." } },
    { "name": "endColumnIndex",     "type": "Integer", "required": true,  "meta": { "description": "Last column of the range (0-based, exclusive)." } },
    {
      "name": "conditionType",
      "type": "String",
      "required": true,
      "meta": {
        "description": "The type of condition.",
        "options": ["TEXT_CONTAINS", "TEXT_NOT_CONTAINS", "TEXT_STARTS_WITH", "TEXT_ENDS_WITH", "NUMBER_GREATER_THAN", "NUMBER_LESS_THAN", "NUMBER_BETWEEN", "DATE_AFTER", "DATE_BEFORE", "CUSTOM_FORMULA"]
      }
    },
    { "name": "conditionValues",    "type": "String",  "required": false, "meta": { "description": "Value(s) for the condition (e.g. a number, text string, or formula)." } },
    { "name": "backgroundColorRed", "type": "Float",   "required": false, "meta": { "description": "Red component of background color (0.0–1.0). Default: 1.0" } },
    { "name": "backgroundColorGreen","type": "Float",  "required": false, "meta": { "description": "Green component of background color (0.0–1.0). Default: 1.0" } },
    { "name": "backgroundColorBlue","type": "Float",   "required": false, "meta": { "description": "Blue component of background color (0.0–1.0). Default: 1.0" } },
    { "name": "textColorRed",       "type": "Float",   "required": false, "meta": { "description": "Red component of text color (0.0–1.0). Default: 0.0" } },
    { "name": "textColorGreen",     "type": "Float",   "required": false, "meta": { "description": "Green component of text color (0.0–1.0). Default: 0.0" } },
    { "name": "textColorBlue",      "type": "Float",   "required": false, "meta": { "description": "Blue component of text color (0.0–1.0). Default: 0.0" } },
    { "name": "ruleIndex",          "type": "Integer", "required": false, "meta": { "description": "Priority index for the rule (0 = highest priority). Default: 0" } }
  ],
  "output": [],
  "steps": [
    {
      "type": "http",
      "http": {
        "method": "POST",
        "path": "{{spreadsheetId}}:batchUpdate",
        "headers": { "Content-Type": "application/json" },
        "bodyTemplate": {
          "value": "{\"requests\": [{\"addConditionalFormatRule\": {\"rule\": {\"ranges\": [{\"sheetId\": {{sheetId}}, \"startRowIndex\": {{startRowIndex}}, \"endRowIndex\": {{endRowIndex}}, \"startColumnIndex\": {{startColumnIndex}}, \"endColumnIndex\": {{endColumnIndex}}}], \"booleanRule\": {\"condition\": {\"type\": \"{{conditionType}}\", \"values\": [{\"userEnteredValue\": \"{{conditionValues}}\"}]}, \"format\": {\"backgroundColor\": {\"red\": {{backgroundColorRed}}, \"green\": {{backgroundColorGreen}}, \"blue\": {{backgroundColorBlue}}}, \"textFormat\": {\"foregroundColor\": {\"red\": {{textColorRed}}, \"green\": {{textColorGreen}}, \"blue\": {{textColorBlue}}}}}}}, \"index\": {{ruleIndex}}}}]}",
          "placeholders": [
            { "name": "sheetId",              "type": "Integer", "description": "The ID of the sheet tab (0 = first sheet).",                    "required": true },
            { "name": "startRowIndex",        "type": "Integer", "description": "First row of the range (0-based, inclusive).",                  "required": true },
            { "name": "endRowIndex",          "type": "Integer", "description": "Last row of the range (0-based, exclusive).",                   "required": true },
            { "name": "startColumnIndex",     "type": "Integer", "description": "First column of the range (0-based, inclusive).",               "required": true },
            { "name": "endColumnIndex",       "type": "Integer", "description": "Last column of the range (0-based, exclusive).",                "required": true },
            { "name": "conditionType",        "type": "String",  "description": "The type of condition.",                                        "required": true },
            { "name": "conditionValues",      "type": "String",  "description": "Value(s) for the condition (e.g. a number, text, or formula).", "required": false, "default": "" },
            { "name": "backgroundColorRed",   "type": "Float",   "description": "Red component of background color (0.0–1.0).",                 "required": false, "default": "1.0" },
            { "name": "backgroundColorGreen", "type": "Float",   "description": "Green component of background color (0.0–1.0).",               "required": false, "default": "1.0" },
            { "name": "backgroundColorBlue",  "type": "Float",   "description": "Blue component of background color (0.0–1.0).",                "required": false, "default": "1.0" },
            { "name": "textColorRed",         "type": "Float",   "description": "Red component of text color (0.0–1.0).",                       "required": false, "default": "0.0" },
            { "name": "textColorGreen",       "type": "Float",   "description": "Green component of text color (0.0–1.0).",                     "required": false, "default": "0.0" },
            { "name": "textColorBlue",        "type": "Float",   "description": "Blue component of text color (0.0–1.0).",                      "required": false, "default": "0.0" },
            { "name": "ruleIndex",            "type": "Integer", "description": "Priority index for the rule (0 = highest priority).",           "required": false, "default": "0" }
          ]
        }
      }
    }
  ]
}