0
# Formatting & Display
1
2
Methods for controlling visual appearance including cell formatting, merging, colors, notes, and display options.
3
4
## Capabilities
5
6
### Cell and Range Formatting
7
8
Apply formatting to cells and ranges.
9
10
```python { .api }
11
class Worksheet:
12
def format(ranges: str, format: Dict) -> Dict:
13
"""
14
Apply formatting to specified range.
15
16
Parameters:
17
- ranges (str): A1 notation range to format.
18
- format (Dict): Formatting specification following Google Sheets API format.
19
20
Returns:
21
Dict: Response from format operation.
22
"""
23
24
def batch_format(formats: List[Dict]) -> Dict:
25
"""
26
Apply multiple formats in single request.
27
28
Parameters:
29
- formats (List[Dict]): List of format requests.
30
31
Returns:
32
Dict: Response from batch format operation.
33
"""
34
```
35
36
### Cell Merging
37
38
Merge and unmerge cells.
39
40
```python { .api }
41
class Worksheet:
42
def merge_cells(name: str, merge_type: str = "MERGE_ALL") -> Dict:
43
"""
44
Merge cells in specified range.
45
46
Parameters:
47
- name (str): A1 notation range to merge.
48
- merge_type (str): Type of merge ("MERGE_ALL", "MERGE_COLUMNS", "MERGE_ROWS"). Default: "MERGE_ALL".
49
50
Returns:
51
Dict: Response from merge operation.
52
"""
53
54
def unmerge_cells(name: str) -> Dict:
55
"""
56
Unmerge cells in specified range.
57
58
Parameters:
59
- name (str): A1 notation range to unmerge.
60
61
Returns:
62
Dict: Response from unmerge operation.
63
"""
64
```
65
66
### Tab Appearance
67
68
Control worksheet tab appearance.
69
70
```python { .api }
71
class Worksheet:
72
def update_tab_color(color: Dict) -> Dict:
73
"""
74
Update worksheet tab color.
75
76
Parameters:
77
- color (Dict): Color specification with 'red', 'green', 'blue' keys (0-1 values).
78
79
Returns:
80
Dict: Response from color update.
81
"""
82
```
83
84
### Notes and Comments
85
86
Add and manage cell notes.
87
88
```python { .api }
89
class Worksheet:
90
def get_notes(default_empty_value: str = "", grid_range: str = None) -> List[List[str]]:
91
"""
92
Returns a list of lists containing all notes in the sheet or range.
93
94
Parameters:
95
- default_empty_value (str): Determines which value to use for cells without notes. Default: "".
96
- grid_range (str, optional): Range name in A1 notation, e.g. 'A1:A5'. If None, gets all notes.
97
98
Returns:
99
List[List[str]]: List of lists containing all notes in the sheet or range.
100
"""
101
102
def update_notes(notes: Dict[str, str]) -> None:
103
"""
104
Update multiple notes. The notes are attached to a certain cell.
105
106
Parameters:
107
- notes (Dict[str, str]): A dict of notes with their cells coordinates (A1 format) and respective content.
108
109
Returns:
110
None
111
"""
112
113
def update_note(cell: str, content: str) -> None:
114
"""
115
Update the content of the note located at cell.
116
117
Parameters:
118
- cell (str): A string with cell coordinates in A1 notation, e.g. 'D7'.
119
- content (str): The text note to insert.
120
121
Returns:
122
None
123
"""
124
125
def insert_notes(notes: Dict[str, str]) -> None:
126
"""
127
Insert multiple notes. The notes are attached to a certain cell.
128
129
Parameters:
130
- notes (Dict[str, str]): A dict of notes with their cells coordinates (A1 format) and respective content.
131
132
Returns:
133
None
134
"""
135
136
def clear_notes(ranges: List[str]) -> None:
137
"""
138
Clear all notes located at the coordinates pointed to by ranges.
139
140
Parameters:
141
- ranges (List[str]): List of A1 coordinates where to clear the notes, e.g. ["A1", "GH42", "D7"].
142
143
Returns:
144
None
145
"""
146
147
def clear_note(cell: str) -> None:
148
"""
149
Clear a note. The note is attached to a certain cell.
150
151
Parameters:
152
- cell (str): A string with cell coordinates in A1 notation, e.g. 'D7'.
153
154
Returns:
155
None
156
"""
157
158
def batch_merge(merges: List[Dict], merge_type: str = "MERGE_ALL") -> Any:
159
"""
160
Merge multiple ranges at the same time.
161
162
Parameters:
163
- merges (List[Dict]): List of dictionaries with the ranges (A1-notation) and optional 'mergeType' field.
164
- merge_type (str): Default merge type for all merges missing the mergeType. Default: "MERGE_ALL".
165
166
Returns:
167
Any: The body of the request response.
168
"""
169
```
170
171
### Data Organization
172
173
Sort and filter data.
174
175
```python { .api }
176
class Worksheet:
177
def sort(specs: List[Dict], range: str = None) -> Dict:
178
"""
179
Sort data by specified criteria.
180
181
Parameters:
182
- specs (List[Dict]): List of sort specifications with 'dimension', 'sortOrder' keys.
183
- range (str, optional): A1 notation range to sort. If None, sorts entire sheet.
184
185
Returns:
186
Dict: Response from sort operation.
187
"""
188
189
def set_basic_filter(name: str = None) -> Dict:
190
"""
191
Set basic filter on data range.
192
193
Parameters:
194
- name (str, optional): A1 notation range for filter. If None, applies to entire sheet.
195
196
Returns:
197
Dict: Response from filter setup.
198
"""
199
```
200
201
### Protected Ranges
202
203
Create and manage protected ranges for access control.
204
205
```python { .api }
206
class Worksheet:
207
def add_protected_range(name: str, editor_users_emails: List[str] = None,
208
editor_groups_emails: List[str] = None, description: str = None,
209
warning_only: bool = False, requesting_user_can_edit: bool = False) -> Dict:
210
"""
211
Add protected range to prevent unauthorized edits.
212
213
Parameters:
214
- name (str): A1 notation range to protect.
215
- editor_users_emails (List[str], optional): List of user emails who can edit.
216
- editor_groups_emails (List[str], optional): List of group emails who can edit.
217
- description (str, optional): Description of the protection.
218
- warning_only (bool): Show warning instead of preventing edits. Default: False.
219
- requesting_user_can_edit (bool): Allow requesting user to edit. Default: False.
220
221
Returns:
222
Dict: Response containing protection details.
223
"""
224
225
def delete_protected_range(protected_range_id: str) -> Dict:
226
"""
227
Remove protected range.
228
229
Parameters:
230
- protected_range_id (str): ID of protected range to delete.
231
232
Returns:
233
Dict: Response from deletion.
234
"""
235
```
236
237
### Export Options
238
239
Export worksheet with specific formatting.
240
241
```python { .api }
242
class Worksheet:
243
def export(format: str, gid: int = None) -> str:
244
"""
245
Export worksheet in specified format.
246
247
Parameters:
248
- format (str): Export format ("csv", "tsv", "pdf", "zip").
249
- gid (int, optional): Grid ID. If None, uses worksheet's grid ID.
250
251
Returns:
252
str: Export URL or content.
253
"""
254
```
255
256
### Range Operations
257
258
Work with named ranges and value ranges.
259
260
```python { .api }
261
class Worksheet:
262
def range(name: str) -> List[Cell]:
263
"""
264
Get range as list of Cell objects.
265
266
Parameters:
267
- name (str): A1 notation range.
268
269
Returns:
270
List[Cell]: List of Cell instances in the range.
271
"""
272
```
273
274
### Display Properties
275
276
```python { .api }
277
class Worksheet:
278
@property
279
def hidden -> bool:
280
"""Whether worksheet is hidden."""
281
282
@property
283
def tab_color -> Dict:
284
"""Worksheet tab color specification."""
285
```
286
287
## Formatting Examples
288
289
### Basic Cell Formatting
290
291
```python
292
# Format text style
293
text_format = {
294
"textFormat": {
295
"bold": True,
296
"italic": False,
297
"fontSize": 12,
298
"fontFamily": "Arial"
299
}
300
}
301
worksheet.format('A1:C1', text_format)
302
303
# Format background color
304
bg_format = {
305
"backgroundColor": {
306
"red": 0.8,
307
"green": 0.9,
308
"blue": 1.0
309
}
310
}
311
worksheet.format('A1:A10', bg_format)
312
313
# Format numbers
314
number_format = {
315
"numberFormat": {
316
"type": "CURRENCY",
317
"pattern": "$#,##0.00"
318
}
319
}
320
worksheet.format('B1:B10', number_format)
321
```
322
323
### Advanced Formatting
324
325
```python
326
# Comprehensive cell formatting
327
full_format = {
328
"backgroundColor": {"red": 0.9, "green": 0.9, "blue": 0.9},
329
"textFormat": {
330
"foregroundColor": {"red": 0, "green": 0, "blue": 0},
331
"fontSize": 11,
332
"bold": True
333
},
334
"borders": {
335
"top": {"style": "SOLID", "width": 1},
336
"bottom": {"style": "SOLID", "width": 1},
337
"left": {"style": "SOLID", "width": 1},
338
"right": {"style": "SOLID", "width": 1}
339
},
340
"horizontalAlignment": "CENTER",
341
"verticalAlignment": "MIDDLE"
342
}
343
worksheet.format('A1:Z1', full_format)
344
345
# Conditional formatting (via batch_format)
346
conditional_format = {
347
"requests": [{
348
"addConditionalFormatRule": {
349
"rule": {
350
"ranges": [{"sheetId": worksheet.id, "startRowIndex": 1, "endRowIndex": 100, "startColumnIndex": 0, "endColumnIndex": 5}],
351
"booleanRule": {
352
"condition": {
353
"type": "NUMBER_GREATER",
354
"values": [{"userEnteredValue": "100"}]
355
},
356
"format": {
357
"backgroundColor": {"red": 0.8, "green": 1, "blue": 0.8}
358
}
359
}
360
},
361
"index": 0
362
}
363
}]
364
}
365
worksheet.batch_format([conditional_format])
366
```
367
368
### Merging and Organization
369
370
```python
371
# Merge header cells
372
worksheet.merge_cells('A1:E1', merge_type='MERGE_ALL')
373
374
# Set tab color
375
worksheet.update_tab_color({
376
'red': 0.2,
377
'green': 0.6,
378
'blue': 0.9
379
})
380
381
# Sort data by column A (ascending), then column B (descending)
382
sort_specs = [
383
{'dimension': 0, 'sortOrder': 'ASCENDING'}, # Column A
384
{'dimension': 1, 'sortOrder': 'DESCENDING'} # Column B
385
]
386
worksheet.sort(sort_specs, 'A2:E100')
387
388
# Add notes
389
worksheet.insert_note('A1', 'This is the header row')
390
391
# Protect important data
392
worksheet.add_protected_range(
393
'A1:E1',
394
editor_users_emails=['admin@company.com'],
395
description='Header row - protected from edits'
396
)
397
```