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.
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.
-
link
(syncToCloud=True)[source]¶ 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
(save_grid=True)[source]¶ 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.
-
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> ]
- ranges – list of ranges to get data as - objects of
-
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
- cell_list – List of a
-
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: 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)