Worksheet

class pygsheets.Worksheet(spreadsheet, jsonSheet)[source]

A worksheet.

Parameters:
  • spreadsheet – Spreadsheet object to which this worksheet belongs to
  • jsonSheet

    Contains properties to initialize this worksheet.

    Ref to api details for more info

id

The ID of this worksheet.

index

The index of this worksheet

title

The title of this worksheet.

hidden

Mark the worksheet as hidden.

url

The url of this worksheet.

rows

Number of rows active within the sheet. A new sheet contains 1000 rows.

cols

Number of columns active within the sheet.

frozen_rows

Number of frozen rows.

frozen_cols

Number of frozen columns.

merged_ranges

Ranges of merged cells in this sheet.

linked

If the sheet is linked.

refresh(update_grid=False)[source]

refresh worksheet data

Link the spreadsheet with cloud, so all local changes will be updated instantly. Data fetches will work only in linked sheet. All the data update calls made when sheet is unlinked will be replayed on linking.

Parameters:syncToCloud – update the cloud with local changes (data_grid), cached update calls if set to true update the local copy with cloud if set to false

Unlink the spreadsheet with cloud, so that any changes made wont be updated instantaneously. All the data update calls are cached and will be called once the sheet is linked again.

Warning

After unlinking, functions which return data won’t work.

sync()[source]

sync the worksheet (datagrid, and worksheet properties) to cloud

cell(addr)[source]

Returns cell object at given address.

Parameters:addr – cell address as either tuple (row, col) or cell label ‘A1’ or Address
Returns:an instance of a Cell

Example:

>>> wks.cell((1,1))
<Cell R1C1 "I'm cell A1">
>>> wks.cell('A1')
<Cell R1C1 "I'm cell A1">
range(crange, returnas='cells')[source]

Returns a list of Cell objects from specified range.

Parameters:
  • crange – A string with range value in common format, e.g. ‘A1:A5’.
  • returnas – can be ‘matrix’, ‘cell’, ‘range’ the corresponding type will be returned
get_value(addr, value_render=<ValueRenderOption.FORMATTED_VALUE: 'FORMATTED_VALUE'>)[source]

value of a cell at given address

Parameters:
  • addr – cell address as either tuple or label
  • value_render – how the output values should rendered. api docs
get_values(start, end, returnas='matrix', majdim='ROWS', include_tailing_empty=True, include_tailing_empty_rows=False, value_render=<ValueRenderOption.FORMATTED_VALUE: 'FORMATTED_VALUE'>, date_time_render_option=<DateTimeRenderOption.SERIAL_NUMBER: 'SERIAL_NUMBER'>, grange=None, **kwargs)[source]

Returns a range of values from start cell to end cell. It will fetch these values from remote and then processes them. Will return either a simple list of lists, a list of Cell objects or a DataRange object with all the cells inside.

Parameters:
  • start – Top left position as tuple or label
  • end – Bottom right position as tuple or label
  • grange – give range as grid range object, object of GridRange
  • majdim – The major dimension of the matrix. (‘ROWS’) ( ‘COLUMNS’ not implemented )
  • returnas – The type to return the fetched values as. (‘matrix’, ‘cell’, ‘range’)
  • include_tailing_empty – whether to include empty trailing cells/values after last non-zero value in a row
  • include_tailing_empty_rows – whether to include tailing rows with no values; if include_tailing_empty is false, will return unfilled list for each empty row, else will return rows filled with empty cells
  • value_render – how the output values should rendered. api docs
  • date_time_render_option – How dates, times, and durations should be represented in the output. This is ignored if valueRenderOption is FORMATTED_VALUE. The default dateTime render option is [DateTimeRenderOption.SERIAL_NUMBER].
Returns:

‘range’: DataRange ‘cell’: [Cell] ‘matrix’: [[ … ], [ … ], …] append ‘-unlinked’ to get unlinked objects

