Worksheet

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

A class for worksheet object.

Parameters:
  • spreadsheet – Spreadsheet object to which this worksheet belongs to
  • jsonSheet – The JsonSheet containing all properties of this sheet Ref to api details for more info
id

Id of a worksheet.

index

Index of worksheet

title

Title of a worksheet.

rows

Number of rows

cols

Number of columns

refresh(update_grid=False)[source]

refresh worksheet data

Link the spread sheet with cloud, so all local changes will be updated instantly, so does all data fetches

Parameters:syncToCloud – update the cloud with local changes 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

sync()[source]

sync the worksheet 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 – cann be ‘matrix’, ‘cell’, ‘range’ the correponding type will be returned
get_value(addr)[source]

value of a cell at given address

Parameters:addr – cell address as either tuple or label
get_values(start, end, returnas='matrix', majdim='ROWS', include_empty=True, include_all=False, value_render='FORMATTED_VALUE')[source]

Returns value of cells given the topleft corner position and bottom right position

Parameters:
  • start – topleft position as tuple or label
  • end – bottomright position as tuple or label
  • majdim – output as rowwise or columwise, only for matrix takes - ‘ROWS’ or ‘COLMUNS’
  • returnas – return as list of strings of cell objects takes - ‘matrix’, ‘cell’, ‘range’
  • include_empty – include empty trailing cells/values until last non-zero value, ignored if inclue_all is True
  • include_all – include all the cells in the range empty/non-empty, will return exact rectangle
  • value_render – format of output values

Example:

>>> wks.get_values((1,1),(3,3))
[[u'another look.', u'', u'est'],
 [u'EE 4212', u"it's down there "],
 [u'ee 4210', u'somewhere, let me take ']]
get_all_values(returnas='matrix', majdim='ROWS', include_empty=True)[source]

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

Parameters:
  • majdim – output as row wise or columwise
  • returnas ('matrix','cell') – return as list of strings of cell objects
  • include_empty – whether to include empty 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)[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.
Returns:

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

get_row(row, returnas='matrix', include_empty=True)[source]

Returns a list of all values in a row.

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

Parameters:
  • include_empty – whether to include empty values
  • row – index of row
  • returnas – (‘matrix’ or ‘cell’) return as cell objects or just 2d array
get_col(col, returnas='matrix', include_empty=True)[source]

Returns a list of all values in column col.

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

Parameters:
  • include_empty – whether to include empty values
  • col – index of col
  • returnas – (‘matrix’ or ‘cell’) return as cell objects or just values
update_cell(addr, val, parse=True)[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 else as if the user typed them into the UI

Example:

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

Updates cells in batch, it can take either a cell list or a range and values. cell list is only efficient for large lists.

Parameters:
  • cell_list – List of a Cell objects to update with their values
  • crange – range in format A1:A2 or just ‘A1’ or even (1,2) end cell will be infered 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
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 rows number.
  • cols – New columns number.
add_rows(rows)[source]

Adds rows to worksheet.

Parameters:rows – Rows number to add.
add_cols(cols)[source]

Adds colums to worksheet.

Parameters:cols – Columns number to add.
delete_cols(index, number=1)[source]

delete a number of colums stating from index

Parameters:
  • index – indenx of first col to delete
  • number – number of cols to delete
delete_rows(index, number=1)[source]

delete a number of rows stating 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 a column after the column <col> and fill with values <values> Widens the worksheet if there are more values than columns.

Parameters:
  • col – column number after which new column should be inserted
  • number – number of columns to be inserted
  • values – values matrix to filled in new column
  • inherit – If dimension properties should be extended from the dimensions before or after the newly inserted dimensions
insert_rows(row, number=1, values=None, inherit=False)[source]

Insert a row after the row <row> and fill with values <values> Widens the worksheet if there are more values than columns.

Parameters:
  • row – row after which new colum should be inserted
  • number – number of rows to be inserted
  • values – values matrix to be filled in new row
  • inherit – If dimension properties should be extended from the dimensions before or after the newly inserted dimensions
clear(start='A1', end=None)[source]

clears the worksheet by default, if range given then clears range

Parameters:
  • start – topright cell address
  • end – bottom left cell of range
adjust_column_width(start, end=None, pixel_size=100)[source]

Adjust the width of one or more columns

Parameters:
  • start – index of the column to be resized
  • end – index of the end column that will be resized
  • pixel_size – width in pixels
adjust_row_height(start, end=None, pixel_size=100)[source]

Adjust the height of one or more rows

Parameters:
  • start – index of the row to be resized
  • end – index of the end row that will be resized
  • pixel_size – height in pixels
append_table(start='A1', end=None, values=None, dimension='ROWS', overwrite=False)[source]
Search for a table in the given range and will
append it with values
Parameters:
  • start – start cell of range
  • end – end cell of range
  • values – List of values for the new row.
  • dimension – table dimension on which the values should be appended. can be ‘ROWS’ or ‘COLUMNS’
  • overwrite – The new data overwrites existing data in the areas it is written.
find(query, replace=None, force_fetch=True)[source]

Finds first cell matching query.

Parameters:
  • query – A text string or compiled regular expression.
  • replace – string to replace
  • force_fetch – if local datagrid should be updated before searching, even if file is not modified
create_named_range(name, start, end)[source]

Create a named range in this sheet

Parameters:
  • name – Name of the named range
  • start – top right cell adress
  • end – bottom right cell adress
get_named_ranges(name='')[source]

get a named range given name

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

delete a named range

Parameters:
  • name – name of named range to be deleted
  • range_id – id of the named range
set_dataframe(df, start, copy_index=False, copy_head=True, fit=False, escape_formulae=False, nan='NaN')[source]

set the values of a pandas dataframe at cell <start>

Parameters:
  • df – pandas dataframe
  • start – top right cell address from where values are inserted
  • copy_index – if index should be copied (multi index supported)
  • copy_head – if headers should be copied
  • fit – should the worksheet should be resized to fit the dataframe
  • escape_formulae – If any value starts with an equals sign =, it will be prefixed with a apostrophe ‘, to avoid being interpreted as a formula.
  • nan – value to replace NaN with
get_as_df(has_header=True, index_colum=None, start=None, end=None, numerize=True, empty_value='')[source]

get value of worksheet as a pandas dataframe

Parameters:
  • has_header – If is True intrept first row as DF header
  • index_colum – worksheet column number to use as DF index
  • numerize – If True, cell values will be numerized
  • empty_value – value used to indicate empty cell value
  • start – top left cell of dataframe, if not set whole sheet will be fetched
  • end – bottom right cell of dataframe, if not set whole sheet will be fetched
Returns:

pandas.Dataframe

export(fformat='text/csv:.csv', filename=None)[source]

Export the worksheet in specified format.

Parameters:
  • fformat – A format of the output as Enum ExportType
  • filename – name of file exported with extension
copy_to(spreadsheet_id)[source]

copy the worksheet to specified spreadsheet

Parameters:spreadsheet_id – id of the spreadsheet to copy