import pygsheets

gc = pygsheets.authorize()

# Open spreadsheet and then workseet
sh = gc.open('my new ssheet')
wks = sh.sheet1

# Update a cell with value (just to let him know values is updated ;) )
wks.update_cell('A1', "Hey yank this numpy array")

# update the sheet with array
wks.update_cells('A2', my_nparray.to_list())

# share the sheet with your friend
sh.share("myFriend@gmail.com")

# You can open a spreadsheet by its title as it appears in Google Docs 
sh = gc.open("pygsheetTest")

# If you want to be specific, use a key
sht1 = gc.open_by_key('1mwA-NmvjDqd3A65c8hsxOpqdfdggPR0fgfg5nXRKScZAuM')

# Or,paste the entire url
sht2 = gc.open_by_url('https://docs.google.com/spreadsheets/d/1mwA...AuM/edit')

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

# or copy from another worksheet
wks = sh.add_worksheet("new sheet", src_worksheet=another_wks)

# delete this wroksheet
del_worksheet(wks)

# unshare the sheet
sh.remove_permissions("myNotSoFriend@gmail.com")

# 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.values(start=(1,1), end=(20,20), returnas='matrix')

# Get all values of sheet as 2d list of cells
cell_matrix = wks.all_values('cell')

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

# 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]

# append row to a table anywhere in worksheet
wks.append_row([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)

#set the values of a pandas dataframe to sheet
wks.set_dataframe(df,(1,1))

#you can also get the values of sheet as dataframe
df = wks.get_as_df(head=1)

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

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

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

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

# Or Update a range
cell_list = worksheet.range('A1:C7')
for cell in cell_list:
    cell.value = 'O_0'

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

# get neighbouring cells
c2 = c1.neighbour('topright')

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

#write notes on cell
c1.note = "yo mom"
c.update()