0
# Cell and Range Operations
1
2
Individual cell manipulation and range-based operations including formatting, formulas, and bulk data handling.
3
4
## Capabilities
5
6
### Cell Access and Manipulation
7
8
Work with individual cells including values, formulas, and formatting.
9
10
```python { .api }
11
class Worksheet:
12
def cell(self, addr) -> Cell:
13
"""
14
Get cell object by address.
15
16
Parameters:
17
- addr (str): Cell address (e.g., 'A1', 'B2')
18
19
Returns:
20
Cell: Cell object for manipulation
21
"""
22
23
def range(self, name, returnas='cells') -> DataRange:
24
"""
25
Get cells in a given range.
26
27
Parameters:
28
- name (str): Range name or address (e.g., 'A1:C3', 'myRange')
29
- returnas (str): Return format ('cells', 'matrix', 'range')
30
31
Returns:
32
DataRange or other format based on returnas parameter
33
"""
34
```
35
36
### Cell Properties and Values
37
38
Access and modify cell properties, values, and metadata.
39
40
```python { .api }
41
class Cell:
42
def __init__(self, pos, val='', worksheet=None, cell_data=None):
43
"""
44
Initialize cell object.
45
46
Parameters:
47
- pos (str or tuple): Cell position
48
- val: Initial value
49
- worksheet (Worksheet): Parent worksheet
50
- cell_data (dict): Cell data from API
51
"""
52
53
@property
54
def row(self) -> int:
55
"""Row number (1-indexed)."""
56
57
@property
58
def col(self) -> int:
59
"""Column number (1-indexed)."""
60
61
@property
62
def label(self) -> str:
63
"""Cell label (e.g., 'A1')."""
64
65
@property
66
def address(self) -> str:
67
"""Cell address."""
68
69
@property
70
def value(self):
71
"""Cell value (formatted)."""
72
73
@value.setter
74
def value(self, val):
75
"""Set cell value."""
76
77
@property
78
def value_unformatted(self):
79
"""Cell value (unformatted)."""
80
81
@property
82
def formula(self) -> str:
83
"""Cell formula."""
84
85
@formula.setter
86
def formula(self, formula):
87
"""Set cell formula."""
88
89
@property
90
def note(self) -> str:
91
"""Cell note/comment."""
92
93
@note.setter
94
def note(self, note):
95
"""Set cell note/comment."""
96
```
97
98
### Cell Formatting
99
100
Apply various formatting options to cells including text, number, and visual formatting.
101
102
```python { .api }
103
class Cell:
104
def set_text_format(self, attribute, value):
105
"""
106
Set text formatting attribute.
107
108
Parameters:
109
- attribute (str): Format attribute ('bold', 'italic', 'underline', etc.)
110
- value: Attribute value
111
112
Returns:
113
Cell: Self for method chaining
114
"""
115
116
def set_number_format(self, format_type, pattern=None):
117
"""
118
Set number formatting.
119
120
Parameters:
121
- format_type (FormatType): Number format type
122
- pattern (str): Custom format pattern
123
124
Returns:
125
Cell: Self for method chaining
126
"""
127
128
def set_text_rotation(self, angle):
129
"""
130
Set text rotation angle.
131
132
Parameters:
133
- angle (int): Rotation angle in degrees
134
135
Returns:
136
Cell: Self for method chaining
137
"""
138
139
def set_horizontal_alignment(self, alignment):
140
"""
141
Set horizontal text alignment.
142
143
Parameters:
144
- alignment (HorizontalAlignment): Alignment option
145
146
Returns:
147
Cell: Self for method chaining
148
"""
149
150
def set_vertical_alignment(self, alignment):
151
"""
152
Set vertical text alignment.
153
154
Parameters:
155
- alignment (VerticalAlignment): Alignment option
156
157
Returns:
158
Cell: Self for method chaining
159
"""
160
161
@property
162
def color(self) -> tuple:
163
"""Cell background color as RGB tuple."""
164
165
@color.setter
166
def color(self, color_value):
167
"""Set cell background color."""
168
```
169
170
### Cell Operations
171
172
Perform operations on cells including updates, linking, and navigation.
173
174
```python { .api }
175
class Cell:
176
def update(self, force=False):
177
"""
178
Apply pending changes to cell.
179
180
Parameters:
181
- force (bool): Force update even if no changes detected
182
"""
183
184
def refresh(self):
185
"""Refresh cell data from API."""
186
187
def fetch(self):
188
"""Fetch latest cell data from API."""
189
190
def neighbour(self, direction) -> Cell:
191
"""
192
Get neighboring cell.
193
194
Parameters:
195
- direction (str): Direction ('right', 'left', 'up', 'down')
196
197
Returns:
198
Cell: Neighboring cell object
199
"""
200
201
def link(self, worksheet=None, update=True):
202
"""
203
Link cell to worksheet for automatic updates.
204
205
Parameters:
206
- worksheet (Worksheet): Worksheet to link to
207
- update (bool): Whether to update immediately
208
"""
209
210
def unlink(self):
211
"""Unlink cell from worksheet."""
212
```
213
214
### Data Range Operations
215
216
Work with ranges of cells for bulk operations and advanced data manipulation.
217
218
```python { .api }
219
class DataRange:
220
def __init__(self, start=None, end=None, worksheet=None, name='', data=None, name_id=None, namedjson=None, protectedjson=None, grange=None):
221
"""
222
Initialize data range object.
223
224
Parameters:
225
- namedjson (dict): Named range JSON data
226
- name_id (str): Named range ID
227
- worksheet (Worksheet): Parent worksheet
228
- protectedjson (dict): Protected range JSON data
229
- protect_id (str): Protected range ID
230
"""
231
232
@property
233
def name(self) -> str:
234
"""Range name."""
235
236
@name.setter
237
def name(self, name):
238
"""Set range name."""
239
240
@property
241
def protected(self) -> bool:
242
"""Whether range is protected."""
243
244
@property
245
def start_addr(self) -> Address:
246
"""Start address of range."""
247
248
@property
249
def end_addr(self) -> Address:
250
"""End address of range."""
251
252
@property
253
def range(self) -> str:
254
"""Range address string."""
255
256
@property
257
def cells(self) -> list:
258
"""List of Cell objects in range."""
259
260
def update_values(self, values, **kwargs):
261
"""
262
Update values in the range.
263
264
Parameters:
265
- values: Data to update (list of lists)
266
- **kwargs: Additional options
267
"""
268
269
def apply_format(self, cell_list, fields="userEnteredFormat"):
270
"""
271
Apply formatting to range.
272
273
Parameters:
274
- cell_list: List of Cell objects with formatting
275
- fields (str): Fields to update
276
"""
277
278
def sort(self, basecolumnindex=0, sortorder="ASCENDING"):
279
"""
280
Sort range by column.
281
282
Parameters:
283
- basecolumnindex (int): Column index to sort by
284
- sortorder (str): Sort order ('ASCENDING' or 'DESCENDING')
285
"""
286
287
def clear(self, fields="userEnteredValue"):
288
"""
289
Clear range contents.
290
291
Parameters:
292
- fields (str): Fields to clear
293
"""
294
```
295
296
### Address Utilities
297
298
Flexible address representation and manipulation for cells and ranges.
299
300
```python { .api }
301
class Address:
302
def __init__(self, label=None, row=None, col=None, index=1):
303
"""
304
Initialize address object.
305
306
Parameters:
307
- label (str): Cell label (e.g., 'A1')
308
- row (int): Row number
309
- col (int): Column number
310
- index (int): Base index (0 or 1)
311
"""
312
313
@property
314
def label(self) -> str:
315
"""Address label (e.g., 'A1')."""
316
317
@property
318
def row(self) -> int:
319
"""Row number."""
320
321
@property
322
def col(self) -> int:
323
"""Column number."""
324
325
@property
326
def index(self) -> tuple:
327
"""Address as (row, col) tuple."""
328
329
class GridRange:
330
def __init__(self, label=None, start=None, end=None, worksheet=None):
331
"""
332
Initialize grid range object.
333
334
Parameters:
335
- label (str): Range label (e.g., 'A1:C3')
336
- start (Address): Start address
337
- end (Address): End address
338
- worksheet (Worksheet): Parent worksheet
339
"""
340
341
@staticmethod
342
def create(start, end=None, worksheet=None) -> GridRange:
343
"""
344
Create GridRange from addresses.
345
346
Parameters:
347
- start (str or Address): Start address
348
- end (str or Address): End address
349
- worksheet (Worksheet): Parent worksheet
350
351
Returns:
352
GridRange: New grid range object
353
"""
354
355
@property
356
def start(self) -> Address:
357
"""Start address."""
358
359
@property
360
def end(self) -> Address:
361
"""End address."""
362
363
@property
364
def label(self) -> str:
365
"""Range label."""
366
367
@property
368
def height(self) -> int:
369
"""Range height in rows."""
370
371
@property
372
def width(self) -> int:
373
"""Range width in columns."""
374
```
375
376
## Usage Examples
377
378
### Basic Cell Operations
379
380
```python
381
import pygsheets
382
383
# Get worksheet
384
gc = pygsheets.authorize()
385
sh = gc.open('My Spreadsheet')
386
wks = sh.sheet1
387
388
# Get cell and set value
389
cell = wks.cell('A1')
390
cell.value = 'Hello World'
391
cell.update()
392
393
# Method chaining for formatting
394
wks.cell('B1').set_text_format('bold', True).value = 'Bold Text'
395
396
# Set formula
397
wks.cell('C1').formula = '=SUM(A1:A10)'
398
399
# Add note/comment
400
wks.cell('D1').note = 'This is a comment'
401
402
# Format cell
403
cell = wks.cell('E1')
404
cell.value = 1234.56
405
cell.set_number_format(pygsheets.FormatType.CURRENCY)
406
cell.color = (1.0, 0.8, 0.8) # Light red background
407
cell.update()
408
```
409
410
### Range Operations
411
412
```python
413
# Get range of cells
414
cells_range = wks.range('A1:C3', returnas='range')
415
416
# Set range name
417
cells_range.name = 'my_data'
418
419
# Update range values
420
data = [['Name', 'Age', 'City'],
421
['Alice', 25, 'NYC'],
422
['Bob', 30, 'LA']]
423
cells_range.update_values(data)
424
425
# Apply formatting to range
426
header_cells = wks.range('A1:C1', returnas='cells')
427
for cell in header_cells:
428
cell.set_text_format('bold', True)
429
cell.color = (0.8, 0.8, 1.0) # Light blue
430
431
cells_range.apply_format(header_cells)
432
433
# Sort range
434
cells_range.sort(basecolumnindex=1, sortorder="DESCENDING") # Sort by age column
435
436
# Clear range
437
cells_range.clear()
438
```
439
440
### Address Manipulation
441
442
```python
443
# Create addresses
444
addr1 = pygsheets.Address('A1')
445
addr2 = pygsheets.Address(row=5, col=3) # C5
446
447
# Create grid range
448
grid_range = pygsheets.GridRange.create('A1', 'C5')
449
print(f"Range: {grid_range.label}") # A1:C5
450
print(f"Size: {grid_range.width}x{grid_range.height}") # 3x5
451
452
# Use with worksheet
453
cells = wks.range(grid_range.label, returnas='cells')
454
```
455
456
## Types
457
458
### Alignment Types
459
460
```python { .api }
461
class HorizontalAlignment:
462
LEFT = 'LEFT'
463
RIGHT = 'RIGHT'
464
CENTER = 'CENTER'
465
NONE = 'NONE'
466
467
class VerticalAlignment:
468
TOP = 'TOP'
469
MIDDLE = 'MIDDLE'
470
BOTTOM = 'BOTTOM'
471
NONE = 'NONE'
472
```
473
474
### Format Types
475
476
```python { .api }
477
class FormatType:
478
CUSTOM = 'CUSTOM'
479
TEXT = 'TEXT'
480
NUMBER = 'NUMBER'
481
PERCENT = 'PERCENT'
482
CURRENCY = 'CURRENCY'
483
DATE = 'DATE'
484
TIME = 'TIME'
485
DATE_TIME = 'DATE_TIME'
486
SCIENTIFIC = 'SCIENTIFIC'
487
```