0
# Data Validation and Formatting
1

2
Advanced formatting capabilities including conditional formatting, data validation rules, and comprehensive cell styling options.
3

4
## Capabilities
5

6
### Data Validation
7

8
Set up data validation rules to control data entry and ensure data quality.
9

10
```python { .api }
11
class Worksheet:
12
    def set_data_validation(self, crange, condition_type, condition_values, **kwargs):
13
        """
14
        Set data validation rule for range.
15
        
16
        Parameters:
17
        - crange (str): Range to apply validation to
18
        - condition_type (str): Type of validation condition
19
        - condition_values (list): Values for validation condition
20
        - **kwargs: Additional validation options (strict, show_custom_ui, input_message, etc.)
21
        """
22
```
23

24
### Conditional Formatting
25

26
Apply conditional formatting rules based on cell values and conditions.
27

28
```python { .api }
29
class Worksheet:
30
    def add_conditional_formatting(self, crange, condition_type, condition_values, format=None, **kwargs):
31
        """
32
        Add conditional formatting rule.
33
        
34
        Parameters:
35
        - crange (str): Range to apply formatting to
36
        - condition_type (str): Type of condition
37
        - condition_values (list): Values for condition
38
        - format (dict): Formatting to apply when condition is met
39
        - **kwargs: Additional formatting options
40
        """
41
```
42

43
### Cell Formatting
44

45
Apply comprehensive formatting to cells and ranges including colors, borders, and text styles.
46

47
```python { .api }
48
class Worksheet:
49
    def apply_format(self, ranges, cell_format, fields="userEnteredFormat"):
50
        """
51
        Apply formatting to specified ranges.
52
        
53
        Parameters:
54
        - ranges: Range or list of ranges to format
55
        - cell_format (dict or Cell): Format specification
56
        - fields (str): Fields to update in the format
57
        """
58
    
59
    def merge_cells(self, start, end, merge_type='MERGE_ALL'):
60
        """
61
        Merge cells in specified range.
62
        
63
        Parameters:
64
        - start (str): Start cell address
65
        - end (str): End cell address
66
        - merge_type (str): Type of merge operation
67
        """
68
```
69

70
### Filters and Sorting
71

72
Set up basic filters and sorting for data ranges.
73

74
```python { .api }
75
class Worksheet:
76
    def set_basic_filter(self, start_row=1, end_row=None, start_col=1, end_col=None):
77
        """
78
        Set basic filter on worksheet range.
79
        
80
        Parameters:
81
        - start_row (int): Starting row for filter
82
        - end_row (int): Ending row for filter
83
        - start_col (int): Starting column for filter
84
        - end_col (int): Ending column for filter
85
        """
86
    
87
    def clear_basic_filter(self):
88
        """Remove basic filter from worksheet."""
89
    
90
    def sort_range(self, start, end, basecolumnindex=0, sortorder="ASCENDING"):
91
        """
92
        Sort range by specified column.
93
        
94
        Parameters:
95
        - start (str): Start cell of range
96
        - end (str): End cell of range
97
        - basecolumnindex (int): Column index to sort by (0-indexed)
98
        - sortorder (str): Sort order ('ASCENDING' or 'DESCENDING')
99
        """
100
```
101

102
### Named and Protected Ranges
103

104
Create and manage named ranges and protected ranges for data organization and security.
105

106
```python { .api }
107
class Worksheet:
108
    def create_named_range(self, name, start, end=None, gid=None):
109
        """
110
        Create named range.
111
        
112
        Parameters:
113
        - name (str): Name for the range
114
        - start (str): Start cell address
115
        - end (str): End cell address
116
        - gid (int): Worksheet ID (uses current worksheet if None)
117
        
118
        Returns:
119
        str: Named range ID
120
        """
121
    
122
    def get_named_range(self, name) -> DataRange:
123
        """
124
        Get named range by name.
125
        
126
        Parameters:
127
        - name (str): Name of the range
128
        
129
        Returns:
130
        DataRange: Named range object
131
        """
132
    
133
    def get_named_ranges(self) -> list:
134
        """
135
        Get all named ranges in worksheet.
136
        
137
        Returns:
138
        list: List of DataRange objects
139
        """
140
    
141
    def delete_named_range(self, name):
142
        """
143
        Delete named range.
144
        
145
        Parameters:
146
        - name (str): Name of range to delete
147
        """
148
    
149
    def create_protected_range(self, start, end=None, named_range_id='', **kwargs) -> str:
150
        """
151
        Create protected range.
152
        
153
        Parameters:
154
        - start (str): Start cell address
155
        - end (str): End cell address
156
        - named_range_id (str): ID of named range to protect
157
        - **kwargs: Additional protection options (description, editors, etc.)
158
        
159
        Returns:
160
        str: Protected range ID
161
        """
162
    
163
    def remove_protected_range(self, protected_range_id):
164
        """
165
        Remove protected range.
166
        
167
        Parameters:
168
        - protected_range_id (str): ID of protected range to remove
169
        """
170
    
171
    def get_protected_ranges(self) -> list:
172
        """
173
        Get all protected ranges in worksheet.
174
        
175
        Returns:
176
        list: List of DataRange objects
177
        """
178
```
179

