Conditional Formatting

Conditional formatting lets you format cells so that their appearance changes dynamically according to the value they contain, or to values in other cells. There are many possible applications of conditional formatting, including uses such as:

  • Highlight cells above some threshold (For example, using bold text for all transactions over $2000)
  • Format cells so their color varies with their value (For example, applying a more intense red background as the amount over $2000 increases)
  • Dynamically format cells based on the content of other cells (For example, highlighting the address of properties whose "time on market" field is > 90 days)

You can even format cells based on their value with respect to that of other cells. For example, you could format a range of cells based on their value compared to the median value of the range:

Screenshot of formatting to highlight values above/below median

In this example, cells in each row are formatted according to how the value in their age field compares to the median value of all the ages. Rows whose age is above the median have red text, and those below the median have a red background. Two of the rows have a value for age that matches the median age; these cells receive no special formatting. (For the source code that creates this conditional formatting, see the Example below.)

Conditional formatting rules

Conditional formatting is expressed using formatting rules. Each spreadsheet stores a list of these rules, and applies them in the same order as they appear in the list. The Sheets API lets you add, update, and delete these formatting rules.

Each Rule specifies a target range, type of rule, conditions for triggering the rule, and formatting to apply.

Target range — This can be a single cell, a range of cells, or multiple ranges.

Type of rule — There are two categories of rules:

  • Boolean rules apply a format only if specific criteria are met.
  • Gradient rules calculate the background color of a cell, based on the value of the cell.

The conditions that are evaluated, as well as the formats that you can apply, are different for each of these types of rule, as detailed in the following sections.

Boolean rules

Boolean rules define whether or not to apply a specific format, based on a condition that evaluates to True or False. A boolean rule takes the form:

{
  "condition": {
    object(BooleanCondition)
  },
  "format": {
    object(CellFormat)
  },
}

The condition can use one of the built-in condition types, or it can use a custom formula for more complex evaluations.

Built-in rules let you apply formatting according to numeric thresholds, text comparison, or whether a cell is populated. These are always evaluated with respect to the target cell.

Custom formula is a special condition type that lets you apply formatting according to an arbitrary expression, which also allows evaluation of any cell, not just the target cell.

To define the formatting applied by a boolean rule, you use a subset of the CellFormat type to define:

  • Whether or not the text in the cell is bold, italic, or strikethrough
  • The color of the text in the cell
  • The background color of the cell

Gradient rules

Gradient rules define a range of colors that correspond to a range of values. A gradient rule takes the form:

{
  "minpoint": {
    object(InterpolationPoint)
  },
  "midpoint": {
    object(InterpolationPoint)
  },
  "maxpoint": {
    object(InterpolationPoint)
  },
}

Each InterpolationPoint defines a color and its corresponding value. The set of three points thus defines a color gradient.

Managing conditional formatting rules

To create, modify, or delete conditional formatting rules, use the BatchUpdate method with the appropriate request type:

Example

The following example shows how to create the conditional formatting shown in the screenshot at the beginning of this page. For additional examples, see the conditional formatting samples page.

Apps Script

sheets/api/spreadsheet_snippets.gs
var myRange = Sheets.newGridRange();
myRange.sheetId = 0;
myRange.startRowIndex = 0;
myRange.endRowIndex = 11;
myRange.startColumnIndex = 0;
myRange.endColumnIndex = 4;

// Request 1
var rule1ConditionalValue = Sheets.newConditionValue();
rule1ConditionalValue.userEnteredValue = '=GT($D2,median($D$2:$D$11))';

var rule1ConditionFormat = Sheets.newCellFormat();
rule1ConditionFormat.textFormat = Sheets.newTextFormat();
rule1ConditionFormat.textFormat.foregroundColor = Sheets.newColor();
rule1ConditionFormat.textFormat.foregroundColor.red = 0.8;

var rule1Condition = Sheets.newBooleanCondition();
rule1Condition.type = 'CUSTOM_FORMULA';
rule1Condition.values = [rule1ConditionalValue];

var rule1BooleanRule = Sheets.newBooleanRule();
rule1BooleanRule.condition = rule1Condition;
rule1BooleanRule.format = rule1ConditionFormat;

var rule1 = Sheets.newConditionalFormatRule();
rule1.ranges = [myRange];
rule1.booleanRule = rule1BooleanRule;

var request1 = Sheets.newRequest();
var addConditionalFormatRuleRequest1 = Sheets.newAddConditionalFormatRuleRequest();
addConditionalFormatRuleRequest1.rule = rule1;
addConditionalFormatRuleRequest1.index = 0;
request1.addConditionalFormatRule = addConditionalFormatRuleRequest1;

// Request 2
var rule2ConditionalValue = Sheets.newConditionValue();
rule2ConditionalValue.userEnteredValue = '=LT($D2,median($D$2:$D$11))';

var rule2ConditionFormat = Sheets.newCellFormat();
rule2ConditionFormat.textFormat = Sheets.newTextFormat();
rule2ConditionFormat.textFormat.foregroundColor = Sheets.newColor();
rule2ConditionFormat.textFormat.foregroundColor.red = 1;
rule2ConditionFormat.textFormat.foregroundColor.green = 0.4;
rule2ConditionFormat.textFormat.foregroundColor.blue = 0.4;