get_values_batch(ranges, majdim='ROWS', value_render=<ValueRenderOption.FORMATTED_VALUE: 'FORMATTED_VALUE'>, date_time_render_option=<DateTimeRenderOption.SERIAL_NUMBER: 'SERIAL_NUMBER'>, **kwargs)[source]

Returns a range of values from start cell to end cell. It will fetch these values from remote and then processes them. Will return either a simple list of lists, a list of Cell objects or a DataRange object with all the cells inside.

Parameters:
  • ranges – list of ranges to get data as - objects of GridRange, or tuple (with start and end) or A1 notation string or dict in GridRange format
  • majdim – The major dimension of the matrix. (‘ROWS’) ( ‘COLMUNS’ not implemented )
  • value_render – refer get_values
  • date_time_render_option – refer get_values

Example:

>>> wks.get_values_batch( ['A1:A2', 'C1:C2'] )
  [ [['3'], ['4']], [['c']]]
>>> wks.get_values_batch( [('1', None), ('5', None)] )
[ <values of row 1>, <values of row 5> ]
>>> wks.get_values_batch( [('A1', 'B2'), ('5', None), 'Sheet1!D1:F10', 'A'])
[ <values list of lists> ]
get_all_values(returnas='matrix', majdim='ROWS', include_tailing_empty=True, include_tailing_empty_rows=True, **kwargs)[source]

Returns a list of lists containing all cells’ values as strings.