180
## Usage Examples
181

182
### Data Validation Rules
183

184
```python
185
import pygsheets
186

187
# Get worksheet
188
gc = pygsheets.authorize()
189
sh = gc.open('Data Entry Form')
190
wks = sh.sheet1
191

192
# Dropdown validation (list of values)
193
wks.set_data_validation(
194
    'B2:B10',
195
    'ONE_OF_LIST',
196
    ['Option 1', 'Option 2', 'Option 3'],
197
    strict=True,
198
    show_custom_ui=True
199
)
200

201
# Number range validation
202
wks.set_data_validation(
203
    'C2:C10',
204
    'NUMBER_BETWEEN',
205
    [1, 100],
206
    input_message='Enter a number between 1 and 100'
207
)
208

209
# Date validation
210
from datetime import date
211
wks.set_data_validation(
212
    'D2:D10',
213
    'DATE_AFTER',
214
    [date.today()],
215
    strict=True
216
)
217

218
# Email validation (regex pattern)
219
wks.set_data_validation(
220
    'E2:E10',
221
    'CUSTOM_FORMULA',
222
    ['=ISEMAIL(E2)'],
223
    input_message='Enter a valid email address'
224
)
225

226
# Checkbox validation
227
wks.set_data_validation(
228
    'F2:F10',
229
    'CHECKBOX',
230
    [True, False]
231
)
232
```
233

234
### Conditional Formatting
235

236
```python
237
# Highlight cells based on value
238
red_format = {
239
    'backgroundColor': {'red': 1.0, 'green': 0.8, 'blue': 0.8}
240
}
241

242
# Highlight values greater than 100
243
wks.add_conditional_formatting(
244
    'A1:A20',
245
    'NUMBER_GREATER',
246
    [100],
247
    format=red_format
248
)
249

250
# Color scale formatting
251
wks.add_conditional_formatting(
252
    'B1:B20',
253
    'COLOR_SCALE',
254
    [],
255
    format={
256
        'colorScale': {
257
            'minpoint': {'color': {'red': 1, 'green': 1, 'blue': 1}},
258
            'maxpoint': {'color': {'red': 0, 'green': 1, 'blue': 0}}
259
        }
260
    }
261
)
262

263
# Text contains formatting
264
yellow_format = {
265
    'backgroundColor': {'red': 1.0, 'green': 1.0, 'blue': 0.8}
266
}
267
wks.add_conditional_formatting(
268
    'C1:C20',
269
    'TEXT_CONTAINS',
270
    ['error'],
271
    format=yellow_format
272
)
273

274
# Custom formula formatting
275
wks.add_conditional_formatting(
276
    'D1:D20',
277
    'CUSTOM_FORMULA',
278
    ['=D1>AVERAGE($D$1:$D$20)'],
279
    format={'textFormat': {'bold': True}}
280
)
281
```
282

283
### Advanced Cell Formatting
284

285
```python
286
# Create formatted header row
287
header_format = {
288
    'backgroundColor': {'red': 0.2, 'green': 0.4, 'blue': 0.8},
289
    'textFormat': {
290
        'bold': True,
291
        'fontSize': 12,
292
        'foregroundColor': {'red': 1, 'green': 1, 'blue': 1}
293
    },
294
    'horizontalAlignment': 'CENTER'
295
}
296

297
wks.apply_format('A1:E1', header_format)
298

299
# Number formatting for currency
300
currency_format = {
301
    'numberFormat': {
302
        'type': 'CURRENCY',
303
        'pattern': '$#,##0.00'
304
    }
305
}
306
wks.apply_format('C2:C100', currency_format)
307

308
# Date formatting
309
date_format = {
310
    'numberFormat': {
311
        'type': 'DATE',
312
        'pattern': 'mm/dd/yyyy'
313
    }
314
}
315
wks.apply_format('D2:D100', date_format)
316

317
# Percentage formatting
318
percent_format = {
319
    'numberFormat': {
320
        'type': 'PERCENT',
321
        'pattern': '0.00%'
322
    }
323
}
324
wks.apply_format('E2:E100', percent_format)
325
```
326

327
### Borders and Styling
328

