Worksheet

ValueRange

class gspread.worksheet.ValueRange(iterable=(), /)

The class holds the returned values.

This class inherit the list object type. It behaves exactly like a list.

The values are stored in a matrix.

The property gspread.worksheet.ValueRange.major_dimension() holds the major dimension of the first list level.

The inner lists will contain the actual values.

Examples:

>>> worksheet.get("A1:B2")
[
    [
        "A1 value",
        "B1 values",
    ],
    [
        "A2 value",
        "B2 value",
    ]
]

>>> worksheet.get("A1:B2").major_dimension
ROW

Note

This class should never be instantiated manually. It will be instantiated using the response from the sheet API.

first(default: str | None = None) → str | None

Returns the value of a first cell in a range.

If the range is empty, return the default value.

property major_dimension: str

The major dimension of this range

Can be one of:

  • ROW: the first list level holds rows of values

  • COLUMNS: the first list level holds columns of values

property range: str

The range of the values

Worksheet

class gspread.worksheet.Worksheet(spreadsheet_id: str, client: HTTPClient, properties: MutableMapping[str, Any])

The class that represents a single sheet in a spreadsheet (aka “worksheet”).

acell(label: str, value_render_option: ValueRenderOption = ValueRenderOption.formatted) → Cell

Returns an instance of a gspread.cell.Cell.

Parameters:

  • label (str) – Cell label in A1 notation Letter case is ignored.

  • value_render_option (ValueRenderOption) – (optional) Determines how values should be rendered in the output. See ValueRenderOption in the Sheets API.

Example:

>>> worksheet.acell('A1')
<Cell R1C1 "I'm cell A1">
add_cols(cols: int) → None

Adds columns to worksheet.

Parameters:

cols (int) – Number of new columns to add.

add_dimension_group_columns(start: int, end: int) → MutableMapping[str, Any]

Group columns in order to hide them in the UI.

Note

API behavior with nested groups and non-matching [start:end) range can be found here: Add Dimension Group Request

Parameters:

  • start (int) – The start (inclusive) of the group

  • end (int) – The end (exclusive) of the group

add_dimension_group_rows(start: int, end: int) → MutableMapping[str, Any]

Group rows in order to hide them in the UI.

Note

API behavior with nested groups and non-matching [start:end) range can be found here Add Dimension Group Request

Parameters:

  • start (int) – The start (inclusive) of the group

  • end (int) – The end (exclusive) of the group

add_protected_range(name: str, editor_users_emails: Sequence[str], editor_groups_emails: Sequence[str] = [], description: str | None = None, warning_only: bool = False, requesting_user_can_edit: bool = False) → MutableMapping[str, Any]

Add protected range to the sheet. Only the editors can edit the protected range.

Google API will automatically add the owner of this SpreadSheet. The list editor_users_emails must at least contain the e-mail address used to open that SpreadSheet.

editor_users_emails must only contain e-mail addresses who already have a write access to the spreadsheet.

Parameters:

name (str) – A string with range value in A1 notation, e.g. ‘A1:A5’.

Alternatively, you may specify numeric boundaries. All values index from 1 (one):

Parameters:

  • first_row (int) – First row number

  • first_col (int) – First column number

  • last_row (int) – Last row number

  • last_col (int) – Last column number

For both A1 and numeric notation:

Parameters:

  • editor_users_emails (list) – The email addresses of users with edit access to the protected range. This must include your e-mail address at least.

  • editor_groups_emails (list) – (optional) The email addresses of groups with edit access to the protected range.

  • description (str) – (optional) Description for the protected ranges.

  • warning_only (boolean) – (optional) When true this protected range will show a warning when editing. Defaults to False.

  • requesting_user_can_edit (boolean) – (optional) True if the user who requested this protected range can edit the protected cells. Defaults to False.

add_rows(rows: int) → None

Adds rows to worksheet.

Parameters:

rows (int) – Number of new rows to add.

append_row(values: Sequence[str | int | float], value_input_option: ValueInputOption = ValueInputOption.raw, insert_data_option: InsertDataOption | None = None, table_range: str | None = None, include_values_in_response: bool = False) → MutableMapping[str, Any]

Adds a row to the worksheet and populates it with values.

Widens the worksheet if there are more values than columns.

Parameters:

  • values (list) – List of values for the new row.

  • value_input_option (ValueInputOption) – (optional) Determines how the input data should be interpreted. See ValueInputOption in the Sheets API reference.

  • insert_data_option (str) – (optional) Determines how the input data should be inserted. See InsertDataOption in the Sheets API reference.

  • table_range (str) – (optional) The A1 notation of a range to search for a logical table of data. Values are appended after the last row of the table. Examples: A1 or B2:D4

  • include_values_in_response (bool) – (optional) Determines if the update response should include the values of the cells that were appended. By default, responses do not include the updated values.

append_rows(values: Sequence[Sequence[str | int | float]], value_input_option: ValueInputOption = ValueInputOption.raw, insert_data_option: InsertDataOption | None = None, table_range: str | None = None, include_values_in_response: bool | None = None) → MutableMapping[str, Any]

Adds multiple rows to the worksheet and populates them with values.

Widens the worksheet if there are more values than columns.

Parameters:

  • values (list) – List of rows each row is List of values for the new row.

  • value_input_option (ValueInputOption) – (optional) Determines how input data should be interpreted. Possible values are ValueInputOption.raw or ValueInputOption.user_entered. See ValueInputOption in the Sheets API.

  • insert_data_option (str) – (optional) Determines how the input data should be inserted. See InsertDataOption in the Sheets API reference.

  • table_range (str) – (optional) The A1 notation of a range to search for a logical table of data. Values are appended after the last row of the table. Examples: A1 or B2:D4

  • include_values_in_response (bool) – (optional) Determines if the update response should include the values of the cells that were appended. By default, responses do not include the updated values.

