DataRange

class pygsheets.datarange.DataRange(start=None, end=None, worksheet=None, name='', data=None, name_id=None, namedjson=None, protectedjson=None, grange=None)[source]

DataRange specifies a range of cells in the sheet. It can be unbounded on one or more axes. DataRange is for storing/manipulating a range of data in worksheet. This class can be used for group operations, e.g. changing format of all cells in a given range. This can also represent named ranges protected ranges, banned ranges etc.

All the proteted range properties are stored in protected_properties.

Parameters:
  • start – top left cell address. can be unbounded.
  • end – bottom right cell address
  • worksheet – worksheet where this range belongs
  • name – name of the named range
  • data – data of the range in as row major matrix
  • name_id – id of named range
  • namedjson – json representing the NamedRange from api
>>> drange = Datarange(start='A1', end='B2', worksheet=wks)
<Datarange Sheet1!A1:B2>
>>> drange.name = "my_named_range" # make this datarange a named range
<Datarange my_named_range Sheet1!A1:B2>
>>> drange.protected = True # make the range protected
<Datarange my_named_range Sheet1!A1:B2 protected>
>>> drange.start_addr = 'B' # make the range unbounded on rows
<Datarange my_named_range Sheet1!A:B protected>
>>> drange.end_addr = None # make the range unbounded on both axes
<Datarange my_named_range Sheet1 protected>
name

name of the named range. setting a name will make this a range a named range setting this to empty string will delete the named range

name_id

if of the named range

protect_id

id of the protected range

protected

get/set the range as protected setting this to False will make this range unprotected

editors

Lists the editors of the protected range can also set a list of editors, take a tuple (‘users’ or ‘groups’, [<editors>]) can also set (‘domainUsersCanEdit’, Boolean)

requesting_user_can_edit

if the requesting user can edit protected range

description

if the requesting user can edit protected range

start_addr

top-left address of the range

end_addr

bottom-right address of the range

range

Range in format A1:C5

worksheet

linked worksheet

cells

Get cells of this range

link the datarange so that all properties are synced right after setting them

Parameters:update – if the range should be synced to cloud on link

unlink the sheet so that all properties are not synced as it is changed

fetch(only_data=True)[source]

update the range data/properties from cloud

Warning

Currently only data is fetched not properties, so only_data wont work

Parameters:only_data – fetch only data
apply_format(cell=None, fields=None, cell_json=None)[source]

Change format of all cells in the range

Parameters:
  • cell – a model :class: Cell whose format will be applied to all cells
  • fields – comma seprated string of fields of cell to apply, refer to google api docs
  • cell_json – if not providing a cell object, provide a cell json. refer to google api docs
update_values(values=None)[source]

Update the worksheet with values of the cells in this range

Parameters:values – values as matrix, which has same size as the range
sort(basecolumnindex=0, sortorder='ASCENDING')[source]

sort the values in the datarange

Parameters:
  • basecolumnindex – Index of the base column in which sorting is to be done (Integer). The index here is the index of the column in range (first columen is 0).
  • sortorder – either “ASCENDING” or “DESCENDING” (String)
clear(fields='userEnteredValue')[source]

Clear values in this datarange.

Reference:

Parameters:fields – Comma separated list of field masks.
update_named_range()[source]

update the named range properties

update_protected_range(fields='*')[source]

update the protected range properties

update_borders(top=False, right=False, bottom=False, left=False, inner_horizontal=False, inner_vertical=False, style='NONE', width=1, red=0, green=0, blue=0)[source]

update borders for range

NB use style=’NONE’ to erase borders
default color is black
Parameters:
  • top – make a top border
  • right – make a right border
  • bottom – make a bottom border
  • left – make a left border
  • style – either ‘SOLID’, ‘DOTTED’, ‘DASHED’, ‘SOLID’, ‘SOLID_MEDIUM’, ‘SOLID_THICK’, ‘DOUBLE’ or ‘NONE’ (String).
  • width – border width (depreciated) (Integer).
  • red – 0-255 (Integer).
  • green – 0-255 (Integer).
  • blue – 0-255 (Integer).
merge_cells(merge_type='MERGE_ALL')[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)