var rule2Condition = Sheets.newBooleanCondition();
rule2Condition.type = 'CUSTOM_FORMULA';
rule2Condition.values = [rule1ConditionalValue];

var rule2BooleanRule = Sheets.newBooleanRule();
rule2BooleanRule.condition = rule2Condition;
rule2BooleanRule.format = rule2ConditionFormat;

var rule2 = Sheets.newConditionalFormatRule();
rule2.ranges = [myRange];
rule2.booleanRule = rule2BooleanRule;

var request2 = Sheets.newRequest();
var addConditionalFormatRuleRequest2 = Sheets.newAddConditionalFormatRuleRequest();
addConditionalFormatRuleRequest2.rule = rule2;
addConditionalFormatRuleRequest2.index = 0;
request2.addConditionalFormatRule = addConditionalFormatRuleRequest2;

// Batch send the requests
var requests = [request1, request2];
var batchUpdate = Sheets.newBatchUpdateSpreadsheetRequest();
batchUpdate.requests = requests;
var response = Sheets.Spreadsheets.batchUpdate(batchUpdate, spreadsheetId);

Java

sheets/snippets/src/main/java/SpreadsheetSnippets.java
List<GridRange> ranges = Collections.singletonList(new GridRange()
        .setSheetId(0)
        .setStartRowIndex(1)
        .setEndRowIndex(11)
        .setStartColumnIndex(0)
        .setEndColumnIndex(4)
);
List<Request> requests = Arrays.asList(
        new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest()
                .setRule(new ConditionalFormatRule()
                        .setRanges(ranges)
                        .setBooleanRule(new BooleanRule()
                                .setCondition(new BooleanCondition()
                                        .setType("CUSTOM_FORMULA")
                                        .setValues(Collections.singletonList(
                                                new ConditionValue().setUserEnteredValue(
                                                        "=GT($D2,median($D$2:$D$11))")
                                        ))
                                )
                                .setFormat(new CellFormat().setTextFormat(
                                        new TextFormat().setForegroundColor(
                                                new Color().setRed(0.8f))
                                ))
                        )
                )
                .setIndex(0)
        ),
        new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest()
                .setRule(new ConditionalFormatRule()
                        .setRanges(ranges)
                        .setBooleanRule(new BooleanRule()
                                .setCondition(new BooleanCondition()
                                        .setType("CUSTOM_FORMULA")
                                        .setValues(Collections.singletonList(
                                                new ConditionValue().setUserEnteredValue(
                                                        "=LT($D2,median($D$2:$D$11))")
                                        ))
                                )
                                .setFormat(new CellFormat().setBackgroundColor(
                                        new Color().setRed(1f).setGreen(0.4f).setBlue(0.4f)
                                ))
                        )
                )
                .setIndex(0)
        )
);

BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest()
        .setRequests(requests);
BatchUpdateSpreadsheetResponse result = service.spreadsheets()
        .batchUpdate(spreadsheetId, body)
        .execute();
System.out.printf("%d cells updated.", result.getReplies().size());

JavaScript

sheets/snippets/snippets.js
var myRange = {
  sheetId: 0,
  startRowIndex: 1,
  endRowIndex: 11,
  startColumnIndex: 0,
  endColumnIndex: 4,
};
var requests = [{
  addConditionalFormatRule: {
    rule: {
      ranges: [ myRange ],
      booleanRule: {
        condition: {
          type: 'CUSTOM_FORMULA',
          values: [ { userEnteredValue: '=GT($D2,median($D$2:$D$11))' } ]
        },
        format: {
          textFormat: { foregroundColor: { red: 0.8 } }
        }
      }
    },
    index: 0
  }
}, {
  addConditionalFormatRule: {
    rule: {
      ranges: [ myRange ],
      booleanRule: {
        condition: {
          type: 'CUSTOM_FORMULA',
          values: [ { userEnteredValue: '=LT($D2,median($D$2:$D$11))' } ]
        },
        format: {
          backgroundColor: { red: 1, green: 0.4, blue: 0.4 }
        }
      }
    },
    index: 0
  }
}];

var body = {
  requests
};
gapi.client.sheets.spreadsheets.batchUpdate({
  spreadsheetId: spreadsheetId,
  resource: body
}).then((response) => {
  var result = response.result;
  console.log(`${result.replies.length} cells updated.`);
});

Node.js

sheets/snippets/snippets.js
const myRange = {
  sheetId: 0,
  startRowIndex: 1,
  endRowIndex: 11,
  startColumnIndex: 0,
  endColumnIndex: 4,
};
const requests = [{
  addConditionalFormatRule: {
    rule: {
      ranges: [myRange],
      booleanRule: {
        condition: {
          type: 'CUSTOM_FORMULA',
          values: [{userEnteredValue: '=GT($D2,median($D$2:$D$11))'}],
        },
        format: {
          textFormat: {foregroundColor: {red: 0.8}},
        },
      },
    },
    index: 0,
  },
}, {
  addConditionalFormatRule: {
    rule: {
      ranges: [myRange],
      booleanRule: {
        condition: {
          type: 'CUSTOM_FORMULA',
          values: [{userEnteredValue: '=LT($D2,median($D$2:$D$11))'}],
        },
        format: {
          backgroundColor: {red: 1, green: 0.4, blue: 0.4},
        },
      },
    },
    index: 0,
  },
}];

