Managing Google sheet from Python

Authorizing pygsheets

Google Sheets API

Connecting to google drive

import pygsheets
path='G:\\My drive\\testing\\google-sheet\\creds1.json'
gc = pygsheets.authorize(service_account_file=path)

Opening the file and adding data

sh = gc.open('pythontest') # Open the google sheet phthontest
wks = sh[0] # select the first sheet 
wks.update_col(2,['ab','cd','ef']) # add list to 2nd column

Information about the google sheet

sh = gc.open('pythontest') # Open the google sheet phthontest
print(sh.id) #  id of the sheet 
print(sh.title) # pythontext  output as title
print(sh.url) # address of the file to open in browser
print(sh.updated) # last updated date and time of the file

wk1 = sh[0] # select the first worksheet 
print('Rows: ',wk1.rows) # Rows : 1000
print('Columns: ',wk1.cols) # Columns: 26 

print(wk1.get_value('B2')) # data at cell B2 
print(wk1.cell((2,2)).value) # data at row_number=2 col_number=2 ( B2)

print(wk1.get_all_values(),include_tailing_empty=False)

wk1.update_value('A1',5)      #  Store  5 at A1
wk1.update_value('A2',10)     #  Store 10 at A2
wk1.update_value('A3','=A1+A2',True) # Add data at A1 and A2 and store at A3

wk1.update_value('A1',20)  #  Update 20 at A1

Delete value using clear

wk1.clear('B3','C5') # clear data from grid B3 to C5

wk1.clear('*','*') # clear all cells

print(wk1.get_row(2,include_tailing_empty=False))
#['12', '34', 'ab', 'cd', '55', 'cv']
print(wk1.get_col(3,include_tailing_empty=False))
#['', 'ab', 'Al', '45']

#insert after row 2 , one row with these values 
wk1.insert_rows(row =2, number = 1, values =['BB', 40,'CC',60,'DD'])
#insert after col 3 ,one column with the values
wk1.insert_cols(col =3, number = 1, values =['','kk','tt',34, 40])

Using Google sheets data as option of Combobox

We can collect data from google sheet and add as options for a Combobox by using pygsheets library and google drive API.
Options of Combobox from Google sheet data»

Data Transfer from Google sheets to MySQL and vice versa

By using Pandas DataFrame we can transfer data from MySQL database table to Google sheets and from Google sheets to MySQL.
Data Transfer between Google sheet and MySQL»
Data Transfer between Google sheet and SQLite»