Parameters:
  • majdim – output as row wise or column-wise
  • returnas ('matrix','cell', 'range) – return as list of strings of cell objects
  • include_tailing_empty – whether to include empty trailing cells/values after last non-zero value
  • include_tailing_empty_rows – whether to include rows with no values; if include_tailing_empty is false, will return unfilled list for each empty row, else will return rows filled with empty string
  • kwargs – all parameters of pygsheets.Worksheet.get_values()

Example:

>>> wks.get_all_values()
[[u'another look.', u'', u'est'],
 [u'EE 4212', u"it's down there "],
 [u'ee 4210', u'somewhere, let me take ']]
get_all_records(empty_value='', head=1, majdim='ROWS', numericise_data=True, **kwargs)[source]

Returns a list of dictionaries, all of them having

  • the contents of the spreadsheet’s with the head row as keys, And each of these dictionaries holding
  • the contents of subsequent rows of cells as values.

Cell values are numericised (strings that can be read as ints or floats are converted).

Parameters:
  • empty_value – determines empty cell’s value
  • head – determines which row to use as keys, starting from 1 following the numeration of the spreadsheet.
  • majdim – ROW or COLUMN major form
  • numericise_data – determines if data is converted to numbers or left as string
  • kwargs – all parameters of pygsheets.Worksheet.get_values()
Returns:

a list of dict with header column values as head and rows as list

Warning

Will work nicely only if there is a single table in the sheet

get_row(row, returnas='matrix', include_tailing_empty=True, **kwargs)[source]

Returns a list of all values in a row.

Empty cells in this list will be rendered as empty strings .

Parameters:
  • include_tailing_empty – whether to include empty trailing cells/values after last non-zero value
  • row – index of row
  • kwargs – all parameters of pygsheets.Worksheet.get_values()
  • returnas – (‘matrix’, ‘cell’, ‘range’) return as cell objects or just 2d array or range object
get_col(col, returnas='matrix', include_tailing_empty=True, **kwargs)[source]

Returns a list of all values in column col.

Empty cells in this list will be rendered as :const:` ` .

Parameters:
  • include_tailing_empty – whether to include empty trailing cells/values after last non-zero value
  • col – index of col
  • kwargs – all parameters of pygsheets.Worksheet.get_values()
  • returnas – (‘matrix’ or ‘cell’ or ‘range’) return as cell objects or just values
get_gridrange(start, end)[source]

get a range in gridrange format

Parameters:
  • start – start address
  • end – end address
update_value(addr, val, parse=None)[source]

Sets the new value to a cell.

Parameters:
  • addr – cell address as tuple (row,column) or label ‘A1’.
  • val – New value
  • parse – if False, values will be stored as is else as if the user typed them into the UI default is spreadsheet.default_parse

Example:

>>> wks.update_value('A1', '42') # this could be 'a1' as well
<Cell R1C1 "42">
>>> wks.update_value('A3', '=A1+A2', True)
<Cell R1C3 "57">
update_values(crange=None, values=None, cell_list=None, extend=False, majordim='ROWS', parse=None)[source]

Updates a range cell values, it can take either a cell list or a range and its values. cell list is only efficient for small lists. This will only update the cell values not other properties.

Parameters:
  • cell_list – List of a Cell objects to update with their values. If you pass a matrix to this, then it is assumed that the matrix is continuous (range), and will just update values based on label of top left and bottom right cells.
  • crange – range in format A1:A2 or just ‘A1’ or even (1,2) end cell will be inferred from values
  • values – matrix of values if range given, if a value is None its unchanged
  • extend – add columns and rows to the workspace if needed (not for cell list)
  • majordim – major dimension of given data
  • parse – if the values should be as if the user typed them into the UI else its stored as is. Default is spreadsheet.default_parse
update_values_batch(ranges, values, majordim='ROWS', parse=None)[source]

update multiple ranges of values in a single call.

Parameters:
  • ranges – list of addresses of the range. can be GridRange, label, tuple, etc
  • values – list of values corresponding to ranges, should be list of matrices
  • majordim – major dimension of values provided. ‘ROWS’ or ‘COLUMNS’
  • parse – if the values should be as if the user typed them into the UI else its stored as is. Default is spreadsheet.default_parse

Example: >>> wks.update_values_batch([‘A1:A2’, ‘B1:B2’], [[[1],[2]], [[3],[4]]]) >>> wks.get_values_batch([‘A1:A2’, ‘B1:B2’]) [[[‘1’], [‘2’]], [[‘3’], [‘4’]]]

>>> wks.update_values_batch([((1,1), (2,1)), 'B1:B2'], [[[1,2]], [[3,4]]], 'COLUMNS')
>>> wks.get_values_batch(['A1:A2', 'B1:B2'])
[[['1'], ['2']], [['3'], ['4']]]
update_cells(cell_list, fields='*')[source]

update cell properties and data from a list of cell objects

Parameters:
  • cell_list – list of cell objects
  • fields – cell fields to update, in google FieldMask format
update_col(index, values, row_offset=0)[source]

update an existing colum with values

Parameters:
  • index – index of the starting column form where value should be inserted
  • values – values to be inserted as matrix, column major
  • row_offset – rows to skip before inserting values
update_row(index, values, col_offset=0)[source]

Update an existing row with values

Parameters:
  • index – Index of the starting row form where value should be inserted
  • values – Values to be inserted as matrix
  • col_offset – Columns to skip before inserting values
resize(rows=None, cols=None)[source]

Resizes the worksheet.

Parameters:
  • rows – New number of rows.
  • cols – New number of columns.
add_rows(rows)[source]

Adds new rows to this worksheet.

Parameters:rows – How many rows to add (integer)
add_cols(cols)[source]

Add new columns to this worksheet.

Parameters:cols – How many columns to add (integer)
delete_cols(index, number=1)[source]

Delete ‘number’ of columns from index.

Parameters:
  • index – Index of first column to delete
  • number – Number of columns to delete
delete_rows(index, number=1)[source]

Delete ‘number’ of rows from index.

Parameters:
  • index – Index of first row to delete
  • number – Number of rows to delete
insert_cols(col, number=1, values=None, inherit=False)[source]

Insert new columns after ‘col’ and initialize all cells with values. Increases the number of rows if there are more values in values than rows.

Reference: insert request

Parameters:
  • col – Index of the col at which the values will be inserted.
  • number – Number of columns to be inserted.
  • values – Content to be inserted into new columns.
  • inherit – New cells will inherit properties from the column to the left (True) or to the right (False).
insert_rows(row, number=1, values=None, inherit=False)[source]

Insert a new row after ‘row’ and initialize all cells with values.

Widens the worksheet if there are more values than columns.

Reference: insert request

Parameters:
  • row – Index of the row at which the values will be inserted.
  • number – Number of rows to be inserted.
  • values – Content to be inserted into new rows.
  • inherit – New cells will inherit properties from the row above (True) or below (False).
clear(start='A1', end=None, fields='userEnteredValue')[source]

Clear all values in worksheet. Can be limited to a specific range with start & end.

Fields specifies which cell properties should be cleared. Use “*” to clear all fields.

Reference:

Parameters:
  • start – Top left cell label.
  • end – Bottom right cell label.
  • fields – Comma separated list of field masks.
adjust_column_width(start, end=None, pixel_size=None)[source]

Set the width of one or more columns.

Parameters:
  • start – Index of the first column to be widened.
  • end – Index of the last column to be widened.
  • pixel_size – New width in pixels or None to set width automatically based on the size of the column content.
apply_format(ranges, format_info, fields='userEnteredFormat')[source]

apply formatting for for multiple ranges

Parameters:
  • ranges – list of ranges (any type) to apply the formats to
  • format_info – list or single pygsheets cell or dict of properties specifying the formats to be updated, see this for available options. if a list is given it should match size of ranges.
  • fields – fields to be updated in the cell

Example: >>> wks.apply_format(‘A1:A10’, {“numberFormat”: {“type”: “NUMBER”}}) >>> wks.apply_format(‘A1:A10’, “TEXT”) # by default number format is assumed >>> wks.apply_format(ranges=[‘A1:B1’, ‘D:E’], format_info={‘numberFormat’: {“type”: “NUMBER”}}) >>> mcell = Cell(‘A1’) # dummy cell >>> mcell.format = (pygsheets.FormatType.PERCENT, ‘’) >>> wks.apply_format(ranges=[‘A1:B1’, ‘D:E’], format_info=mcell)

update_dimensions_visibility(start, end=None, dimension='ROWS', hidden=True)[source]

Hide or show one or more rows or columns.

Parameters:
  • start – Index of the first row or column.
  • end – Index of the last row or column.
  • dimension – ‘ROWS’ or ‘COLUMNS’
  • hidden – Hide rows or columns
hide_dimensions(start, end=None, dimension='ROWS')[source]

Hide one ore more rows or columns.

Parameters:
  • start – Index of the first row or column.
  • end – Index of the last row or column.
  • dimension – ‘ROWS’ or ‘COLUMNS’
show_dimensions(start, end=None, dimension='ROWS')[source]

Show one ore more rows or columns.

Parameters:
  • start – Index of the first row or column.
  • end – Index of the last row or column.
  • dimension – ‘ROWS’ or ‘COLUMNS’
adjust_row_height(start, end=None, pixel_size=None)[source]

Adjust the height of one or more rows.

Parameters:
  • start – Index of first row to be heightened.
  • end – Index of last row to be heightened.
  • pixel_size – New height in pixels or None to set height automatically based on the size of the row content.
append_table(values, start='A1', end=None, dimension='ROWS', overwrite=False, **kwargs)[source]

Append a row or column of values to an existing table in the sheet. The input range is used to search for existing data and find a “table” within that range. Values will be appended to the next row of the table, starting with the first column of the table. The return value contains the index of the appended table. It is useful to get the index of last row or last column.

Reference: request

Parameters:
  • values – List of values for the new row or column.
  • start – Top left cell of the range (requires a label).
  • end – Bottom right cell of the range (requires a label).
  • dimension – Dimension to which the values will be added (‘ROWS’ or ‘COLUMNS’)
  • overwrite – If true will overwrite data present in the spreadsheet. Otherwise will create new rows to insert the data into.
Returns:

A :class:dict containing the result of request

replace(pattern, replacement=None, **kwargs)[source]

Replace values in any cells matched by pattern in this worksheet. Keyword arguments not specified will use the default value.

If the worksheet is

  • Unlinked : Uses self.find(pattern, **kwargs) to find the cells and then replace the values in each cell.
  • Linked : The replacement will be done by a findReplaceRequest as defined by the Google Sheets API. After the request the local copy is updated.

Reference: request

Parameters:
  • pattern – Match cell values.
  • replacement – Value used as replacement.
  • searchByRegex – Consider pattern a regex pattern. (default False)
  • matchCase – Match case sensitive. (default False)
  • matchEntireCell – Only match on full match. (default False)
  • includeFormulas – Match fields with formulas too. (default False)
find(pattern, searchByRegex=False, matchCase=False, matchEntireCell=False, includeFormulas=False, cols=None, rows=None, forceFetch=True)[source]

Finds all cells matched by the pattern.

Compare each cell within this sheet with pattern and return all matched cells. All cells are compared as strings. If replacement is set, the value in each cell is set to this value. Unless full_match is False in in which case only the matched part is replaced.

Note

  • Formulas are searched as their calculated values and not the actual formula.
  • Find fetches all data and then run a linear search on then, so this will be slow if you have a large sheet
Parameters:
  • pattern – A string pattern.
  • searchByRegex – Compile pattern as regex. (default False)
  • matchCase – Comparison is case sensitive. (default False)
  • matchEntireCell – Only match a cell if the pattern matches the entire value. (default False)
  • includeFormulas – Match cells with formulas. (default False)
  • rows – Range of rows to search in as tuple, example (2, 10)
  • cols – Range of columns to search in as tuple, example (3, 10)
  • forceFetch – If the offline data should be updated before search. (default False)
Returns:

A list of Cells.

create_named_range(name, start=None, end=None, grange=None, returnas='range')[source]

Create a new named range in this worksheet. Provide either start and end or grange.

Reference: Named range Api object

Parameters:
  • name – Name of the range.
  • start – Top left cell address (label or coordinates)
  • end – Bottom right cell address (label or coordinates)
  • grange – grid range, object of GridRange
Returns:

DataRange

get_named_range(name)[source]

Get a named range by name.

Reference: Named range Api object

Parameters:name – Name of the named range to be retrieved.
Returns:DataRange
Raises:RangeNotFound – if no range matched the name given.
get_named_ranges(name='')[source]

Get named ranges from this worksheet.

Reference: Named range Api object

Parameters:name – Name of the named range to be retrieved, if omitted all ranges are retrieved.
Returns:DataRange
delete_named_range(name, range_id='')[source]

Delete a named range.

Reference: Named range Api object

Parameters:
  • name – Name of the range.
  • range_id – Id of the range (optional)
create_protected_range(start=None, end=None, grange=None, named_range_id=None, returnas='range')[source]

Create protected range. Provide either start and end or grange.

Reference: Protected range Api object

Parameters:
  • start – address of the topleft cell
  • end – address of the bottomright cell
  • grange – grid range to protect, object of GridRange
  • named_range_id – id of named range to protect
  • returnas – ‘json’ or ‘range’
remove_protected_range(range_id)[source]

Remove protected range.

Reference: Protected range Api object

Parameters:range_id – ID of the protected range.
get_protected_ranges()[source]

returns protected ranges in this sheet

Returns:Protected range objects
Return type:Datarange
set_dataframe(df, start, copy_index=False, copy_head=True, extend=False, fit=False, escape_formulae=False, **kwargs)[source]

Load sheet from Pandas Dataframe.

Will load all data contained within the Pandas data frame into this worksheet. It will begin filling the worksheet at cell start. Supports multi index and multi header datarames.

Parameters:
  • df – Pandas data frame.
  • start – Address of the top left corner where the data should be added.
  • copy_index – Copy data frame index (multi index supported).
  • copy_head – Copy header data into first row.
  • extend – Add columns and rows to the worksheet if necessary, but won’t delete any rows or columns.
  • fit – Resize the worksheet to fit all data inside if necessary.
  • escape_formulae – Any value starting with an equal or plus sign (=/+), will be prefixed with an apostrophe (’) to avoid value being interpreted as a formula.
  • nan – Value with which NaN values are replaced. by default it will be replaced with string ‘nan’. for converting nan values to empty cells set nan=””.
get_as_df(has_header=True, index_column=None, start=None, end=None, numerize=True, empty_value='', value_render=<ValueRenderOption.FORMATTED_VALUE: 'FORMATTED_VALUE'>, **kwargs)[source]

Get the content of this worksheet as a pandas data frame.

Parameters:
  • has_header – Interpret first row as data frame header.
  • index_column – Column to use as data frame index (integer).
  • numerize – Numerize cell values.
  • empty_value – Placeholder value to represent empty cells when numerizing.
  • start – Top left cell to load into data frame. (default: A1)
  • end – Bottom right cell to load into data frame. (default: (rows, cols))
  • value_render – How the output values should returned, api docs By default, will convert everything to strings. Setting as UNFORMATTED_VALUE will do numerizing, but values will be unformatted.
  • include_tailing_empty – whether to include empty trailing cells/values after last non-zero value in a row
  • include_tailing_empty_rows – whether to include tailing rows with no values; if include_tailing_empty is false, will return unfilled list for each empty row, else will return rows filled with empty cells
Returns:

pandas.Dataframe

export(file_format=<ExportType.CSV: 'text/csv:.csv'>, filename=None, path='')[source]

Export this worksheet to a file.

Note

  • Only CSV & TSV exports support single sheet export. In all other cases the entire spreadsheet will be exported.
  • This can at most export files with 10 MB in size!
Parameters:
  • file_format – Target file format (default: CSV), enum :class:<pygsheets.ExportType>
  • filename – Filename (default: spreadsheet id + worksheet index).
  • path – Directory the export will be stored in. (default: current working directory)
copy_to(spreadsheet_id)[source]

Copy this worksheet to another spreadsheet.

This will copy the entire sheet into another spreadsheet and then return the new worksheet. Can be slow for huge spreadsheets.

Reference: request

Parameters:spreadsheet_id – The id this should be copied to.
Returns:Copy of the worksheet in the new spreadsheet.
sort_range(start, end, basecolumnindex=0, sortorder='ASCENDING')[source]

Sorts the data in rows based on the given column index.

Parameters:
  • start – Address of the starting cell of the grid.
  • end – Address of the last cell of the grid to be considered.
  • basecolumnindex – Index of the base column in which sorting is to be done (Integer), default value is 0. The index here is the index of the column in worksheet.
  • sortorder – either “ASCENDING” or “DESCENDING” (String)

Example: If the data contain 5 rows and 6 columns and sorting is to be done in 4th column. In this case the values in other columns also change to maintain the same relative values.

add_chart(domain, ranges, title=None, chart_type=<ChartType.COLUMN: 'COLUMN'>, anchor_cell=None)[source]
Creates a chart in the sheet and returns a chart object. The X-axis is called the domain and the Y-axis is
called range. There can only be a single domain as it is the values against which the ranges are plotted.
You can have multiple ranges, and all of them will be plotted against the values on the domain
(depending on chart_type).

For example, suppose you want to plot temperature against the years. Here Year will be the domain and Temperature will be a range. Now suppose you want to add a plot of rainfall also to this chart (given you have the same year range). You can just add the rainfall data as a range.

Parameters:
  • domain – Cell range of the desired chart domain (x-axis) in the form of tuple of adresses (start_address, end_address)
  • ranges – Cell ranges of the desired ranges (y-axis) in the form of list of tuples of adresses
  • title – Title of the chart
  • chart_type – Basic chart type (default: COLUMN)
  • anchor_cell – position of the left corner of the chart in the form of cell address or cell object
Returns:

Chart

Example:

To plot a chart with x values from ‘A1’ to ‘A6’ and y values from ‘B1’ to ‘B6’

>>> wks.add_chart(('A1', 'A6'), [('B1', 'B6')], 'TestChart')
<Chart 'COLUMN' 'TestChart'>
get_charts(title=None)[source]

Returns a list of chart objects, can be filtered by title.

Parameters:title – title to be matched.
Returns:list of Chart
set_data_validation(start=None, end=None, condition_type=None, condition_values=None, grange=None, **kwargs)[source]

Sets a data validation rule to every cell in the range. To clear validation in a range, call this with no condition_type specified.

refer to api docs for possible inputs.

Parameters:
  • start – start address
  • end – end address
  • grange – address as grid range
  • condition_type – validation condition type: possible values
  • condition_values – list of values for supporting condition type. For example , when condition_type is NUMBER_BETWEEN, value should be two numbers indicationg lower and upper bound. See api docs for more info.
  • kwargs – other options of rule. possible values: inputMessage, strict, showCustomUi ref
set_basic_filter(start=None, end=None, grange=None, sort_order=None, sort_foreground_color=None, sort_background_color=None, sort_column_index=None, filter_column_index=None, hidden_values=None, condition_type=None, condition_values=None, filter_foreground_color=None, filter_background_color=None)[source]

Sets a basic filter to a row in worksheet.

refer to api docs for possible inputs.

Parameters:
  • start – start address
  • end – end address
  • grange – address as grid range
  • sort_order – either “ASCENDING” or “DESCENDING” (String)
  • sort_foreground_color – either Color obj (Tuple) or ThemeColorType (String). please refer to api docs for possible inputs.
  • sort_background_color – either Color obj (Tuple) or ThemeColorType (String). please refer to api docs for possible inputs.
  • sort_column_index – the position of column for sort.
  • filter_column_index – the position of column for filter.
  • hidden_values – values which are hidden by filter.
  • condition_type – validation condition type: possible values
  • condition_values – list of values for supporting condition type. For example , when condition_type is NUMBER_BETWEEN, value should be two numbers indicationg lower and upper bound. It also can be this enum. See api docs for more info.
  • filter_foreground_color – either Color obj (Tuple) or ThemeColorType (String). please refer to api docs for possible inputs.
  • filter_background_color – either Color obj (Tuple) or ThemeColorType (String). please refer to api docs for possible inputs.
clear_basic_filter()[source]

Clear a basic filter in worksheet

refer to api docs for possible inputs.

add_conditional_formatting(start, end, condition_type, format, condition_values=None, grange=None)[source]

Adds a new conditional format rule.

Parameters:
  • start – start address
  • end – end address
  • grange – address as grid range
  • condition_type – validation condition type: possible values
  • condition_values – list of values for supporting condition type. For example , when condition_type is NUMBER_BETWEEN, value should be two numbers indicationg lower and upper bound. It also can be this enum. See api docs for more info.
  • format – cell format json to apply if condition succeedes. refer. <https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#CellFormat>
merge_cells(start=None, end=None, merge_type='MERGE_ALL', grange=None)[source]

Merge cells in range

! You can’t vertically merge cells that intersect an existing filter

Parameters:
  • merge_type – either ‘MERGE_ALL’ ,’MERGE_COLUMNS’ ( = merge multiple rows (!) together to make column(s)) ,’MERGE_ROWS’ ( = merge multiple columns (!) together to make a row(s)) ,’NONE’ (unmerge)
  • start – start Address
  • end – end Address
get_developer_metadata(key=None)[source]

Fetch developer metadata associated with this worksheet

Parameters:key – the key of the metadata to fetch. If unspecified, all metadata will be returned
create_developer_metadata(key, value=None)[source]

Create a new developer metadata associated with this worksheet

Will return None when in batch mode, otherwise will return a DeveloperMetadata object

Parameters:
  • key – the key of the metadata to be created
  • value – the value of the metadata to be created (optional)