0
# Utilities & Helpers
1

2
Utility functions and helper classes for coordinate conversion, data processing, and working with spreadsheet elements.
3

4
## Capabilities
5

6
### Coordinate Conversion
7

8
Convert between A1 notation and row/column coordinates.
9

10
```python { .api }
11
def a1_to_rowcol(label: str) -> Tuple[int, int]:
12
    """
13
    Convert A1 notation to row/column coordinates.
14

15
    Parameters:
16
    - label (str): A1 notation cell address (e.g., "A1", "Z10", "AA5").
17

18
    Returns:
19
    Tuple[int, int]: Row and column as 1-indexed integers (row, col).
20
    """
21

22
def rowcol_to_a1(row: int, col: int) -> str:
23
    """
24
    Convert row/column coordinates to A1 notation.
25

26
    Parameters:
27
    - row (int): Row number (1-indexed).
28
    - col (int): Column number (1-indexed).
29

30
    Returns:
31
    str: A1 notation cell address.
32
    """
33

34
def column_letter_to_index(column: str) -> int:
35
    """
36
    Convert column letter to index.
37

38
    Parameters:
39
    - column (str): Column letter (e.g., "A", "Z", "AA").
40

41
    Returns:
42
    int: Column index (1-indexed).
43
    """
44
```
45

46
### Range Processing
47

48
Work with ranges and range notation.
49

50
```python { .api }
51
def absolute_range_name(sheet_name: str, range_name: str) -> str:
52
    """
53
    Create absolute range name with sheet reference.
54

55
    Parameters:
56
    - sheet_name (str): Worksheet name.
57
    - range_name (str): A1 notation range.
58

59
    Returns:
60
    str: Absolute range name (e.g., "'Sheet1'!A1:B10").
61
    """
62

63
def a1_range_to_grid_range(name: str, default_sheet_id: int = 0) -> Dict:
64
    """
65
    Convert A1 notation range to grid range object.
66

67
    Parameters:
68
    - name (str): A1 notation range.
69
    - default_sheet_id (int): Sheet ID if not specified in range. Default: 0.
70

71
    Returns:
72
    Dict: Grid range object with startRowIndex, endRowIndex, startColumnIndex, endColumnIndex.
73
    """
74

75
def is_full_a1_notation(range_name: str) -> bool:
76
    """
77
    Check if range name includes sheet reference.
78

79
    Parameters:
80
    - range_name (str): Range name to check.
81

82
    Returns:
83
    bool: True if range includes sheet reference.
84
    """
85
```
86

87
### Data Type Conversion
88

89
Convert and process cell values.
90

91
```python { .api }
92
def numericise(value: str, default_blank: str = "", ignore: List[str] = None,
93
               allow_underscores_in_numeric_literals: bool = False) -> Union[str, int, float]:
94
    """
95
    Convert string values to appropriate numeric types.
96

97
    Parameters:
98
    - value (str): String value to convert.
99
    - default_blank (str): Value to return for blank strings. Default: "".
100
    - ignore (List[str], optional): List of strings to not convert.
101
    - allow_underscores_in_numeric_literals (bool): Allow underscores in numbers. Default: False.
102

103
    Returns:
104
    Union[str, int, float]: Converted value (int, float, or original string).
105
    """
106

107
def to_records(values: List[List], keys: List[str] = None, **kwargs) -> List[Dict]:
108
    """
109
    Convert 2D list to list of dictionaries.
110

111
    Parameters:
112
    - values (List[List]): 2D array of values.
113
    - keys (List[str], optional): Keys for dictionaries. If None, uses first row.
114
    - **kwargs: Additional arguments passed to record conversion.
115

116
    Returns:
117
    List[Dict]: List of dictionaries with keys as column headers.
118
    """
119
```
120

121
### Data Processing
122

123
Process and manipulate data structures.
124

