Эта страница переведена с помощью Cloud Translation API.

Условное форматирование

Условное форматирование позволяет форматировать ячейки таким образом, чтобы их внешний вид динамически изменялся в зависимости от содержащегося в них значения или значений в других ячейках. Существует множество возможных применений условного форматирования, включая следующие:

Выделите ячейки, сумма которых превышает определенный порог (например, выделите жирным шрифтом все транзакции на сумму более 2000 долларов).
Отформатируйте ячейки так, чтобы их цвет менялся в зависимости от значения (например, применяйте более насыщенный красный фон по мере увеличения суммы свыше 2000 долларов).
Динамическое форматирование ячеек на основе содержимого других ячеек (например, выделение адреса объектов недвижимости, у которых поле «время на рынке» превышает 90 дней).

Вы даже можете форматировать ячейки на основе их значений и значений других ячеек. Например, вы можете отформатировать диапазон ячеек, сравнивая их значения со средним значением этого диапазона:

Форматирование для выделения значений выше или ниже медианного возраста.

Рисунок 1. Форматирование для выделения значений выше или ниже медианного возраста.

В этом примере ячейки в каждой строке форматируются в соответствии со значением в столбце age по сравнению со средним значением всех возрастов. Строки, возраст которых выше среднего, имеют красный текст, а строки, возраст которых ниже среднего, — красный фон. В двух строках значение age совпадает со средним возрастом (48), и эти ячейки не получают специального форматирования. (Исходный код, создающий это условное форматирование, см. в примере ниже.)

Правила условного форматирования

Условное форматирование выражается с помощью правил форматирования . Каждая электронная таблица хранит список этих правил и применяет их в том же порядке, в котором они указаны в списке. API Google Sheets позволяет добавлять, обновлять и удалять эти правила форматирования.

Каждое правило задает целевой диапазон, тип правила, условия для его срабатывания и любое применяемое форматирование.

Целевой диапазон — это может быть одна ячейка, диапазон ячеек или несколько диапазонов.

Типы правил — Существует две категории правил:

Логические правила применяют формат только при соблюдении определенных критериев.
Правила градиента вычисляют цвет фона ячейки на основе значения этой ячейки.

Условия, которые оцениваются, и форматы, которые вы можете применять, различаются для каждого из этих типов правил, как подробно описано в следующих разделах.

Логические правила

Класс BooleanRule определяет, следует ли применять определенный формат, на основе условия BooleanCondition , которое принимает значение true или false . Правило Boolean имеет следующий вид:

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

В качестве условия можно использовать встроенный ConditionType , или же пользовательскую формулу для более сложных вычислений.

Встроенные типы позволяют применять форматирование в соответствии с числовыми порогами, сравнением текста или заполненностью ячейки. Например, NUMBER_GREATER означает, что значение ячейки должно быть больше значения условия. Правила всегда оцениваются по отношению к целевой ячейке.

Пользовательская формула — это особый тип условия, позволяющий применять форматирование в соответствии с произвольным выражением, при этом допускается оценка любой ячейки, а не только целевой. Формула условия должна возвращать значение true .

Для определения форматирования, применяемого логическим правилом, используется подмножество типа CellFormat , которое определяет:

Выделение текста в ячейке жирным шрифтом, курсивом или зачеркиванием.
Цвет текста в ячейке.
Цвет фона ячейки.

Используйте только перечисленные выше функции форматирования. Хотя тип CellFormat может выражать и другие виды форматирования, включая подчеркивание, выравнивание или границы, попытка использовать их в правиле условного форматирования приведет к ошибке 400 invalid request .

Правила градиента

Класс GradientRule определяет диапазон цветов, соответствующих диапазону значений. Правило градиента имеет следующий вид:

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

Каждая InterpolationPoint определяет цвет и соответствующее ему значение. Набор из трех точек определяет цветовой градиент.

Управление правилами условного форматирования

Для создания, изменения или удаления правил условного форматирования используйте метод spreadsheets.batchUpdate с соответствующим типом запроса:

Добавьте правила в список по указанному индексу, используя метод AddConditionalFormatRuleRequest .
Замените или измените порядок правил в списке по указанному индексу, используя метод UpdateConditionalFormatRuleRequest .
Удалите правила из списка по указанному индексу, используя DeleteConditionalFormatRuleRequest .