batch_clear(ranges: Sequence[str]) → MutableMapping[str, Any]

Clears multiple ranges of cells with 1 API call.

Batch Clear

Examples:

worksheet.batch_clear(['A1:B1','my_range'])

# Note: named ranges are defined in the scope of
# a spreadsheet, so even if `my_range` does not belong to
# this sheet it is still updated

New in version 3.8.0.

batch_format(formats: List[CellFormat]) → MutableMapping[str, Any]

Formats cells in batch.

Parameters:

formats (list) –

List of ranges to format and the new format to apply to each range.

The list is composed of dict objects with the following keys/values:

  • range : A1 range notation

  • format : a valid dict object with the format to apply for that range see CellFormat in the Sheets API for available fields.

Examples:

# Format the range ``A1:C1`` with bold text
# and format the range ``A2:C2`` a font size of 16

formats = [
    {
        "range": "A1:C1",
        "format": {
            "textFormat": {
                "bold": True,
            },
        },
    },
    {
        "range": "A2:C2",
        "format": {
            "textFormat": {
                "fontSize": 16,
            },
        },
    },
]
worksheet.batch_format(formats)

New in version 5.4.

batch_get(ranges: Iterable[str], major_dimension: Dimension | None = None, value_render_option: ValueRenderOption | None = None, date_time_render_option: DateTimeOption | None = None) → List[ValueRange]

Returns one or more ranges of values from the sheet.

Parameters:

  • ranges (list) – List of cell ranges in the A1 notation or named ranges.

  • major_dimension (Dimension) – (optional) The major dimension of the values. Dimension.rows (“ROWS”) or Dimension.cols (“COLUMNS”). Defaults to Dimension.rows

  • value_render_option (ValueRenderOption) –

    (optional) Determines how values should be rendered in the output. See ValueRenderOption in the Sheets API.

    Possible values are:

    ValueRenderOption.formatted

    (default) Values will be calculated and formatted according to the cell’s formatting. Formatting is based on the spreadsheet’s locale, not the requesting user’s locale.

    ValueRenderOption.unformatted

    Values will be calculated, but not formatted in the reply. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return the number 1.23.

    ValueRenderOption.formula

    Values will not be calculated. The reply will include the formulas. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return “=A1”.

  • date_time_render_option (DateTimeOption) –

    (optional) How dates, times, and durations should be represented in the output.

    Possible values are:

    DateTimeOption.serial_number

    (default) Instructs date, time, datetime, and duration fields to be output as doubles in “serial number” format, as popularized by Lotus 1-2-3.

    DateTimeOption.formatted_string

    Instructs date, time, datetime, and duration fields to be output as strings in their given number format (which depends on the spreadsheet locale).

    Note

    This is ignored if value_render_option is ValueRenderOption.formatted.

    The default date_time_render_option is DateTimeOption.serial_number.

New in version 3.3.

Examples:

# Read values from 'A1:B2' range and 'F12' cell
worksheet.batch_get(['A1:B2', 'F12'])
batch_update(data: Iterable[MutableMapping[str, Any]], raw: bool = True, value_input_option: ValueInputOption | None = None, include_values_in_response: bool | None = None, response_value_render_option: ValueRenderOption | None = None, response_date_time_render_option: DateTimeOption | None = None) → MutableMapping[str, Any]

Sets values in one or more cell ranges of the sheet at once.

Parameters:

  • data (list) – List of dictionaries in the form of {‘range’: ‘…’, ‘values’: [[.., ..], …]} where range is a target range to update in A1 notation or a named range, and values is a list of lists containing new values.

  • value_input_option (ValueInputOption) –

    (optional) How the input data should be interpreted. Possible values are:

    • ValueInputOption.raw

      The values the user has entered will not be parsed and will be stored as-is.

    • ValueInputOption.user_entered

      The values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI.

  • response_value_render_option (ValueRenderOption) –

    (optional) Determines how values should be rendered in the output. See ValueRenderOption in the Sheets API.

    Possible values are:

    ValueRenderOption.formatted

    (default) Values will be calculated and formatted according to the cell’s formatting. Formatting is based on the spreadsheet’s locale, not the requesting user’s locale.

    ValueRenderOption.unformatted

    Values will be calculated, but not formatted in the reply. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return the number 1.23.

    ValueRenderOption.formula

    Values will not be calculated. The reply will include the formulas. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return “=A1”.

  • response_date_time_render_option (str) –

    (optional) How dates, times, and durations should be represented in the output.

    Possible values are:

    DateTimeOption.serial_number

    (default) Instructs date, time, datetime, and duration fields to be output as doubles in “serial number” format, as popularized by Lotus 1-2-3.

    DateTimeOption.formatted_string

    Instructs date, time, datetime, and duration fields to be output as strings in their given number format (which depends on the spreadsheet locale).

    Note

    This is ignored if value_render_option is ValueRenderOption.formatted.

    The default date_time_render_option is DateTimeOption.serial_number.

Examples:

worksheet.batch_update([{
    'range': 'A1:B1',
    'values': [['42', '43']],
}, {
    'range': 'my_range',
    'values': [['44', '45']],
}])

# Note: named ranges are defined in the scope of
# a spreadsheet, so even if `my_range` does not belong to
# this sheet it is still updated

New in version 3.3.

cell(row: int, col: int, value_render_option: ValueRenderOption = ValueRenderOption.formatted) → Cell

