Utils
gspread.utils
This module contains utility functions.
- class gspread.utils.DateTimeOption(value, names=None, *, module=None, qualname=None, type=None, start=1, boundary=None)
- formatted_string = 'FORMATTED_STRING'
- serial_number = 'SERIAL_NUMBER'
- class gspread.utils.Dimension(value, names=None, *, module=None, qualname=None, type=None, start=1, boundary=None)
- cols = 'COLUMNS'
- rows = 'ROWS'
- class gspread.utils.ExportFormat(value, names=None, *, module=None, qualname=None, type=None, start=1, boundary=None)
- CSV = 'text/csv'
- EXCEL = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
- OPEN_OFFICE_SHEET = 'application/vnd.oasis.opendocument.spreadsheet'
- PDF = 'application/pdf'
- TSV = 'text/tab-separated-values'
- ZIPPED_HTML = 'application/zip'
- class gspread.utils.GridRangeType(value, names=None, *, module=None, qualname=None, type=None, start=1, boundary=None)
- ListOfLists = 'ListOfLists'
- ValueRange = 'ValueRange'
- class gspread.utils.InsertDataOption(value, names=None, *, module=None, qualname=None, type=None, start=1, boundary=None)
- insert_rows = 'INSERT_ROWS'
- overwrite = 'OVERWRITE'
- class gspread.utils.MergeType(value, names=None, *, module=None, qualname=None, type=None, start=1, boundary=None)
- merge_all = 'MERGE_ALL'
- merge_columns = 'MERGE_COLUMNS'
- merge_rows = 'MERGE_ROWS'
- class gspread.utils.MimeType(value, names=None, *, module=None, qualname=None, type=None, start=1, boundary=None)
- csv = 'text/csv'
- excel = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
- google_sheets = 'application/vnd.google-apps.spreadsheet'
- open_office_sheet = 'application/vnd.oasis.opendocument.spreadsheet'
- pdf = 'application/pdf'
- tsv = 'text/tab-separated-values'
- zip = 'application/zip'
- class gspread.utils.PasteOrientation(value, names=None, *, module=None, qualname=None, type=None, start=1, boundary=None)
- normal = 'NORMAL'
- transpose = 'TRANSPOSE'
- class gspread.utils.PasteType(value, names=None, *, module=None, qualname=None, type=None, start=1, boundary=None)
- conditional_formating = 'PASTE_CONDITIONAL_FORMATTING'
- data_validation = 'PASTE_DATA_VALIDATION'
- format = 'PASTE_FORMAT'
- formula = 'PASTE_NO_BORDERS'
- no_borders = 'PASTE_NO_BORDERS'
- normal = 'PASTE_NORMAL'
- values = 'PASTE_VALUES'
- class gspread.utils.ValueInputOption(value, names=None, *, module=None, qualname=None, type=None, start=1, boundary=None)
- raw = 'RAW'
- user_entered = 'USER_ENTERED'
- class gspread.utils.ValueRenderOption(value, names=None, *, module=None, qualname=None, type=None, start=1, boundary=None)
- formatted = 'FORMATTED_VALUE'
- formula = 'FORMULA'
- unformatted = 'UNFORMATTED_VALUE'
- gspread.utils.a1_range_to_grid_range(name: str, sheet_id: int | None = None) Dict[str, int]
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: str) Tuple[int, int]
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: str, range_name: str | None = None) str
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.cast_to_a1_notation(method: Callable[[...], Any]) Callable[[...], Any]
Decorator function casts wrapped arguments to A1 notation in range method calls.
- gspread.utils.column_letter_to_index(column: str) int
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, start_row_index, start_col_index)
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.
start_row_index – The index of the first row of the values in the worksheet. e.g., if the values are in rows 3-5, this should be 2.
start_col_index – The index of the first column of the values in the worksheet. e.g., if the values are in columns C-E, this should be 2.
- 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: 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.fill_gaps(L: List[List[Any]], rows: int | None = None, cols: int | None = None, padding_value: Any = '') List[List[Any]]
Fill gaps in a list of lists. e.g.,:
>>> L = [ ... [1, 2, 3], ... ] >>> fill_gaps(L, 2, 4) [ [1, 2, 3, ""], ["", "", "", ""] ]
- gspread.utils.finditem(func: Callable[[T], bool], seq: Iterable[T]) T
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: Any) bool
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: AnyStr | None, empty2zero: bool = False, default_blank: Any = '', allow_underscores_in_numeric_literals: bool = False) int | float | AnyStr | None
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: List[AnyStr | None], empty2zero: bool = False, default_blank: Any = '', allow_underscores_in_numeric_literals: bool = False, ignore: List[int] = []) List[int | float | AnyStr | None]
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.rowcol_to_a1(row: int, col: int) str
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.to_records(headers: Iterable[Any] = [], values: Iterable[Iterable[Any]] = [[]]) List[Dict[str, str | int | float]]
Builds the list of dictionaries, all of them have the headers sequence as keys set, each key is associated to the corresponding value for the same index in each list from the matrix
values
. There are as many dictionaries as they are entry in the list of given values.- Parameters:
list – headers the key set for all dictionaries
list – values a matrix of values
Examples:
>>> to_records(["name", "City"], [["Spiderman", "NY"], ["Batman", "Gotham"]]) [ { "Name": "Spiderman", "City": "NY", }, { "Name": "Batman", "City": "Gotham", }, ]