0
# Data Access & Manipulation
1

2
The Worksheet class provides comprehensive methods for reading, writing, and manipulating cell data and ranges within individual worksheets.
3

4
## Capabilities
5

6
### Reading Data
7

8
Access cell values and ranges with various formatting options.
9

10
```python { .api }
11
class Worksheet:
12
    def get_all_values(value_render_option: str = "FORMATTED_VALUE",
13
                       date_time_render_option: str = "SERIAL_NUMBER",
14
                       major_dimension: str = "ROWS") -> List[List]:
15
        """
16
        Get all values from worksheet as 2D list.
17

18
        Parameters:
19
        - value_render_option (str): "FORMATTED_VALUE", "UNFORMATTED_VALUE", or "FORMULA". Default: "FORMATTED_VALUE".
20
        - date_time_render_option (str): "SERIAL_NUMBER" or "FORMATTED_STRING". Default: "SERIAL_NUMBER".
21
        - major_dimension (str): "ROWS" or "COLUMNS". Default: "ROWS".
22

23
        Returns:
24
        List[List]: 2D array of cell values.
25
        """
26

27
    def get_values(range_name: str = None, major_dimension: str = "ROWS",
28
                   value_render_option: str = "FORMATTED_VALUE",
29
                   date_time_render_option: str = "SERIAL_NUMBER",
30
                   maintain_size: bool = False) -> List[List]:
31
        """
32
        Get values from specified range or entire worksheet.
33

34
        Parameters:
35
        - range_name (str, optional): A1 notation range. If None, gets all values.
36
        - major_dimension (str): "ROWS" or "COLUMNS". Default: "ROWS".
37
        - value_render_option (str): Value rendering option. Default: "FORMATTED_VALUE".
38
        - date_time_render_option (str): Date/time rendering option. Default: "SERIAL_NUMBER".
39
        - maintain_size (bool): Maintain original grid size. Default: False.
40

41
        Returns:
42
        List[List]: 2D array of cell values.
43
        """
44

45
    def get(range_name: str, major_dimension: str = "ROWS",
46
            value_render_option: str = "FORMATTED_VALUE",
47
            date_time_render_option: str = "SERIAL_NUMBER") -> List[List]:
48
        """
49
        Get values from specified range.
50

51
        Parameters:
52
        - range_name (str): A1 notation range.
53
        - major_dimension (str): "ROWS" or "COLUMNS". Default: "ROWS".
54
        - value_render_option (str): Value rendering option. Default: "FORMATTED_VALUE".
55
        - date_time_render_option (str): Date/time rendering option. Default: "SERIAL_NUMBER".
56

57
        Returns:
58
        List[List]: 2D array of cell values.
59
        """
60

61
    def acell(label: str, value_render_option: str = "FORMATTED_VALUE") -> Cell:
62
        """
63
        Get cell by A1 notation.
64

65
        Parameters:
66
        - label (str): A1 notation cell address (e.g., "A1", "B5").
67
        - value_render_option (str): Value rendering option. Default: "FORMATTED_VALUE".
68

69
        Returns:
70
        Cell: Cell instance with coordinate and value information.
71
        """
72

73
    def cell(row: int, col: int, value_render_option: str = "FORMATTED_VALUE") -> Cell:
74
        """
75
        Get cell by row and column coordinates (1-indexed).
76

77
        Parameters:
78
        - row (int): Row number (1-indexed).
79
        - col (int): Column number (1-indexed).
80
        - value_render_option (str): Value rendering option. Default: "FORMATTED_VALUE".
81

82
        Returns:
83
        Cell: Cell instance with coordinate and value information.
84
        """
85

86
    def row_values(row: int, value_render_option: str = "FORMATTED_VALUE") -> List:
87
        """
88
        Get all values from specified row.
89

90
        Parameters:
91
        - row (int): Row number (1-indexed).
92
        - value_render_option (str): Value rendering option. Default: "FORMATTED_VALUE".
93

94
        Returns:
95
        List: List of cell values from the row.
96
        """
97

98
    def col_values(col: int, value_render_option: str = "FORMATTED_VALUE") -> List:
99
        """
100
        Get all values from specified column.
101

102
        Parameters:
103
        - col (int): Column number (1-indexed).
104
        - value_render_option (str): Value rendering option. Default: "FORMATTED_VALUE".
105

106
        Returns:
107
        List: List of cell values from the column.
108
        """
109
```
110

111
### Finding and Searching
112

113
Locate cells by value or pattern matching.
114