329
```python
330
# Add borders to range
331
border_format = {
332
    'borders': {
333
        'top': {'style': 'SOLID', 'width': 1},
334
        'bottom': {'style': 'SOLID', 'width': 1},
335
        'left': {'style': 'SOLID', 'width': 1},
336
        'right': {'style': 'SOLID', 'width': 1}
337
    }
338
}
339
wks.apply_format('A1:E10', border_format)
340

341
# Merge cells for title
342
wks.merge_cells('A1', 'E1', merge_type='MERGE_ALL')
343
wks.update_value('A1', 'Report Title')
344

345
# Apply title formatting
346
title_format = {
347
    'textFormat': {
348
        'bold': True,
349
        'fontSize': 16
350
    },
351
    'horizontalAlignment': 'CENTER'
352
}
353
wks.apply_format('A1', title_format)
354
```
355

356
### Named and Protected Ranges
357

358
```python
359
# Create named range
360
wks.update_values('A1:C3', [['Name', 'Score', 'Grade'], ['Alice', 95, 'A'], ['Bob', 87, 'B']])
361
wks.create_named_range('student_data', 'A1', 'C3')
362

363
# Use named range in formula
364
wks.update_value('E1', '=AVERAGE(student_data)')
365

366
# Get named range
367
student_range = wks.get_named_range('student_data')
368
print(f"Range: {student_range.range}")
369

370
# Create protected range
371
protected_id = wks.create_protected_range(
372
    'A1', 'C1',  # Protect header row
373
    description='Header row - do not edit',
374
    editors=['admin@example.com']
375
)
376

377
# List all protected ranges
378
protected_ranges = wks.get_protected_ranges()
379
for prange in protected_ranges:
380
    print(f"Protected: {prange.range}")
381
```
382

383
### Filtering and Sorting
384

385
```python
386
# Set up basic filter on data
387
wks.set_basic_filter(start_row=1, end_row=100, start_col=1, end_col=5)
388

389
# Sort data by column
390
wks.sort_range('A2', 'E100', basecolumnindex=2, sortorder='DESCENDING')  # Sort by column C
391

392
# Clear filter when done
393
wks.clear_basic_filter()
394
```
395

396
## Types
397

398
### Validation Condition Types
399

400
```python { .api }
401
class ValidationConditionType:
402
    NUMBER_GREATER = 'NUMBER_GREATER'
403
    NUMBER_GREATER_THAN_EQ = 'NUMBER_GREATER_THAN_EQ'
404
    NUMBER_LESS = 'NUMBER_LESS'
405
    NUMBER_LESS_THAN_EQ = 'NUMBER_LESS_THAN_EQ'
406
    NUMBER_EQ = 'NUMBER_EQ'
407
    NUMBER_NOT_EQ = 'NUMBER_NOT_EQ'
408
    NUMBER_BETWEEN = 'NUMBER_BETWEEN'
409
    NUMBER_NOT_BETWEEN = 'NUMBER_NOT_BETWEEN'
410
    TEXT_CONTAINS = 'TEXT_CONTAINS'
411
    TEXT_NOT_CONTAINS = 'TEXT_NOT_CONTAINS'
412
    TEXT_STARTS_WITH = 'TEXT_STARTS_WITH'
413
    TEXT_ENDS_WITH = 'TEXT_ENDS_WITH'
414
    TEXT_EQ = 'TEXT_EQ'
415
    TEXT_IS_EMAIL = 'TEXT_IS_EMAIL'
416
    TEXT_IS_URL = 'TEXT_IS_URL'
417
    DATE_EQ = 'DATE_EQ'
418
    DATE_BEFORE = 'DATE_BEFORE'
419
    DATE_AFTER = 'DATE_AFTER'
420
    DATE_ON_OR_BEFORE = 'DATE_ON_OR_BEFORE'
421
    DATE_ON_OR_AFTER = 'DATE_ON_OR_AFTER'
422
    DATE_BETWEEN = 'DATE_BETWEEN'
423
    ONE_OF_RANGE = 'ONE_OF_RANGE'
424
    ONE_OF_LIST = 'ONE_OF_LIST'
425
    BLANK = 'BLANK'
426
    NOT_BLANK = 'NOT_BLANK'
427
    CUSTOM_FORMULA = 'CUSTOM_FORMULA'
428
    CHECKBOX = 'CHECKBOX'
429
```
430

431
### Merge Types
432

433
```python { .api }
434
class MergeType:
435
    MERGE_ALL = 'MERGE_ALL'
436
    MERGE_COLUMNS = 'MERGE_COLUMNS' 
437
    MERGE_ROWS = 'MERGE_ROWS'
438
```