Returns an instance of a gspread.cell.Cell located at row and col column.

Parameters:

  • row (int) – Row number.

  • col (int) – Column number.

  • value_render_option (ValueRenderOption) – (optional) Determines how values should be rendered in the output. See ValueRenderOption in the Sheets API.

Example:

>>> worksheet.cell(1, 1)
<Cell R1C1 "I'm cell A1">
Return type:

gspread.cell.Cell

clear() → MutableMapping[str, Any]

Clears all cells in the worksheet.

clear_basic_filter() → MutableMapping[str, Any]

Remove the basic filter from a worksheet.

New in version 3.4.

clear_note(cell: str) → None

Clear a note. The note is attached to a certain cell.

Parameters:

cell (str) – A string with cell coordinates in A1 notation, e.g. ‘D7’.

Alternatively, you may specify numeric boundaries. All values index from 1 (one):

Parameters:

  • first_row (int) – First row number

  • first_col (int) – First column number

  • last_row (int) – Last row number

  • last_col (int) – Last column number

New in version 3.7.

clear_notes(ranges: Iterable[str]) → None

Clear all notes located at the at the coordinates pointed to by ranges.

Parameters:

list (ranges) – List of A1 coordinates where to clear the notes. e.g. ["A1", "GH42", "D7"]

clear_tab_color() → MutableMapping[str, Any]

Clears the worksheet’s tab color. Use update_tab_color() to set the color.

property col_count: int

Number of columns.

Warning

This value is fetched when opening the worksheet. This is not dynamically updated when adding columns, yet.

col_values(col: int, value_render_option: ValueRenderOption = ValueRenderOption.formatted) → List[int | float | str | None]

Returns a list of all values in column col.

Empty cells in this list will be rendered as None.

Parameters:

  • col (int) – Column number (one-based).

  • value_render_option (ValueRenderOption) – (optional) Determines how values should be rendered in the output. See ValueRenderOption in the Sheets API.

property column_count: int

Number of columns

columns_auto_resize(start_column_index: int, end_column_index: int) → MutableMapping[str, Any]

Updates the size of rows or columns in the worksheet.

Index start from 0

Parameters:

  • start_column_index – The index (inclusive) to begin resizing

  • end_column_index – The index (exclusive) to finish resizing

New in version 3.4.

Changed in version 5.3.3.

copy_range(source: str, dest: str, paste_type: PasteType = PasteType.normal, paste_orientation: PasteOrientation = PasteOrientation.normal) → MutableMapping[str, Any]

Copies a range of data from source to dest

Note

paste_type values are explained here: Paste Types

Parameters:

  • source (str) – The A1 notation of the source range to copy

  • dest (str) – The A1 notation of the destination where to paste the data Can be the A1 notation of the top left corner where the range must be paste ex: G16, or a complete range notation ex: G16:I20. The dimensions of the destination range is not checked and has no effect, if the destination range does not match the source range dimension, the entire source range is copies anyway.

  • paste_type (PasteType) – the paste type to apply. Many paste type are available from the Sheet API, see above note for detailed values for all values and their effects. Defaults to PasteType.normal

  • paste_orientation (PasteOrientation) – The paste orient to apply. Possible values are: normal to keep the same orientation, transpose where all rows become columns and vice versa.

copy_to(destination_spreadsheet_id: str) → MutableMapping[str, Any]

Copies this sheet to another spreadsheet.

Parameters:

spreadsheet_id (str) – The ID of the spreadsheet to copy the sheet to.

Returns:

a dict with the response containing information about the newly created sheet.

Return type:

dict

cut_range(source: str, dest: str, paste_type: PasteType = PasteType.normal) → MutableMapping[str, Any]

Moves a range of data form source to dest

Note

paste_type values are explained here: Paste Types

Parameters:

  • source (str) – The A1 notation of the source range to move

  • dest (str) – The A1 notation of the destination where to paste the data it must be a single cell in the A1 notation. ex: G16

  • paste_type (PasteType) – the paste type to apply. Many paste type are available from the Sheet API, see above note for detailed values for all values and their effects. Defaults to PasteType.normal

define_named_range(name: str, range_name: str) → MutableMapping[str, Any]
Parameters:

name (str) – A string with range value in A1 notation, e.g. ‘A1:A5’.

Alternatively, you may specify numeric boundaries. All values index from 1 (one):

Parameters:

  • first_row (int) – First row number

  • first_col (int) – First column number

  • last_row (int) – Last row number

  • last_col (int) – Last column number

  • range_name – The name to assign to the range of cells

Returns:

the response body from the request

Return type:

dict

delete_columns(start_index: int, end_index: int | None = None) → MutableMapping[str, Any]

Deletes multiple columns from the worksheet at the specified index.

Parameters:

  • start_index (int) – Index of a first column for deletion.

  • end_index (int) – Index of a last column for deletion. When end_index is not specified this method only deletes a single column at start_index.

delete_dimension(dimension: Dimension, start_index: int, end_index: int | None = None) → MutableMapping[str, Any]

Deletes multi rows from the worksheet at the specified index.

Parameters:

  • dimension (Dimension) – A dimension to delete. Dimension.rows or Dimension.cols.

  • start_index (int) – Index of a first row for deletion.

  • end_index (int) – Index of a last row for deletion. When end_index is not specified this method only deletes a single row at start_index.

delete_dimension_group_columns(start: int, end: int) → MutableMapping[str, Any]

Remove the grouping of a set of columns.

Note

API behavior with nested groups and non-matching [start:end) range can be found here Delete Dimension Group Request

Parameters:

  • start (int) – The start (inclusive) of the group

  • end (int) – The end (exclusive) of the group

