Utils

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.convert_credentials(credentials)
gspread.utils.extract_id_from_url(url)
gspread.utils.fill_gaps(L, rows=None, cols=None)
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.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)
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.