Пример

В следующем примере показано, как создать условное форматирование, представленное на скриншоте в верхней части этой страницы. Дополнительные примеры см. на странице «Примеры условного форматирования» .

Apps Script

sheets/api/spreadsheet_snippets.gs

Посмотреть на GitHub

/**
 * conditional formatting
 * @param {string} spreadsheetId spreadsheet ID
 * @returns {*} spreadsheet
 */
Snippets.prototype.conditionalFormatting = (spreadsheetId) => {
  try {
    const myRange = Sheets.newGridRange();
    myRange.sheetId = 0;
    myRange.startRowIndex = 0;
    myRange.endRowIndex = 11;
    myRange.startColumnIndex = 0;
    myRange.endColumnIndex = 4;

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

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

    const rule1Condition = Sheets.newBooleanCondition();
    rule1Condition.type = "CUSTOM_FORMULA";
    rule1Condition.values = [rule1ConditionalValue];

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

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

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

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

    const 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;

    const rule2Condition = Sheets.newBooleanCondition();
    rule2Condition.type = "CUSTOM_FORMULA";
    rule2Condition.values = [rule2ConditionalValue];

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

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

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

    // Batch send the requests
    const requests = [request1, request2];
    const batchUpdate = Sheets.newBatchUpdateSpreadsheetRequest();
    batchUpdate.requests = requests;
    const response = Sheets.Spreadsheets.batchUpdate(
      batchUpdate,
      spreadsheetId,
    );
    return response;
  } catch (err) {
    // TODO (developer) - Handle exception
    console.log("Failed with error %s", err.message);
  }
};

Java

sheets/snippets/src/main/java/ConditionalFormatting.java

Посмотреть на GitHub

import com.google.api.client.googleapis.json.GoogleJsonError;
import com.google.api.client.googleapis.json.GoogleJsonResponseException;
import com.google.api.client.http.HttpRequestInitializer;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.gson.GsonFactory;
import com.google.api.services.sheets.v4.Sheets;
import com.google.api.services.sheets.v4.SheetsScopes;
import com.google.api.services.sheets.v4.model.AddConditionalFormatRuleRequest;
import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetRequest;
import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetResponse;
import com.google.api.services.sheets.v4.model.BooleanCondition;
import com.google.api.services.sheets.v4.model.BooleanRule;
import com.google.api.services.sheets.v4.model.CellFormat;
import com.google.api.services.sheets.v4.model.Color;
import com.google.api.services.sheets.v4.model.ConditionValue;
import com.google.api.services.sheets.v4.model.ConditionalFormatRule;
import com.google.api.services.sheets.v4.model.GridRange;
import com.google.api.services.sheets.v4.model.Request;
import com.google.api.services.sheets.v4.model.TextFormat;
import com.google.auth.http.HttpCredentialsAdapter;
import com.google.auth.oauth2.GoogleCredentials;
import java.io.IOException;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

/* Class to demonstrate the use of Spreadsheet Conditional Formatting API */
public class ConditionalFormatting {
  /**
   * Create conditional formatting.
   *
   * @param spreadsheetId - Id of the spreadsheet.
   * @return updated changes count.
   * @throws IOException - if credentials file not found.
   */
  public static BatchUpdateSpreadsheetResponse conditionalFormat(String spreadsheetId)
      throws IOException {
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
    GoogleCredentials credentials = GoogleCredentials.getApplicationDefault()
        .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));
    HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter(
        credentials);

    // Create the sheets API client
    Sheets service = new Sheets.Builder(new NetHttpTransport(),
        GsonFactory.getDefaultInstance(),
        requestInitializer)
        .setApplicationName("Sheets samples")
        .build();

    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)
        )
    );

    BatchUpdateSpreadsheetResponse result = null;
    try {
      // Execute the requests.
      BatchUpdateSpreadsheetRequest body =
          new BatchUpdateSpreadsheetRequest()
              .setRequests(requests);
      result = service.spreadsheets()
          .batchUpdate(spreadsheetId, body)
          .execute();
      System.out.printf("%d cells updated.", result.getReplies().size());
    } catch (GoogleJsonResponseException e) {
      // TODO(developer) - handle error appropriately
      GoogleJsonError error = e.getDetails();
      if (error.getCode() == 404) {
        System.out.printf("Spreadsheet not found with id '%s'.\n", spreadsheetId);
      } else {
        throw e;
      }
    }
    return result;
  }
}