delete_dimension_group_rows(start: int, end: int) → MutableMapping[str, Any]

Remove the grouping of a set of rows.

Note

API behavior with nested groups and non-matching [start:end) range can be found here Delete Dimension Group Request

Parameters:

  • start (int) – The start (inclusive) of the group

  • end (int) – The end (exclusive) of the group

delete_named_range(named_range_id: str) → MutableMapping[str, Any]
Parameters:

named_range_id (str) – The ID of the named range to delete. Can be obtained with Spreadsheet.list_named_ranges()

Returns:

the response body from the request

Return type:

dict

delete_protected_range(id: str) → MutableMapping[str, Any]

Delete protected range identified by the ID id.

To retrieve the ID of a protected range use the following method to list them all: list_protected_ranges()

delete_rows(start_index: int, end_index: int | None = None) → MutableMapping[str, Any]

Deletes multiple rows from the worksheet at the specified index.

Parameters:

  • start_index (int) – Index of a first row for deletion.

  • end_index (int) – Index of a last row for deletion. When end_index is not specified this method only deletes a single row at start_index.

Example:

# Delete rows 5 to 10 (inclusive)
worksheet.delete_rows(5, 10)

# Delete only the second row
worksheet.delete_rows(2)
duplicate(insert_sheet_index: int | None = None, new_sheet_id: int | None = None, new_sheet_name: str | None = None) → Worksheet

Duplicate the sheet.

Parameters:

  • insert_sheet_index (int) – (optional) The zero-based index where the new sheet should be inserted. The index of all sheets after this are incremented.

  • new_sheet_id (int) – (optional) The ID of the new sheet. If not set, an ID is chosen. If set, the ID must not conflict with any existing sheet ID. If set, it must be non-negative.

  • new_sheet_name (str) – (optional) The name of the new sheet. If empty, a new name is chosen for you.

Returns:

a newly created gspread.worksheet.Worksheet.

New in version 3.1.

find(query: str | Pattern, in_row: bool | None = None, in_column: bool | None = None, case_sensitive: bool = True) → Cell | None

Finds the first cell matching the query.

Parameters:

  • query (str, re.RegexObject) – A literal string to match or compiled regular expression.

  • in_row (int) – (optional) One-based row number to scope the search.

  • in_column (int) – (optional) One-based column number to scope the search.

  • case_sensitive (bool) – (optional) comparison is case sensitive if set to True, case insensitive otherwise. Default is True. Does not apply to regular expressions.

Returns:

the first matching cell or None otherwise

Return type:

gspread.cell.Cell

findall(query: str | Pattern, in_row: int | None = None, in_column: int | None = None, case_sensitive: bool = True) → List[Cell]

Finds all cells matching the query.

Returns a list of gspread.cell.Cell.

Parameters:

  • query (str, re.RegexObject) – A literal string to match or compiled regular expression.

  • in_row (int) – (optional) One-based row number to scope the search.

  • in_column (int) – (optional) One-based column number to scope the search.

  • case_sensitive (bool) – (optional) comparison is case sensitive if set to True, case insensitive otherwise. Default is True. Does not apply to regular expressions.

Returns:

the list of all matching cells or empty list otherwise

Return type:

list

format(ranges: List[str] | str, format: MutableMapping[str, Any]) → MutableMapping[str, Any]

Format a list of ranges with the given format.

Parameters:

  • ranges (str|list) – Target ranges in the A1 notation.

  • format (dict) – Dictionary containing the fields to update. See CellFormat in the Sheets API for available fields.

Examples:

# Set 'A4' cell's text format to bold
worksheet.format("A4", {"textFormat": {"bold": True}})

# Set 'A1:D4' and 'A10:D10' cells's text format to bold
worksheet.format(["A1:D4", "A10:D10"], {"textFormat": {"bold": True}})

# Color the background of 'A2:B2' cell range in black,
# change horizontal alignment, text color and font size
worksheet.format("A2:B2", {
    "backgroundColor": {
      "red": 0.0,
      "green": 0.0,
      "blue": 0.0
    },
    "horizontalAlignment": "CENTER",
    "textFormat": {
      "foregroundColor": {
        "red": 1.0,
        "green": 1.0,
        "blue": 1.0
      },
      "fontSize": 12,
      "bold": True
    }
})

New in version 3.3.

freeze(rows: int | None = None, cols: int | None = None) → MutableMapping[str, Any]

Freeze rows and/or columns on the worksheet.

Parameters:

  • rows – Number of rows to freeze.

  • cols – Number of columns to freeze.

property frozen_col_count: int

Number of frozen columns.

property frozen_row_count: int

Number of frozen rows.

get(range_name: str | None = None, major_dimension: Dimension | None = None, value_render_option: ValueRenderOption | None = None, date_time_render_option: DateTimeOption | None = None, combine_merged_cells: bool = False, maintain_size: bool = False, pad_values: bool = False, return_type: GridRangeType = GridRangeType.ValueRange) → ValueRange | List[List[Any]]

Reads values of a single range or a cell of a sheet.

Returns a ValueRange (list of lists) containing all values from a specified range or cell

By default values are returned as strings. See value_render_option to change the default format.