const resource = {
  requests,
};
this.sheetsService.spreadsheets.batchUpdate({
  spreadsheetId,
  resource,
}, (err, response) => {
  if (err) {
    // Handle error.
    console.log(err);
  } else {
    console.log(`${response.replies.length} cells updated.`);
  }
});

PHP

sheets/snippets/src/SpreadsheetSnippets.php
<?php
$myRange = [
    'sheetId' => 0,
    'startRowIndex' => 1,
    'endRowIndex' => 11,
    'startColumnIndex' => 0,
    'endColumnIndex' => 4,
];

$requests = [
    new Google_Service_Sheets_Request([
        'addConditionalFormatRule' => [
            'rule' => [
                'ranges' => [ $myRange ],
                'booleanRule' => [
                    'condition' => [
                        'type' => 'CUSTOM_FORMULA',
                        'values' => [ [ 'userEnteredValue' => '=GT($D2,median($D$2:$D$11))' ] ]
                    ],
                    'format' => [
                        'textFormat' => [ 'foregroundColor' => [ 'red' => 0.8 ] ]
                    ]
                ]
            ],
            'index' => 0
        ]
    ]),
    new Google_Service_Sheets_Request([
        'addConditionalFormatRule' => [
            'rule' => [
                'ranges' => [ $myRange ],
                'booleanRule' => [
                    'condition' => [
                        'type' => 'CUSTOM_FORMULA',
                        'values' => [ [ 'userEnteredValue' => '=LT($D2,median($D$2:$D$11))' ] ]
                    ],
                    'format' => [
                        'backgroundColor' => [ 'red' => 1, 'green' => 0.4, 'blue' => 0.4 ]
                    ]
                ]
            ],
            'index' => 0
        ]
    ])
];

$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
    'requests' => $requests
]);
$response = $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);
printf("%d cells updated.", count($response->getReplies()));
return $response;

Python

sheets/snippets/spreadsheet_snippets.py
my_range = {
    'sheetId': 0,
    'startRowIndex': 1,
    'endRowIndex': 11,
    'startColumnIndex': 0,
    'endColumnIndex': 4,
}
requests = [{
    'addConditionalFormatRule': {
        'rule': {
            'ranges': [my_range],
            'booleanRule': {
                'condition': {
                    'type': 'CUSTOM_FORMULA',
                    'values': [{
                        'userEnteredValue':
                            '=GT($D2,median($D$2:$D$11))'
                    }]
                },
                'format': {
                    'textFormat': {
                        'foregroundColor': {'red': 0.8}
                    }
                }
            }
        },
        'index': 0
    }
}, {
    'addConditionalFormatRule': {
        'rule': {
            'ranges': [my_range],
            'booleanRule': {
                'condition': {
                    'type': 'CUSTOM_FORMULA',
                    'values': [{
                        'userEnteredValue':
                            '=LT($D2,median($D$2:$D$11))'
                    }]
                },
                'format': {
                    'backgroundColor': {
                        'red': 1,
                        'green': 0.4,
                        'blue': 0.4
                    }
                }
            }
        },
        'index': 0
    }
}]
body = {
    'requests': requests
}
response = service.spreadsheets() \
    .batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()
print('{0} cells updated.'.format(len(response.get('replies'))))

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb
my_range = {
  sheet_id: 0,
  start_row_index: 1,
  end_row_index: 11,
  start_column_index: 0,
  end_column_index: 4
}
requests = [{
  add_conditional_format_rule: {
    rule: {
      ranges: [my_range],
      boolean_rule: {
        condition: {
          type: 'CUSTOM_FORMULA',
          values: [{ user_entered_value: '=GT($D2,median($D$2:$D$11))' }]
        },
        format: {
          text_format: { foreground_color: { red: 0.8 }}
        }
      }
    },
    index: 0
  }
}, {
  add_conditional_format_rule: {
    rule: {
      ranges: [my_range],
      boolean_rule: {
        condition: {
          type: 'CUSTOM_FORMULA',
          values: [{ user_entered_value: '=LT($D2,median($D$2:$D$11))' }]
        },
        format: {
          background_color: { red: 1, green: 0.4, blue: 0.4 }
        }
      }
    },
    index: 0
  }
}]
body = {
  requests: requests
}
batch_update = Google::Apis::SheetsV4::BatchUpdateSpreadsheetRequest.new
batch_update.requests = requests
result = service.batch_update_spreadsheet(spreadsheet_id, batch_update)
puts "#{result.replies.length} cells updated."

Envoyer des commentaires concernant…

Besoin d'aide ? Consultez notre page d'assistance.