125
```python { .api }
126
def fill_gaps(values: List[List], rows: int = None, cols: int = None) -> List[List]:
127
    """
128
    Fill gaps in 2D list to ensure rectangular shape.
129

130
    Parameters:
131
    - values (List[List]): 2D list with potential gaps.
132
    - rows (int, optional): Target number of rows. If None, uses maximum row count.
133
    - cols (int, optional): Target number of columns. If None, uses maximum column count.
134

135
    Returns:
136
    List[List]: Filled 2D list with consistent dimensions.
137
    """
138

139
def cell_list_to_rect(cell_list: List[Cell]) -> List[List[Cell]]:
140
    """
141
    Convert list of Cell objects to rectangular 2D structure.
142

143
    Parameters:
144
    - cell_list (List[Cell]): List of Cell objects.
145

146
    Returns:
147
    List[List[Cell]]: 2D list of Cell objects arranged by row/column.
148
    """
149

150
def filter_dict_values(input_dict: Dict, filter_function: Callable) -> Dict:
151
    """
152
    Filter dictionary values using provided function.
153

154
    Parameters:
155
    - input_dict (Dict): Input dictionary to filter.
156
    - filter_function (Callable): Function to test each value.
157

158
    Returns:
159
    Dict: Filtered dictionary.
160
    """
161
```
162

163
### URL and ID Extraction
164

165
Extract identifiers from Google Sheets URLs.
166

167
```python { .api }
168
def extract_id_from_url(url: str) -> str:
169
    """
170
    Extract spreadsheet ID from Google Sheets URL.
171

172
    Parameters:
173
    - url (str): Google Sheets URL.
174

175
    Returns:
176
    str: Spreadsheet ID.
177

178
    Raises:
179
    NoValidUrlKeyFound: If URL doesn't contain valid spreadsheet ID.
180
    """
181

182
def get_gid_from_url(url: str) -> str:
183
    """
184
    Extract worksheet GID from Google Sheets URL.
185

186
    Parameters:
187
    - url (str): Google Sheets URL with GID parameter.
188

189
    Returns:
190
    str: Worksheet GID.
191
    """
192
```
193

194
### Helper Functions
195

196
Utility functions for data search and manipulation.
197

198
```python { .api }
199
def finditem(func: Callable, seq: Iterable) -> Any:
200
    """
201
    Find first item in sequence matching condition.
202

203
    Parameters:
204
    - func (Callable): Function to test each item.
205
    - seq (Iterable): Sequence to search.
206

207
    Returns:
208
    Any: First matching item, or None if not found.
209
    """
210

211
def quote(value: str, safe: str = "", encoding: str = None, errors: str = None) -> str:
212
    """
213
    URL encode string value.
214

215
    Parameters:
216
    - value (str): String to encode.
217
    - safe (str): Characters not to encode. Default: "".
218
    - encoding (str, optional): Character encoding.
219
    - errors (str, optional): Error handling scheme.
220

221
    Returns:
222
    str: URL-encoded string.
223
    """
224
```
225

226
### Data Processing Utilities
227

228
Helper functions for processing and manipulating worksheet data.
229

230
```python { .api }
231
def wid_to_gid(wid: str) -> str:
232
    """
233
    Calculate gid of a worksheet from its wid.
234

235
    Parameters:
236
    - wid (str): Worksheet ID (wid).
237

238
    Returns:
239
    str: The calculated gid.
240
    """
241

242
def is_scalar(x: Any) -> bool:
243
    """
244
    Return True if the value is scalar. A scalar is not a sequence but can be a string.
245

246
    Parameters:
247
    - x (Any): Value to check.
248

249
    Returns:
250
    bool: True if the value is scalar.
251
    """
252

253
def combined_merge_values(worksheet_metadata: Dict, values: List[List[Any]],
254
                         start_row_index: int, start_col_index: int) -> List[List[Any]]:
255
    """
256
    For each merged region, replace all values with the value of the top-left cell of the region.
257

258
    Parameters:
259
    - worksheet_metadata (Dict): The metadata returned by the Google API for the worksheet.
260
    - values (List[List[Any]]): The values matrix to process.
261
    - start_row_index (int): Starting row index.
262
    - start_col_index (int): Starting column index.
263

264
    Returns:
265
    List[List[Any]]: The processed values with merged regions replaced.
266
    """
267

268
def rightpad(row: List[Any], max_len: int, padding_value: Any = "") -> List[Any]:
269
    """
270
    Right-pad a row to reach the specified length.
271

272
    Parameters:
273
    - row (List[Any]): The row to pad.
274
    - max_len (int): The target length.
275
    - padding_value (Any): Value to use for padding. Default: "".
276

277
    Returns:
278
    List[Any]: The padded row.
279
    """
280

281
def fill_gaps(L: List[List[Any]], rows: int = None, cols: int = None,
282
              padding_value: Any = "") -> List[List[Any]]:
283
    """
284
    Fill gaps in a list of lists to make it rectangular.
285

286
    Parameters:
287
    - L (List[List[Any]]): The list of lists to fill gaps in.
288
    - rows (int, optional): Target number of rows.
289
    - cols (int, optional): Target number of columns.
290
    - padding_value (Any): Value to use for filling gaps. Default: "".
291

292
    Returns:
293
    List[List[Any]]: The filled matrix.
294
    """
295

296
def find_table(values: List[List[str]], start_range: str, direction: str = "table") -> List[List[str]]:
297
    """
298
    Expands a list of values based on non-null adjacent cells.
299

300
    Parameters:
301
    - values (List[List[str]]): Values where to find the table.
302
    - start_range (str): The starting cell range in A1 notation.
303
    - direction (str): The expand direction ('right', 'down', or 'table'). Default: 'table'.
304

305
    Returns:
306
    List[List[str]]: The resulting matrix.
307
    """
308
```
309