Parameters:

  • range_name (str) – (optional) Cell range in the A1 notation or a named range. If not specified the method returns values from all non empty cells.

  • major_dimension (Dimension) – (optional) The major dimension of the values. Dimension.rows (“ROWS”) or Dimension.cols (“COLUMNS”). Defaults to Dimension.rows

  • value_render_option (ValueRenderOption) –

    (optional) Determines how values should be rendered in the output. See ValueRenderOption in the Sheets API.

    Possible values are:

    ValueRenderOption.formatted

    (default) Values will be calculated and formatted according to the cell’s formatting. Formatting is based on the spreadsheet’s locale, not the requesting user’s locale.

    ValueRenderOption.unformatted

    Values will be calculated, but not formatted in the reply. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return the number 1.23.

    ValueRenderOption.formula

    Values will not be calculated. The reply will include the formulas. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return “=A1”.

  • date_time_render_option (DateTimeOption) –

    (optional) How dates, times, and durations should be represented in the output.

    Possible values are:

    DateTimeOption.serial_number

    (default) Instructs date, time, datetime, and duration fields to be output as doubles in “serial number” format, as popularized by Lotus 1-2-3.

    DateTimeOption.formatted_string

    Instructs date, time, datetime, and duration fields to be output as strings in their given number format (which depends on the spreadsheet locale).

    Note

    This is ignored if value_render_option is ValueRenderOption.formatted.

    The default date_time_render_option is DateTimeOption.serial_number.

  • combine_merged_cells (bool) –

    (optional) If True, then all cells that are part of a merged cell will have the same value as the top-left cell of the merged cell. Defaults to False.

    Warning

    Setting this to True will cause an additional API request to be made to retrieve the values of all merged cells.

  • maintain_size (bool) – (optional) If True, then the returned values will have the same size as the requested range_name. Defaults to False.

  • pad_values (bool) –

    (optional) If True, then empty cells will be filled with empty strings. Defaults to False.

    Warning

    The returned array will not be rectangular unless this is set to True. If this is a problem, see also maintain_size.

  • return_type (GridRangeType) – (optional) The type of object to return. Defaults to gspread.utils.GridRangeType.ValueRange. The other option is gspread.utils.GridRangeType.ListOfLists.

Return type:

gspread.worksheet.ValueRange

New in version 3.3.

Examples:

# Return all values from the sheet
worksheet.get()

# Return value of 'A1' cell
worksheet.get('A1')

# Return values of 'A1:B2' range
worksheet.get('A1:B2')

# Return all values from columns "A" and "B"
worksheet.get('A:B')

# Return values of 'my_range' named range
worksheet.get('my_range')

# Return unformatted values (e.g. numbers as numbers)
worksheet.get('A2:B4', value_render_option=ValueRenderOption.unformatted)

# Return cell values without calculating formulas
worksheet.get('A2:B4', value_render_option=ValueRenderOption.formula)
get_all_cells() → List[Cell]

Returns a list of all Cell of the current sheet.

get_all_records(head=1, expected_headers=None, value_render_option=None, default_blank='', numericise_ignore=[], allow_underscores_in_numeric_literals=False, empty2zero=False) → List[Dict[str, str | int | float]]

Returns a list of dictionaries, all of them having the contents of the spreadsheet with the head row as keys and each of these dictionaries holding the contents of subsequent rows of cells as values.

This method uses the function gspread.utils.to_records() to build the resulting records. It mainly wraps around the function and handle the simplest use case using a header row (default = 1) and the the reste of the entire sheet.

Note

for any particular use-case, please get your dataset, your headers then use the function gspread.utils.to_records() to build the records.

Cell values are numericised (strings that can be read as ints or floats are converted), unless specified in numericise_ignore

Parameters:

  • head (int) – (optional) Determines which row to use as keys, starting from 1 following the numeration of the spreadsheet.

  • expected_headers (list) –

    (optional) List of expected headers, they must be unique.

    Note

    returned dictionaries will contain all headers even if not included in this list

  • value_render_option (ValueRenderOption) – (optional) Determines how values should be rendered in the output. See ValueRenderOption in the Sheets API.

  • default_blank (str) – (optional) Determines which value to use for blank cells, defaults to empty string.

  • numericise_ignore (list) – (optional) List of ints of indices of the columns (starting at 1) to ignore numericising, special use of [‘all’] to ignore numericising on all columns.

  • allow_underscores_in_numeric_literals (bool) – (optional) Allow underscores in numeric literals, as introduced in PEP 515

  • empty2zero (bool) – (optional) Determines whether empty cells are converted to zeros when numericised, defaults to False.

Examples:

# Sheet data:
#      A    B    C
#
# 1    A1   B2   C3
# 2    A6   B7   C8
# 3    A11  B12  C13

# Read all rows from the sheet
>>> worksheet.get_all_records()
[
    {"A1": "A6", "B2": "B7", "C3": "C8"},
    {"A1": "A11", "B2": "B12", "C3": "C13"}
]
get_all_values(range_name: str | None = None, major_dimension: Dimension | None = None, value_render_option: ValueRenderOption | None = None, date_time_render_option: DateTimeOption | None = None, combine_merged_cells: bool = False, maintain_size: bool = False, pad_values: bool = True, return_type: GridRangeType = GridRangeType.ListOfLists) → List[List[T]]

Alias to get_values()

get_note(cell: str) → str

Get the content of the note located at cell, or the empty string if the cell does not have a note.

Parameters:

cell (str) – A string with cell coordinates in A1 notation, e.g. ‘D7’.

get_tab_color() → str | None

Tab color style in hex format. String.

get_values(range_name: str | None = None, major_dimension: Dimension | None = None, value_render_option: ValueRenderOption | None = None, date_time_render_option: DateTimeOption | None = None, combine_merged_cells: bool = False, maintain_size: bool = False, pad_values: bool = True, return_type: GridRangeType = GridRangeType.ListOfLists) → List[List[T]]

Alias for get()…

with return_type set to List[List[Any]] and pad_values set to True (legacy method)

hide() → MutableMapping[str, Any]

