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:

tuple

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.cell_list_to_rect(cell_list: List[Cell]) List[List[str | None]]
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:
  • red (float) – Red color value (0-1).

  • green (float) – Green color value (0-1).

  • blue (float) – Blue color value (0-1).

Returns:

Hex color code in the format “#RRGGBB”.

Return type:

str

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:

dict

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.extract_id_from_url(url: str) str
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, ""],
    ["", "", "", ""]
]
Parameters:
  • L (list[list[T]]) – List of lists to fill gaps in.

  • rows (int) – Number of rows to fill.

  • cols (int) – Number of columns to fill.

  • padding_value (T) – Default value to fill gaps with.

Returns:

List of lists with gaps filled.

Return type:

list[list[T]]:

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.quote(value: str, safe: str = '', encoding: str = 'utf-8') str
gspread.utils.rightpad(row: List[Any], max_len: int, padding_value: Any = '') List[Any]
gspread.utils.rowcol_to_a1(row: int, col: int) str

Translates a row and column cell address to A1 notation.

Parameters:
  • row (int, str) – The row of the cell to be converted. Rows start at index 1.

  • col – The column of the cell to be converted. Columns start at index 1.

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",
    },
]
gspread.utils.wid_to_gid(wid: str) str

Calculate gid of a worksheet from its wid.