310
### Color Utilities
311

312
Color format conversion and processing.
313

314
```python { .api }
315
def convert_colors_to_hex_value(input_dict_or_list: Union[Dict, List]) -> Union[Dict, List]:
316
    """
317
    Convert color values to hexadecimal format.
318

319
    Parameters:
320
    - input_dict_or_list (Union[Dict, List]): Input containing color values.
321

322
    Returns:
323
    Union[Dict, List]: Input with colors converted to hex values.
324
    """
325
```
326

327
### Decorator Utilities
328

329
Function decorators for parameter validation.
330

331
```python { .api }
332
def accepted_kwargs(**kwargs) -> Callable:
333
    """
334
    Decorator to validate accepted keyword arguments.
335

336
    Parameters:
337
    - **kwargs: Accepted keyword arguments and their types/validators.
338

339
    Returns:
340
    Callable: Decorator function.
341
    """
342
```
343

344
## Enums and Constants
345

346
### Export Formats
347

348
```python { .api }
349
class ExportFormat(Enum):
350
    PDF = "pdf"
351
    EXCEL = "xlsx"
352
    ODS = "ods"
353
    CSV = "csv"
354
    TSV = "tsv"
355
    ZIP = "zip"
356
```
357

358
### Dimension Types
359

360
```python { .api }
361
class Dimension(Enum):
362
    ROWS = "ROWS"
363
    COLUMNS = "COLUMNS"
364
```
365

366
### Value Rendering Options
367

368
```python { .api }
369
class ValueRenderOption(Enum):
370
    FORMATTED_VALUE = "FORMATTED_VALUE"
371
    UNFORMATTED_VALUE = "UNFORMATTED_VALUE"
372
    FORMULA = "FORMULA"
373

374
class ValueInputOption(Enum):
375
    RAW = "RAW"
376
    USER_ENTERED = "USER_ENTERED"
377

378
class DateTimeRenderOption(Enum):
379
    SERIAL_NUMBER = "SERIAL_NUMBER"
380
    FORMATTED_STRING = "FORMATTED_STRING"
381
```
382

383
### Data Input Options
384

385
```python { .api }
386
class InsertDataOption(Enum):
387
    OVERWRITE = "OVERWRITE"
388
    INSERT_ROWS = "INSERT_ROWS"
389

390
class MergeType(Enum):
391
    MERGE_ALL = "MERGE_ALL"
392
    MERGE_COLUMNS = "MERGE_COLUMNS"
393
    MERGE_ROWS = "MERGE_ROWS"
394
```
395

396
### Formatting Constants
397