Hides the current worksheet from the UI.

hide_columns(start: int, end: int) → MutableMapping[str, Any]

Explicitly hide the given column index range.

Index starts from 0.

Parameters:

  • start (int) – The (inclusive) starting column to hide

  • end (int) – The (exclusive) end column to hide

hide_gridlines() → MutableMapping[str, Any]

Hide gridlines on the current worksheet

hide_rows(start: int, end: int) → MutableMapping[str, Any]

Explicitly hide the given row index range.

Index starts from 0.

Parameters:

  • start (int) – The (inclusive) starting row to hide

  • end (int) – The (exclusive) end row to hide

property id: int

Worksheet ID.

property index: int

Worksheet index.

insert_cols(values: Sequence[Sequence[str | int | float]], col: int = 1, value_input_option: ValueInputOption = ValueInputOption.raw, inherit_from_before: bool = False) → MutableMapping[str, Any]

Adds multiple new cols to the worksheet at specified index and populates them with values.

Parameters:

  • values (list) – List of col lists. a list of lists, with the lists each containing one col’s values. Increases the number of rows if there are more values than columns.

  • col (int) – Start col to update (one-based). Defaults to 1 (one).

  • value_input_option (ValueInputOption) – (optional) Determines how input data should be interpreted. Possible values are ValueInputOption.raw or ValueInputOption.user_entered. See ValueInputOption in the Sheets API.

  • inherit_from_before (bool) –

    (optional) If True, new columns will inherit their properties from the previous column. Defaults to False, meaning that new columns acquire the properties of the column immediately after them.

    Warning

    inherit_from_before must be False if adding at the left edge of a spreadsheet (col=1), and must be True if adding at the right edge of the spreadsheet.

insert_note(cell: str, content: str) → None

Insert a note. The note is attached to a certain cell.

Parameters:

  • cell (str) – A string with cell coordinates in A1 notation, e.g. ‘D7’.

  • content (str) – The text note to insert.

Alternatively, you may specify numeric boundaries. All values index from 1 (one):

Parameters:

  • first_row (int) – First row number

  • first_col (int) – First column number

  • last_row (int) – Last row number

  • last_col (int) – Last column number

New in version 3.7.

insert_notes(notes: Mapping[str, str]) → None

insert multiple notes. The notes are attached to a certain cell.

Parameters:

dict (notes) –

A dict of notes with their cells coordinates and respective content

dict format is:

  • key: the cell coordinates as A1 range format

  • value: the string content of the cell

Example:

{
    "D7": "Please read my notes",
    "GH42": "this one is too far",
}

New in version 5.9.

insert_row(values: Sequence[str | int | float], index: int = 1, value_input_option: ValueInputOption = ValueInputOption.raw, inherit_from_before: bool = False) → MutableMapping[str, Any]

Adds a row to the worksheet at the specified index and populates it with values.

Widens the worksheet if there are more values than columns.

Parameters:

  • values (list) – List of values for the new row.

  • index (int) – (optional) Offset for the newly inserted row.

  • value_input_option (ValueInputOption) – (optional) Determines how input data should be interpreted. Possible values are ValueInputOption.raw or ValueInputOption.user_entered. See ValueInputOption in the Sheets API.

  • inherit_from_before (bool) –

    (optional) If True, the new row will inherit its properties from the previous row. Defaults to False, meaning that the new row acquires the properties of the row immediately after it.

    Warning

    inherit_from_before must be False when adding a row to the top of a spreadsheet (index=1), and must be True when adding to the bottom of the spreadsheet.

insert_rows(values: Sequence[Sequence[str | int | float]], row: int = 1, value_input_option: ValueInputOption = ValueInputOption.raw, inherit_from_before: bool = False) → MutableMapping[str, Any]

Adds multiple rows to the worksheet at the specified index and populates them with values.

Parameters:

  • values (list) – List of row lists. a list of lists, with the lists each containing one row’s values. Widens the worksheet if there are more values than columns.

  • row (int) – Start row to update (one-based). Defaults to 1 (one).

  • value_input_option (ValueInputOption) – (optional) Determines how input data should be interpreted. Possible values are ValueInputOption.raw or ValueInputOption.user_entered. See ValueInputOption in the Sheets API.

  • inherit_from_before (bool) –

    (optional) If true, new rows will inherit their properties from the previous row. Defaults to False, meaning that new rows acquire the properties of the row immediately after them.

    Warning

    inherit_from_before must be False when adding rows to the top of a spreadsheet (row=1), and must be True when adding to the bottom of the spreadsheet.

property isSheetHidden: bool

Worksheet hidden status.

property is_gridlines_hidden: bool

Whether or not gridlines hidden. Boolean. True if hidden. False if shown.

list_dimension_group_columns() → List[MutableMapping[str, Any]]

List all the grouped columns in this worksheet.

Returns:

list of the grouped columns

Return type:

list

list_dimension_group_rows() → List[MutableMapping[str, Any]]

List all the grouped rows in this worksheet.

Returns:

list of the grouped rows

Return type:

list

merge_cells(name: str, merge_type: str = MergeType.merge_all)

Merge cells.

Parameters:

  • name (str) – Range name in A1 notation, e.g. ‘A1:A5’.

  • merge_type (MergeType) – (optional) one of MergeType.merge_all, MergeType.merge_columns, or MergeType.merge_rows. Defaults to MergeType.merge_all. See MergeType in the Sheets API reference.

Alternatively, you may specify numeric boundaries. All values index from 1 (one):

Parameters:

  • first_row (int) – First row number

  • first_col (int) – First column number

  • last_row (int) – Last row number

  • last_col (int) – Last column number

