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.MergeType: MergeType(merge_all, merge_columns, merge_rows)

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:: tuple

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, 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)

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.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, ""],
    ["", "", "", ""]
]

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.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:

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.wid_to_gid(wid): Calculate gid of a worksheet from its wid.