115
```python { .api }
116
class Worksheet:
117
    def find(query: str, in_row: int = None, in_column: int = None, case_sensitive: bool = True) -> Cell:
118
        """
119
        Find first cell matching query.
120

121
        Parameters:
122
        - query (str): Text or regular expression to search for.
123
        - in_row (int, optional): Limit search to specific row.
124
        - in_column (int, optional): Limit search to specific column.
125
        - case_sensitive (bool): Case-sensitive search. Default: True.
126

127
        Returns:
128
        Cell: First matching cell.
129

130
        Raises:
131
        CellNotFound: If no matching cell is found.
132
        """
133

134
    def find_all(query: str, in_row: int = None, in_column: int = None, case_sensitive: bool = True) -> List[Cell]:
135
        """
136
        Find all cells matching query.
137

138
        Parameters:
139
        - query (str): Text or regular expression to search for.
140
        - in_row (int, optional): Limit search to specific row.
141
        - in_column (int, optional): Limit search to specific column.
142
        - case_sensitive (bool): Case-sensitive search. Default: True.
143

144
        Returns:
145
        List[Cell]: List of matching cells.
146
        """
147

148
    def findall(query: str, in_row: int = None, in_column: int = None) -> List[Cell]:
149
        """
150
        Find all cells matching query (deprecated, use find_all).
151

152
        Parameters:
153
        - query (str): Text or regular expression to search for.
154
        - in_row (int, optional): Limit search to specific row.
155
        - in_column (int, optional): Limit search to specific column.
156

157
        Returns:
158
        List[Cell]: List of matching cells.
159
        """
160
```
161

162
### Writing and Updating Data
163

164
Update cell values and ranges with various input options.
165

166
```python { .api }
167
class Worksheet:
168
    def update(range_name: str = None, values: List[List] = None, value_input_option: str = "RAW",
169
               major_dimension: str = None, include_values_in_response: bool = None,
170
               response_value_render_option: str = None, response_date_time_render_option: str = None) -> Dict:
171
        """
172
        Update values in specified range or entire worksheet.
173

174
        Parameters:
175
        - range_name (str, optional): A1 notation range. If None, starts from A1.
176
        - values (List[List], optional): 2D array of values to update.
177
        - value_input_option (str): "RAW" or "USER_ENTERED". Default: "RAW".
178
        - major_dimension (str, optional): "ROWS" or "COLUMNS".
179
        - include_values_in_response (bool, optional): Include updated values in response.
180
        - response_value_render_option (str, optional): Value rendering for response.
181
        - response_date_time_render_option (str, optional): Date/time rendering for response.
182

183
        Returns:
184
        Dict: Response from update operation.
185
        """
186

187
    def update_acell(label: str, value: Any) -> Cell:
188
        """
189
        Update single cell by A1 notation.
190

191
        Parameters:
192
        - label (str): A1 notation cell address (e.g., "A1", "B5").
193
        - value (Any): Value to set in the cell.
194

195
        Returns:
196
        Cell: Updated cell instance.
197
        """
198

199
    def update_cell(row: int, col: int, value: Any) -> Cell:
200
        """
201
        Update single cell by row and column coordinates.
202

203
        Parameters:
204
        - row (int): Row number (1-indexed).
205
        - col (int): Column number (1-indexed).
206
        - value (Any): Value to set in the cell.
207

208
        Returns:
209
        Cell: Updated cell instance.
210
        """
211

212
    def update_cells(range_name: str, values: List[List], value_input_option: str = "RAW") -> Dict:
213
        """
214
        Update multiple cells in specified range.
215

216
        Parameters:
217
        - range_name (str): A1 notation range.
218
        - values (List[List]): 2D array of values.
219
        - value_input_option (str): "RAW" or "USER_ENTERED". Default: "RAW".
220

221
        Returns:
222
        Dict: Response from update operation.
223
        """
224
```
225

226
### Appending Data
227

228
Add new data to existing ranges.
229

230
```python { .api }
231
class Worksheet:
232
    def append_row(values: List, value_input_option: str = "RAW", insert_data_option: str = "INSERT_ROWS",
233
                   table_range: str = None, include_values_in_response: bool = False) -> Dict:
234
        """
235
        Append single row of data.
236

237
        Parameters:
238
        - values (List): List of values for the new row.
239
        - value_input_option (str): "RAW" or "USER_ENTERED". Default: "RAW".
240
        - insert_data_option (str): "OVERWRITE" or "INSERT_ROWS". Default: "INSERT_ROWS".
241
        - table_range (str, optional): A1 notation range to append to.
242
        - include_values_in_response (bool): Include updated values in response. Default: False.
243

244
        Returns:
245
        Dict: Response from append operation.
246
        """
247

248
    def append_rows(values: List[List], value_input_option: str = "RAW", insert_data_option: str = "INSERT_ROWS",
249
                    table_range: str = None, include_values_in_response: bool = False) -> Dict:
250
        """
251
        Append multiple rows of data.
252

253
        Parameters:
254
        - values (List[List]): 2D array of values for new rows.
255
        - value_input_option (str): "RAW" or "USER_ENTERED". Default: "RAW".
256
        - insert_data_option (str): "OVERWRITE" or "INSERT_ROWS". Default: "INSERT_ROWS".
257
        - table_range (str, optional): A1 notation range to append to.
258
        - include_values_in_response (bool): Include updated values in response. Default: False.
259

260
        Returns:
261
        Dict: Response from append operation.
262
        """
263
```
264