398
```python { .api }
399
class PasteType(Enum):
400
    PASTE_NORMAL = "PASTE_NORMAL"
401
    PASTE_VALUES = "PASTE_VALUES"
402
    PASTE_FORMAT = "PASTE_FORMAT"
403
    PASTE_NO_BORDERS = "PASTE_NO_BORDERS"
404
    PASTE_FORMULA = "PASTE_FORMULA"
405
    PASTE_DATA_VALIDATION = "PASTE_DATA_VALIDATION"
406
    PASTE_CONDITIONAL_FORMATTING = "PASTE_CONDITIONAL_FORMATTING"
407

408
class PasteOrientation(Enum):
409
    NORMAL = "NORMAL"
410
    TRANSPOSE = "TRANSPOSE"
411

412
class DelimiterType(Enum):
413
    COMMA = "COMMA"
414
    SEMICOLON = "SEMICOLON"
415
    PERIOD = "PERIOD"
416
    SPACE = "SPACE"
417
    CUSTOM = "CUSTOM"
418
    AUTODETECT = "AUTODETECT"
419
```
420

421
### Regular Expressions
422

423
```python { .api }
424
A1_ADDR_RE: Pattern
425
    """Regular expression for A1 cell addresses."""
426

427
A1_ADDR_FULL_RE: Pattern
428
    """Regular expression for full A1 addresses including sheet references."""
429
```
430

431
## Data Classes
432

433
### Grid and Range Classes
434

435
```python { .api }
436
class GridRange:
437
    """Grid range representation with start/end row/column indices."""
438
    sheetId: int
439
    startRowIndex: int
440
    endRowIndex: int
441
    startColumnIndex: int
442
    endColumnIndex: int
443

444
class RowData:
445
    """Represents data for a single row."""
446
    values: List[CellData]
447

448
class CellData:
449
    """Represents data for a single cell."""
450
    userEnteredValue: ExtendedValue
451
    effectiveValue: ExtendedValue
452
    formattedValue: str
453
    userEnteredFormat: CellFormat
454
    effectiveFormat: CellFormat
455

456
class ExtendedValue:
457
    """Extended value supporting different data types."""
458
    numberValue: float
459
    stringValue: str
460
    boolValue: bool
461
    formulaValue: str
462
```
463

464
### Formatting Classes
465

466
```python { .api }
467
class Color:
468
    """RGB color representation."""
469
    red: float    # 0-1
470
    green: float  # 0-1
471
    blue: float   # 0-1
472
    alpha: float  # 0-1
473

474
class ColorStyle:
475
    """Color style with theme support."""
476
    rgbColor: Color
477
    themeColor: str
478

479
class TextFormat:
480
    """Text formatting specification."""
481
    foregroundColor: ColorStyle
482
    fontFamily: str
483
    fontSize: int
484
    bold: bool
485
    italic: bool
486
    strikethrough: bool
487
    underline: bool
488

489
class NumberFormat:
490
    """Number formatting specification."""
491
    type: str     # "NUMBER", "CURRENCY", "PERCENT", etc.
492
    pattern: str  # Format pattern
493

494
class CellFormat:
495
    """Complete cell formatting specification."""
496
    numberFormat: NumberFormat
497
    backgroundColor: ColorStyle
498
    textFormat: TextFormat
499
    horizontalAlignment: str
500
    verticalAlignment: str
501
    wrapStrategy: str
502
    textDirection: str
503
```
504

505
## Usage Examples
506

507
```python
508
# Convert coordinates
509
row, col = gspread.utils.a1_to_rowcol('B5')  # (5, 2)
510
cell_addr = gspread.utils.rowcol_to_a1(5, 2)  # 'B5'
511

512
# Process data
513
values = [['Name', 'Age'], ['Alice', '25'], ['Bob', '30']]
514
records = gspread.utils.to_records(values)
515
# [{'Name': 'Alice', 'Age': 25}, {'Name': 'Bob', 'Age': 30}]
516

517
# Extract spreadsheet ID from URL
518
url = 'https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit'
519
sheet_id = gspread.utils.extract_id_from_url(url)
520

521
# Fill gaps in data
522
sparse_data = [['A', 'B'], ['C']]
523
filled_data = gspread.utils.fill_gaps(sparse_data)
524
# [['A', 'B'], ['C', '']]
525

526
# Use enums
527
from gspread.utils import ValueRenderOption, MergeType
528
worksheet.get_all_values(value_render_option=ValueRenderOption.UNFORMATTED_VALUE)
529
worksheet.merge_cells('A1:B2', merge_type=MergeType.MERGE_COLUMNS)
530
```