Returns:

the response body from the request

Return type:

dict

range(name: str = '') → List[Cell]

Returns a list of gspread.cell.Cell objects from a specified range.

Parameters:

name (str) – A string with range value in A1 notation (e.g. ‘A1:A5’) or the named range to fetch.

Alternatively, you may specify numeric boundaries. All values index from 1 (one):

Parameters:

  • first_row (int) – First row number

  • first_col (int) – First column number

  • last_row (int) – Last row number

  • last_col (int) – Last column number

Return type:

list

Example:

>>> # Using A1 notation
>>> worksheet.range('A1:B7')
[<Cell R1C1 "42">, ...]

>>> # Same with numeric boundaries
>>> worksheet.range(1, 1, 7, 2)
[<Cell R1C1 "42">, ...]

>>> # Named ranges work as well
>>> worksheet.range('NamedRange')
[<Cell R1C1 "42">, ...]

>>> # All values in a single API call
>>> worksheet.range()
[<Cell R1C1 'Hi mom'>, ...]
resize(rows: int | None = None, cols: int | None = None) → MutableMapping[str, Any]

Resizes the worksheet. Specify one of rows or cols.

Parameters:

  • rows (int) – (optional) New number of rows.

  • cols (int) – (optional) New number columns.

property row_count: int

Number of rows.

row_values(row: int, major_dimension: Dimension | None = None, value_render_option: ValueRenderOption | None = None, date_time_render_option: DateTimeOption | None = None) → List[int | float | str | None]

Returns a list of all values in a row.

Empty cells in this list will be rendered as None.

Parameters:

  • row (int) – Row number (one-based).

  • major_dimension (Dimension) – (optional) The major dimension of the values. Dimension.rows (“ROWS”) or Dimension.cols (“COLUMNS”). Defaults to Dimension.rows

  • value_render_option (ValueRenderOption) –

    (optional) Determines how values should be rendered in the output. See ValueRenderOption in the Sheets API.

    Possible values are:

    ValueRenderOption.formatted

    (default) Values will be calculated and formatted according to the cell’s formatting. Formatting is based on the spreadsheet’s locale, not the requesting user’s locale.

    ValueRenderOption.unformatted

    Values will be calculated, but not formatted in the reply. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return the number 1.23.

    ValueRenderOption.formula

    Values will not be calculated. The reply will include the formulas. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return “=A1”.

  • date_time_render_option (DateTimeOption) –

    (optional) How dates, times, and durations should be represented in the output.

    Possible values are:

    DateTimeOption.serial_number

    (default) Instructs date, time, datetime, and duration fields to be output as doubles in “serial number” format, as popularized by Lotus 1-2-3.

    DateTimeOption.formatted_string

    Instructs date, time, datetime, and duration fields to be output as strings in their given number format (which depends on the spreadsheet locale).

    Note

    This is ignored if value_render_option is ValueRenderOption.formatted.

    The default date_time_render_option is DateTimeOption.serial_number.

rows_auto_resize(start_row_index: int, end_row_index: int) → MutableMapping[str, Any]

Updates the size of rows or columns in the worksheet.

Index start from 0

Parameters:

  • start_row_index – The index (inclusive) to begin resizing

  • end_row_index – The index (exclusive) to finish resizing

New in version 5.3.3.

set_basic_filter(name: str | None = None)

Add a basic filter to the worksheet. If a range or boundaries are passed, the filter will be limited to the given range.

Parameters:

name (str) – A string with range value in A1 notation, e.g. A1:A5.

Alternatively, you may specify numeric boundaries. All values index from 1 (one):

Parameters:

  • first_row (int) – First row number

  • first_col (int) – First column number

  • last_row (int) – Last row number

  • last_col (int) – Last column number

New in version 3.4.

show() → MutableMapping[str, Any]

Show the current worksheet in the UI.

show_gridlines() → MutableMapping[str, Any]

Show gridlines on the current worksheet

sort(*specs: Tuple[int, Literal['asc', 'des']], range: str | None = None) → MutableMapping[str, Any]

Sorts worksheet using given sort orders.

Parameters:

  • specs (list) – The sort order per column. Each sort order represented by a tuple where the first element is a column index and the second element is the order itself: ‘asc’ or ‘des’.

  • range (str) – The range to sort in A1 notation. By default sorts the whole sheet excluding frozen rows.

Example:

# Sort sheet A -> Z by column 'B'
wks.sort((2, 'asc'))

# Sort range A2:G8 basing on column 'G' A -> Z
# and column 'B' Z -> A
wks.sort((7, 'asc'), (2, 'des'), range='A2:G8')

New in version 3.4.

property tab_color: str | None

Tab color style. Hex with RGB color values.

property title: str

Worksheet title.

unhide_columns(start: int, end: int) → MutableMapping[str, Any]

Explicitly unhide the given column index range.

Index start from 0.

Parameters:

  • start (int) – The (inclusive) starting column to hide

  • end (int) – The (exclusive) end column to hide

unhide_rows(start: int, end: int) → MutableMapping[str, Any]

Explicitly unhide the given row index range.

Index start from 0.

Parameters:

  • start (int) – The (inclusive) starting row to hide

  • end (int) – The (exclusive) end row to hide

unmerge_cells(name: str) → MutableMapping[str, Any]

Unmerge cells.

Unmerge previously merged cells.

Parameters:

name (str) – Range name in A1 notation, e.g. ‘A1:A5’.

Alternatively, you may specify numeric boundaries. All values index from 1 (one):

Parameters:

  • first_row (int) – First row number

  • first_col (int) – First column number

  • last_row (int) – Last row number

  • last_col (int) – Last column number

