Ta strona została przetłumaczona przez Cloud Translation API.

Formatowanie warunkowe

Formatowanie warunkowe pozwala formatować komórki tak, aby ich wygląd zmieniał się dynamicznie w zależności od wartości, które zawierają, lub wartości w innych komórkach. Formatowanie warunkowe ma wiele zastosowań, w tym:

Zaznaczaj komórki powyżej określonego progu (np. za pomocą pogrubionego tekstu w przypadku wszystkich transakcji powyżej 2000 USD).
Formatuj komórki tak, aby ich kolor zmieniał się w zależności od wartości (np. stosując bardziej intensywny czerwony kolor tła,gdy kwota przekracza 2000 USD).
Formatowanie komórek dynamicznie na podstawie zawartości innych komórek (np. wyróżnianie adresów nieruchomości, których pole „Czas na rynku” zawiera wartość większą niż 90 dni).

Możesz nawet formatować komórki na podstawie ich wartości i wartości innych komórek. Na przykład możesz sformatować zakres komórek na podstawie ich wartości w porównaniu z wartością średnią tego zakresu:

Formatowanie służące do wyróżniania wartości powyżej lub poniżej mediany wieku.

Rysunek 1. Formatowanie służące do wyróżniania wartości powyżej lub poniżej mediany wieku.

W tym przykładzie komórki w każdym wierszu są formatowane zgodnie z wartością w kolumnie age w porównaniu z medianą wartością wszystkich grup wiekowych. Wiersze, których wiek jest wyższy niż mediana, mają czerwony tekst, a te poniżej mediany – czerwone tło. Dwa z tych wierszy mają wartość age odpowiadającą średniej długości życia (48 lat), a te komórki nie mają specjalnego formatowania. (kod źródłowy, który tworzy to formatowanie warunkowe, znajdziesz w przykładzie poniżej).

Reguły formatowania warunkowego

Formatowanie warunkowe jest wyrażane za pomocą reguł formatowania. Każdy arkusz kalkulacyjny przechowuje listę tych reguł i stosuje je w tej samej kolejności, w jakiej się znajdują na liście. Interfejs Google Sheets API umożliwia dodawanie, aktualizowanie i usuwanie tych reguł formatowania.

Każda reguła określa zakres docelowy, typ reguły, warunki jej aktywacji oraz formatowanie.

Zakres docelowy – może to być pojedyncza komórka, zakres komórek lub wiele zakresów.

Typ reguły – istnieją 2 kategorie reguł:

Reguły logiczne stosują format tylko wtedy, gdy są spełnione określone kryteria.
Reguły gradientu obliczają kolor tła komórki na podstawie jej wartości.

Warunki, które są oceniane, oraz formaty, które możesz zastosować, różnią się w zależności od typu reguły. Szczegółowe informacje znajdziesz w następnych sekcjach.

Reguły logiczne

Element BooleanRule określa, czy zastosować określony format na podstawie elementu BooleanCondition, który ma wartość true lub false. Reguła logiczna ma postać:

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

Warunek może używać wbudowanej funkcji ConditionType lub formuły niestandardowej w przypadku bardziej złożonych ocen.

Wbudowane typy umożliwiają zastosowanie formatowania na podstawie progów liczbowych, porównań tekstowych lub tego, czy komórka jest wypełniona. Na przykład NUMBER_GREATER oznacza, że wartość komórki musi być większa niż wartość warunku. Reguły są zawsze oceniane na podstawie komórki docelowej.

Formuła niestandardowa to specjalny typ warunku, który umożliwia zastosowanie formatowania zgodnie z dowolnym wyrażeniem. Umożliwia on również ocenę dowolnej komórki, a nie tylko komórki docelowej. Formuła warunku musi zwracać wartość true.

Aby zdefiniować formatowanie stosowane przez regułę logiczną, użyj podzbioru typu CellFormat, aby zdefiniować:

Czy tekst w komórce jest pogrubiony, kursywą czy przekreślony.
Kolor tekstu w komórce.
Kolor tła komórki.

Używaj tylko funkcji formatowania wymienionych powyżej. Chociaż typ CellFormat może wyrażać inne formatowanie, w tym podkreślenie, wyrównanie lub obramowanie, próba użycia tych wartości w regułach formatowania warunkowego spowoduje błąd 400 invalid request.

Reguły gradientu

GradientRuleokreśla zakres kolorów odpowiadający zakresowi wartości. Reguła gradientu ma postać:

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

Każdy element InterpolationPointokreśla kolor i odpowiadającą mu wartość. Gradient kolorów jest definiowany przez zestaw 3 punktów.

Zarządzanie regułami formatowania warunkowego

Aby utworzyć, zmodyfikować lub usunąć reguły formatowania warunkowego, użyj metody spreadsheets.batchUpdate z odpowiednim typem żądania:

Dodaj reguły do listy pod podanym indeksem, używając funkcji AddConditionalFormatRuleRequest.
Za pomocą opcji UpdateConditionalFormatRuleRequest możesz zastąpić reguły na liście lub zmienić ich kolejność.
Użyj instrukcji DeleteConditionalFormatRuleRequest, aby usunąć reguły z listy w danym indeksie.

Przykład

Z poniższego przykładu dowiesz się, jak utworzyć formatowanie warunkowe widoczne na zrzucie ekranu u góry tej strony. Więcej przykładów znajdziesz na stronie z próbkami Formatowanie warunkowe.

Google Apps Script

sheets/api/spreadsheet_snippets.gs

Wyświetl w GitHubie

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

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

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

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

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

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

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

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

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

    let rule2Condition = Sheets.newBooleanCondition();
    rule2Condition.type = 'CUSTOM_FORMULA';
    rule2Condition.values = [rule2ConditionalValue];

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

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

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

    // Batch send the requests
    const requests = [request1, request2];
    let 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

Wyświetl w GitHubie

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

Wyświetl w GitHubie

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

Wyświetl w GitHubie

/**
 * Conditionally formats a Spreadsheet.
 * @param {string} spreadsheetId A Spreadsheet ID.
 * @return {obj} spreadsheet information
 */
async function conditionalFormatting(spreadsheetId) {
  const {GoogleAuth} = require('google-auth-library');
  const {google} = require('googleapis');

  const auth = new GoogleAuth({
    scopes: 'https://www.googleapis.com/auth/spreadsheets',
  });

  const service = google.sheets({version: 'v4', auth});
  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,
  };
  try {
    const response = await service.spreadsheets.batchUpdate({
      spreadsheetId,
      resource,
    });
    console.log(`${response.data.replies.length} cells updated.`);
    return response;
  } catch (err) {
    // TODO (developer) - Handle exception
    throw err;
  }
}

PHP

sheets/snippets/src/SpreadsheetConditionalFormatting.php

Wyświetl w GitHubie

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

Wyświetl w GitHubie

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

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb

Wyświetl w GitHubie

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