Interfejs Google Sheets API umożliwia tworzenie i aktualizowanie reguł formatowania warunkowego w arkuszach kalkulacyjnych. Za pomocą formatowania warunkowego można kontrolować tylko niektóre typy formatowania (pogrubienie, kursywa, przekreślenie, kolor pierwszego planu i kolor tła). Przykłady na tej stronie pokazują, jak wykonywać typowe operacje formatowania warunkowego za pomocą interfejsu Sheets API.
Te przykłady są przedstawione jako żądania HTTP, aby były neutralne pod względem języka. Aby dowiedzieć się, jak za pomocą bibliotek klienta interfejsu API Google wdrożyć zbiorcze aktualizowanie w różnych językach, przeczytaj artykuł Aktualizowanie arkuszy kalkulacyjnych.
W tych przykładach obiekty zastępcze SPREADSHEET_ID i SHEET_ID wskazują, gdzie należy podać te identyfikatory. Identyfikator arkusza kalkulacyjnego znajdziesz w adresie URL arkusza. Identyfikator arkusza możesz uzyskać, korzystając z metody spreadsheets.get. Zakresy są określane za pomocą notacji A1. Przykładowy zakres to Arkusz1!A1:D5.
Dodawanie warunkowego gradientu kolorów w wierszu
Poniższy przykładowy kod metody spreadsheets.batchUpdate pokazuje, jak za pomocą metody AddConditionalFormatRuleRequest utworzyć nowe reguły formatowania warunkowego gradientu dla wierszy 10 i 11 w arkuszu. Pierwsza reguła określa, że kolory tła komórek w tym wierszu są ustawiane zgodnie z ich wartością. Najniższa wartość w wierszu jest oznaczona na ciemnoczerwono, a najwyższa – na jasnozielono. Kolor pozostałych wartości jest interpolowany. Druga reguła działa tak samo, ale z użyciem określonych wartości liczbowych określających punkty końcowe gradientu (i inne kolory). Prośba używa parametru sheets.InterpolationPointType jako type.
Protokół żądania przedstawiono poniżej.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
"requests": [
{
"addConditionalFormatRule": {
"rule": {
"ranges": [
{
"sheetId": SHEET_ID,
"startRowIndex": 9,
"endRowIndex": 10,
}
],
"gradientRule": {
"minpoint": {
"color": {
"green": 0.2,
"red": 0.8
},
"type": "MIN"
},
"maxpoint": {
"color": {
"green": 0.9
},
"type": "MAX"
},
}
},
"index": 0
}
},
{
"addConditionalFormatRule": {
"rule": {
"ranges": [
{
"sheetId": SHEET_ID,
"startRowIndex": 10,
"endRowIndex": 11,
}
],
"gradientRule": {
"minpoint": {
"color": {
"green": 0.8,
"red": 0.8
},
"type": "NUMBER",
"value": "0"
},
"maxpoint": {
"color": {
"blue": 0.9,
"green": 0.5,
"red": 0.5
},
"type": "NUMBER",
"value": "256"
},
}
},
"index": 1
}
},
]
}
Po przesłaniu prośby zastosowana reguła formatowania zaktualizuje arkusz. Ponieważ gradient w wierszu 11 ma ustawioną wartość maksymalną 256, wszystkie wartości powyżej mają kolor:
Dodawanie reguły formatowania warunkowego do zestawu zakresów
Poniższy przykładowy kod metody spreadsheets.batchUpdate pokazuje, jak za pomocą funkcji AddConditionalFormatRuleRequest utworzyć nową regułę formatowania warunkowego dla kolumn A i C w arkuszu.
Reguła ta określa, że komórki z wartościami równymi 10 lub mniejszymi mają tło w kolorze ciemnoczerwonym. Reguła jest wstawiana pod indeksem 0, więc ma pierwszeństwo przed innymi regułami formatowania. Prośba używa wartości ConditionType jako type dla BooleanRule.
Protokół żądania przedstawiono poniżej.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
"requests": [
{
"addConditionalFormatRule": {
"rule": {
"ranges": [
{
"sheetId": SHEET_ID,
"startColumnIndex": 0,
"endColumnIndex": 1,
},
{
"sheetId": SHEET_ID,
"startColumnIndex": 2,
"endColumnIndex": 3,
},
],
"booleanRule": {
"condition": {
"type": "NUMBER_LESS_THAN_EQ",
"values": [
{
"userEnteredValue": "10"
}
]
},
"format": {
"backgroundColor": {
"green": 0.2,
"red": 0.8,
}
}
}
},
"index": 0
}
}
]
}
Po przesłaniu żądania zastosowana reguła formatowania zaktualizuje arkusz:
Dodawanie reguł formatowania warunkowego dat i tekstu do zakresu
Ten przykład kodu metody spreadsheets.batchUpdate pokazuje, jak za pomocą funkcji AddConditionalFormatRuleRequestutworzyć nowe reguły formatowania warunkowego dla zakresu A1:D5 w arkuszu na podstawie wartości daty i tekstu w tych komórkach. Jeśli tekst zawiera ciąg „Cost” (niezależnie od wielkości liter), pierwsza reguła powoduje zaznaczenie tekstu w komórce czcionką pogrubioną. Jeśli komórka zawiera datę wcześniejszą niż zeszły tydzień, druga reguła powoduje, że tekst w komórce jest pogrubiony i ma kolor niebieski. Prośba używa wartości ConditionType jako type dla BooleanRule.
Protokół żądania przedstawiono poniżej.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
"requests": [
{
"addConditionalFormatRule": {
"rule": {
"ranges": [
{
"sheetId": SHEET_ID,
"startRowIndex": 0,
"endRowIndex": 5,
"startColumnIndex": 0,
"endColumnIndex": 4,
}
],
"booleanRule": {
"condition": {
"type": "TEXT_CONTAINS",
"values": [
{
"userEnteredValue": "Cost"
}
]
},
"format": {
"textFormat": {
"bold": true
}
}
}
},
"index": 0
}
},
{
"addConditionalFormatRule": {
"rule": {
"ranges": [
{
"sheetId": SHEET_ID,
"startRowIndex": 0,
"endRowIndex": 5,
"startColumnIndex": 0,
"endColumnIndex": 4,
}
],
"booleanRule": {
"condition": {
"type": "DATE_BEFORE",
"values": [
{
"relativeDate": "PAST_WEEK"
}
]
},
"format": {
"textFormat": {
"italic": true,
"foregroundColor": {
"blue": 1
}
}
}
}
},
"index": 1
}
}
]
}
Po przesłaniu prośby zastosowana reguła formatowania zaktualizuje arkusz. W tym przykładzie bieżąca data to 26.09.2016:
Dodawanie reguły formuły niestandardowej do zakresu
Poniższy przykładowy kod metody spreadsheets.batchUpdate pokazuje, jak za pomocą funkcji AddConditionalFormatRuleRequestutworzyć nową regułę formatowania warunkowego dla zakresu B5:B8 w arkuszu na podstawie niestandardowej formuły. Reguła oblicza iloczyn komórek w kolumnach A i B. Jeśli produkt ma więcej niż 120 punktów, tekst w komórce jest pogrubiony i pochylony. Prośba używa wartości ConditionType jako type dla BooleanRule.
Protokół żądania przedstawiono poniżej.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
"requests": [
{
"addConditionalFormatRule": {
"rule": {
"ranges": [
{
"sheetId": SHEET_ID,
"startColumnIndex": 2,
"endColumnIndex": 3,
"startRowIndex": 4,
"endRowIndex": 8
}
],
"booleanRule": {
"condition": {
"type": "CUSTOM_FORMULA",
"values": [
{
"userEnteredValue": "=GT(A5*B5,120)"
}
]
},
"format": {
"textFormat": {
"bold": true,
"italic": true
}
}
}
},
"index": 0
}
}
]
}
Po przesłaniu prośby zastosowana reguła formatowania zaktualizuje arkusz:
Usuwanie reguły formatowania warunkowego
Poniższy przykładowy kod metody spreadsheets.batchUpdate pokazuje, jak użyć funkcji DeleteConditionalFormatRuleRequest, aby usunąć regułę formatowania warunkowego o indeksie 0 w arkuszu określonym przez zmienną SHEET_ID.
Protokół żądania przedstawiono poniżej.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
"requests": [
{
"deleteConditionalFormatRule": {
"sheetId": SHEET_ID,
"index": 0
}
}
]
}
Przeczytaj listę reguł formatowania warunkowego
Poniższy przykład kodu metody spreadsheets.get pokazuje, jak pobrać tytuł, SHEET_ID i listę wszystkich reguł formatowania warunkowego dla każdego arkusza w arkuszu kalkulacyjnym. Parametr zapytania fields określa, jakie dane mają być zwracane.
Protokół żądania przedstawiono poniżej.
GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets(properties(title,sheetId),conditionalFormats)
Odpowiedź zawiera zasób Spreadsheet, który zawiera tablicę obiektów Sheet, z których każdy ma element SheetProperties i tablicę elementów ConditionalFormatRule. Jeśli dane pole odpowiedzi ma ustawioną wartość domyślną, jest pomijane w odpowiedzi. Prośba używa wartości ConditionType jako type dla BooleanRule.
{
"sheets": [
{
"properties": {
"sheetId": 0,
"title": "Sheet1"
},
"conditionalFormats": [
{
"ranges": [
{
"startRowIndex": 4,
"endRowIndex": 8,
"startColumnIndex": 2,
"endColumnIndex": 3
}
],
"booleanRule": {
"condition": {
"type": "CUSTOM_FORMULA",
"values": [
{
"userEnteredValue": "=GT(A5*B5,120)"
}
]
},
"format": {
"textFormat": {
"bold": true,
"italic": true
}
}
}
},
{
"ranges": [
{
"startRowIndex": 0,
"endRowIndex": 5,
"startColumnIndex": 0,
"endColumnIndex": 4
}
],
"booleanRule": {
"condition": {
"type": "DATE_BEFORE",
"values": [
{
"relativeDate": "PAST_WEEK"
}
]
},
"format": {
"textFormat": {
"foregroundColor": {
"blue": 1
},
"italic": true
}
}
}
},
...
]
}
]
}
Zmień regułę formatowania warunkowego lub jej priorytet
Ten przykładowy kod metody spreadsheets.batchUpdate pokazuje, jak używać metody UpdateConditionalFormatRuleRequest z wieloma żądaniami. Pierwsze żądanie przenosi istniejącą regułę formatowania warunkowego na wyższy indeks (z 0 na 2, co zmniejsza jej priorytet). Druga prośba zastępuje regułę formatowania warunkowego o indeksie 0 nową regułą, która formatuje komórki zawierające dokładnie określony tekst („Całkowity koszt”) w zakresie A1:D5. Przenoszenie pierwszego żądania jest zakończone przed rozpoczęciem drugiego, więc drugie żądanie zastępuje regułę, która pierwotnie znajdowała się pod indeksem 1. W prośbie używa się wartości ConditionType jako type dla BooleanRule.
Protokół żądania przedstawiono poniżej.
POST https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID:batchUpdate
{
"requests": [
{
"updateConditionalFormatRule": {
"sheetId": SHEET_ID,
"index": 0,
"newIndex": 2
},
"updateConditionalFormatRule": {
"sheetId": SHEET_ID,
"index": 0,
"rule": {
"ranges": [
{
"sheetId": SHEET_ID,
"startRowIndex": 0,
"endRowIndex": 5,
"startColumnIndex": 0,
"endColumnIndex": 4,
}
],
"booleanRule": {
"condition": {
"type": "TEXT_EQ",
"values": [
{
"userEnteredValue": "Total Cost"
}
]
},
"format": {
"textFormat": {
"bold": true
}
}
}
}
}
}
]
}