After logging in to your google account, visit this URL https://console.cloud.google.com/apis/
GO to APIs overview
(Create a new project if not created.)
Click Credentials ( at left side menu )
Create credentials > Service Account
Service Account Details ( enter details )
Select the service account just created.
Visit Keys tab at top
Add Key > Create a New Key ( download the JSON file Save it in your computer and note the path. )
Service account email address (select service account details for edit ) to be given share access to the directory or to the file or directory to work with. Don't forget to enable Google Drive API for your project.
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
Let us work on first sheet.
wk1 = sh[0] # select the first worksheet
print('Rows: ',wk1.rows) # Rows : 1000
print('Columns: ',wk1.cols) # Columns: 26
Collect data from cell and update the same
print(wk1.get_value('B2')) # data at cell B2
print(wk1.cell((2,2)).value) # data at row_number=2 col_number=2 ( B2)
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
The third parameter parse if False then the data is typed and not evaluated and placed.
Update the parse to True again and change the value at A1. The SUM will be reflected 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
Getting all row values and all column values as List. If we make the include_trailing_empty=True then all blanks after the last value will be included.
#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])