Returns:

the response body from the request

Return type:

dict

update(values: Iterable[Iterable[Any]], range_name: str | None = None, raw: bool = True, major_dimension: Dimension | None = None, value_input_option: ValueInputOption | None = None, include_values_in_response: bool | None = None, response_value_render_option: ValueRenderOption | None = None, response_date_time_render_option: DateTimeOption | None = None) → MutableMapping[str, Any]

Sets values in a cell range of the sheet.

Parameters:

  • values (list) – The data to be written in a matrix format.

  • range_name (str) – (optional) The A1 notation of the values to update.

  • raw (bool) – The values will not be parsed by Sheets API and will be stored as-is. For example, formulas will be rendered as plain strings. Defaults to True. This is a shortcut for the value_input_option parameter.

  • major_dimension (Dimension) – (optional) The major dimension of the values. Dimension.rows (“ROWS”) or Dimension.cols (“COLUMNS”). Defaults to Dimension.rows

  • value_input_option (ValueInputOption) –

    (optional) How the input data should be interpreted. Possible values are:

    ValueInputOption.raw

    (default) The values the user has entered will not be parsed and will be stored as-is.

    ValueInputOption.user_entered

    The values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI.

  • response_value_render_option (ValueRenderOption) –

    (optional) Determines how values should be rendered in the output. See ValueRenderOption in the Sheets API.

    Possible values are:

    ValueRenderOption.formatted

    (default) Values will be calculated and formatted according to the cell’s formatting. Formatting is based on the spreadsheet’s locale, not the requesting user’s locale.

    ValueRenderOption.unformatted

    Values will be calculated, but not formatted in the reply. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return the number 1.23.

    ValueRenderOption.formula

    Values will not be calculated. The reply will include the formulas. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return “=A1”.

  • response_date_time_render_option (str) –

    (optional) How dates, times, and durations should be represented in the output.

    Possible values are:

    DateTimeOption.serial_number

    (default) Instructs date, time, datetime, and duration fields to be output as doubles in “serial number” format, as popularized by Lotus 1-2-3.

    DateTimeOption.formatted_string

    Instructs date, time, datetime, and duration fields to be output as strings in their given number format (which depends on the spreadsheet locale).

    Note

    This is ignored if value_render_option is ValueRenderOption.formatted.

    The default date_time_render_option is DateTimeOption.serial_number.

Examples:

# Sets 'Hello world' in 'A2' cell
worksheet.update([['Hello world']], 'A2')

# Updates cells A1, B1, C1 with values 42, 43, 44 respectively
worksheet.update([[42, 43, 44]])

# Updates A2 and A3 with values 42 and 43
# Note that update range can be bigger than values array
worksheet.update([[42], [43]], 'A2:B4')

# Add a formula
worksheet.update([['=SUM(A1:A4)']], 'A5', raw=False)

# Update 'my_range' named range with values 42 and 43
worksheet.update([[42], [43]], 'my_range')

# Note: named ranges are defined in the scope of
# a spreadsheet, so even if `my_range` does not belong to
# this sheet it is still updated

New in version 3.3.

update_acell(label: str, value: int | float | str) → MutableMapping[str, Any]

Updates the value of a cell.

Parameters:

  • label (str) – Cell label in A1 notation.

  • value – New value.

Example:

worksheet.update_acell('A1', '42')
update_cell(row: int, col: int, value: int | float | str) → MutableMapping[str, Any]

Updates the value of a cell.

Parameters:

  • row (int) – Row number.

  • col (int) – Column number.

  • value – New value.

Example:

worksheet.update_cell(1, 1, '42')
update_cells(cell_list: List[Cell], value_input_option: ValueInputOption = ValueInputOption.raw) → Mapping[str, Any]

Updates many cells at once.

Parameters:

  • cell_list (list) – List of gspread.cell.Cell objects to update.

  • value_input_option (ValueInputOption) –

    (optional) How the input data should be interpreted. Possible values are:

    ValueInputOption.raw

    (default) The values the user has entered will not be parsed and will be stored as-is.

    ValueInputOption.user_entered

    The values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI.

    See ValueInputOption in the Sheets API.

Example:

# Select a range
cell_list = worksheet.range('A1:C7')

for cell in cell_list:
    cell.value = 'O_o'

# Update in batch
worksheet.update_cells(cell_list)
update_index(index: int) → MutableMapping[str, Any]

Updates the index property for the worksheet.

See the Sheets API documentation for information on how updating the index property affects the order of worksheets in a spreadsheet.

To reorder all worksheets in a spreadsheet, see Spreadsheet.reorder_worksheets.

New in version 3.4.

update_note(cell: str, content: str) → None

Update the content of the note located at cell.

Parameters:

  • cell (str) – A string with cell coordinates in A1 notation, e.g. ‘D7’.

  • note (str) – The text note to insert.

New in version 3.7.

update_notes(notes: Mapping[str, str]) → None

update multiple notes. The notes are attached to a certain cell.

Parameters:

dict (notes) –

A dict of notes with their cells coordinates and respective content

dict format is:

  • key: the cell coordinates as A1 range format

  • value: the string content of the cell

Example:

{
    "D7": "Please read my notes",
    "GH42": "this one is too far",
}

New in version 5.9.

update_tab_color(color: str) → MutableMapping[str, Any]

Changes the worksheet’s tab color. Use clear_tab_color() to remove the color.

Parameters:

color (str) – Hex color value.

update_title(title: str) → MutableMapping[str, Any]

Renames the worksheet.

Parameters:

title (str) – A new title.

property url: str

Worksheet URL.