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.

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, so does all data fetches

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

Unlink the spread sheet with cloud, so all local changes will be made on local copy fetched

Warning

After unlinking update functions will 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’
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'>, **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
  • majdim – The major dimension of the matrix. (‘ROWS’) ( ‘COLMUNS’ 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’: [[ … ], [ … ], …]

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 columwise
  • 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 wich 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 cell values in batch, it can take either a cell list or a range and 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 continous (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_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=100)[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.
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 first 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 first row or column.
  • dimension – ‘ROWS’ or ‘COLUMNS’
adjust_row_height(start, end=None, pixel_size=100)[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.
append_table(values, start='A1', end=None, dimension='ROWS', overwrite=False, **kwargs)[source]

Append a row or column of values.

This will append the list of provided values to the

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.
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)[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)
Returns:

A list of Cells.

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

Create a new named range in this worksheet.

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)
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, end, returnas='range')[source]

Create protected range.

Reference: Protected range Api object

Parameters:
  • start – adress of the topleft cell
  • end – adress of the bottomright cell
  • 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, 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.
  • fit – Resize the worksheet to fit all data inside if necessary.
  • escape_formulae – Any value starting with an equal sign (=), will be prefixed with an apostroph (‘) to avoid value being interpreted as a formula.
  • nan – Value with which NaN values are replaced.
get_as_df(has_header=True, index_colum=None, start=None, end=None, numerize=True, empty_value='', value_render=<ValueRenderOption.FORMATTED_VALUE: 'FORMATTED_VALUE'>, include_tailing_empty=True)[source]

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

Parameters:
  • has_header – Interpret first row as data frame header.
  • index_colum – 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 – include tailing empty cells in each row
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)
  • 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 retuns a chart object.

Parameters:
  • domain – Cell range of the desired chart domain in the form of tuple of adresses
  • ranges – Cell ranges of the desired ranges 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