265
### Batch Operations
266

267
Perform multiple operations efficiently.
268

269
```python { .api }
270
class Worksheet:
271
    def batch_get(ranges: List[str], value_render_option: str = "FORMATTED_VALUE",
272
                  date_time_render_option: str = "SERIAL_NUMBER", major_dimension: str = "ROWS") -> List[List]:
273
        """
274
        Get multiple ranges in single request.
275

276
        Parameters:
277
        - ranges (List[str]): List of A1 notation ranges.
278
        - value_render_option (str): Value rendering option. Default: "FORMATTED_VALUE".
279
        - date_time_render_option (str): Date/time rendering option. Default: "SERIAL_NUMBER".
280
        - major_dimension (str): "ROWS" or "COLUMNS". Default: "ROWS".
281

282
        Returns:
283
        List[List]: List of value arrays for each range.
284
        """
285

286
    def batch_clear(ranges: List[str]) -> Dict:
287
        """
288
        Clear multiple ranges in single request.
289

290
        Parameters:
291
        - ranges (List[str]): List of A1 notation ranges to clear.
292

293
        Returns:
294
        Dict: Response from clear operation.
295
        """
296

297
    def batch_update(body: Dict) -> Dict:
298
        """
299
        Execute batch update request.
300

301
        Parameters:
302
        - body (Dict): Batch update request body.
303

304
        Returns:
305
        Dict: Response from batch update operation.
306
        """
307
```
308

309
### Data Processing
310

311
Work with structured data and records.
312

313
```python { .api }
314
class Worksheet:
315
    def get_all_records(empty_value: str = "", head: int = 1, expected_headers: List[str] = None,
316
                       default_blank: str = "", allow_underscores_in_numeric_literals: bool = False,
317
                       numericise_ignore: List[str] = None, value_render_option: str = "FORMATTED_VALUE") -> List[Dict]:
318
        """
319
        Get all records as list of dictionaries using first row as headers.
320

321
        Parameters:
322
        - empty_value (str): Value to use for empty cells. Default: "".
323
        - head (int): Row number containing headers (1-indexed). Default: 1.
324
        - expected_headers (List[str], optional): List of expected header names.
325
        - default_blank (str): Default value for blank cells. Default: "".
326
        - allow_underscores_in_numeric_literals (bool): Allow underscores in numbers. Default: False.
327
        - numericise_ignore (List[str], optional): Headers to not convert to numbers.
328
        - value_render_option (str): Value rendering option. Default: "FORMATTED_VALUE".
329

330
        Returns:
331
        List[Dict]: List of dictionaries with headers as keys.
332
        """
333
```
334

335
### Clearing Data
336

337
Remove values from cells and ranges.
338

339
```python { .api }
340
class Worksheet:
341
    def clear() -> Dict:
342
        """
343
        Clear all values from worksheet.
344

345
        Returns:
346
        Dict: Response from clear operation.
347
        """
348
```
349

350
Usage examples:
351

352
```python
353
# Get all values
354
all_data = worksheet.get_all_values()
355

356
# Get specific range
357
range_data = worksheet.get('A1:C10')
358

359
# Get single cell
360
cell = worksheet.acell('B5')
361
print(f"Value: {cell.value}, Row: {cell.row}, Col: {cell.col}")
362

363
# Find cells
364
found_cell = worksheet.find('Alice')
365
all_matches = worksheet.find_all('.*@gmail\.com', case_sensitive=False)
366

367
# Update single cell
368
worksheet.update_acell('A1', 'New Value')
369

370
# Update range
371
worksheet.update('A1:C2', [
372
    ['Name', 'Age', 'City'],
373
    ['Alice', 25, 'NYC']
374
])
375

376
# Append data
377
worksheet.append_row(['Bob', 30, 'SF'])
378

379
# Get records as dictionaries
380
records = worksheet.get_all_records()
381
for record in records:
382
    print(f"Name: {record['Name']}, Age: {record['Age']}")
383
```