import pygsheets
c = pygsheets.authorize()
sh = c.open('spreadsheet')

# create a new sheet with 50 rows and 60 colums
wks = sh.add_worksheet("new sheet",rows=50,cols=60)

# create a new sheet with 50 rows and 60 colums at the begin of worksheets
wks = sh.add_worksheet("new sheet",rows=50,cols=60,index=0)

# or copy from another worksheet
wks = sh.add_worksheet("new sheet", src_worksheet='<other worksheet instance>')

# delete this wroksheet
sh.del_worksheet(wks)

# unshare the sheet
sh.remove_permissions("[email protected]")

import pygsheets
c = pygsheets.authorize()
sh = c.open('spreadsheet')

# Select worksheet by id, index, title.
wks = sh.worksheet_by_title("my test sheet")

# By any property
wks = sh.worksheet('index', 0)

# Get a list of all worksheets
wks_list = sh.worksheets()

# Or just
wks = sh[0]

# Get values as 2d array('matrix') which can easily be converted to an numpy aray or as 'cell' list
values_mat = wks.get_values(start=(1,1), end=(20,20), returnas='matrix')

# Get values of - rows A1 to B10, column C, 1st row, 10th row
wks.get_values_batch(['A1:B10', 'C', '1', (10, None)])

# Get all values of sheet as 2d list of cells
cell_matrix = wks.get_all_values(returnas='matrix')

# update a range of values with a cell list or matrix
wks.update_values(crange='A1:E10', values=values_mat)

# update multiple ranges with bath update
wks.update_values_batch(['A1:A2', 'B1:B2'], [[[1],[2]], [[3],[4]]])

# Insert 2 rows after 20th row and fill with values
wks.insert_rows(row=20, number=2, values=values_list)

# resize by changing rows and colums
wks.rows=30

# use the worksheet as a csv
for row in wks:
    print(row)

# get values by indexes
 A1_value = wks[0][0]

# clear all values
wks.clear()

# Search for a table in the worksheet and append a row to it
wks.append_table(values=[1,2,3,4])

# export a worksheet as csv
wks.export(pygsheets.ExportType.CSV)

# Find/Replace cells with string value
cell_list = worksheet.find("query string")

# Find/Replace cells with regexp
filter_re = re.compile(r'(small|big) house')
cell_list = worksheet.find(filter_re, searchByRegex=True)
cell_list = worksheet.replace(filter_re, 'some house', searchByRegex=True)

# Move a worksheet in the same spreadsheet (update index)
wks.index = 2 # index start at 1 , not 0

# Update title
wks.title = "NewTitle"

# Update hidden state
wks.hidden = False

# working with named ranges
wks.create_named_range('A1', 'A10', 'prices')
wks.get_named_range('prices')
wks.get_named_ranges()  # will return a list of DataRange objects
wks.delete_named_range('prices')

# Plot a chart/graph
wks.add_chart(('A1', 'A6'), [('B1', 'B6')], 'Health Trend')

# create drop-downs
wks.set_data_validation(start='C4', end='E7', condition_type='NUMBER_BETWEEN', condition_values=[2,10], strict=True, inputMessage="inut between 2 and 10")

c1 = Cell('A1',"hello")  # create a unlinked cell
c1 = worksheet.cell('A1')  # creates a linked cell whose changes syncs instantanously
cl.value  # Getting cell value
c1.value_unformatted #Getting cell unformatted value
c1.formula # Getting cell formula if any
c1.note # any notes on the cell
c1.address # address object with cell position

cell_list = worksheet.range('A1:C7')  # get a range of cells 
cell_list = worksheet.col(5, returnas='cell')  # return all cells in 5th column(E)

# using linked cells
c1 = worksheet.cell('B1') # created from worksheet, so linked cell
c1.col = 5  # Now c1 correponds to E1
c1.value = "hoho"  # will change the value of E1

# Or onliner
worksheet.update_value('B1', 'hehe')

# get a range of cells
cell_list = worksheet.range('A1:C7')
cell_list = worksheet.get_values(start='A1', end='C7', returnas='cells')
cell_list = worksheet.get_row(2, returnas='cells')


# add formula
c1.formula = 'A1+C2'
c1.formula # '=A1+C2'

# get neighbouring cells
c2 = c1.neighbour('topright') # you can also specify relative position as tuple eg (1,1)

# set cell format
c1.set_number_format(pygsheets.FormatType.NUMBER, '00.0000')

# write notes on cell
c1.note = "yo mom"

# set cell color
c1.color = (1.0, 1.0, 1.0, 1.0) # Red, Green, Blue, Alpha

# set text format
c1.text_format['fontSize'] = 14
c1.set_text_format('bold', True)

# sync the changes
 c1.update()

# you can unlink a cell and set all required properties and then link it
# So yu could create a model cell and update multiple sheets
c.unlink()
c.note = "offine note"
c.link(wks1, True)
c.link(wks2, True)

# Getting a Range object
rng = wks.get_values('A1', 'C5', returnas='range')
rng.start_addr = 'A' # make the range unbounded on rows <Datarange Sheet1!A:B>
drange.end_addr = None # make the range unbounded on both axes <Datarange Sheet1>

# Named ranges
rng.name = 'pricesRange'  # will make this range a named range
rng = wks.get_named_ranges('commodityCount') # directly get a named range
rng.name = ''  # will delete this named range

#Protected ranges
rng.protected = True
rng.editors = ('users', '[email protected]')

# Setting Format
 # first create a model cell with required properties
model_cell = Cell('A1')
model_cell.color = (1.0,0,1.0,1.0) # rose color cell
model_cell.format = (pygsheets.FormatType.PERCENT, '')

 # Setting format to multiple cells in one go
rng.apply_format(model_cell)  # will make all cell in this range rose color and percent format
# Or if you just want to apply format, you can skip fetching data while creating datarange
Datarange('A1','A10', worksheet=wks).apply_format(model_cell)

# get cells in range
cell = rng[0][1]