Managing Google sheet from Python

Python Google sheets

Authorization through Google drive API to connect to google sheets from Python pygsheets library

Authorizing pygsheets

Read more about Authorizing pygsheets here .

Google Sheets API

After logging in to your google account, visit this URL
GO to APIs overview
(Create a new project if not created.)
Click Credentials ( at left side menu )
Google API credentials
Create credentials > Service Account
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. )
download Json file
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.

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 ='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
Adding data to google sheet

Information about the google sheet

sh ='pythontest') # Open the google sheet phthontest
print( #  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)
Print all values of the worksheet
Updating values at cell
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.

updating with parse=False in google sheet
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
Deleting data over a range in google sheet
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.
#['12', '34', 'ab', 'cd', '55', 'cv']
#['', 'ab', 'Al', '45']
Getting rows and columns as list from google sheet

Inserting rows and columns
#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])
Inserting row and column in google sheet

Using Google sheets data as option of Combobox

Combobox Options from Python Google sheets
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
Pandas DataFrame to Google sheet by set_dataframe get_as_df() append_table() Treeview from Google sheet data
Collecting kyeword suggestions from Google and storing in Google sheets


* indicates required
Subscribe to plus2net

    Post your comments , suggestion , error , requirements etc here

    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2022 All rights reserved worldwide Privacy Policy Disclaimer