JavaScript

sheets/snippets/sheets_conditional_formatting.js

Посмотреть на GitHub

function conditionalFormatting(spreadsheetId, callback) {
  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 body = {
    requests,
  };
  try {
    gapi.client.sheets.spreadsheets.batchUpdate({
      spreadsheetId: spreadsheetId,
      resource: body,
    }).then((response) => {
      const result = response.result;
      console.log(`${result.replies.length} cells updated.`);
      if (callback) callback(response);
    });
  } catch (err) {
    document.getElementById('content').innerText = err.message;
    return;
  }
}

Node.js

sheets/snippets/sheets_conditional_formatting.js

Посмотреть на GitHub

import {GoogleAuth} from 'google-auth-library';
import {google} from 'googleapis';

/**
 * Applies conditional formatting to a spreadsheet.
 * @param {string} spreadsheetId The ID of the spreadsheet.
 * @return {Promise<object>} The response from the batch update.
 */
async function conditionalFormatting(spreadsheetId) {
  // Authenticate with Google and get an authorized client.
  const auth = new GoogleAuth({
    scopes: 'https://www.googleapis.com/auth/spreadsheets',
  });

  // Create a new Sheets API client.
  const service = google.sheets({version: 'v4', auth});

  // The range to apply the conditional formatting to.
  const myRange = {
    sheetId: 0,
    startRowIndex: 1,
    endRowIndex: 11,
    startColumnIndex: 0,
    endColumnIndex: 4,
  };

  // The requests to apply conditional formatting.
  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,
      },
    },
  ];

  // Create the batch update request.
  const resource = {
    requests,
  };

  // Execute the batch update request.
  const response = await service.spreadsheets.batchUpdate({
    spreadsheetId,
    resource,
  });
  console.log(`${response.data.replies.length} cells updated.`);
  return response;
}

PHP

sheets/nippets/src/SpreadsheetConditionalFormatting.php

Посмотреть на GitHub

<?php
use Google\Client;
use Google\Service\Drive;
use Google\Service\Sheets\BatchUpdateSpreadsheetRequest;
use Google\Service\Sheets\Request;

function conditionalFormatting($spreadsheetId)
    {
        /* Load pre-authorized user credentials from the environment.
           TODO(developer) - See https://developers.google.com/identity for
            guides on implementing OAuth2 for your application. */
        $client = new Google\Client();
        $client->useApplicationDefaultCredentials();
        $client->addScope(Google\Service\Drive::DRIVE);
        $service = new Google_Service_Sheets($client);

        try{
            $myRange = [
                'sheetId' => 0,
                'startRowIndex' => 1,
                'endRowIndex' => 11,
                'startColumnIndex' => 0,
                'endColumnIndex' => 4,
            ];
            //execute the request
            $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;
    }
    catch(Exception $e) {
        // TODO(developer) - handle error appropriately
        echo 'Message: ' .$e->getMessage();
    }
}

Python

sheets/snippets/sheets_conditional_formatting.py

Посмотреть на GitHub

import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


def conditional_formatting(spreadsheet_id):
  """
  Creates the batch_update the user has access to.
  Load pre-authorized user credentials from the environment.
  TODO(developer) - See https://developers.google.com/identity
  for guides on implementing OAuth2 for the application.
  """
  creds, _ = google.auth.default()
  # pylint: disable=maybe-no-member
  try:
    service = build("sheets", "v4", credentials=creds)

    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(f"{(len(response.get('replies')))} cells updated.")
    return response

  except HttpError as error:
    print(f"An error occurred: {error}")
    return error


if __name__ == "__main__":
  # Pass: spreadsheet_id
  conditional_formatting("1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k")

Руби

sheets/snippets/lib/spreadsheet_snippets.rb

Посмотреть на GitHub

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."

Условное форматирование Оптимизируйте свои подборки Сохраняйте и классифицируйте контент в соответствии со своими настройками.

Правила условного форматирования

Логические правила

Правила градиента

Управление правилами условного форматирования

Пример

Apps Script

Java

JavaScript

Node.js

PHP

Python

Руби

Условное форматирование