Utils
- gspread.utils.Dimension
Dimension(rows, cols)
- gspread.utils.DateTimeOption
DateTimeOption(serial_number, formatted_string, formated_string)
- gspread.utils.ExportFormat
ExportFormat(PDF, EXCEL, CSV, OPEN_OFFICE_SHEET, TSV, ZIPPED_HTML)
- gspread.utils.MimeType
MimeType(google_sheets, pdf, excel, csv, open_office_sheet, tsv, zip)
- gspread.utils.PasteOrientation
PasteOrientation(normal, transpose)
- gspread.utils.PasteType
PasteType(normal, values, format, no_borders, formula, data_validation, conditional_formating)
- gspread.utils.ValueInputOption
ValueInputOption(raw, user_entered)
- gspread.utils.ValueRenderOption
ValueRenderOption(formatted, unformatted, formula)
gspread.utils
This module contains utility functions.
- gspread.utils.a1_range_to_grid_range(name, sheet_id=None)
Converts a range defined in A1 notation to a dict representing a GridRange.
All indexes are zero-based. Indexes are half open, e.g the start index is inclusive and the end index is exclusive: [startIndex, endIndex).
Missing indexes indicate the range is unbounded on that side.
Examples:
>>> a1_range_to_grid_range('A1:A1') {'startRowIndex': 0, 'endRowIndex': 1, 'startColumnIndex': 0, 'endColumnIndex': 1}
>>> a1_range_to_grid_range('A3:B4') {'startRowIndex': 2, 'endRowIndex': 4, 'startColumnIndex': 0, 'endColumnIndex': 2}
>>> a1_range_to_grid_range('A:B') {'startColumnIndex': 0, 'endColumnIndex': 2}
>>> a1_range_to_grid_range('A5:B') {'startRowIndex': 4, 'startColumnIndex': 0, 'endColumnIndex': 2}
>>> a1_range_to_grid_range('A1') {'startRowIndex': 0, 'endRowIndex': 1, 'startColumnIndex': 0, 'endColumnIndex': 1}
>>> a1_range_to_grid_range('A') {'startColumnIndex': 0, 'endColumnIndex': 1}
>>> a1_range_to_grid_range('1') {'startRowIndex': 0, 'endRowIndex': 1}
>>> a1_range_to_grid_range('A1', sheet_id=0) {'sheetId': 0, 'startRowIndex': 0, 'endRowIndex': 1, 'startColumnIndex': 0, 'endColumnIndex': 1}
- gspread.utils.a1_to_rowcol(label)
Translates a cell’s address in A1 notation to a tuple of integers.
- Parameters:
label (str) – A cell label in A1 notation, e.g. ‘B1’. Letter case is ignored.
- Returns:
a tuple containing row and column numbers. Both indexed from 1 (one).
- Return type:
Example:
>>> a1_to_rowcol('A1') (1, 1)
- gspread.utils.absolute_range_name(sheet_name, range_name=None)
Return an absolutized path of a range.
>>> absolute_range_name("Sheet1", "A1:B1") "'Sheet1'!A1:B1"
>>> absolute_range_name("Sheet1", "A1") "'Sheet1'!A1"
>>> absolute_range_name("Sheet1") "'Sheet1'"
>>> absolute_range_name("Sheet'1") "'Sheet''1'"
>>> absolute_range_name("Sheet''1") "'Sheet''''1'"
>>> absolute_range_name("''sheet12''", "A1:B2") "'''''sheet12'''''!A1:B2"
- gspread.utils.accepted_kwargs(**default_kwargs)
>>> @accepted_kwargs(d='d', e=None) ... def foo(a, b, c='c', **kwargs): ... return { ... 'a': a, ... 'b': b, ... 'c': c, ... 'd': kwargs['d'], ... 'e': kwargs['e'], ... } ...
>>> foo('a', 'b') {'a': 'a', 'b': 'b', 'c': 'c', 'd': 'd', 'e': None}
>>> foo('a', 'b', 'NEW C') {'a': 'a', 'b': 'b', 'c': 'NEW C', 'd': 'd', 'e': None}
>>> foo('a', 'b', e='Not None') {'a': 'a', 'b': 'b', 'c': 'c', 'd': 'd', 'e': 'Not None'}
>>> foo('a', 'b', d='NEW D') {'a': 'a', 'b': 'b', 'c': 'c', 'd': 'NEW D', 'e': None}
>>> foo('a', 'b', a_typo='IS DETECTED') Traceback (most recent call last): ... TypeError: foo got unexpected keyword arguments: ['a_typo']
>>> foo('a', 'b', d='NEW D', c='THIS DOES NOT WORK BECAUSE OF d') Traceback (most recent call last): ... TypeError: foo got unexpected keyword arguments: ['c']
- gspread.utils.cast_to_a1_notation(method)
Decorator function casts wrapped arguments to A1 notation in range method calls.
- gspread.utils.cell_list_to_rect(cell_list)
- gspread.utils.column_letter_to_index(column)
Converts a column letter to its numerical index.
This is useful when using the method
gspread.worksheet.Worksheet.col_values()
. Which requires a column index.This function is case-insensitive.
Raises
gspread.exceptions.InvalidInputValue
in case of invalid input.Examples:
>>> column_letter_to_index("a") 1
>>> column_letter_to_index("A") 1
>>> column_letter_to_index("AZ") 52
>>> column_letter_to_index("!@#$%^&") ... gspread.exceptions.InvalidInputValue: invalid value: !@#$%^&, must be a column letter
- gspread.utils.combined_merge_values(worksheet_metadata, values)
For each merged region, replace all values with the value of the top-left cell of the region. e.g., replaces [ [1, None, None], [None, None, None], ] with [ [1, 1, None], [1, 1, None], ] if the top-left four cells are merged.
- Parameters:
worksheet_metadata – The metadata returned by the Google API for the worksheet. Should have a “merges” key.
values – The values returned by the Google API for the worksheet. 2D array.
- gspread.utils.convert_colors_to_hex_value(red: float = 0.0, green: float = 0.0, blue: float = 0.0) str
Convert RGB color values to a hex color code.
- Parameters:
- Returns:
Hex color code in the format “#RRGGBB”.
- Return type:
- Raises:
ValueError: If any color value is out of the accepted range (0-1).
Example:
>>> convert_colors_to_hex_value(0.2, 0, 0.8) '#3300CC'
>>> convert_colors_to_hex_value(green=0.5) '#008000'
- gspread.utils.convert_credentials(credentials)
- gspread.utils.convert_hex_to_colors_dict(hex_color: str) Mapping[str, float]
Convert a hex color code to RGB color values.
- Parameters:
hex_color (str) – Hex color code in the format “#RRGGBB”.
- Returns:
Dict containing the color’s red, green and blue values between 0 and 1.
- Return type:
- Raises:
ValueError: If the input hex string is not in the correct format or length.
- Examples:
>>> convert_hex_to_colors_dict("#3300CC") {'red': 0.2, 'green': 0.0, 'blue': 0.8}
>>> convert_hex_to_colors_dict("#30C") {'red': 0.2, 'green': 0.0, 'blue': 0.8}
- gspread.utils.deprecation_warning(version: str, msg: str) None
Emit a deprecation warning.
..note:
This warning can be silenced by setting the environment variable: GSPREAD_SILENCE_WARNINGS=1
- gspread.utils.extract_id_from_url(url)
- gspread.utils.fill_gaps(L, rows=None, cols=None, padding_value='')
Fill gaps in a list of lists. e.g.,:
>>> L = [ ... [1, 2, 3], ... ] >>> fill_gaps(L, 2, 4) [ [1, 2, 3, ""], ["", "", "", ""] ]
- gspread.utils.filter_dict_values(D)
Return a shallow copy of D with all None values excluded.
>>> filter_dict_values({'a': 1, 'b': 2, 'c': None}) {'a': 1, 'b': 2}
>>> filter_dict_values({'a': 1, 'b': 2, 'c': 0}) {'a': 1, 'b': 2, 'c': 0}
>>> filter_dict_values({}) {}
>>> filter_dict_values({'imnone': None}) {}
- gspread.utils.finditem(func, seq)
Finds and returns first item in iterable for which func(item) is True.
- gspread.utils.get_a1_from_absolute_range(range_name: str) str
Get the A1 notation from an absolute range name. “Sheet1!A1:B2” -> “A1:B2” “A1:B2” -> “A1:B2”
- Args:
range_name (str): The range name to check.
- Returns:
str: The A1 notation of the range name stripped of the sheet.
- gspread.utils.is_full_a1_notation(range_name: str) bool
Check if the range name is a full A1 notation. “A1:B2”, “Sheet1!A1:B2” are full A1 notations “A1:B”, “A1” are not
- Args:
range_name (str): The range name to check.
- Returns:
bool: True if the range name is a full A1 notation, False otherwise.
Examples:
>>> is_full_a1_notation("A1:B2") True
>>> is_full_a1_notation("A1:B") False
- gspread.utils.is_scalar(x)
Return True if the value is scalar.
A scalar is not a sequence but can be a string.
>>> is_scalar([]) False
>>> is_scalar([1, 2]) False
>>> is_scalar(42) True
>>> is_scalar('nice string') True
>>> is_scalar({}) True
>>> is_scalar(set()) True
- gspread.utils.numericise(value, empty2zero=False, default_blank='', allow_underscores_in_numeric_literals=False)
Returns a value that depends on the input:
Float if input is a string that can be converted to Float
Integer if input is a string that can be converted to integer
Zero if the input is a string that is empty and empty2zero flag is set
The unmodified input value, otherwise.
Examples:
>>> numericise("faa") 'faa'
>>> numericise("3") 3
>>> numericise("3_2", allow_underscores_in_numeric_literals=False) '3_2'
>>> numericise("3_2", allow_underscores_in_numeric_literals=True) 32
>>> numericise("3.1") 3.1
>>> numericise("2,000.1") 2000.1
>>> numericise("", empty2zero=True) 0
>>> numericise("", empty2zero=False) ''
>>> numericise("", default_blank=None) >>>
>>> numericise("", default_blank="foo") 'foo'
>>> numericise("") ''
>>> numericise(None) >>>
- gspread.utils.numericise_all(values, empty2zero=False, default_blank='', allow_underscores_in_numeric_literals=False, ignore=[])
Returns a list of numericised values from strings except those from the row specified as ignore.
- Parameters:
values (list) – Input row
empty2zero (bool) – (optional) Whether or not to return empty cells as 0 (zero). Defaults to
False
.default_blank (str) – Which value to use for blank cells, defaults to empty string.
allow_underscores_in_numeric_literals (bool) – Whether or not to allow visual underscores in numeric literals
ignore (list) – List of ints of indices of the row (index 1) to ignore numericising.
- gspread.utils.quote(value, safe='', encoding='utf-8')
- gspread.utils.rightpad(row, max_len, padding_value='')
- gspread.utils.rowcol_to_a1(row, col)
Translates a row and column cell address to A1 notation.
- Parameters:
- Returns:
a string containing the cell’s coordinates in A1 notation.
Example:
>>> rowcol_to_a1(1, 1) A1
- gspread.utils.wid_to_gid(wid)
Calculate gid of a worksheet from its wid.